데이터 처리와 가공을 위한 오라클 함수
오라클 함수의 종류
1. 내장 함수
1) 단일행 함수(single-row function) : 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
2) 다중행 함수(multiple-row function): 여러 행이 입력되어 하나의 행의 결과로 반환되는 함수2. 사용자 정의 함수
문자 데이터를 가공하는 문자함수
-
1. 대,소문자를 바꿔 주는 UPPER, LOWER, INITCAP 함수
UPPER() : 소문자 -> 대문자
LOWER() : 대문자 -> 소문자
INITCAP() : 첫글자만 대문자로 -
2. 문자열 길이를 구하는 LENGTH 함수
참고) DUAL 테이블
-
3. 문자열 일부를 추출하는 SUBSTR 함수
4. 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수
-1번 위치부터 시작
-키워드를 못찾은 경우 0을 반환
INSTR(컬럼, '찾는 키워드')
INSTR(컬럼, '찾는 키워드', 검색 시작 위치)참고)
indexOf와 비슷 - 특정 문자열 위치를 찾음
5. 특정 문자를 다른 문자로 바꾸는 REPLACE 함수
REPLACE(컬럼, '찾는 문자열','치환된 문자열')
6. 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
1) LPAD : 왼쪽 패딩
LPAD(컬럼, 문자열, 자리수, '채워넣을 문자');
2) RPAD : 오른쪽 패딩
RPAD(컬럼 또는 문자열, 자리수, '채워넣을 문자')-
7. 두 문자열 데이터를 합치는 CONCAT 함수
-> 문자열 2개를 1개로 결합
-
8. 문자열 데이터를 연결하는 || 연산자
-> ENAME||':'||JOB
SELECT EMPNO, ENAME, JOB, CONCAT(CONCAT(ENAME, ':'), JOB) FROM EMP;
SELECT EMPNO, ENAME, JOB, ENAME || ' : ' || JOB FROM EMP;9. 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
TRIM - 양쪽 여백 제거
TRIM(LEADING FROM 문자열) : 왼쪽 여백 제거
TRIM(TRAILING FROM 문자열) : 오른쪽 여백 제거
SELECT TRIM(' ABC ') || 'DEF' FROM DUAL;
SELECT TRIM(TRAILING FROM' ABC ') || 'DEF' FROM DUAL;
SELECT TRIM(LEADING FROM' ABC ') || 'DEF' FROM DUAL;TRIM(LEADING FROM '*') : 왼쪽 문자 제거
SELECT TRIM(LEADING '_' FROM'_____ABC_____') || 'DEF' FROM DUAL;숫자 데이터를 연산하고 수치를 조정하는 숫자 함수
1.특정 위치에서 반올림하는 ROUND
2.특정 위치에서 버리는 TRUNC 함수
TRUNC: 절사
3.지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수
CEIL : 올림
FLOOR : 버림
- 숫자를 나눈 나머지 값을 구하는 MOD 함수
MOD : 나머지
날짜 데이터를 다루는 날짜 함수
1.DATE형 데이터는 다음과 같이 간단한 연산이 가능
1) 날짜 데이터 + 숫자 : 숫자만큼 일수가 더해진다.
2) 날짜 데이터 - 숫자 : 숫자만큼 일수가 빼진다.
3) 날짜 데이터 - 날짜 데이터 : 날짜 사이 일수 차이
4) 날짜 데이터 + 날짜 데이터 : 오류 발생2. SYSDATE 함수를 사용하여 날짜 출력하기
SYSDATE : 현재 시스템의 날짜 시간
3. 몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수
-월 단위로 가감
4. 두 날짜 간의 개월 수 차이
SELECT HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;5. 돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수
NEXT_DAY(날짜, 요일) : 다음 요일의 날짜
SELECT NEXT_DAY(SYSDATE, '목') "다음 목요일" FROM DUAL; 참고) DAY: 요일
LAST_DAY(날짜) : 날짜에 해당하는 월의 마지막 일자
SELECT LAST_DAY(SYSDATE) "마지막 일자" FROM DUAL; 자료형을 변환하는 형 변환 함수
- 자동 형 변환, 암시적 형변환
'2024-05-09' : 문자 -> DATE 형 변환
'1000' : 문자 -> 연산시 -> NUMBER 형으로 변환
SELECT '1000' + 200 FROM DUAL;-> 형식을 오라클이 충분히 파악 가능한 경우
형변환 함수 - TO_CHAR, TO_NUMBER, TO_DATE
- 날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
SELECT EMPNO, ENAME, TO_CHAR(SAL, '$999,999') FROM EMP; - 문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수
SELECT TO_NUMBER('1,000,000', '999,999,999') + 200 FROM DUAL;- 문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수
형식
YYYY -> 4자리 연도 : 2024
YY -> 2자리 연도 24
RRRR -> 4자리 연도
RR -> 2자리 연도
MM : 월
DD : 일
HH24 : 24시간 표기 시간
HH12 / HH : 12시간 표기 시간
MI : 분
SS : 초NULL 처리 함수
-연산 X
100 + NULL
1. NVL 함수
NVL(컬럼, 기본값)
SELECT
EMPNO, ENAME, SAL, COMM,
SAL * 12 + NVL(COMM,0) 연봉
FROM EMP;- NVL2 함수
-삼항조건 연산자와 비슷
NVL2(컬럼, NULL이 아닐때 값, NULL일때 값)
SELECT
EMPNO, ENAME, SAL, COMM,
SAL * 12 + NVL(COMM, 0) 연봉,
NVL2(COMM, 'O', 'X') "커미션 유무"
FROM EMP;상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문
-switch ~ case와 유사한 문법
- DECODE 함수
: 오라클 전용 함수
SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB,
'MANAGER', SAL * 1.1,
'SALESMAN', SAL * 1.05,
'ANALYST', SAL,
SAL * 1.03) "내년 급여"
FROM EMP;- CASE 문
: 표준 함수
SELECT EMPNO, ENAME, JOB, SAL,
CASE JOB
WHEN 'MANAGER' THEN SAL * 1.1
WHEN 'SALESMAN' THEN SAL * 1.05
WHEN 'ANALYST' THEN SAL
ELSE SAL * 1.03
END "내년 급여"
FROM EMP;