SQL에서는 수학의 함수 개념을 사용한다. 특정 값이나 열의 값을 받아서 입력하여 계산한 결과를 보여준다. SQL에서 함수는 DBMS가 제공하는 내장함수 built-in function와 정의함수 user-defined function로 나뉜다. 여기서는 내장함수만을 다루도록 하겠다.
숫자 함수
-78과 +78의 절댓값을 구한다고 해보자. SQL로 구현한 코드는 다음과 같다.
SELECT ABS(-78), ABS(+78)
FROM Dual;결과값은 78, 78이다.
※위의 dual 테이블은 함수계산을 위한 '가상의 테이블'이다.
다음은 round 함수를 사용한 코드를 보자.
SELECT ROUND(4.875, 1)
FROM Dual;4.875의 소수 첫째 자리까지 반올림을 하는 코드다. 결과값으로 4.9가 된다. 참고로 소수점 이상으로 반올림하고 싶다면 두 번째 매개변수에 마이너스를 주면 된다.
문자함수
replace함수를 써서 코드로 작성해보겠다. 문제는 다음과 같다. 도서제목에 야구가 포함된 도서의 제목을 농구로 변경한 후 보여라.
SELECT bookid, REPLACE(bookname, '야구', '농구') bookname, publisher, price
FROM Book;결과값으로 bookname 열의 데이터에서 야구가 농구로 바뀐 채 출력된다.
날짜, 시간 함수
오라클 기준 데이터타입은 문자열 말고도 date 타입, 즉 날짜와 시간에 관련한 데이터 타입이 있다.
여기서 눈 여겨 봐야 할 함수는 to_date와 to_char가 있다. to_char 함수는 date 타입 데이터를 char 타입으로 바꾸어 준다. to_date는 그 반대다.
잘 보면 달을 늘릴 수는 있어도 일자를 늘릴 순 없는 것이 보일 것이다. 그럴 땐 date 타입 값에 원하는 날짜만큼 값을 추가하면 된다. 예를 들어 한 서점에서 주문일로부터 10일 후 매출이 확정된다고 가정해보자.
확정일자는 다음과 같이 표시된다.
SELECT orderdate+10 "확정"
FROM Orders
--데이터 타입 변경 함수의 예
SELECT TO_CHAR(orderdate, 'yyyy-mm-dd dy') "주문일"
FROM Orders
WHERE orderdate=TO_DATE('20140707', 'yyyymmdd')to_date와 to_char함수의 사용의 예는 위와 같다.
NULL 값
null은 0이나 공백과는 다른 특정한 값이다. 이는 비교연산이 불가능하며 다른 연산을 수행해도 마찬가지로 null이 반환된다. 다만 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되고 count 함수의 결과는 0이 된다. 또한 count함수에서 *로 모든 행을 집계할 시 null을 포함한 값이 반환된다.
해당 행이 null인지 확인하기 위해서는 IS NULL, IS NOT NULL 연산자를 이용한다.
만약 받는 값이 null이고 다른 값으로 대치하고 싶다면 NVM함수를 사용한다.
NVL(속성, 값) /* 속성 값이 NULL이면 '값'으로 대치한다 */
SELECT name "이름", NVL(phone, '연락처없음') "전화번호"
FROM Customer;
뷰 view
하나 이상의 테이블을 합해서 만든 가상의 테이블이다. 작성하는 데에 오래 걸리는 복잡한 질의를 미리 작성해놓을 수 있다. 또한 보안성을 중시해 민감한 데이터를 빼고 만들 수 있으며, 필요한 정보만을 다룬 일반적인 독립테이블이기에 마음대로 사용할 수 있다.
뷰의 특징으로는 원본 데이터 값이 수정됨에 따라 같이 변하는 것이 있다. 또 인덱스의 독립적인 생성이 어렵다. 이는 인덱스가 원본 테이블에 저장되기 때문이다. 물론 삽입 삭제 갱신 연산에 많은 제약 또한 있다.
뷰의 생성
CREATE VIEW 뷰이름 [(열이름 [ ,...n ])]
AS SELECT 문
//뷰의 예1
CREATE VIEW vw_Customer
AS SELECT *
FROM Customer
WHERE address LIKE '%대한민국%';
//뷰의 예2
CREATE VIEW vw_Orders (orderid, custid, name, bookid, bookname, saleprice, orderdate)
AS SELECT od.orderid, od.custid, cs.name,
od.bookid, bk.bookname, od.saleprice, od.orderdate
FROM Orders od, Customer cs, Book bk
WHERE od.custid =cs.custid AND od.bookid =bk.bookid뷰의 예2에서 열이름까지 정의한 것은 두 테이블에서 조인한 속성을 가져오기 때문이다.
뷰의 수정
CREATE OR REPLACE VIEW 뷰이름 [(열이름 [ ,...n ])]
AS SELECT 문
//뷰 수정의 예
CREATE OR REPLACE VIEW vw_Customer (custid, name, address)
AS SELECT custid, name, address
FROM Customer
WHERE address LIKE '%영국%'뷰는 alter문 사용이 불가하다. 다만 or replace view 키워드로 재생성하는 것은 가능하다.
뷰의 삭제
DROP VIEW 뷰이름 [ ,...n ]
//삭제의 예
DROP VIEW vw_Customer;
인덱스 index
책의 차례나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터구조다.
인덱스의 특징은 다음과 같다.
- 테이블에서 한 개 이상의 속성을 이용하여 생성
- 빠른 검색과 함께 효율적인 레코드 접근이 가능
- 저장된 값들은 테이블의 부분집합
- 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요
- 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지
인덱스에서는 여러 종류가 있지만 b-tree를 가장 기본적으로 사용한다.
이런 인덱스를 생성할 때 주의사항이 있다.
- 조인이나 WHERE 절에 자주 사용되는 속성이어야 한다.
- 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.
- 속성이 가공되는 경우 사용하지 않는 것을 권장한다.
인덱스 생성
CREATE [REVERSE]┃[UNIQUE] [BITMAP] INDEX 인덱스이름]
ON 테이블이름 (컬럼 [ASC┃DESC] [{, 컬럼 [ASC | DESC]} …]);
//인덱스 생성의 예
CREATE INDEX ix_Book ON Book (bookname)
인덱스 재구성 및 삭제
인덱스 데이터의 삽입삭제가 빈번해지면 인덱스 구성이 비효율적이 된다. 따라서 수정하지 않고 재생성하는 것이 효율적일 경우 재구성할 수 있다.
인덱스 재구성은 alter index 키워드를 사용한다.
ALTER [REVERSE] [UNIQUE] INDEX 인덱스이름
[ON {ONLY} 테이블이름 {컬럼이름 [{, 컬럼이름 } …])] REBUILD;
//예시, 인덱스 ix_book을 재구성하는 것
ALTER INDEX ix_Book REBUILD;
//삭제
DROP INDEX 인덱스이름