SQL 기본 및 활용-3.Function

hoon·2024년 11월 14일

SQLD

sqld

목록 보기

6/10

FUNCTION

함수의 유형

생성 주체

사용자 정의 함수 (User Defined Function): 사용자가 직접 정의하는 함수
내장 함수 (Built-in Function): 벤더가 미리 정의해 제공하는 함수

적용 범위

단일 행 함수 (Single-Row Function): 한 번에 하나의 행에만 적용되는 함수
- 문자형 함수: 문자열을 다루는 함수
- 숫자형 함수: 숫자 데이터를 다루는 함수
- 날짜형 함수: 날짜 데이터를 처리하는 함수
- 제어 함수: 조건에 따라 다른 결과를 반환하는 함수
- 변환 함수: 데이터 타입을 변환하는 함수
- NULL 관련 함수: NULL 값을 처리하는 함수
다중 행 함수 (Multi-Row Function): 여러 행에 대해 집계하여 결과를 반환하는 함수
- 그룹 함수 (Group Function): 여러 행을 그룹화하여 집계하는 함수
- 집계 함수 (Aggregate Function): SUM, AVG, COUNT 등 여러 행의 데이터를 집계하는 함수
- 윈도우 함수 (Window Function): 데이터의 특정 창(window)을 설정하고 그 안에서 계산하는 함수

Oracle의 단일행 내장 함수

종류	개요	주요 함수
문자형 함수	문자열 변수를 처리	LOWER, UPPER, ASCII, CHR, CONCAT, SUBSTR, LENGTH, LTRIM, RTRIM, TRIM
숫자형 함수	숫자형 변수를 처리	ABS, SIGN, MOD, CEIL, FLOOR, ROUND, TRUNC, SIN, COS, TAN, EXP, POWER, SQRT, LOG, LN
변환 함수	문자, 숫자, DATE형 값의 타입 변환	TO_CHAR, TO_NUMBER, TO_DATE
날짜형 함수	DATE 타입의 변수를 처리	SYSDATE, EXTRACT, TO_NUMBER(TO_CHAR(‘DD’‘MM’‘YY’))
제어 함수	논리값에 따른 값의 처리	CASE, DECODE
NULL 관련 함수	NULL 처리	NVL, NULLIF, COALESCE

카테고리	함수	설명
문자형 함수	CONCAT	두 개의 문자열을 연결함
	SUBSTR	문자열에서 지정된 위치부터 부분 문자열을 반환함
	LENGTH	문자열의 길이를 반환함
	TRIM	문자열의 양쪽 공백을 제거함
숫자형 함수	MOD	두 숫자의 나머지를 반환함
	ROUND	지정된 자리에서 숫자를 반올림함
변환 함수	TO_CHAR	숫자나 날짜를 문자형으로 변환함
	TO_NUMBER	문자를 숫자형으로 변환함
	TO_DATE	문자를 날짜형으로 변환함
날짜형 함수	SYSDATE	현재 날짜와 시간을 반환함
	TO_NUMBER(TO_CHAR(‘DD’‘MM’‘YY’))	날짜를 숫자형으로 변환하는 예시
NULL 관련 함수	NVL	NULL 값을 다른 값으로 대체함
	NULLIF	두 값이 같으면 NULL을 반환하고, 다르면 첫 번째 값을 반환함

문자형 함수

