함수 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