함수
데이터를 가공, 변환, 계산하여 원하는 형태로 출력
집계, 날짜 처리, 문자열 처리 등 반복 작업을 단순화
단일 행 함수
하나의 행을 입력받아 하나의 행을 출력하는 함수
문자형 함수
| 함수명 | 설명 | 예시 및 결과 예 |
|---|---|---|
UPPER() | 문자열을 모두 대문자로 변환 | UPPER('oracle') → 'ORACLE' |
LOWER() | 문자열을 모두 소문자로 변환 | LOWER('ORACLE') → 'oracle' |
INITCAP() | 각 단어의 첫 글자를 대문자로 변환 | INITCAP('hello world') → 'Hello World' |
LENGTH() | 문자열의 길이 반환 | LENGTH('abcde') → 5 |
SUBSTR() | 문자열의 일부를 잘라냄 (시작위치, 길이) | SUBSTR('abcdef', 2, 3) → 'bcd' |
INSTR() | 특정 문자 또는 문자열의 위치 반환 | INSTR('oracle', 'a') → 3 |
TRIM() | 앞뒤의 공백 또는 특정 문자 제거 | TRIM(' O ') → 'O' |
LTRIM() | 왼쪽 공백 또는 특정 문자 제거 | LTRIM(' hi') → 'hi' |
RTRIM() | 오른쪽 공백 또는 특정 문자 제거 | RTRIM('hi ') → 'hi' |
REPLACE() | 문자열 내 문자 치환 | REPLACE('banana', 'a', '*') → 'b*n*n*' |
TRANSLATE() | 문자열 내 문자들을 1:1 매핑 변환 | TRANSLATE('12345', '123', 'abc') → 'abc45' |
CONCAT() | 두 문자열 연결 | CONCAT('Hello', 'World') → 'HelloWorld' |
LPAD() | 문자열을 왼쪽에서부터 채움 (패딩) | LPAD('123', 5, '0') → '00123' |
RPAD() | 문자열을 오른쪽에서부터 채움 (패딩) | RPAD('123', 5, '*') → '123**' |
숫자형 함수
| 함수명 | 설명 | 예시 및 결과 예 |
|---|---|---|
ABS() | 절댓값 반환 | ABS(-5) → 5 |
CEIL() | 올림 (주어진 수보다 크거나 같은 정수 중 최소값) | CEIL(3.14) → 4 |
FLOOR() | 내림 (주어진 수보다 작거나 같은 정수 중 최대값) | FLOOR(3.99) → 3 |
ROUND() | 반올림 (소수점 자리 지정 가능) | ROUND(123.456, 2) → 123.46 |
TRUNC() | 소수점 이하 버림 | TRUNC(123.456, 2) → 123.45 |
MOD() | 나머지 연산 (modulo) | MOD(10, 3) → 1 |
POWER() | 거듭제곱 계산 | POWER(2, 3) → 8 |
SQRT() | 제곱근 계산 | SQRT(9) → 3 |
SIGN() | 수의 부호 반환 (양수=1, 0=0, 음수=-1) | SIGN(-5) → -1, SIGN(0) → 0 |
EXP() | 자연상수 e의 거듭제곱 반환 | EXP(1) → 2.718... |
LN() | 자연로그 (밑이 e) | LN(EXP(1)) → 1 |
LOG(n, b) | 밑이 b인 로그(log_b n) 반환 | LOG(100, 10) → 2 |
CHR(n) | 아스키 코드 값 n에 해당하는 문자 반환 | CHR(65) → 'A', CHR(10) → 줄바꿈 문자 |
날짜형 함수
| 함수명 | 설명 | 예시 및 결과 예 |
|---|---|---|
SYSDATE | 현재 날짜와 시간 반환 (DB 서버 시간 기준) | SYSDATE → 2025-05-15 14:30:00 |
CURRENT_DATE | 현재 날짜와 시간 반환 (사용자 세션 시간대 기준) | CURRENT_DATE → 2025-05-15 14:30:00 |
SYSTIMESTAMP | 현재 타임스탬프(날짜 + 시간 + 소수초 + 시간대) 반환 | SYSTIMESTAMP → 2025-05-15 14:30:00.123456 +09:00 |
ADD_MONTHS(d, n) | 날짜 d에서 n개월 더함 | ADD_MONTHS('2024-01-31', 1) → 2024-02-29 |
MONTHS_BETWEEN(d1, d2) | 두 날짜 사이의 개월 수 반환 | MONTHS_BETWEEN('2024-06-01', '2024-01-01') → 5 |
NEXT_DAY(d, '요일') | 날짜 d 이후의 다음 '요일' 날짜 반환 | NEXT_DAY(SYSDATE, 'MONDAY') → 다음 월요일 |
LAST_DAY(d) | 해당 월의 마지막 날 반환 | LAST_DAY('2024-02-15') → 2024-02-29 |
TRUNC(d [, fmt]) | 날짜 또는 시간 자름 (일/월/년 단위 절삭) | TRUNC(SYSDATE) → 2025-05-15 00:00:00 |
ROUND(d [, fmt]) | 날짜 반올림 (기준: 15일 기준 or 시간 기준) | ROUND(TO_DATE('2024-05-14'), 'MONTH') → 2024-05-01 |
| EXTRACT(YEAR | MONTH | DAY FROM d) | 날짜에서 연/월/일 추출 | EXTRACT(YEAR FROM SYSDATE) → 2025 |
변환형 함수
| 함수명 | 설명 | 예시 및 결과 예 |
|---|---|---|
TO_CHAR(expr [, fmt]) | 숫자 또는 날짜를 문자열로 변환 | TO_CHAR(12345) → '12345'TO_CHAR(SYSDATE, 'YYYY-MM-DD') → '2025-05-15' |
TO_DATE(str, fmt) | 문자열을 날짜로 변환 | TO_DATE('2024-01-01', 'YYYY-MM-DD') → 2024-01-01 |
TO_NUMBER(str [, fmt]) | 문자열을 숫자로 변환 | TO_NUMBER('123.45') → 123.45 |
CAST(expr AS datatype) | 명시적으로 데이터 타입을 변환 | CAST('123' AS NUMBER) → 123CAST(SYSDATE AS TIMESTAMP) |
TO_TIMESTAMP(str [, fmt]) | 문자열을 타임스탬프로 변환 | TO_TIMESTAMP('2024-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') |
TO_YMINTERVAL(str) | 문자열을 연/월 간격 간격 데이터로 변환 | TO_YMINTERVAL('02-06') → +02-06 (2년 6개월) |
TO_DSINTERVAL(str) | 문자열을 일/시간 간격 간격 데이터로 변환 | TO_DSINTERVAL('5 12:30:00') → +5 12:30:00 |
NUMTODSINTERVAL(n, 'UNIT') | 숫자를 일/시간 단위 간격으로 변환 | NUMTODSINTERVAL(2, 'DAY') → +2 00:00:00 |
NUMTOYMINTERVAL(n, 'UNIT') | 숫자를 연/월 단위 간격으로 변환 | NUMTOYMINTERVAL(3, 'MONTH') → +00-03 |
다중 행 함수
여러 행의 값을 입력 받아 하나의 행을 출력하는 함수
그룹 함수
| 함수명 | 설명 | 예시 및 결과 예 |
|---|---|---|
SUM() | 그룹 내 숫자 합계 | SUM(salary) → 총 급여 합계 |
AVG() | 그룹 내 숫자 평균 | AVG(salary) → 평균 급여 |
MAX() | 그룹 내 최댓값 | MAX(hire_date) → 가장 최근 입사일 |
MIN() | 그룹 내 최솟값 | MIN(salary) → 최저 급여 |
COUNT(*) | 그룹 내 전체 행 수 | COUNT(*) → 총 인원 수 |
COUNT(expr) | NULL 제외하고 지정 열의 값 개수 세기 | COUNT(dept_id) → 부서 ID가 있는 행 수 |
STDDEV() | 그룹의 표준편차 | STDDEV(salary) → 급여의 표준편차 |
VARIANCE() | 그룹의 분산 | VARIANCE(salary) → 급여의 분산 |
GROUPING() | ROLLUP 또는 CUBE 연산 시 NULL 여부 구분 | GROUPING(dept_id) = 1이면 집계 행 의미 |
MEDIAN() | 중앙값 (Oracle 10g 이상 지원) | MEDIAN(salary) → 중간 급여값 |
LISTAGG() | 문자열 그룹을 하나로 합쳐서 나열 | LISTAGG(job_id, ', ') WITHIN GROUP (ORDER BY job_id) |
COUNT(*)을 제외한 모든 그룹함수는 NULL 값을 자동으로 제외하고 연산한다
고급 함수
NVL
NULL 값을 포함하는 컬럼을 지정한 값으로 변경할 때 사용
- 모든 데이터 타입에 적용 가능
- 변경하려는 값은 컬럼의 데이터 타입과 일치해야 한다
NVL(email, 'empty@sample.com')DECODE
값을 비교하여 해당하는 값을 돌려주는 함수
DECODE(column1, compare_val1, result1, compare_val2, result2, ..., default)- default를 생략하면 NULL로 지정된다
DECODE 응용
행을 열로 바꾸기(PIVOT)
DECODE() 와 그룹함수를 함께 사용하여 행을 열로 바꿀 수 있다
SELECT
COUNT(DECODE(gender, 'M', 1)) AS male_count,
COUNT(DECODE(gender, 'F', 1)) AS female_count
FROM employees;CASE-WHEN-THEN
java의 if, else-if, else 와 동일
CASE column1
WHEN compare_val1 THEN result1
WHEN compare_val2 THEN result2
ELSE default
ENDPIVOT
데이터의 행과 열을 바꿔서 표현할 수 있다
- Oracle 11g부터 사용 가능하다
기본 사용법
SELECT *
FROM (
원본 테이블 또는 서브쿼리
)
PIVOT (
집계함수(집계대상컬럼)
FOR 피벗기준컬럼 IN (값1 AS 열1, 값2 AS 열2, ...)
);예시
SELECT *
FROM (
SELECT dept_id, title
FROM s_emp
)
PIVOT (
COUNT(*)
FOR title IN ('사원', '과장', '부장', '이사', '사장')
)
ORDER BY dept_id;ROLLUP
GROUP BY + 소계/총계를 자동으로 생성해주는 OLAP 집계 함수
기본 사용법
SELECT 그룹컬럼1, 그룹컬럼2, 집계함수(컬럼)
FROM 테이블
GROUP BY ROLLUP(그룹컬럼1, 그룹컬럼2);예시
SELECT region, month, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region, month);지역별 월별 금액 합을 보여주면서 WEST-NULL-550 과 같이 지역별 소계, NULL-NULL-1000 과 같이 총계도 함께 표시해 준다
CUBE
기본 사용법
SELECT 그룹컬럼1, 그룹컬럼2, 집계함수(컬럼)
FROM 테이블
GROUP BY CUBE(그룹컬럼1, 그룹컬럼2);예시
SELECT region, month, SUM(amount) AS total_sales
FROM sales
GROUP BY CUBE(region, month);CUBE 는 ROLLUP 에서 NULL-JAN-300 과 같이 월별 소계가 추가된 것이다. 즉, ROLLUP 은 그룹컬럼1, 총계까지만 표시하지만 CUBE 는 그룹컬럼2의 소계까지 표시한다
NULL 값 대체하기
ROLLUP 과 CUBE 둘 다 소계, 총계 부분에 NULL로 표시되는데 이 부분을 GROUPING 또는 GROUPING_ID 함수로 명확하게 표현할 수 있다
CASE WHEN GROUPING(month) = 1 THEN '전체' ELSE month END AS monthGROUPING 의 반환값
- 0 : NULL이 포함되지 않은 일반행
- 1 :
ROLLUP이나CUBE에 의해 자동생성되어 NULL이 포함된 행
RANK
행별 순위를 계산해서 보여주는 함수
RANK() OVER (ORDER BY 컬럼명 [ASC | DESC])
RANK() OVER (
PARTITION BY 컬럼명1
ORDER BY 컬럼명2 [ASC | DESC]
)두 번째 예시와 같이 그룹별 순위를 계산해서 표시할 수 있다
