내장함수
- SQL 작성 시 유용한 기능을 제공하는 함수
- RDBMS마다 조금씩 다름
| 공통점 | 차이점 |
|---|
| 함수 | 이름이 있는 코드블럭 | 객체에 종속되어 있지 않음 |
| 메서드 | 이름이 있는 코드블럭 | 객체에 종속되어 있음 |
오라클 내장함수(빌트인 함수)
단일행 함수
문자함수
대소문자 반환
- LOWER(컬럼): 소문자를 반환함
- UPPER(컬럼): 대문자를 반환함
SELECT LOWER(FIRST_NAME), UPPER(LAST_NAME)
FROM EMPLOYEES;
문자열 잘라내기
- SUBSTR(컬럼, 시작위치, 길이)
- ORACLE의 인덱스는 0이 아닌 1부터 시작하므로 참고 할 것
SELECT SUBSTR('오라클공부하기', 4)
FROM DUAL;
-- 공부하기
SELECT SUBSTR('오라클공부하기', -2)
FROM DUAL;
-- 하기
SELECT SUBSTR('오라클공부하기', 4, 2)
FROM DUAL;
-- 공부
SELECT SUBSTR('오라클공부하기', -5, 3)
FROM DUAL;
-- 클공부
문자열 길이 조회
- LENGTH(컬럼): 문자열의 길이를 반환
- LENGTHB(컬럼): 바이트 수 반환. 한글은 1자 당 3바이트
SELECT LENGTH('안녕하세요') -- 글자수 반환(5)
,LENGTHB('안녕하세요') -- 바이트 수 반환(15)
FROM DUAL;
ORDER BY LENGTH(FIRST_NAME) DESC;
-- 글자수 내림차순으로 정렬
문자열 채우기
- LPAD(컬럼, 글자 수, 패딩문자): 왼쪽부터 패딩문자로 자릿수를 채워줌
SELECT LPAD(10, 5) -- 10(앞에 공백 3칸)
,LPAD(10, 5, '0') -- 00010
,LPAD(10, 5, 'A') -- AAA10
- RPAD(컬럼, 글자 수, 패딩문자): 오른쪽부터 패딩문자로 자릿수를 채워줌
SELECT RPAD(10, 5) -- 10 (뒤에 공백 3칸)
,RPAD(10, 5, '#') -- 10###
문자열 연결하기
- CONCAT(컬럼명1, 2): 지정된 컬럼의 값을 이어붙임(2개가 최대)
SELECT FIRST_NAME, LAST_NAME, CONCAT(FIRST_NAME, LAST_NAME)
FROM EMPLOYEES;
- 컬럼명1 || 컬럼명2 || 컬럼명3||...||컬럼명N : 계속 이어붙일 수 있음
SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYESS;
불필요한 공백 없애기
- TRIM(컬럼명): 문자열 끝과 끝에 위치한 공백을 삭제함(문자 사이의 공백은 지우지 않음)
SELECT TRIM(' ABC DEF GHI ')
FROM DUAL;
문자열 바꾸기
- REPLACE(컬럼명, 찾는 문자열, 대체할 문자열)
SELECT FIRST_NAME, REPLACE(FIRST_NAME, 'a', '*')
FROM EMPLOYEES;
- FIRST_NAME의 A가 모두 *로 변경됨
문자열 찾기
- INSTR(컬럼명, '찾을 문자열'): 지정한 컬럼에서 찾는 문자의 위치를 숫자로 반환
SELECT INSTR(FIRST_NAME, 'a')
FROM EMPLOYEES;
-- 이름에 'a'가 들어가는 값의 위차가 반환됨
-- Neena -> 5반환
-- Laura -> 2반환(나타나는 최초값만 반환)
숫자함수
반올림
- round(반올림 할 숫자, 반올림 할 자리수)
- 자리수가 양의 정수면 소수점 자리수
- 자리수가 음의 정수면 일의 자리, 십의 자리, 백의자리 등으로 반올림
SELECT ROUND(123.45, 1), ROUND(123.45, 3)
,ROUND(123.45, -1), ROUND(123.45, -2)
FROM DUAL;
- 반올림할 자리수가 반올림할 숫자의 자리수를 초과하면 변동없이 숫자 그대로 출력됨
소수점 값 버리기
- ROUND(컬럼): 반올림 숫자를 지정하지 않으면 소수점 첫번째 자리에서 반올림함
- TRUNC(컬럼): 소수점 아래를 무조건 버림
SELECT ROUND(123.45), TURNC(123.45)
FROM DUAL;
-- ROUND는 124, TRUNC는 123이 출력됨
천장값과 바닥값
- CEIL(컬럼): 자신보다 큰 정수 중 가장 작은 정수 반환
- FLOOR(컬럼): 자신보다 작은 정수 중 가장 큰 정수 반환
SELECT CEIL(2.6), FLOOR(2.6)
FROM DUAL;
-- CEIL: 3, FLOOR: 2
나머지 구하기
- MOD(숫자1, 2): 첫번째 숫자를 두번째 숫자로 나누고, 나머지 반환
SELECT MOD(10,3), MOD(5,3)
FROM DUAL;
-- 각각 1, 2 출력
날짜함수
현재날짜
- SYSDATE: 시스템의 현재 날짜와 시간정보 반환
- 도구-환경설정-데이터베이스:NLS에서 형식 변환 가능
날짜 반올림
- ROUND(날짜): 해당 날짜의 시간이 정오를 넘었을 경우, 다음날 0시 0분 0초로 반환
SELECT ROUND(SYSDATE)
FROM DUAL;
시간정보 지우기
- TRUNC(날짜): 해당 날짜의 모든 시간정보를 0으로 바꾼 후 반환
SELECT TRUNC(SYSDATE)
FROM DUAL;
개월수 산출
- MONTHS_BETWEEN(날짜1, 날짜2): 두 날짜 사이의 개월수 표시
SELECT MONTHS_BETWEEN(SYSDATE, HIR_DATE)
FROM EMPLOYEES
-- SYSDATE와 HIRE_DATE 간의 개월수 반환
개월수 더하기
- ADD_MONTHS(날짜, 개월수): 날짜에서 지정된 개월 수 만큼 변경 된 날짜 반환
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -3), ADD_MONTHS(SYSDATE, 3)
FROM DUAL;
날짜 연산
- 날짜+숫자: 날짜에서 지정된 숫자만큼 일수가 경과된 날짜 반환
- 날짜-숫자: 날짜에서 지정된 숫자만큼 일수가 감소 된 날짜 반환
- 날짜-날짜: 두 날짜 사이의 일수 반환
- 날짜+날짜: 오류 발생
- 날짜+숫자/24: 지정된 숫자만큼 시간을 증가시킴 +1/24(1시간 증가)
- 날짜-숫자/24: 지정된 숫자만큼 시간을 감소시킴 -1/24(1시간 감소)
기타함수
NVL
- NVL(컬럼명, NULL일 때 대체값): 컬럼이 NULL일 때 대체값 반환.
컬럼값과 대체값의 데이터 타입이 같아야 함.
- NVL2(컬럼명, 값1, 값2): 컬럼 값이 NULL이 아니면 값1을 반환하고 NULL이면 값2를 반환
컬럼값과 대체값의 데이터 타입이 같아야함.
CASE
CASE
WHEN 조건식1 THEN 표현식1
WHEN 조건식2 THEN 표현식2
...
ELSE 표현식3
END
- TURE인 조건식의 표현식을 반환
- 모두 FALSE일 경우 ELSE 뒤의 표현식3을 반환
- 자바의 IF ELSE구문과 비슷함
CASE 값
WHEN 값1 THEN 표현식1
WHEN 값2 THEN 표현식2
...
ELSE 표현식3
END
- CASE 뒤의 값이 WHEN 뒤의 값과 일치하면, 해당하는 표현식 반환
- 모두 FALSE이면 ELSE 뒤의 표현식3 반환
- 자바의 SWITCH구문과 비슷함
DECODE
DECODE(컬럼명 값1, 표현식1,
값2, 표현식2,
...
표현식4)
- 값과 일치하는 표현식을 반환함
- Equal 비교만 가능
- 값이 모두 false 일 경우 표현식4를 반환하고, 표현식4가 지정되어 있지 않으면 null반환
변환함수
- 묵시적형변환: RDBMS가 자동으로 데이터 타입을 변환함
SELECT '1000' * 10, '1000'/10
FROM DUAL;
-- '1000'은 문자열이지만 자동으로 숫자로 변환됨
- 명시적형변환: 변환함수를 이용해 데이터 타입을 변환함
SELECT TO_CHAR(123.456, '999.999')
FROM DUAL;
-- 출력값: 123.456
SELECT TO_CHAR(123, '00000')
FROM DUAL;
-- 출력값: 00123
- 문자👉숫자: TO_NUMBER('문자', '패턴')
SELECT TO_NUMBER('100,000', '999,999')
FROM DUAL;
-- 출력값: 100000
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD') AS 변환날짜
FROM DUAL;
-- 출력값: 2021-10-25
- 문자👉날짜: TO_DATE('날짜형식의문자', '날짜패턴')
SELECT TO_DATE('20211025_123456', 'YYYYMMDD_HH24MISS')
FROM DUAL;
-- 출력값: 20211025_오후12:34:56
- 패턴문자
- 9: 딱 떨어지게 계산되며 주로 정수를 다룰때 사용
- 0: 빈자리에 0이 채워지며 주로 소수를 다룰때 사용
- , : 천단위 표시
- . : 소수점 표시
- FM: 문자열 공백 제거
다중행 함수
- group by절로 묶어서 그룹 당 하나의 결과 계산
- sum(컬럼명): 해당 컬럼의 합계 반환. null행 제외
- avg(컬럼명): 해당 컬럼의 평균값 반환. null행 제외
- max(컬럼명): 해당 컬럼의 최대값 반환. null행 제외
- min(컬럼명): 해당 컬럼의 최소값 반환. null행 제외
- count(＊ 혹은 컬럼명): 행의 갯수 반환. *인 경우 해당 컬럼 값이 null이어도 포함시킴. 컬럼명을 적으면 해당 컬럼이 null 일 시 제외됨
