오라클 함수 종류
1) 내장 함수(built-in function): 오라클에서 기본으로 제공
2) 사용자 정의 함수(user-defined function): 사용자가 필요에 의해 정의한 함수
내장함수의 종류
1) 단일행 함수(single-row function): 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
2) 다중행 함수(multiple-row function): 여러 행이 입력되고 하나의 행으로 결과 반환되는 함수
단일행 함수
1. 문자 함수
- 대소문자
- UPPER(문자열): 모두 대문자로
- LOWER(문자열): 모두 소문자로
- INITCAP(문자열): 첫 글자는 대문자, 나머지는 소문자로
SELECT *
FROM EMP
WHERE UPPER(ENAME) = UPPER('Jay');
// 사원이름이 jay, JAY, Jay 등 대소문자에 구애받지 않고 조회할 때 사용가능
- 문자열 길이
- LENGTH(문자열): 문자열의 길이
- LENGTH(문자열): 바이트 수 길이
- 문자열 추출
- SUBSTR(문자열 데이터, 시작 위치, 추출 길이): 문자열 데이터의 시작 위치부터 추출 길이만큼 추출. 시작 위치가 음수일 경우 마지막 위치부터 거꾸로.
- SUBSTR(문자열 데이터, 시작 위치): 문자열 데이터의 시작 위치부터 끝까지 추출
- ※ 시작 위치는 1이 첫번째
SELECT JOB
, SUBSTR(JOB, -LENGTH(JOB)) // 전체
, SUBSTR(JOB, -LENGTH(JOB), 2)
, SUBSTR(JOB, -3)
FROM EMP;
// 다른 함수의 결과 값을 SUBSTR의 입력값으로 사용 가능
- 문자 위치 찾기
- INSTR([대상 문자열 데이터(필수)], [찾을 부분 문자(필수)], [대상 문자열 데이터의 위치(선택, 기본값 1)], [찾으려는 문자가 몇 번째인지 지정(선택, 기본값 1)])
SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1
, INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2
, INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
FROM DUAL;
// 결과값 3, 12, 4
- 문자 바꾸기
- REPLACE([문자열 데이터(필수)], [찾는 문자(필수)], [대체할 문자(선택)])
- ※ 대체할 문자를 넣지 않으면 삭제된다.
- 빈 공간 문자로 채우기
- LPAD([문자열 데이터(필수)], [데이터 자릿수(필수)], [채울 문자(선택)]): 남는 빈 공간을 왼쪽에 채운다
- RPAD([문자열 데이터(필수)], [데이터 자릿수(필수)], [채울 문자(선택)]): 남는 빈 공간을 오른쪽에 채운다
- ※ 채울 문자가 없으면 공백으로 띄어쓰기 출력
- 문자열 합치기
- CONCAT(문자열1, 문자열2): 1과 2를 합친다
SELECT CONCAT(EMPNO, CONCAT(' : ', ENAME))
FROM EMP
WHERE ENAME = 'JAY';
// 결과값: 7788 : JAY
- 특정 문자 지우기
- TRIM([삭제 옵션(선택)], [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)]
- 삭제할 문자가 없으면 공백이 제거됨. 삭제할 문자가 모두 삭제되는 것이 아니라 방향에 따라 하나씩만 삭제됨.
- 삭제 옵션: LEADING(왼쪽), TRAILING(오른쪽), BOTH(양쪽)
- LTRIM([원본 문자열 데이터(필수), [삭제할 문자 집합(선택)])
- RTRIM([원본 문자열 데이터(필수), [삭제할 문자 집합(선택)])
- ※ 집합에 포함되지 않은 문자가 나올 때까지 문자 집합 모두 삭제
|| 연산자
CONCAT과 유사하게 열이나 문자열을 연결
2. 숫자 함수
- 반올림
- ROUND([숫자(필수)], [반올림 위치(선택)])
- ※ 반올림 위치를 지정하지 않으면 소수점 첫 번째 자리에서 반올림
SELECT ROUND(1234.56)
, ROUND(1234.56, 0)
, ROUND(1234.56, 1)
FROM DUAL;
// 결과값: 1235, 1235, 1234.6
- 버리기
- TRUNC([숫자(필수)], [버림 위치(선택)])
- ※ 버림 위치를 지정하지 않으면 소수점 첫 번째 자리에서 버림
- 올림과 내림
- CEIL([숫자(필수)]): 올림
- FLOOR([숫자(필수)]): 내림
- 나머지
- MOD([나눗셈 될 수(필수), [나눌 수(필수)])
3. 날짜 함수
| 연산 | 설명 |
| 날짜 데이터 + 숫자 | 숫자만큼 일수 이후의 날짜 |
| 날짜 데이터 - 숫자 | 숫자만큼 일수 이전의 날짜 |
| 날짜 데이터 - 날짜 데이터 | 두 날짜 간의 일수 차이 |
| 날짜 데이터 + 날짜 데이터 | 연산불가 |
- 현재 날짜와 시간 SYSDATE
- 몇 개월 이후 날짜
- ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(필수)])
- 개월 수 차이
- MONTHS_BETWEEN([날짜 데이터1(필수)], [날짜 데이터2(필수)])
- 비교 위치에 따라 양수, 음수가 나올 수 있음
- 소수점까지 나오므로 TRUNC로 버리기
- 돌아오는 요일
- NEXT_DAY([날짜 데이터(필수)], [요일 문자(필수)])
- 마지막 날짜
- LAST_DAY(날짜 데이터(필수)])
4. 형 변환 함수
- TO_CHAR()
- TO_NUMBER()
- TO_DATE()
5. NULL처리 함수
- NVL, NVL2: 특정 열의 데이터가 NULL일 때, 연산 수행을 위해 데이터를 다른 값으로 대체해 주어야 할 때 사용
- NVL([NULL인지 확인할 데이터(필수)], [NULL일 경우 반환할 데이터(필수)])
- NVL([NULL인지 확인할 데이터(필수)], [NULL이 아닐 경우 반환할 데이터(필수)], [NULL일 경우 반환할 데이터(필수)])
6. 상황에 따른 데이터 반환 함수
- DECODE 함수
SELECT EMPNO, ENAME,
DECODE(JOB, // 검사 대상이 될 데이터, 연산이나 함수의 결과
'MANAGER', SAL*1.1,
'SALESMAN', SAL*1.3,
SAL*1.5) AS UPSAL // 위에 해당하는 조건이 없을 때 반환할 결과
FROM DUAL;- CASE문
SELECT EMPNO, ENAME,
CASE JOB
WHEN 'MANAGER' THEN SAL*1.1
WHEN 'SALESMAN' THEN SAL*1.3
ELSE SAL*1.5
END AS UPSAL
FROM DUAL;※ 기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE함수와 달리 CASE문은 각 조건에 사용하는 데이터가 서로 상관없어도 된다. 또 기준 데이터 값이 같은(=) 데이터 외에 다양한 조건을 사용할 수 있다.
※ DECODE와 CASE는 모두 조건별로 동일한 자료형의 데이터를 반환해야 한다.
다중행 함수
※ 다중행 함수는 하나의 행으로 출력이 나오기 때문에 SELECT절에 여러 행이 결과로 나오는 열을 같이 사용할 수 없다.
NULL은 제외된다.
- 합계 SUM()
- 개수 COUNT()
- 최댓값, 최솟값 MIN(), MAX()
- 평균 AVG()