내장 함수 개요
벤더에서 제공하는 내장 함수와 사용자가 정의할 수 있는 함수로 구분.
벤더마다 차이가 있다.
내장 함수는 다시 함수 입력 값이 단일행 값이 입력되는 단일행 함수와 여러 행의 값이 입력되는 다중행 함수로 나눌 수 있다. 다중행 함수는 집계함수, 그룹 함수, 윈도우 함수로 나눌 수 있다.
이 장에서는 단일행 함수만 다룬다.
함수는 입력되는 값이 아무리 많아도 출력은 하나만 되는 M:1 관계라는 중요한 특징을 갖고 있다. 단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다. 다중행 함수의 경우 여러 레코드의 값들을 입력 인수로 사용한다.
함수명(칼럼이나 표현식 [, Arg1, Arg2, ...])처리하는 데이터의 형식에 따라서 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수로 나눌 수 있다.
단일행 함수의 종류
문자형 함수(문자를 입력하면 문자나 숫자 값을 반환한다.)
LOWER, UPPER, ASCII, CHR, CONCAT, SUBSTR, LENGTH, LEN, LTRIM, RTRIM, TRIM
숫자형 함수(숫자를 입력하면 숫자 값을 반환한다.)
ABS, SIGN, MOD, CEIL, FLOOR, ROUND, TRUNC, SIN, COS, TAN, EXP, POWER, SQRT, LOG, LN
날짜형 함수(DATE 타입의 값을 연산한다.)
SYSDATE, EXTRACT, TO_NUMBER(TO_CHAR(d, 'YYYY'|'MM'|'DD')
변환형 함수(문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.)
CAST, TO_NUMBER, TO_CHAR, TO_DATE
NULL 관련 함수(NULL을 처리하기 위한 함수)
NVL, NULLIF, COALESCE
단일행 함수의 중요한 특징은 다음과 같다.
- SELECT, WHERE, ORDER BY 절에 사용 가능
- 각 행들에 대해 개별적으로 작용해 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.
- 여러 인자를 입력해도 단 하나의 결과만 리턴한다.
- 함수의 인자로 상수 변수 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수 있다.
- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다.
문자형 함수
문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수다. 몇몇 문자형 함수는 결과를 숫자로 리턴하기도 한다.
SELECT LOWER('SQL Expert') AS LOWER
, UPPER('SQL Expert') AS UPPER
, ASCII('A') AS ASCII
, CHR(65) AS CHR
, CONCAT('RDBMS', ' SQL') AS CONCAT
, SUBSTR('SQL Expert', 5, 3) AS SUBSTR
, LENGTH('SQL Expert') AS LENGTH
, LTRIM('xxxYYZZxYZ', 'x') AS LTRIM
, RTRIM('XXYYzzYYzz', 'z') AS RTRIM
, TRIM('x' FROM 'xxYYZZxYzxx') AS TRIM
FROM DUAL;
DUAL 테이블의 특성은 다음과 같다.
- 사용자가 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
- SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
- DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행 1건을 포함하고 있다.
함수 RTRIM은 공백 제거 및 CHAR와 VARCHAR 데이터 유형을 비교할 때 유용하게 사용된다.
함수는 특별한 제약조건이 없다면 여러 개 중첩해 사용할 수 있다.
함수3(함수2 (함수1 (칼럼이나 표현식 [,Arg1]) [, Arg2]) [,Arg3])
SELECT CONCAT(CONCAT('안녕', '하'), '세요') AS CONCAT FROM DUAL;
숫자형 함수
숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수
SELECT ABS(-15), SIGN(-20), SIGN(0), SIGN(+20), MOD(7,3)
, CEIL(38.123), FLOOR(38.123), ROUND(38.5235, 1), TRUNC(38.5235, 2)
FROM DUAL;
SELECT SIN(0), COS(0), TAN(0), EXP(2)
, POWER(2, 3), SQRT(4), LOG(10, 100), LN(7.3890561)
FROM DUAL;
날짜형 함수
날짜형 함수느 DATE 타입의 값을 연산하는 함수다.
Oracle의 TO_NUMBER(TO_CHAR()) 함수는 변환성 함수로 구분할 수도 있으나, SQL Server의 YEAR, MONTH, DAY 함수와 매핑하기 위해 날짜형 함수에서 설명한다.
날짜는 여러 가지 형식으로 출력되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다.
SELECT SYSDATE
, EXTRACT(YEAR FROM SYSDATE) AS 년도
, EXTRACT(MONTH FROM SYSDATE) AS 월
, EXTRACT(DAY FROM SYSDATE) AS 일
, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) AS 년도
, TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) AS 월
, TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) AS 일
, SYSDATE + 1 AS 더하기
, SYSDATE - 1 AS 뺴기
, SYSDATE - (SYSDATE - 365) AS 날짜수
FROM DUAL;
첫 행은 지금의 시간을 구하는 행이다.
그 다음 년도, 월, 일은 각각 년, 월, 일을 구하는 방식입니다.
더하기와 빼기는 날짜에 숫자를 더하고 빼기를 해서 날짜를 구하는 행이다.
날짜수는 지금 날짜에서 1년 전의 날짜를 빼서 총 365일 차이가 나는 걸 알 수 있다.
변환형 함수
변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우 사용된다. 변환형 함수는 크게 두가지 방식이 있다.
암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있다. 자동으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.
명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수는 다음과 같다.
변환형 함수를 사용해 출력 형식을 지정할 때, 숫자형과 날짜형의 경우 상당히 많은 포맷이 벤더별로 제공된다.
벤더별 데이터 유형과 함께 데이터 출력의 포맷 부분은 벤더의 고유 항목이 많으므로 메뉴얼을 참고하기 바란다.
SELECT SYSDATE
, TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS 날짜
, TO_CHAR(SYSDATE, 'YYYY. MON, DAY') AS 문자형
, TO_CHAR(123456789 / 1200, '$999,999,999.99') AS 환율반영달러
, TO_CHAR(123456789, 'L999,999,999') AS 원화
, TO_NUMBER('123', '999') + TO_NUMBER('456', '999') AS 우편번호함
FROM DUAL;
첫 행은 지금의 시간을 구하는 행이다.
그 날짜와 문자형 행은 지금의 시간을 가공한 행이다.
환율반영달러와 원화는 123456789원에 대해 달러와 원화에 대해 나타낸 것이다.
우편번호함은 두 개의 문자열를 숫자형으로 변경해서 더한 것이다.
CASE 표현
IF-THEN-ELSE 논리와 유사한 방식이다.
SQL의 비교 연산 기능을 보완하는 역할. 함수와 같은 성격을 갖고 있으며, Oracle의 Decode 함수와 같은 기능을 하므로 단일행 내장 함수에서 같이 설명한다.
여기서부터 예시로 사용될 테이블은 인터넷 상에서 돌아다니는 EMP 테이블과 DEPT 테이블입니다.
SELECT * FROM EMP;
SELECT * FROM DEPT;
IF SAL > 2000
THEN SALARY = SAL
ELSE SALARY = 2000위 예제와 같은 기능을 하는 CASE 표현은 다음과 같다.
SELECT ENAME
, CASE
WHEN SAL > 2000 THEN SAL
ELSE 2000
END AS SALARY
FROM EMP;
CASE 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있다. Oracle은 DECODE 함수를 사용할 수 있다.
CASE SIMPLE_CASE EXPRESSION 조건 [ELSE 디폴트값] END
SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건 내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE절을 수행한다.
CASE SEARCHED_CASE_EXPRESSION 조건 [ELSE 디폴트값] END
SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건 내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE 절을 수행한다.
DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ..., 디폴트값])
Oracle에서만 사용되는 함수로, 표현식 값이 기준값1이면 값 1을 출력, 기준값2이면 값 2를 출력한다. 기준값이 없으면 디폴트 값을 출력한다. CASE 표현의 SIMPLE_ CASE_EXPRESSION 조건과 동일하다.
IF-THEN-ELSE 논리를 구현하는 CASE Expressions은 Simple Case Expression과 Searched Case Expression 두 가지 표현법 중 하나를 선택해서 사용하게 된다.
CASE
SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSSION 조건
[ELSE 디폴트값]
END첫 번째 SIMPLE_CASE_EXPRESSION은 CASE 다음 바로 조건에 사용되는 칼럼이나 표현식이다. 다음 WHEN절에서 앞에서 정의한 칼럼이나 표현식과 같은지와 다른지를 판단하는 문장으로 EQUI(=) 조건만 사용한다면 SEARCHED_CASEEXPRESSION보다 간단하게 사용할 수 있는 장점이 있다.
CASE
EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR
ELSE DEFAULT_EXPR
ENDSELECT LOC
, CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'DALLAS' THEN 'CENTER'
ELSE 'ETC'
END AS AREA
FROM DEPT;
두 번째 SEARCHED_CASE_EXPRESSION은 CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN절에서 EQUI(=) 조건 포함 여러 조건(>, >=, <. <=)을 이용한 조건절을 사용할 수 있으므로 SIMPLE_CASE_ExPRESSION보다 훨씬 다양한 조건을 적용할 수 있는 장점이 있다.
CASE
WHEN CONDITION THEN RETURN_EXPR
ELSE DEFAULT_EXPR
END예제
SELECT ENAME
, CASE
WHEN SAL >= 3000 THEN 'HIGH'
WHEN SAL >= 1000 THEN 'MID'
ELSE 'LOW'
END AS GRADE
FROM EMP;
CASE 표현은 함수의 성질을 갖고 있으므로 다른 함수처럼 중첩해 사용할 수 있다.
마지막으로 DECODE 함수이다.
SELECT ENAME
,DEPTNO
,DECODE(DEPTNO, 10, '10번', NULL) AS DECODE
FROM EMP;
NULL 함수
SELECT NVL(NULL, 'THIS IS NULL') AS NVL
, NULLIF('HI', 'HI') AS NULLIF1
, NULLIF('HI', 'hi') AS NULLIF2
, COALESCE(1, 2, 3) AS COALESCE1
, COALESCE(NULL, NULL, 3) AS COALESCE2
, COALESCE(1, NULL, 3) AS COALESCE3
FROM DUAL;
