- SQL 함수 : SELECT 절, WHERE 절, UPDATE 절 등에서 사용가능
1. 단일행 함수
▶ 숫자 함수
| 함수 |
기능 |
사용 예 |
| SIN, COS, TAN |
삼각함수(싸인, 코싸인, 탄젠트 결과값을 반환) |
SIN(1), COS(1), TAN(1) |
| ASIN, ACOS, ATAN |
삼각함수의 역함수(범위 : -1 ~ 1) |
ASIN(0.5), ACOS(0.5), ATAN(0.5) |
| ROUND(숫자, n) |
n 자리를 기준으로 반올림을 처리해주는 함수 |
ROUND(48.678, 2) → 소수점 이하 둘(두번째 파라미터 값)째자리까지 표현 / ROUND(48.674, 0) → 정수로 표현(0은 생략가능) / ROUND(48.674, -1) → 10의 자리까지 표현 |
| TRUNC |
절삭으로 처리해 주는 함수(버림) |
ROUND 함수와 사용방법 동일함 |
| MOD |
나머지를 반환하는 함수 |
MOD(5,2) → 5를 2로 나눈 나머지 반환 |
| POWER(숫자, n) |
n제곱한 결과를 반환하는 함수 |
POWER(5,3) → 5의 3승을 결과값으로 반환 |
| SQRT |
n제곱근 결과값을 반환하는 함수 |
SQRT(2) → 루트 2에 대한 결과값 반환 |
| LOG(m,n) |
밑을 m으로 한 n의 로그 값을 반환 |
LOG(10,20) |
| LN |
자연 로그 함수로 밑수가 e인 로그 함수 |
LN(95) |
| SIGN |
연산 결과값이 양수이면 1, 0이면 0, 음수면 -1 반환하는 함수. ※매출이나 수지와 관련하여 적자 및 흑자의 개념을 나타낼 때 종종 사용 |
SIGN(5-2) |
▶ 문자 함수
| 함수 |
기능 |
사용 예 |
| ASCII |
매개변수로 넘겨받은 문자 → 아스키코드값 반환 |
ASCII('A') |
| CHR |
매개변수로 넘겨받은 아스키코드값 → 문자 반환 |
CHR(65) |
| CONCAT(s1, s2) |
문자열 기반으로 데이터 결합을 수행하는 함수. 2개의 문자열만 결합가능. 내부적으로 숫자나 날짜를 문자로 변환해주는 과정이 포함되어 있음(최종적으로 문자형태로 결합) |
CONCAT(ENAME, JOB,DEPTNO) / CONCAT(’배가’,’고프다’ ) |
| LOWER |
모두 소문자로 반환 |
LOWER('oRaCLe') |
| UPPER |
모두 대문자로 반환 |
UPPER('oRaCLe') |
| INITCAP |
첫 글자만 대문자로 하고 나머지는 모두 소문자로 변환하여 반환 |
INITCAP('oRaCLe') |
| LPAD(s,n,c) |
Byte 를 확보하여 왼쪽부터 문자로 채우는 기능을 가진 함수 |
LPAD('ORACLE',10,'*') → 10Byte 공간 확보 후 ORACLE 문자열을 담고 남아있는 Byte 공간을 세 번째 파라미터 값으로 왼쪽부터 채움 |
| RPAD(s,n,c) |
Byte 를 확보하여 오른쪽부터 문자로 채우는 기능을 가진 함수 |
RPAD('ORACLE',10,'*') → 10Byte 공간 확보 후 ORACLE 문자열을 담고 남아있는 Byte 공간을 세 번째 파라미터 값으로 오른쪽부터 채움 |
| LTRIM(s1,s2) |
대상 문자열(s1)의 왼쪽부터 지정한 문자를 제거하여 반환. 대소문자 구분하며, 공백을 제거하는 함수로 많이 사용함 |
LTRIM('AAAAORACLEORACLE', 'ORA') = CLEORACLE |
| RTRIM(s1,s2) |
대상 문자열(s1)의 오른쪽부터 지정한 문자를 제거하여 반환 |
LTRIM 함수와 사용방법 동일함 |
| TRIM(c FROM s) |
대상 문자열(s)의 양쪽에서 지정된 문자를 삭제. 파라미터 값으로 문자열만 넣으면 양쪽 공백 제거 |
TRIM(' TEST ') |
| TRANSLATE |
1:1 로 바꿔줌 |
('010-9322-9543','0123456789','공일이삼사오육칠팔구') = 공일공-구삼이이-구오사삼 |
| REPLACE(s1, s2, s3) |
두 번째 파라미터 값이 있으면 세 번째 파라미터 값으로 바꿈 |
REPLACE('MY ORACLE SERVER ORAHOME', 'ORA', '오라') = MY 오라CLE SERVER 오라HOME |
| SUBSTR |
문자열을 추출하는 기능을 가진 함수. 첫 번째 파라미터 값은 대상 문자열, 두 번째 파라미터 값은 추출을 시작하는 위치(인덱스의 처음은 1), 세 번째 파라미터 값은 추출할 문자열의 갯수(생략시 끝까지) |
SUBSTR(ENAME, 1, 2) |
| SUBSTRB |
STBSTR 함수와 기능은 동일하나, 바이트 기반 |
STBSTR 함수와 사용방법 동일 |
| LENGTH |
글자 수 반환 |
LENGTH(ENAME) |
| LENGTHB |
바이트 수 반환 |
LENGTHB(ENAME) |
| INSTR(s1, s2, n, k) |
대상 문자열에서 해당 문자열이 나타나는 위치 반환. 첫 번째 파라미터 값은 대상문자열, 두 번째 파라미터 값은 해당 문자열, 세 번째 파라미터 값은 찾기 시작하는 위치, 네 번째 파라미터 값은 몇 번째 등장하는 값을 찾을 것인지에 대한 설정(값을 1로 설정할 경우 생략 가능) |
INSTR('ORACLE ORAHOME BIORA', 'ORA', 2, 1) = 8 |
| REVERSE |
대상 문자열을 거꾸로 반환. 단, 한글은 제외(사용불가) |
REVERSE('ORACLE') |
▶ 날짜, 시간 함수
| 함수 |
기능 |
사용 예 |
| ADD_MONTHS |
개월 수를 더하거나 빼주는 함수 |
ADD_MONTHS(SYSDATE,2) → 현재시간에서 2개월 후 |
| MONTHS_BETWEEN |
첫 번째 인자값에서 두 번째 인자값을 뺀 개월 수를 반환. 반환된 값이 음수, 양수이냐에 따라 과거, 미래를 알 수 있음 |
MONTHS_BETWEEN(SYSDATE, TO_DATE('2002-05-31', 'YYYY-MM-DD')) |
| NEXT_DAY |
첫번째 파라미터 값을 기준으로 가장 빠르게 돌아오는 요일 반환. 오라클안에 기본 세션 설정에서 국가 설정이 다르면 에러나는 경우가 있음. ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN'; |
NEXT_DAY(SYSDATE,'토') |
| LAST_DAY |
해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환 |
LAST_DAY(SYSDATE) |
| ROUND(DATE) |
날짜 반올림하여 반환 |
ROUND(SYSDATE, 'YEAR') → 연도까지 반올림(상반기, 하반기 기준) / ROUND(SYSDATE, 'MONTH') → 월까지 반올림(15일 기준) / ROUND(SYSDATE, 'DD') → 일까지 반올림(정오 기준) / ROUND(SYSDATE, 'DAY') → 일까지 반올림(수요일 정오 기준) |
| TRUNC(DATE) |
날짜 버림하여 반환 |
TRUNC(SYSDATE, 'YEAR') → 연도까지 버림 / TRUNC(SYSDATE, 'MONTH') → 월까지 버림 / TRUNC(SYSDATE, 'DD') → 일까지 버림 / TRUNC(SYSDATE, 'DAY') → 일까지 버림 |
| EXTRACT |
연도, 월, 일을 추출하여 숫자타입으로 반환 |
EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(DAY FROM SYSDATE) |
| TO_CHAR |
연도, 월, 일을 추출하여 문자타입으로 반환 |
TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'MM'), TO_CHAR(SYSDATE, 'DD') |
| TO_YMINTERVAL |
연부터 월까지의 간격 |
SYSDATE + TO_YMINTERVAL('01-02') = 1년 2개월 후의 날짜 |
| TO_DSINTERVAL |
일부터 초까지의 간격 |
SYSDATE + TO_DSINTERVAL('003 04:05:06') = 3일 4시간 5분 6초 후의 날짜 |
| SYSDATE |
현재 날짜(시간)을 반환 |
SYSDATE |
▶ 변환함수
| 함수 |
기능 |
| TO_CHAR |
숫자나 날짜 데이터를 문자 타입으로 변환시켜주는 함수 |
| TO_DATE |
문자 데이터를 날짜 타입으로 변환시켜주는 함수 |
| TO_NUMBER |
문자 데이터를 숫자 타입으로 변환시켜주는 함수 |
▷ TO_CHAR 활용
· 형식 맞춤 표기 결과값 반환
SELECT TO_CHAR(60000, '99,999') "COL2"
, TO_CHAR(60000, '$99,999') "COL3"
, TO_CHAR(60000, 'L99,999') "COL4" --> L : 어떤 통화가 들어갈지 모르는 영역을 비워둔 것
, LTRIM(TO_CHAR(60000, 'L99,999')) "COL4" --> (보통 왼쪽에 들어간 공백을 제거하고 조회함)
FROM DUAL;
--==>>
/*
60,000
$60,000
￦60,000
￦60,000
*/
· 날짜형 → 문자형
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "COL1" -- 2023-10-23
,TO_CHAR(SYSDATE, 'YYYY') "COL2" -- 2023
,TO_CHAR(SYSDATE, 'YEAR') "COL3" -- TWENTY TWENTY-THREE
,TO_CHAR(SYSDATE, 'MM') "COL4" -- 10
,TO_CHAR(SYSDATE, 'MONTH') "COL5" -- 10월
,TO_CHAR(SYSDATE, 'MON') "COL6" -- 10월
,TO_CHAR(SYSDATE, 'DD') "COL7" -- 23
,TO_CHAR(SYSDATE, 'MM-DD') "COL8" -- 10-23
,TO_CHAR(SYSDATE, 'DAY') "COL9" -- 월요일
,TO_CHAR(SYSDATE, 'DY') "COL10" -- 월
,TO_CHAR(SYSDATE, 'HH24') "COL11" -- 10
,TO_CHAR(SYSDATE, 'HH') "COL12" -- 10
,TO_CHAR(SYSDATE, 'HH AM') "COL13" -- 10 오전
,TO_CHAR(SYSDATE, 'HH PM') "COL14" -- 10 오전 (현재가 오전이면 오전으로 조회됨)
,TO_CHAR(SYSDATE, 'MI') "COL15" -- 30
,TO_CHAR(SYSDATE, 'SS') "COL16" -- 54
,TO_CHAR(SYSDATE, 'SSSSS') "COL17" -- 37873
,TO_CHAR(SYSDATE, 'Q') "COL18" -- 4 (4사분기)
FROM DUAL;
※ 'TO_DATE()' 함수를 통해 문자 타입을 날짜 타입으로 변환을 수행하는 과정에서 내부적으로 해당 날짜에 대한 유효성 검사가 이루어짐
▶ NULL 관련 함수
- NULL 의 처리 : NULL 은 상태의 값을 의미하며 실제 존재하지 않는 값이기 때문에 NULL 이 연산에 포함 될 경우 결과는 무조건 NULL임. NULL 은 실제 존재하는 값이 아니기 때문에 일반 연산자를 활용하여 비교할 수 없음
| 함수 |
기능 |
사용 예 |
| NVL |
첫 번째 파라미터 값이 NULL 이면, 두 번째 파라미터 값을 반환하며, 첫 번째 파라미터 값이 NULL 이 아니면, 그 값을 그대로 반환 |
NVL(NULL, 10) = 10 / NVL(5, 10) = 5 |
| NVL2 |
첫 번째 파라미터 값이 NULL 이 아닌 경우, 두 번째 파라미터 값을 반환하고 첫 번째 파라미터 값이 NULL 인 경우, 세 번째 파라미터 값을 반환 |
NVL2(COMM, SAL12+COMM, SAL12) |
| COALESCE |
매개변수 제한이 없는 형태로 인지하고 활용. 맨 앞에 있는 매개변수부터 차례로 NULL 인지 아닌지 확인하여 NULL 인 경우에는 그 다음 매개변수의 값을 반환. NVL, NVL2과 비교했을 때 모든 경우의 수를 고려할 수 있다는 특징이 있음 |
COALESCE(NULL, NULL, 30, NULL, NULL, 60) = 30 |
2. 분석함수
- 분석함수를 사용할 때는 OVER 절을 함께 사용해야 하며, OVER 절 내부에 PATITION BY 절을 사용하지 않으면 쿼리 결과 전체를 집계 (PARTITION BY 절을 사용하면 쿼리 결과에서 해당 칼럼을 그룹으로 묶어서 결과를 표시)
| 구분 |
함수 |
| 집계 |
COUNT, MAX, MIN, SUM, AVG |
| 순위 |
ROW_NUMBER, RANK, DENSE_RANK |
| 순서 |
FIRST_VALUE, LAST_VALUE, LAG, LEAD |
| 통계 |
STDDEV, VARIANCE |
| 비율 |
RATIO_TO_REPORT, CUME_DIST, PERCENT_RANK, NTITLE |