2-1-3. 함수

1. 내장 함수 개요

종류	내용	함수의 예
문자형	문자를 입력, 문자나 숫자 값을 반환	LOWER, UPPER, ASCII, CHR/CHAR, CONCAT, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM
숫자형	숫자 입력, 숫자 반환	ABS, SIGN, MOD, CEIL/CEILING, FLOOR, ROUND, TRUNG, SIN, COS, TAN, EXP, POWER, SQRT, LOG, LN
날짜형	DATE 타입 값 연산	SYSDATE/GENDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(D, 'YYYY'\|'MM'\|'DD)) / YEAR\|MONTH\|DAY
변환형	데이터 타입을 변환한다	(CAST, TO_NUMBER, TO_CHAR, TO_DATE)/(CAST, CONVERT)
NULL	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

- (A / B) -> A: 오라클 함수, B: SQL Server 함수

단일행 함수의 중요한 특징은 다음과 같다.

- SELECT, WHERE, ORDER BY 절에 사용 가능하다.

- 각 행들에 대해 개별적으로 작용해 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.

- 여러 인자(Argument)를 입력해도, 단 하나의 결과만 리턴한다.

- 함수의 인자로 상수/변수/표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있으나 여러 개의 인수를 가질 수도 있다.

- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는, 함수의 중첩이 가능하다.

2. 문자형 함수

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳 문자를 모두 소문자로 바꾸어 준다.
UPPER(문자열)	문자열의 알파벳 문자를 모두 대문자로 바꾸어 준다.
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꾸어준다.
CHR/CHAR(ASCII 번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT(문자열1, 문자열2)	문자열1, 문자열2를 연결한다.
SUBSTR/SUBSTRING(문자열, m[,n])	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생략되면 마지막 문자까지이다.
LENGTH/LEN(문자열)	문자열의 개수(길이)를 숫자값으로 돌려준다.
LTRIM (문자열 [,지정문자]) / LTRIM(문자열)	문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다. 이때, 지정 문자를 생략하면 공백(' ')값이 디폴트. SQL Server에서는 지정문자 설정 불가(공백만 제거 가능)
RTRIM(문자열 [,지정문자]) / RTRIM(문자열)	문자열의 마지막 문자부터 확인. 내용은 위와 동일
TRIM([leading\|trailing\|both] 지정문자 FROM 문자열) / TRIM (지정문자 FROM 문자열)	문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자를 제거한다. SQL Server에서는 both가 디폴트, 양쪽에 있는 지정 문자만 제거 가능하다.

문자형 함수들이 적용됐을 때 리턴되는 값은 아래와 같다.

문자형 함수 사용	결과 값 및 설명
LOWER('SQL Expert')	'sql expert'
UPPER('SQL Expert')	'SQL EXPERT'
ASCII('A')	65
CHR(65) / CHAR(65)	'A'
CONCAT('RDBMS',' SQL')	'RDBMS SQL'
'RDBMS' \|\| ' SQL' / 'RDBMS'+' SQL'	'RDBMS SQL'
SUBSTR('SQL Expert', 5, 3) / SUBSTRING('SQL Expert', 5, 3)	'Exp'
LENGTH('SQL Expert') / LEN('SQL Expert')	10
LTRIM('xxxYYZZxYZ', 'x')	'YYZZxYZ'
RTRIM('XXYYzzXYzz','z')	'XXYYzzXY'
RTRIM('ABC ')	'ABC'
TRIM('x' FROM 'xxYYZZxYZxx')	'YYZZxYZ'

3. 숫자형 함수

숫자형 함수	함수 설명
ABS(숫자)	숫자의 절댓값을 반환한다
SIGN(숫자)	숫자가 양수인지, 음수인지 0인지 구별한다. 양수면 1, 0이면 0, 음수면 -1
MOD(숫자1, 숫자2)	숫자 1을 숫자2로 나누어 나머지 값을 리턴한다. MOD 함수는 % 연산자로도 대체할 수 있다.
CEIL/CEILING(숫자)	숫자보다 크거나 같은 최소 정수를 리턴한다(올림)
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수를 리턴한다(내림)
ROUND(숫자 [, m])	숫자를 소수점 m자리에서 반올림해 리턴한다. m이 생략되면 디폴트 값은 0이다.(정수 반환)
TRUNC(숫자 [, m])	숫자를 소수 m자리에서 잘라서 버린다. m이 생략되면 디폴트 값은 0이다. SQL Server에서 TRUNC 함수는 제공하지 않는다.
SIN, COS, TAN, ...	숫자의 삼각함수 값을 리턴한다.
EXP(숫자)	숫자의 Exponential 값을 리턴한다.
POWER(숫자1, 숫자2)	숫자1의 숫자2 거듭제곱 값을 리턴한다.
SQRT(숫자)	숫자의 제곱근 값을 리턴한다
LOG(숫자1, 숫자2)/ LOG(숫자2, 숫자1)	숫자1을 밑수로 하는 숫자2의 로그 값을 리턴한다. Oracle, SQL Server에서 위치가 다르므로 유의하자.
LN(숫자)	숫자의 자연 로그 값을 리턴한다. SQL Server에서는 LN 함수를 제공하지 않는다.

위의 숫자형 함수들이 적용되었을 때 리턴되는 값은 아래와 같다.

숫자형 함수 사용	결과 값 및 설명
ABS(-15)	15
SIGN(-20) SIGN(0) SIGN(20)	-1 0 1
MOD(7,3) / 7%3	1
CEIL(39.123) / CEILING(39.123) CEILING(-39.123)	40 -39
FLOOR(38.123) FLOOR(-38.123)	38 -39
ROUND(38.5235, 3) ROUND(38.5235, 1) ROUND(38.5235, 0) ROUND(38.5235)	38.524 38.5 39 39
TRUNC(38.5235, 3) TRUNC(38.5235, 1) TRUNC(38.5235, 0) TRUNC(38.5235)	38.523 38.5 38 38
SIN(0) COS(0) TAN(0)	0 1 0
EXP(2)	7.3890561
SQRT(4)	2
LOG(10, 100) - Oracle/ LOG(100, 10) - SQL Server	2
LN(7.3890561)	2

4. 날짜형 함수

날싸형 함수	함수 설명
SYSDATE/ GETDATE()	현재 날짜와 시각을 출력한다.
EXTRACT('YEAR'\|'MONTH'\|'DAY' from d)/ DATEPART('YEAR'\|'MONTH'\|'DAY', d)	날짜 데이터에서 연월일 데이터를 출력할 수 있다. 시분초도 가능하다.
TO_NUMBER(TO_CHAR(d, 'YYYY'))/ YEAR(d)	날짜 데이터에서 '연' 데이터를 출력한다.
TO_NUMBER(TO_CHAR(d, 'MM'))/ MONTH(d)	날짜 데이터에서 '월' 데이터를 출력한다.
TO_NUMBER(TO_CHAR(d, 'DD'))/ DAY(d)	날짜 데이터에서 '일' 데이터를 출력한다.

- 위의 TO_NUMBER를 빼면 '문자형'으로 출력된다.

- 또한, 산술 연산자로도 계산이 가능하다.

연산	결과	설명
날짜 + 숫자	날짜	숫자만큼의 일(day) 수를 날짜에 더한다.
날짜 - 숫자	날짜	숫자만큼의 일(day) 수를 날짜에서 뺀다.
날짜1 - 날짜2	날짜 수	두 날짜 사이의 일(DAY) 수를 출력한다.
날짜 + 숫자/24	날짜	시간을 날짜에 더한다. 이런식으로 분, 초도 가능하다.

5. 변환형 함수

- 변환형 함수는 크게 두 가지 방식이 있다.

종류	설명
명시적(Explicit)	데이터 변환형 함수를 써 변환하도록 '명시'해 주는 경우
암시적(Implicit)	데이터베이스가 '자동'으로 데이터 유형을 변환해 계산하는 경우

- 암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으므로, 명시적인 변환 방법을 사용하는 것이 바람직하다.

명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수는 다음과 같다.

변환형 함수	함수 설명
TO_NUMBER(문자열) / CAST (expression AS data_type [(length)])	숫자로 변환 가능한 문자열을 숫자로 변환한다. expression을 목표 데이터 유형으로 변환한다.
TO_CHAR (숫자 \| 날짜 [,FORMAT]) / CONVERT(data_type [(length)], expression [,style])	숫자-날짜를 주어진 FORMAT 형태인 '문자열' 타입으로 변환. expression을 주어진 style 형태인 목표 데이터 유형으로 변환.
TO_DATE(문자열 [, FORMAT]) / CONVERT(data_type [(length)], expression [,style])	문자열을 주어진 FORMAT 형태인 날짜 타입으로 변환. expression을 주어진 style 형태인 목표 데이터 유형으로 변환.

- 명시적 변환형 함수의 경우 Oracle과 SQL Server가 아예 다르므로, 인지해 두어야 한다.

6. CASE 표현

- CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해 SQL의 비교 연산 기능을 보완하는 역할을 한다.

아래에서, DECODE는 Oracle에서만 사용된다.

CASE 표현	함수 설명
CASE SIMPLE_CASE_EXPRESSION 조건 [ELSE 디폴트값] END	조건이 맞으면, 조건 내의 TEHN 절을 수행하고, 조건이 맞지 않으면 ELSE 절을 수행한다.
CASE SEARCHED_CASE_EXPRESSION 조건 [ELSE 디폴트값] END	조건이 맞으면, 조건 내의 THEN 절을 수행하고, 조건이 맞이 않으면 ELSE 절을 수행한다.
DECODE(표현식, 기준값1, 값1, [,기준값2, 값2, ..., 디폴트값])	표현식 값이 기준값1이면 값1을 출력하고, 기준값2면 값2를 출력한다. SIMPLE_CASE_EXPRESSION 조건과 동일하다. Oracle에서만 사용

위의 표만 봤을 때 이해가 안되는게 정상이다.

아래의 표현법 풀이를 보면서 이해해보자.

SIMPLE_CASE_EXPRESSION

- SIMPLE_CASE_EXPRESSION은 CASE 다음 바로 조건에 사용되는 칼럼이나 표현식이다. 다음 WHEN 절에서 앞에 정의한 칼럼이나 표현식과 같은지, 다른지를 판단하는 문장으로, EQUI 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하다.

SELECT LOC
 ,CASE LOC
    	WHEN 'NEW YORK' THEN 'EAST'
        WHEN 'BOSTON' THEN 'EAST'
        WHEN 'CHICAGO' THEN 'CENTER'
        WHEN 'DALLAS' THEN 'WEST'
        ELSE 'ETC'
    END AS AREA
FROM DEPT;

[실행 결과]

LOC	AREA
NEW YORK	EAST
DALLAS	CENTER
SEOUL	ETC

SEARCHED_CASE_EXPRESSION

- CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN절에서 EQUI 조건 포함, 여러 조건(<, >, <=, >=)을 이용한 조건절을 사용할 수 있다.

SELECT ENAME
	, CASE
    	WHEN SAL >= 3000 THEN 'HIGH'
        WHEN SAL >= 1000 THEN 'MID
        ELSE 'LOW'
	END AS SALARY_GRADE
FROM EMP;