[SQLD] SQL 기본 - FUNCTION

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

함수는 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 내장 함수(Built-in Function)와 사용자 정의 함수(User Defined Function)로 나눌 수 있다.

 

함수의 분류

•  내장 함수 (Built-in Function)
  • 단일행 함수 (Single-Row Function)
  • 다중행 함수 (Multi-Row Function)
    • 집계 함수 (Aggregate Function)
    • 그룹 함수 (Group Function)
    • 윈도우 함수 (Window Function)
• 사용자 정의 함수 (User Defined Function)

💡 단일행 함수의 특징
- SELECT, WHERE, ORDER BY, UPDAT의 SET 절에 사용 가능
- 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분할 수 있음
- 1:M 조인이라 하더라고 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용할 수 있음
- 다중행 함수도 단일행 함수와 도일하게 단일 값만을 반환 함

 

02. 단일행 함수의 종류

 

문자형 함수

문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수이다.

몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.

문자열 함수	설명
ASCII(문자)	문자나 숫자를 ASCII 코드로 변환 e.g. ASCII('A') = 65
CHA/CHAR(ASCII 코드 값)	ASCII 코드값을 문자나 숫자로 변환 e.g. CHA(65) / CHAR(65) = 'A'
LOWER(문자열)	알파벳 소문자를 소문자로 변환 e.g. LOWER('SQL Dev) = 'sql dev')
UPPER(문자열)	알파벳 대문자를 대문자로 변환 e.g. UPPER('SQL Dev') = 'SQL DEV'
CONCAT(문자열1, 문자열2)	문자열 1번과 2번을 결합 (Oracle -  '||', SQL Server - '+') e.g. CONCAT('SQL', 'D') / 'SQL' || 'D' / 'SQL' + 'D' = 'SQLD'
SUBSTR/SUBSTRING(문자열, m[, n])	문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자를 리턴 n 생략 시, 마지막 문자까지 리턴 e.g. SUBSTR('SQL Dev', 5, 2) /  SUBSTRING('SQL Dev', 5, 2) = 'De'
LENGTH/LEN(문자열)	문자열의 개수를 숫자값으로 리턴 e.g. LENGTH('SQLD) / LEN(SQLD) = 10
LTRIM(문자열, [지정문자])	문자열 왼쪽부터 지정문자가 나타나면 문자를 삭제 (지정 문자 생략 시, 공백 값이 디폴트) *SQL Server에서는 LTRIM 함수에 지정 문자 사용 불가 = 공백만 제거 e.g. LTRIM('xxxYYZZxyz', 'x') = 'YYZZxYZ'
RTRIM(문자열, [지정문자])	문자열 오른쪽부터 지정문자가 나타나면 문자를 삭제 (지정 문자 생략 시, 공백 값이 디폴트) *SQL Server에서는 RTRIM 함수에 지정 문자 사용 불가 = 공백만 제거 e.g. RTRIM('XXYYzzXYzz'., 'z') = 'XXYYzzYX'
TRIM([leading | trailing | both] 지정 문자 FROM 문자열)	문자열 양쪽으로 지정문자가 나타나면 지정 문자를 제거 (leading | trailing | both 생략 시, both가 디폴트) *SQL Server에서는 TRIM 함수에 지정 문자 사용 불가 = 공백만 제거 e.g. TRIM('x' FROM 'xxYYZZxYZxx') = 'YYZZxYZ'

 

숫자형 함수

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

숫자형 함수	설명
ABS(숫자)	절대값을 돌려줌 e.g. ABS(10.1) = 10
SIGN(숫자)	양수, 음수, 0 중 구별 1(양수), -1(음수), 0(0) e.g. SIGN(10.1) = 1
MOD(숫자1, 숫자1)	숫자1을 숫자2로 나눈 나머지 값을 리턴 (연산자 %와 같음) e.g. MOD(10, 3) = 1
CEIL / CEILLING(숫자)	숫자보다 크거나 같은 최소 정수 리턴  e.g. CEIL(10.1) = 11
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수 리턴 e.g. FLOOR(10.1) = 10
ROUND(숫자[, m])	숫자를 소수점 m자리에서 반올림 (m의 디폴트는 0) e.g. ROUND(10.123) = 10
TRUNC(숫자[, m])	숫자를 소수점 m자리까지만 출력 (m의 디폴트는 0) e.g. TRUNC(10.123, 2) = 10.12
SIN, COS, TAN ...	숫자의 삼각함수 값을 리턴
EXP(), POWER(), SQRT(), LN()	숫자의 지수, 거듭 제곱, 제곱근, 자연 로그 값을 리턴

 

날짜형 함수

날짜형 함수는 DATE 타입의 값을 연산하는 함수이다.

날짜형 함수	설명
SYSDATE / GETDATE()	현재 날짜 출력
EXTRACT('YEAR' | 'MONTH' | 'DAY' from d) / DATEPART('YEAR' | 'MONTH' | 'DAY', d)	날짜 데이터 중 년, 월, 일 출력 시간, 분, 초도 가능

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

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

e.g.
① 날짜 + 숫자 = 날짜 → 숫자만큼의 날수를 날짜에 더한다.
② 날짜 - 숫자 = 날짜 → 숫자만큼의 날수를 날짜에 뺀다.
③ 날짜1 - 날짜2 = 일수 → 다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다.
④ 날짜 + 숫자/24 = 날짜 → 시간을 날짜에 더한다.

 

변환형 함수

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

- 명시적(Explicit) 변환형: 밑의 변환형 함수를 이용하여 개발자가 형변환 시키는 것

- 암시적(Implicit) 변환형: DBMS가 자동으로 형변환 시키는 것

변환형 함수	설명
TO_NUMBER(문자열)	문자열을 숫자로 변환
TO_CHAR(숫자 or 날짜 [, FORMAT])	숫자 혹은 날짜를 FORMAT 현태의 문자열로 변환
TO_DATE(문자열 [, FORMAT])	문자열을 FORMAT 형태의 날짜형으로 변환

 

 

03. CASE 표현

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

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

// WHEN의 조건이 참이면 THEN을 실행하고 거짓이면 ELSE를 실행
[CASE]
CASE
	WHEN 조건식1 THEN 반환값1
    WHEN 조건식2 THEN 반환값2
    ELSE 반환값
	END
FROM 테이블명;


[DECODE]
// 표현식의 값이 기준값 1이면 값1을 출력, 기준값2면 값2를 출력 없으면 디폴트값 출력
DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ..., 디폴트값]) FROM 테이블명;

