Function
입력값을 받아 로직을 돌려 반영값을 돌려줍니다.
변수에 원하는 입력 값을 집어 넣어 사용할 수 있기 때문에 유지 보수에 좋습니다.
오라클에서는 기본적으로 필요한 함수를 제공하지만, 필요하다면 새로 만들 수 있습니다.
함수명을 보고 아래의 정보를 떠올릴 수 있도록 공부해야 합니다.
- 어떤 타입의 파라미터(매개 변수)가 들어갈까?
- 몇 개의 파라미터가 들어갈까?
- 반환되는 값을 무엇일까?
DUAL 테이블
sys 계정에 있는 테이블입니다.
누구나 사용 가능하고 권한 부여가 가능합니다.
DUMMY 컬럼 하나만 존재하며 값은 'X'이고 데이터는 한 행만 존재합니다.
이번에 알아볼 Function, 함수를 실행하기 위해 DUAL 테이블을 사용할 것입니다.
입력 행 기준 분류
오라클 함수는 입력 행을 기준으로 single Row, multi Row 함수로 나뉩니다.
single row function
- 단일 행 기준 작업으로, 행당 하나의 결과 반환
- 입력 값으로 칼럼명이 들어가서, 기준이라는 표시를 줌
- multi row와는 달리
WHERE절에서 행의 데이터를 필터링하기 위해 사용 가능함.
multi row function
- 여러 행 기준 작업으로, 하나의 결과 반환
- 그룹 함수
Single Row Function
1. 문자 타입 입력 함수
* 대소문자 조작
LOWER
- 인자 : (char) 문자 타입 대문자
- 반환값 : (char) 소문자
UPPER
- 인자 : (char) 문자 타입 소문자
- 반환값 : (char) 대문자
INITCAP
- 인자 : (char) 문자 타입 대소문자
- 반환값 : (char) 첫 글자를 대문자로, 뒤의 글자를 소문자로 반환
* 문자열 조작
CONCAT (concatenation) : 문자열 합치기
- 인자 : (char, char2)
- 반환값 : (char) 결합된 문자열 1개
SUBSTR : 문자열 자르기
- 인자 : (char, num(시작점), num2(길이)) / (char, num(시작점))
오른쪽에서부터 시작하고 싶으면 음수로 입력
길이를 입력하지 않으면 마지막 글자까지 반환 - 반환값 : (char) 시작점, 길이로 지정한 문자열 일부분
INSTR : 문자열 중 특정 문자 위치
(왼쪽 부터 오른쪽 방향으로 검색)
(음수를 쓸 경우 반대 방향으로 검색)
- 인자 : (char, char2(찾는 문자열)) / (char, char2, num(시작점), num2(몇번째 단어))
- 반환값 : (num) 찾는 값의 위치 값. 찾는 단어가 없다면
0반환
LPAD : 왼쪽에 문자열 삽입 (RPAD 오른쪽에 삽입)
- 인자 : (char, num(길이), char2(공간 채울 문자열))
- 반환값 : (char) 공백을 지정한 문자로 채워 길이를 맞춘 문자열
TRIM : 문자열 시작 부분과 종료 부분의 공백을 삭제
- 인자 : (char) / (char2(삭제하고 싶은 문자열) FROM char)
- 반환값 : (char) 공백과 char2가 삭제된 문자열
REPLACE : 문자열의 특정 부분을 변경
- 인자 : (char, char2(바꾸고 싶은 문자열), char3(교체할 문자열)) / (char, char2(바꾸고 싶은 문자열) /
- 반환값 : (char) 특정 부분이 변경된 문자열. char3가 없을 경우 char2 삭제
SELECT 'HELLO' || ',' || 'WORLD',
CONCAT('HELLO', CONCAT(',', 'WORLD')) CONCAT,
--> HELLO, WORLD
SUBSTR('HELLO, WORLD', 1, 5) SUBSTR,
--> HELLO
LENGTH('HELLO, WORLD') LENGTH,
--> 11
INSTR('HELLO, WORLD', 'O') INSTR,
--> 5
INSTR('HELLO, WORLD', 'O', 6) INSTR2,
--> 9
LPAD('HELLO, WORLD', 15, '*') LPAD,
--> ***HELLO, WORLD
RPAD('HELLO, WORLD', 15, '_') RPAD,
--> HELLO, WORLD***
REPLACE('HELLO, WORLD', 'O', 'X') REPLACE,
--> HELLX, WXRLD
TRIM(' HELLO, WORLD ') TRIM,
--> HELLO, WORLD
TRIM('D' FROM 'HELLO, WORLD') TRIM2
FROM dual;2. 숫자 타입 입력 함수
숫자 조작
ROUND
- 인자 : (num, num2(반올림 위치))
- 반환값 : (num) 반올림된 값
TRUNC
- 인자 : (num, num2(절삭 위치))
- 반환값 : (num) 절삭된 값
MOD
- 인자 : (num, num2(나눌 값))
- 반환값 : (num) 나머지 값
SELECT ROUND(105.54, 1) round1,
--> 105.5
ROUND(105.54, 0) round2,
--> 106
ROUND(105.54, -1) round3,
--> 110
ROUND(105.54) round4,
--> 105
TRUNC(105.54, 1) trunc1,
--> 105.1
TRUNC(105.54, 0) trunc2,
--> 105
TRUNC(105.54, -1) trunc3,
--> 100
TRUNC(105.54) trunc4
--> 105
FROM dual;- TRUNC와 MOD로 몫과 나머지를 구할 수 있습니다.
SELECT empno, sal,
TRUNC(sal / 1000) --> 월급을 1000으로 나눈 값의 몫
MOD(sal, 1000) --> 월급을 1000으로 나눈 값의 나머지
FROM emp;3. DATE 타입 입력 함수
날짜/시간 조작
* DATE FORMAT
(1) YYYY, MM, DD…
(2) IW : 1년 동안의 주차. (1~53주차)
(3) D: 주간요일. 1~7까지 번호를 매김. (1:일, 2:월, 3:화, 4:수 ~ 7:토)
TO_DATE : 문자를 날짜로 형변환
- 인자 : (char, fomat)
- 반환값 : 날짜 타입. 날짜 타입으로 변환된 문자 값
TO_CHAR : 날짜를 문자로 형변환
- 인자 : (date, fomat)
- 반환값 : 문자 타입. 문자 타입으로 변환된 날짜 값
SYSDATE : 서버의 현재 시간
SELECT TO_DATE('20191231', 'YYYYMMDD') lastday,
TO_DATE('20191231', 'YYYYMMDD') - 5 lastday_before5,
FROM dual;SELECT SYSDATE,
SYSDATE + 1/24 시, -- 시에 1을 더하기
SYSDATE + 1/24/60 분, -- 분에 1을 더하기
SYSDATE + 1/24/60/60 초 -- 초에 1을 더하기
FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD'), -- 년월일
TO_CHAR(SYSDATE), -- 서버 설정 기본
TO_CHAR(SYSDATE, 'YYYY'),-- 년도만
TO_CHAR(SYSDATE, 'HH24') -- 시간만
FROM dual;SELECT SYSDATE, TO_CHAR(SYSDATE, 'IW'),
TO_CHAR(SYSDATE, 'D')
FROM dual;ROUND
- 인자 : (DATE, format)
- 반환값 : (date) 반올림된 날짜 값
TRUNC
- 인자 : (DATE, format)
- 반환값 : (date) 절삭된 날짜 값
MONTHS_BETWEEN
- 인자 : (start date, end date)
- 반환값 : (num) 두 개의 월 사이의 개월 수 간격
ADD_MONTHS
- 인자 : (date, number)
- 반환값 : (date) date로 부터 x개월 뒤의 날짜
NEXT_DAY
- 인자: (date, number)
- 반환값 : (date) date 이후의 가장 첫번째 주간일자에 해당하는 날짜
LAST_DAY
- 인자: (date)
- 반환값 : (date) date가 속한 월의 마지막 일자
SELECT ename, TO_CHAR(hiredate, 'YYYYMMDD HH24:mi:ss') hiredate,
TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate), 2) months_between,
ADD_MONTHS(SYSDATE, 5) add_months,
ADD_MONTHS(TO_DATE('19951223', 'YYYYMMDD'), 15) add_months2,
NEXT_DAY(SYSDATE, 1) next_day,
LAST_DAY(SYSDATE) last_day,
TO_DATE(TO_CHAR(SYSDATE,'YYYYMM') || 01, 'YYYYMMDD') first_day
FROM emp;4. 형변환
- 명시적 형변환 :
TO_DATE,TO_CHAR,TO_NUMBER직접 입력 - 묵시적 형변환 : sql이 알아서 실행 방법을 찾아냄
* NUMBER 형변환
TO_NUMBER(char, format) : (char) → (number)
TO_CHAR(date/num, format) : (number) → (character)
* format
9: 숫자0: 강제 0표시,: 1000자리 표시.: 소수점L: 화폐단위(사용자 지역)$:달러 화폐표시
SELECT :yyyymm, TO_CHAR(LAST_DAY(TO_DATE(:yyyymm, 'yyyymm')), 'dd') DT
FROM dual;
SELECT ename, sal, TO_CHAR(sal, 'L0009,999.00')
FROM emp;5. NULL 처리 함수
NVL
- 인자 : (expr(expression)1, expr2)
- 반환값 : expr1이 NULL 값이 아니면 expr1을 사용하고, NULL 값이면 expr2로 대체
SELECT empno, sal, comm, sal + NVL(comm, 0)
FROM emp;NVL2
- 인자 : (expr1, expr2, expr3)
- 반환값 : expr1이 NULL 값이 아니면 expr2를 반환, NULL이면 expr3을 반환
SELECT comm, sal, NVL2(comm, sal + comm, sal)
FROM emp;NULLIF
- 인자 : (expr1, expr2)
- 반환값 : expr1이 expr2과 같으면 NULL을 반환, 아니면 expr1을 반환
SELECT job, empno, sal, NULLIF(sal, 3000)
FROM emp;COALESCE
원하는 만큼 인자를 입력할 수 있는 가변 인자
스스로를 호출하는 재기 함수
- 인자 : (expr1...)
- 반환값 : 선택한 인자들 중에 NULL이 첫번째 값을 반환
--> Java로 표현하자면
if(ex1 != null)
sysout(ex1);
else
COALESCE(ex2, ex3....);
[...]
if(ex2 != null)
sysout(ex2);
else
COALESCE(ex3....);
[...]6. 조건 분기
CASE
: 참, 거짓을 판단하는 수식으로 값을 반환
CASE
WHEN expr1 비교식 THEN 반환값
WHEN expr2 비교식 THEN 반환값2
...
ELSE 반환값n
END SELECT ename, job, sal,
CASE
WHEN job = 'SALESMAN' THEN sal * 1.05 -- sal + sal * 0.05
WHEN job = 'MANAGER' THEN sal * 1.10
WHEN job = 'PRESIDENT' THEN sal * 1.20
ELSE sal * 1.0
END sal_bonus
FROM emp;DECODE
: 동등비교를 통해 값을 반환 (default를 쓰지 않으면 null 반환)
DECODE(expr1,
search1, return1, (expr1이 search1과 같으면 return1을 반환)
search2, return2,
search3, ruturn3,
...
[, default])
SELECT ename, job, sal,
DECODE(job,
'SALESMAN', sal * 1.05,
'MANAGER', sal * 1.10,
'PRESIDENT', sal * 1.20,
sal * 1.0) sal_bonus
FROM emp;