2-1-2. 함수

1. SQL 내장 함수의 개요

1️⃣ SQL 내장 함수 정의

1) SQL 내장 함수

• SQL에 이미 내장되어 있어 사용자가 단순히 함수를 호출해서 사용할 수 있는 기본 함수

• 데이터베이스에서 데이터를 처리하고 조작하기 위해 미리 정의된 함수
→ 주로 데이터 변환, 요약, 집계, 날짜 및 시간 처리, 문자열 조작 등을 수행하는 데 사용됨

2️⃣ SQL 내장 함수의 종류

1) 유형별

• 집계 함수

• 문자열 함수

• 날자 및 시간 함수

• 변환 함수

• 수학 함수

2) 단일행 함수와 다중행 함수

• 단일행 함수(Single-Row Function)
• 각 행에 대해 하나의 결과값을 반환
• 문자열 함수, 숫자 함수, 날짜 함수, 변환 함수 등

• 다중행 함수(Multi-Row Function)
• 여러 행의 데이터를 집계하여 하나의 결과값을 반환
• 집계 합수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function) 등

2. 함수의 종류

1️⃣ 문자열 함수

1) LOWER('문자열')

• 문자열을 소문자로 변환

• 예

SELECT LOWER('HELLO WORLD' AS lower_string
FROM DUAL

➡️ hello world

2) UPPER('문자열')

• 문자열을 대문자로 변환

• 예

SELECT UPPER('hello world') AS upper_string
FROM DUAL;

➡️ HELLO WORLD

3) ASCII('문자')

• 문자의 ASCII 값을 반환

• 예

SELECT ASCII('A') AS ascii_value
FROM DUAL;

➡️ 65

4) CHR(ASCII 번호) / CHAR

• ASCII 번호에 해당하는 문자를 반환

• 예

SELECT CHR(65) AS char_value
FROM DUAL;

➡️ A

5) CONCAT('문자열1','문자열2')

• 두 문자열을 연결

• 예

SELECT CONCAT('hello', 'world') AS concatenated_string
FROM DUAL;

➡️ hello world

6) SUBSTR('문자열', 시작 위치, [추출할 길이]) / SUBSTRING

• 문자열의 일부분 추출

• 추출할 길이 생략 시 마지막 문자까지 추출

• 예

SELECT SUBSTR('Hello World', 1, 5) AS substring
FROM DUAL;

➡️ Hello

7) LENGTH('문자열') / LEN

• 공백을 포함한 문자열의 길이 반환

• 예

SELECT LENGTH('Hello World') AS string_length
FROM DUAL;

➡️ 11

8) LTRIM(' 문자열')

• 문자열의 왼쪽 공백 제거

• 예

SELECT LTRIM('  Hello World') AS ltrim_string
FROM DUAL;

➡️ Hello World

9) RTRIM('문자열 ')

• 문자열의 오른쪽 공백 제거

• 예

SELECT RTRIM('Hello World  ') AS rtrim_string
FROM DUAL;

➡️ Hello World

10) TRIM(' 문자열 ')

• 문자열의 양쪽 공백 제거

• 예

SELECT TRIM('  Hello World  ') AS trimmed_string
FROM DUAL;

➡️ Hello World

2️⃣ 숫자형 함수

1) ABS(숫자)

• 숫자의 절대값 반환

• 예

SELECT ABS(-15) AS 절댓값
FROM DUAL;

➡️ 15

2) SIGN(숫자)

• 숫자 n의 부호를 반환
• 양수 → 1
• 음수 → -1
• 0 → 0

• 예

SELECT SIGN(-10) AS sign_neg, SIGN(0) AS sign_zero, SIGN(10) AS sign_pos
FROM DUAL;

➡️ -1, 0, 1

3) MOD(n2, n1)

• 숫자 n2를 숫자 n1으로 나눈 나머지 반환

• 예

SELECT MOD(17, 5) AS MOD
FROM DUAL;

➡️ 2

4) CEIL(숫자)

• 숫자 n보다 크거나 같은 최소 정수 반환 (올림)

• 예

SELECT CEIL(4.3) AS CEIL
FROM DUAL;

➡️ 5

5) FLOOR(숫자)

• 숫자 n보다 작거나 같은 최대 정수 반환 (내림)

• 예

SELECT FLOOR(4.7) AS floor
FROM DUAL;

➡️ 4

6) ROUND(n, [m])

• 숫자 n을 소수점 m자리까지 반올림
• m을 생략하면 정수로 반올림

• 예

SELECT ROUND(3.14159, 2) AS round
FROM DUAL;

➡️ 3.14

7) TRUNC(n, [m])