문자형 함수	함수 사용 예	결과
LOWER(문자열)	`LOWER('SQL Expert')`	`sql expert`
UPPER(문자열)	`UPPER('SQL Expert')`	`SQL EXPERT`
ASCII(문자)	`ASCII('A')`, `ASCII('5')`	`65`, `53`
CHR(ASCII 코드)	`CHR(65)`, `CHR(53)`	`A`, `5`
CONCAT(문자열1, 문자열2)	`CONCAT('RDBMS', ' SQL')`	`RDBMS SQL`
	`cf) 'RDBMS'
SUBSTR(문자열, m)	`SUBSTR('SQL Expert', 5)`	`Expert`
SUBSTR(문자열, m, n)	`SUBSTR('SQL Expert', 5, 3)`	`Exp`
LENGTH(문자열)	`LENGTH('SQL Expert')`	`10`

문자형 함수	함수 설명	함수 사용 예	결과
LTRIM(문자열)	문자열의 왼쪽부터 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정 문자가 생략되면 공백 값이 기본값)	`LTRIM(' xxxYYZZxZxZ')`	`xxxYYZZxZxZ`
LTRIM(문자열, 지정문자)	문자열의 왼쪽부터 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정 문자가 생략되면 공백 값이 기본값)	`LTRIM('xxxYYZZxZxZ', 'x')`	`YYZZxZxZ`
RTRIM(문자열)	문자열의 오른쪽부터 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정 문자가 생략되면 공백 값이 기본값)	`RTRIM('zXXYYZZxZx ')`	`zXXYYZZxZx`
RTRIM(문자열, 지정문자)	문자열의 오른쪽부터 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정 문자가 생략되면 공백 값이 기본값)	`RTRIM('zXXYYZZxZxZ', 'Z')`	`zXXYYZZxZx`
TRIM(문자열)	문자열의 양쪽에서 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정 문자와 FROM이 생략되면 공백 값이 기본값)	`TRIM(' x' FROM 'xxYYZZxZx')`	`YYZZxZ`
TRIM(지정문자 FROM 문자열)	문자열의 양쪽에서 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정 문자와 FROM이 생략되면 공백 값이 기본값)	`TRIM(' ' FROM ' ')`	`' '`

숫자형 함수

숫자형 함수	함수 사용 예	결과
ABS(숫자)	`ABS(-15)`	`15`
SIGN(숫자)	`SIGN(-20)`, `SIGN(0)`, `SIGN(10)`	`-1`, `0`, `1`
MOD(숫자1, 숫자2)	`MOD(7, 3)`	`1`
CEIL(숫자)	`CEIL(38.123)`, `CEIL(-38.123)`	`39`, `-38`
FLOOR(숫자)	`FLOOR(38.123)`, `FLOOR(-38.123)`	`38`, `-39`
ROUND(숫자), ROUND(숫자, m)	`ROUND(38.5235, 3)`, `ROUND(38.5235)`	`38.524`, `39`
TRUNC(숫자), TRUNC(숫자, m)	`TRUNC(38.5235, 3)`, `TRUNC(38.5235)`	`38.523`, `38`

변환형 함수

변환형 함수	함수 설명	함수 사용 예
TO_CHAR(숫자/날짜), TO_CHAR(숫자/날짜, FORMAT)	숫자나 날짜를 문자열로 변환	`SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS 타입1,` `TO_CHAR(SYSDATE, 'YYYY.MM.DD.HH24.MI.SS') AS 타입2,` `TO_CHAR(SYSDATE) AS 타입3 FROM DUAL;`
TO_NUMBER(문자열)	문자열을 숫자로 변환	`SELECT '1' + '1' AS 계산 FROM DUAL;` 암시적 변환 `SELECT TO_NUMBER('1') + TO_NUMBER('1') AS 계산 FROM DUAL;` 명시적 변환
TO_DATE(문자열), TO_DATE(문자열, FORMAT)	문자열을 날짜로 변환	`SELECT EXTRACT(YEAR FROM '20170123') AS 연도 FROM DUAL;` → ERROR!!! `SELECT EXTRACT(YEAR FROM TO_DATE('20170123', 'YYYY/MM/DD')) AS 연도 FROM DUAL;`

날짜형 함수

날짜형 함수	함수 설명	함수 사용 예
SYSDATE	현재 날짜와 시각을 반환	`SELECT SYSDATE FROM DUAL;` `SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL;`
EXTRACT('YEAR' \| 'MONTH' \| 'DAY' FROM 날짜)	날짜 데이터에서 연도, 월, 일 정보를 추출	`SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;` `SELECT EXTRACT(MONTH FROM BIRTH_DATE) FROM PLAYER;`
TRUNC(날짜, 'DD')	날짜 데이터에서 시, 분, 초를 잘라냄	`SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL;` `SELECT TRUNC(SYSDATE, 'DD') FROM DUAL;` `SELECT TO_CHAR(TRUNC(SYSDATE, 'DD'), 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL;`

날짜 데이터에서 연도, 월, 일 추출하는 두 가지 방법

날짜형 데이터에서 연도, 월, 일을 추출할 수 있는 두 가지 방법

방법 1: `EXTRACT` 함수 사용

SELECT PLAYER_NAME, BIRTH_DATE,
       EXTRACT(YEAR FROM BIRTH_DATE) AS 생년,
       EXTRACT(MONTH FROM BIRTH_DATE) AS 생월,
       EXTRACT(DAY FROM BIRTH_DATE) AS 생일
FROM PLAYER;

EXTRACT 함수를 사용하여 날짜에서 특정 요소를 추출.
- EXTRACT(YEAR FROM BIRTH_DATE): BIRTH_DATE에서 연도를 추출
- EXTRACT(MONTH FROM BIRTH_DATE): BIRTH_DATE에서 월을 추출
- EXTRACT(DAY FROM BIRTH_DATE): BIRTH_DATE에서 일을 추출

방법 2: `TO_CHAR`와 `TO_NUMBER` 함수 사용

SELECT PLAYER_NAME, BIRTH_DATE,
       TO_NUMBER(TO_CHAR(BIRTH_DATE, 'YYYY')) AS 생년,
       TO_NUMBER(TO_CHAR(BIRTH_DATE, 'MM')) AS 생월,
       TO_NUMBER(TO_CHAR(BIRTH_DATE, 'DD')) AS 생일
FROM PLAYER;

TO_CHAR 함수를 사용하여 날짜를 원하는 형식의 문자열로 변환한 후, TO_NUMBER로 숫자로 변환.
- TO_CHAR(BIRTH_DATE, 'YYYY'): 연도를 문자열로 변환한 후 TO_NUMBER로 숫자로 변환
- TO_CHAR(BIRTH_DATE, 'MM'): 월을 문자열로 변환한 후 TO_NUMBER로 숫자로 변환
- TO_CHAR(BIRTH_DATE, 'DD'): 일을 문자열로 변환한 후 TO_NUMBER로 숫자로 변환

CASE EXPRESSION

표현식이지만 함수의 성격을 갖고 있음
IF ~ THEN ~ ELSE 논리 흐름

SELECT PLAYER_NAME, 
       CASE WHEN HEIGHT > 180 
            THEN HEIGHT 
            ELSE 180 
       END AS NEW_HEIGHT 
FROM PLAYER;

SELECT PLAYER_NAME,  
       CASE  
           WHEN POSITION = 'GK' THEN '골키퍼'  
           WHEN POSITION = 'DF' THEN '수비수'  
           WHEN POSITION = 'MF' THEN '미드필더'  
           WHEN POSITION = 'FW' THEN '공격수'  
           ELSE '그 외'  
       END AS 포지션  
FROM PLAYER;

| - 표준 SQL
- 다양한 조건 사용 가능
- 표현식이 복잡함 |
| Simple Case Expression |

SELECT PLAYER_NAME,  
       CASE POSITION  
           WHEN 'GK' THEN '골키퍼'  
           WHEN 'DF' THEN '수비수'  
           WHEN 'MF' THEN '미드필더'  
           WHEN 'FW' THEN '공격수'  
           ELSE '그 외'  
       END AS 포지션  
FROM PLAYER;

| - 표준 SQL
- 등등(=) 비교에만 사용
- 표현식이 명료함 |

DECODE 함수

Oracle에서만 사용되는 함수
구문: DECODE(표현식, 기준값1, 출력값1 [, 기준값2, 출력값2, ..., 디폴트값])
사용 방법:
- 표현식의 값이 기준값1이면 출력값1을 반환하고, 기준값2이면 출력값2를 반환.
- 지정한 기준값에 맞지 않는 경우, 디폴트값이 있으면 이를 반환함.

예제

SELECT PLAYER_NAME, 
       DECODE(POSITION,
              'GK', '골키퍼',
              'DF', '수비수',
              'MF', '미드필더',
              'FW', '공격수',
              '그 외') AS POSITION
FROM PLAYER;

POSITION이 'GK'인 경우 '골키퍼'를 출력하고, 'DF'인 경우 '수비수', 'MF'인 경우 '미드필더', 'FW'인 경우 '공격수'를 출력.
그 외의 값일 경우 '그 외'를 출력.

NULL 관련 함수

NULL 관련 함수	함수 설명	예시
NVL(표현식, 대체값)	- 표현식의 값이 `NULL`이면 대체값을 반환, `NULL`이 아니면 표현식의 값을 반환 - 표현식의 값과 대체값의 데이터 타입이 같아야 함	`SELECT PLAYER_NAME, POSITION, NVL(POSITION, '없음') AS 포지션 FROM PLAYER;`
NULLIF(표현식1, 표현식2)	- 두 식이 같으면 `NULL`을 반환, 같지 않으면 표현식1의 값을 반환	`SELECT PLAYER_NAME, POSITION, NULLIF(POSITION, 'GK') FROM PLAYER;`
COALESCE(표현식1, 표현식2, ...)	- 임의의 개수의 표현식에서 `NULL`이 아닌 최초의 표현식을 반환 - 모든 표현식이 `NULL`이라면 `NULL`을 반환	`SELECT E_PLAYER_NAME, NICKNAME, PLAYER_NAME, COALESCE(E_PLAYER_NAME, NICKNAME, PLAYER_NAME) FROM PLAYER;`

설명 요약

NVL: NULL 값을 대체값으로 변경해주는 함수.
NULLIF: 두 값이 같으면 NULL을 반환하고, 다르면 첫 번째 값을 반환하는 함수.
COALESCE: 여러 표현식 중 NULL이 아닌 첫 번째 값을 반환하는 함수.

아래와 같이 NVL 함수와 연봉 계산에 대한 내용을 깔끔하게 정리해 드리겠습니다.

NVL을 이용한 연봉 계산

기본 연봉 계산

연봉 계산식: 연봉 = SAL * 12 + COMM

SELECT ENAME, SAL, COMM, (SAL * 12) + COMM AS 연봉
FROM EMP;

위 계산식에서는 COMM이 NULL일 경우 연봉 계산이 정확하지 않을 수 있음.

NVL을 적용한 연봉 재계산

NVL 함수를 사용하여 SAL과 COMM이 NULL일 때 각각 0으로 처리하여 연봉을 계산.

SELECT ENAME, SAL, COMM, NVL(SAL, 0) * 12 + NVL(COMM, 0) AS 연봉
FROM EMP;

NVL(SAL, 0): SAL이 NULL이면 0으로 대체.
NVL(COMM, 0): COMM이 NULL이면 0으로 대체.

hoon

이전 포스트

SQL 기본 및 활용-2.DDL

다음 포스트

SQL 기본 및 활용-3.Function

sqld

함수의 유형

생성 주체

적용 범위

날짜 데이터에서 연도, 월, 일 추출하는 두 가지 방법

방법 1: `EXTRACT` 함수 사용

방법 2: `TO_CHAR`와 `TO_NUMBER` 함수 사용

DECODE 함수

예제

설명 요약

NVL을 이용한 연봉 계산

기본 연봉 계산

NVL을 적용한 연봉 재계산

SQL 기본 및 활용-2.DDL

SQL 기본 및 활용-4.TCL

0개의 댓글

SQL 기본 및 활용-3.Function

sqld

함수의 유형

생성 주체

적용 범위

날짜 데이터에서 연도, 월, 일 추출하는 두 가지 방법

방법 1: EXTRACT 함수 사용

방법 2: TO_CHAR와 TO_NUMBER 함수 사용

DECODE 함수

예제

설명 요약

NVL을 이용한 연봉 계산

기본 연봉 계산

NVL을 적용한 연봉 재계산

SQL 기본 및 활용-2.DDL

SQL 기본 및 활용-4.TCL

0개의 댓글

방법 1: `EXTRACT` 함수 사용

방법 2: `TO_CHAR`와 `TO_NUMBER` 함수 사용