e.g.

[CASE]
SELECT CASE
		WHEN GRADE = 'A' THEN '하'
        WHEN GRADE = 'B' THEN '중'
        ELSE '상'
        END
FROM USER; -- GRADE 값이 A면 하, B면 중 나머지는 상으로 출력

[DECODE]
SELECT DECODE(GRADE, 'A', '참', '거짓') FROM USER;
-- GRAGE 값이 'A'면 참을 출력, 아니면 거짓을 출력

 

 

04. NULL 표현

아직 정의 되지 않은 값으로 값이 없는 값

NULL 함수	설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)	표현식1이 NULL값 이라면 표현식2를 출력
NVL2(표현식1, 표현식2, 표현식3)	표현식1이  NULL이 아니라면 표현식2를 출력 표현식1이 NULL이면 표현식3을 출력
NULLIF(표현식1, 표현식2)	표현식1 = 표현식2 라면 NULL 출력  같지 않으면 표현식1 출력
COALESCE(표현식1, ~, 표현식n)	차례대로 NULL이 아니면 표현식1 출력 표현식1이 NULL이라면 표현식2가 NULL 여부 판단 모든 표현식이 NULL 이라면 NULL 출력 → NULL 값이 아닌 첫번째 표현식을 출력하고 싶을 때, NULL 값이라면 뒤로 패스 패스 후 NULL값이 아니라면 그 표현식을 출력 모두 NULL값이면 NULL 출력