• 숫자 n을 소수점 m자리까지 자름
• m을 생략하면 정수 부분만 남김

• 예

SELECT TRUNC(3.14159, 2) AS trunc
FROM DUAL;

➡️ 3.14

8) SIN(숫자) / COS(숫자) / TAN(숫자)

• 사인, 코사인, 탄젠트 값 반환
• 라디안 단위 사용

• 예

SELECT SIN(1) AS sin, COS(1) AS cos, TAN(1) AS tan
FROM DUAL;

➡️ 0.8414709848078965, 0.5403023058681398, 1.5574077246549023

9) EXP(n)

• e의 n 제곱 값을 반환
• e = 2.71828183…

• 예

SELECT EXP(1) AS EXP
FROM DUAL;

➡️ 2.718281828459045

10) POWER(n2, n1)

• n2의 n1 제곱 반환

• 예

SELECT POWER(2, 3) AS power
FROM DUAL;

➡️ 8

11) SQRT(n)

• n의 제곱근 반환

• 예

SELECT SQRT(9) AS sqrt
FROM DUAL;

➡️ 3

12) LOG(n2, n1)

• 밑이 n1인 n2의 로그 값 반환

• 예

SELECT LOG(2, 8) AS log
FROM DUAL;

➡️ 2.9999999999999 ...

13) LN(n)

• n의 자연로그 값 반환

• 예

SELECT LN(2.718281828459045) AS LN
FROM DUAL;

➡️ 0.9999999999999 ...

3️⃣ 날짜형 함수

1) SYSDATE

• 현재 날짜와 시각 반환

• 예

SELECT SYSDATE AS system_data
FROM DUAL;

➡️ 2024-07-20 18:05:17.000 (현재 날짜 → 조회할 때마다 값 바뀜)

2) CURRENT_DATE

• 세션의 현재 날짜와 시각 반환

• 예

SELECT CURRENT_DATE AS session_date
FROM DUAL;

➡️ 2024-07-20 18:08:20.000

3) ADD_MONTHS(date, n)

• 지정한 개월 수만큼 날짜를 더함

• 예

SELECT ADD_MONTHS(SYSDATE, 3) AS future_date
FROM DUAL;

➡️ 2024-10-20 18:16:03.000
(현재 날짜가 7월 20일이라고 가정했을 때, 현재 날짜에서 3개월을 더한 날짜)

4) MONTHS_BETWEEN(date1, date2)

• 두 날짜 사이의 개월 수 계산

• 예

SELECT MONTHS_BETWEEN(SYSDATE, '2024-01-01') AS months_diff
FROM DUAL;

➡️ 6.63751493428912783751493428912783751493
(현재 날짜가 7월 20일이라고 가정했을 때, 현재 날짜와 ‘2024-01-01’ 사이의 개월 수 차이)

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2024-01-01', 'YYYY-MM-DD')) AS diff
FROM DUAL;

5) NEXT_DAY(date, 'day')

• 주어진 날짜 이후의 특정 요일의 날짜 반환

• 예

SELECT NEXT_DAY('2024-07-20', '월요일')
FROM DUAL;

➡️ 2024-07-22 00:00:00.000

6) LAST_DATY(date)

• 주어진 날짜가 속한 달의 마지막 날짜 반환

• 예

SELECT LAST_DAY(SYSDATE) AS end_of_month
FROM DUAL;

➡️ 2024-07-31 18:38:03.000

7) TRUNC(date)

• 날짜에서 시각 부분을 제거하여 날짜만 반환

• 예

SELECT TRUNC(SYSDATE) AS truncated_date
FROM DUAL;

➡️ 2024-07-20 00:00:00.000
(시분초가 나오지 않게 하려면? → 교재 p.122 참고)

8) ROUND(date, 'format')

• 날짜를 가장 가까운 날짜 또는 시각으로 반올림

• 예

SELECT ROUND(SYSDATE, 'MONTH') AS round_date
FROM DUAL;

➡️ 2024-08-01 00:00:00.000
(현재 날짜가 7월 20일이라면 현재 날짜와 가장 가까운 ‘MONTH’ 반환)

9) (오라클) EXTRACT(part FROM date) / (SQL Server) DATEPART(datepart, date)

• 날짜의 특정 부분(년, 월, 일 등)을 숫자 형식으로 추출

• 예 (오라클)

SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year
FROM DUAL;

➡️ 2024

SELECT EXTRACT(MONTH FROM SYSDATE) AS current_month
FROM DUAL;

➡️ 7

• 예 (SQL Server)

SELECT DATEPART(YEAR, hiredate) AS YEAR,
			 DATEPART(MONTH, hiredate) AS MONTH,
			 DATEPART(DAY, hiredate) AS DAY
