함수 3 (NVL, NVL2, NULLIF, COALESCE DECODE, CASE)
* NVL
- NVL(expr1, expr2) : expr1이 NULL이면 expr2를 반환
- 반환값은 expr1의 데이터값과 동일해야 함
ex) 급여, 커미션, 12*급여*커미션을 조회
1) 커미션의 널을 0으로 처리하지 않으면, 널값*연봉은 널로 되어 정확한 값이 나오지 않음
2) 커미션의 널을 NVL()를 통하여 0으로 처리하니 정상
select salary, commission_pct,NVL(commission_pct,0), 12*salary*commission_pct,12*salary*(1+commission_pct)
from employees;
>
SALARY COMMISSION_PCT NVL(COMMISSION_PCT,0) 12*SALARY*COMMISSION_PCT 12*SALARY*(1+NVL(COMMISSION_PCT,0))
------------------------------------------------------------------------------------------------------------------------
3500 0 42000
3100 0 37200
2600 0 31200
2500 0 30000
14000 0.4 0.4 67200 235200
13500 0.3 0.3 48600 210600
* NVL2
- NVL(expr1, expr2, expr3) : expr1이 NULL이 아니면 expr2를 반환, NULL이면 expr3를 반환
- 인수 expr1은 모든 데이터 유형 사용 가능
- 인수 expr1이 문자값이면 varchar2 유형이 됨
* NULLIF
- NULLIF(expr1, expr2)
- 두 표현식이 동일하면 NULL, 동일하지 않으면 첫번째 표현식(expr1)을 반환
ex)
select first_name, LENGTH(first_name) "성의길이",
last_name, LENGTH(last_name) "이름길이",
NULLIF(LENGTH(first_name),LENGTH(last_name)) "NULLIF결과"
from employees
where first_name in ('Kelly','Karen');
FIRST_NAME 성의길이 LAST_NAME 이름길이 NULLIF결과
----------------------------------------------------------------
Kelly 5 Chung 5
Karen 5 Colmenares 10 5
Karen 5 Partners 8 5
* COALESCE
- COALESCE(expr1, expr2 ..... exprn)
- expr1이 NULL이 아닌경우, expr1을 반환
- expr1이 NULL이고, expr2가 NULL이 아니면, expr2를 반환
- 앞의 모든 표현식이 NULL이면, exprn을 반환
* DECODE
- IF-THEN-ELSE 논리를 사용하여 조건부 조회를 쉽게 수행할 수 있음
- decode(expr, search1, result1
[, search2, result2, ...,]
[, default])
> expr이 search1이면 result1 반환, search2면 result2 반환, 이도저도 아니면 default 반환
cf. 9i에서 case 표현식 (의미는 decode와 동일, ansi sql준수하므로 타 DB와 동일한 구문임)
ex) JOB id에 따라 급여인상을 다르게 계산
>
select last_name, job_id, salary,
decode (job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) "계산된 급여"
from employees;
LAST_NAME JOB_ID SALARY 계산된 급여
--------------------------------------------------
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Austin IT_PROG 4800 5280
* CASE
- 9i에서 추가
- decode와 동일한 구문, ansi sql 준수 구문임
미션) 지난달의 첫째날, 마지막날을 계산하는 모듈(서버시간 기준)
>
select last_day(add_months(sysdate,-2))+1 "지난달의 첫째날",
last_day(add_months(sysdate,-1)) "지난달의 마지막날"
from dual;
지난달의 첫째날 지난달의 마지막날
2017-02-01 오전 11:13:18 2017-02-28 오전 11:13:18
미션) 성명과 커미션을 조회해서 사원이 커미션을 받지않으면 Not Paid라고 표시하시오
select last_name, commission_pct, nvl(to_char(commission_pct), 'Not Paid')
from employees;
LAST_NAME COMMISSION_PCT NVL(TO_CHAR(COMMISSION_PCT),'NOTPAID')
King Not Paid
Kochhar Not Paid
De Haan Not Paid
Hunold Not Paid
Ernst Not Paid
Austin Not Paid
Pataballa Not Paid
Lorentz Not Paid
Greenberg Not Paid
Faviet Not Paid
Chen Not Paid
Sciarra Not Paid
Urman Not Paid
Popp Not Paid
Raphaely Not Paid
Khoo Not Paid
Baida Not Paid
Tobias Not Paid
Himuro Not Paid
Colmenares Not Paid
Weiss Not Paid
Fripp Not Paid
Kaufling Not Paid
Vollman Not Paid
Mourgos Not Paid
Nayer Not Paid
Mikkilineni Not Paid
Landry Not Paid
Markle Not Paid
Bissot Not Paid
Atkinson Not Paid
Marlow Not Paid
Olson Not Paid
Mallin Not Paid
Rogers Not Paid
Gee Not Paid
Philtanker Not Paid
Ladwig Not Paid
Stiles Not Paid
Seo Not Paid
Patel Not Paid
Rajs Not Paid
Davies Not Paid
Matos Not Paid
Vargas Not Paid
Russell 0.4 .4
Partners 0.3 .3
Errazuriz 0.3 .3
Cambrault 0.3 .3
Zlotkey 0.2 .2
Tucker 0.3 .3
Bernstein 0.25 .25
Hall 0.25 .25
Olsen 0.2 .2
Cambrault 0.2 .2
Tuvault 0.15 .15
King 0.35 .35
Sully 0.35 .35
McEwen 0.35 .35
Smith 0.3 .3
Doran 0.3 .3
Sewall 0.25 .25
Vishney 0.25 .25
Greene 0.15 .15
Marvins 0.1 .1
Lee 0.1 .1
Ande 0.1 .1
Banda 0.1 .1
Ozer 0.25 .25
Bloom 0.2 .2
Fox 0.2 .2
Smith 0.15 .15
Bates 0.15 .15
Kumar 0.1 .1
Abel 0.3 .3
Hutton 0.25 .25
Taylor 0.2 .2
Livingston 0.2 .2
Grant 0.15 .15
Johnson 0.1 .1
Taylor Not Paid
Fleaur Not Paid
Sullivan Not Paid
Geoni Not Paid
Sarchand Not Paid
Bull Not Paid
Dellinger Not Paid
Cabrio Not Paid
Chung Not Paid
Dilly Not Paid
Gates Not Paid
Perkins Not Paid
Bell Not Paid
Everett Not Paid
McCain Not Paid
Jones Not Paid
Walsh Not Paid
Feeney Not Paid
OConnell Not Paid
Grant Not Paid
Whalen Not Paid
Hartstein Not Paid
Fay Not Paid
Mavris Not Paid
Baer Not Paid
Higgins Not Paid
Gietz Not Paid