[SQLD 과목2 1장-6절] 함수 (Function)

1. 내장 함수(BUILT-IN FUNCTION) 개요

함수는 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자 정의 함수(User Defined Function)로 나눌 수 있다. 본 절에서는 각 벤더에서 제공하는 DB를 설치하면 기본적으로 제공되는 SQL 내장 함수에 대해 설명한다.

내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다. 다중행 함수는 다시 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 나눌 수 있다. 본 절에서는 단일행 함수에 대해서만 설명한다.

함수의 분류

내장 함수
- 단일행 함수
- 다중행 함수
  - 집계 함수
  - 그룹 함수
  - 윈도우 함수
사용자 정의 함수

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

* Oracle 함수 / SQL Server 함수

단일행 함수의 특징

SELECT, WHERE, ORDER BY 절에 사용 가능
각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴

2. 문자형 함수

문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수이다. 몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.

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

단일행 문자형 함수 사례

문자형 함수 사용	결과 값 및 설명
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'
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'
TRIM('x' FROM 'xxYYZZxYZxx')	'YYZZxYZ'
RTRIM('XXYYZZXYZ ') -> 공백 제거 및 CHAR와 VARCHAR 데이터 유형을 비교할 때 용이하게 사용된다.	'XXYYZZXYZ'

3. 숫자형 함수

숫자형 함수는 숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수이다.

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

단일행 숫자형 함수 사례

숫자형 함수 사용	결과 값 및 설명
ABS(-15)	15
SIGN(-20)	-1
SIGN(0)	0
SIGN(20)	1
MOD(7, 3) / 7 % 3	1
CEIL(38.123) / CEILING(38.123)	39
CEILING(-38.123)	-38
FLOOR(38.123)	38
FLOOR(-38.123)	-39
ROUND(38.5235, 3)	38.524
ROUND(38.5235, 1)	38.5
ROUND(38.5235, 0)	39
ROUND(38.5235)	39 (인수 0이 Default)
TRUNC(38.5235, 3)	38.523
TRUNC(38.5235, 1)	38.5
TRUNC(38.5235, 0)	38
TRUNC(38.5235)	38 (인수 0이 Default)

4. 날짜형 함수

날짜형 함수는 DATE 타입의 값을 연산하는 함수다. Oracle의 TO_NUMBER(TO_CHAR()) 함수의 경우 변환형 함수로 구분할 수도 있으나 SQL Server의 YEAR, MONTH, DAY 함수와 매핑하기 위하여 날짜형 함수에서 설명한다. EXTRACT/DATEPART는 같은 기능을 하는 Oracle 내장 함수와 SQL Server 내장 함수를 표현한 것이다.

단일행 날짜형 함수 종류

날짜형 함수	함수 설명
SYSDATE / GETDATE()	현재 날짜와 시각을 출력한다.
EXTRACT('YEAR' \| 'MONTH' \| 'DAY' from d) / DATEPART('YEAR' \| 'MONTH' \| 'DAY', d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. 시간/분/초도 가능함
TO_NUMBER(TO_CHAR(d, 'YYYY')) / YEAR(d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. Oracle EXTRACT YEAR/MONTH/DAY 옵션이나 SQL Server DEPART YEAR/MONTH/DAY 옵션과 같은 기능이다. TO_NUMBER 함수 제외시 문자형으로 출력됨.
TO_NUMBER(TO_CHAR(d, 'MM')) / MONTH(d)	''
TO_NUMBER(TO_CHAR(d, 'DD')) / DAY(d)	''

DATE 변수가 DB에 어떻게 저장되는지 살펴보면, DB는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다. 날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다.

DB는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. 즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.

단일행 날짜형 데이터 연산

연산	결과	설명
날짜 + 숫자	날짜	숫자만큼의 날수를 날짜에 더한다.
날짜 - 숫자	날짜	숫자만큼의 날수를 날짜에 뺀다.
날짜1 - 날짜2	일수	다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다.
날짜 + 숫자/24	날짜	시간을 날짜에 더한다.

5. 변환형 함수

변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수이다. 변환형 함수는 크게 두 가지 방식이 있다.

데이터 유형 변환의 종류

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

단일행 변환형 함수의 종류

변환형 함수 - Oracle	함수 설명
TO_NUMBER(문자열)	alphanumeric 문자열을 숫자로 변환한다.
TO_CHAR(숫자 \| 날짜 [, FORMAT])	숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환한다.
TO_DATE(문자열 [, FORMAT])	문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다.

변환형 함수 - SQL Server	함수 설명
CAST (expression AS data_type [(length)])	expression을 목표 데이터 유형으로 변환한다.
CONVERT (data_type [(length)], expression [, style])	expression을 목표 데이터 유형으로 변환한다.

6. CASE 표현

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

CASE 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있고, Oracle의 경우 DECODE 함수를 사용할 수도 있다.

단일행 CASE 표현의 종류

[Simple Case Expression]
CASE SIMPLE_CASE_EXPRESSION
WHEN 비교값1 THEN 반환값1
WHEN 비교값2 THEN 반환값2
WHEN 비교값3 THEN 반환값3
...
ELSE 반환값
END

-> CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고, 다음 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 아닌지 판단하는 문장.


[Searched Case Expression]
CASE 
WHEN 조건식1 THEN 반환값1
WHEN 조건식2 THEN 반환값2
WHEN 조건식3 THEN 반환값3
...
ELSE 반환값
END

-> CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN 절에서 EQUI(=) 조건 포함 여러 조건을 이용한 조건절을 사용할 수 있기 때문에 Simple Case Expression보다 훨씬 다양한 조건을 적용할 수 있다.


[DECODE]
DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ... , 디폴트값])

-> Oracle에서만 사용되는 함수로, 표현식의 값이 기준값1이면 값1을 출력하고, 기준값2이면 값2를 출력한다. 그리고 기준값이 없으면 디폴트 값을 출력한다.

7. NULL 관련 표현

NULL의 특성

널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.
테이블을 생성할 때 NOT NULL 또는 PK로 정의하지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
널 값을 포함하는 연산의 경우 결과 값도 널 값이다.

단일행 NULL 관련 함수의 종류

일반형 함수	함수 설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다. NULL 관련 가장 많이 사용되는 함수이므로 상당히 중요하다.
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다.
COALESCE(표현식1, 표현식2, ...)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.