FROM DUAL;

⬇️

YEAR	MONTH	DAY
1980	12	17
1981	2	20
1981	2	22

(월이나 요일 이름이 필요한 경우, DATENAME 함수 사용 가능
DATENAME : DATEPART와 유사, 일부 날짜 부분에 대해 문자열 반환)

10) TO_CHAR(date, 'format')

• 날짜를 지정된 형식의 문자열로 반환

• 예

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM DUAL;

➡️ 2024-07-20 19:07:32

4️⃣ 변환형 함수

1) TO_CHAR(date, 'format')

• 날짜를 지정된 형식의 문자열로 변환

• 예

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM DUAL;

➡️ 2024-07-20 20:25:22

• 숫자를 문자열로 변환

• 예

SELECT TO_CHAR(1234.56, '9,999.99')
FROM DUAL;

➡️ 1,234.56

2) TO_DATE([문자열], 'format')

• 문자열을 지정된 형식의 날짜로 변환

• 예

SELECT TO_DATE('2024-07-21 20:25:22', 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM DUAL;

➡️ 2024-07-21 20:25:22.000

3) TO_NUMBER('string', 'format')

• 문자열 데이터를 숫자 형식으로 변환

• 예

SELECT TO_NUMBER('12345') AS number_value
FROM DUAL;

➡️ 12345

4) CAST(value AS datatype)

• 한 데이터 타입을 다른 데이터 타입으로 변환

• 예

SELECT CAST('12345' AS NUMBER) AS number_value
FROM DUAL;

➡️ 12345

5) CONVERT('string', 'dest_charset', 'source_charset')

• 문자열을 한 문자 세트에서 다른 문자 세트로 변환

• 예

SELECT CONVERT('가나다라마', 'AL32UTF8', 'UTF8') AS converted_string
FROM DUAL;

➡️ 가나다라마
(’가나다라마’ 문자열을 ‘AL32UTF8’ 문자 세트에서 ‘UTF8’ 문자 세트로 변환)

5️⃣ CASE 문

1) CASE 조건문의 기초 개념

• 조건에 따라 다른 값을 반환할 수 있는 조건문

• 자바(Java), 파이썬(Python) 등의 다른 프로그래밍 언어에서 사용되는 IF 조건문과 비슷한 역할

• CASE문은 항상 단일 값을 반환하고, 이 값은 쿼리 결과의 일부가 됨

2) 단순 CASE 문

• 표현식의 값을 조건과 비교하여 해당 조건이 참일 때 지정된 결과 반환

• 형식

CASE expression                // 표현식
		WHEN value1 THEN result1   // 표현식이 value1과 같으면 result1 반환
		WHEN value2 THEN result2   // 표현식이 value2와 같으면 result2 반환
		...
		ELSE default_result        // 위 조건에 해당하지 않으면 default_result 반환
END

• 예시

SELECT ENAME, JOB,
			 CASE JOB
					 WHEN 'CLERK' THEN 'Clerk Job'
					 WHEN 'SALESMAN' THEN 'Salesman Job'
					 WHEN 'MANAGER' THEN 'Manager Job'
					 ELSE 'Other Job'
			 END AS JOB_DESCRIPTION
FROM EMP;

ENAME	JOB	JOB_DESCRIPTION
KING	PRESIDENT	Other Job
BLAKE	MANAGER	Manager Job
CLARK	MANAGER	Manager Job
SCOTT	ANALYST	Other Job
ADAMS	CLERK	Clerk Job
MILLER	CLERK	Clerk Job

3) 검색 CASE 문

• 각 조건을 평가하여 해당 조건이 참일 때 지정된 결과 반환

• 형식

CASE
		WHEN condition1 THEN result1  // condition1이 참이면 result1 반환
		WHEN condition2 THEN result2  // condition2가 참이면 result2 반환
		...
		ELSE default_result           // 위 조건에 해당하지 않으면 default_result 반환
END

• 예시

SELECT ENAME, SAL,
			 CASE
					 WHEN SAL < 1000 THEN 'Low Salary'
					 WHEN SAL BETWEEN 1000 AND 3000 THEN 'Medium Salary'
					 WHEN SAL > 3000 THEN 'High Salary'
					 ELSE 'Unknown'
			 END AS SALARY_GRADE
FROM EMP;

ENAME	SAL	SALARTY_GRADE
KING	5000	High Salary
BLAKE	2850	Medium Salary
CLARK	2450	Medium Salary

4) 중첩된 CASE 문

• 중첨된 CASE 문을 통해 다중 조건을 보다 효율적으로 관리할 수 있음

• 예시

SELECT ENAME AS "사원명", DEPTNO AS "부서번호", SAL AS "급여",
			 CASE DEPTNO
					 WHEN 10 THEN
							 CASE
									 WHEN SAL < 3000 THEN '회계 부서 - 낮은 급여'
									 ELSE '회계 부서 - 높은 급여'
							 END
					 WHEN 20 THEN
							 CASE
									 WHEN SAL < 3000 THEN '연구 부서 - 낮은 급여'
									 ELSE '연구 부서 - 높은 급여'
							 END
					 WHEN 30 THEN
							 CASE
									 WHEN SAL < 3000 THEN '영업 부서 - 낮은 급여'
									 ELSE '영업 부서 - 높은 급여'
							 END
					 ELSE '기타 부서'
			 END AS "부서급여메시지"
FROM EMP;

사원명	부서번호	급여	부서급여메시지
KING	10	5000	회계 부서 - 높은 급여
BLAKE	30	2850	영업 부서 - 낮은 급여
CLARK	10	2450	회계 부서 - 낮은 급여
JONES	20	2970	연구 부서 - 낮은 급여

6️⃣ NULL 관련 함수

1) NULL 기초 개념

• 데이터베이스에서 값이 없음을 나타내는 특별한 표식

• 0, 공백(스페이스), 빈 문자열(공백이 문자열로 저장된 경우)과 다름

• NULL = ‘알 수 없는 값’ or ‘존재하지 않는 값’

2) NULL의 특성

• 비교 불가
• NULL은 다른 값과 비교할 수 없음
• NULL과 어떤 값을 비교해도 결과는 항상 NULL

• 산술 연산 불가
• NULL과의 산술 연산 결과는 항상 NULL

• 문자열 연산 불가
• NULL과의 문자열 결합 연산 결과도 항상 NULL

• 집계 함수
• 대부분의 집계 함수는 NULL 값 무시
• 예) SUM 함수 - NULL값 포함하지 않고 합계 계산

3) NVL(첫 번째 인자, 두 번째 인자) ⭐

• 오라클에만 있는 함수

• 첫 번째 인자가 NULL인 경우, 두 번째 인자로 대체

• 예

ENAME	COMM
KING	NULL
BLAKE	NULL
CLARK	NULL
JONES	NULL
MARTIN	1400
ALLEN	300
TURNER	NULL

SELECT ENAME, NVL(COMM, 0) AS COMMISSION
FROM EMP;

ENAME	COMMISSION
KING	0
BLAKE	0
CLARK	0
JONES	0
MARTIN	1400
ALLEN	300
TURNER	0

4) COALESCE(첫 번째 인자, 두 번째 인자, ... , N번째 인자)

• 표준 SQL (DB 공통)

• 인자 목록에서 첫 번째로 NULL이 아닌 값을 반환

• 예

SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL, 0) AS VALUE
FROM EMP;

ENAME	COMM	SAL	VALUE
KING	NULL	5000	5000
BLAKE	NULL	2850	2850
CLARK	NULL	2450	2450
JONES	NULL	2975	2975
MARTIN	1400	1250	1400
ALLEN	300	1600	300

5) NULLIF(첫 번째 인자, 두 번째 인자)

• 표준 SQL (DB 공통)

• 두 인자가 같으면 NULL 반환, 그렇지 않으면 첫 번째 인자 반환

• 예

SELECT ENAME, COMM, NULLIF(COMM, 0) AS NULLIF_VALUE
FROM EMP;

ENAME	COMM	NULLIF_VALUE
KING	NULL	NULL
BLAKE	NULL	NULL
CLARK	NULL	NULL
JONES	NULL	NULL
MARTIN	1400	1400
ALLEN	300	300
TURNER	0	NULL

6) IFNULL(첫 번째 인자, 두 번째 인자)

• My SQL

• 첫 번째 인자가 NULL이면 두 번째 인자 반환, 그렇지 않으면 첫 번째 인자 반환

• 예

SELECT ENAME, COMM, IFNULL(COMM, 0) AS IFNULL_VALUE
FROM EMP;

ENAME	COMM	IFNULL_VALUE
SMITH	NULL	0
ALLEN	300	300
JONES	NULL	0

7) ISNULL(첫 번째 인자, 두 번째 인자)

• SQL Server

• 첫 번째 인자가 NULL이면 두 번째 인자 반환, 그렇지 않으면 첫 번째 인자 반환

• 예

SELECT ENAME, COMM, ISNULL(COMM, 0) AS ISNULL_VALUE
FROM EMP;

ENAME	COMM	ISNULL_VALUE
SMITH	NULL	0
ALLEN	300	300
WARD	500	500
JONES	NULL	0