[Oracle] NVL, NVL2, NULLIF, COALESCE

중첩 함수

함수를 여러 개 겹쳐서 사용하는 방법이다. 단일행 함수는 중첩하여 실행이 가능함.

안쪽 함수부터 실행하여 결과를 가장 가까운 바깥쪽 함수의 인수로 넘김.

F3( F2 ( F1(column, arg1) , arg2), arg3)

F1의 결괏값이 F2의 첫 인수로, F2의 결과는 F3의 첫 번째 인수로 사용된다.

 

- 주민등록번호에서 생년월일을 추출하여 'YY/MM/DD' 형태로 출력

SELECT TO_CHAR( TO_DATE( SUBSTR(idnum,1,6), 'YYMMDD' ), 'YY/MM/DD' )
    FROM student;

 

NVL : NVL 변환 함수

NULL을 0 또는 다른 값으로 변환하기 위한 함수

NULL은 미확인 값으로 어떠한 연산을 해도 NULL이 나온다.

NVL 함수를 이용하여 NULL을 숫자나 문자로 바꾸어 연산을 수행한다.

 

NVL(expression1, expression2)

expression1 : NULL을 포함하는 칼럼 또는 표현식

expression2 : NULL을 대체하는 값

* expression1과 expression2는 반드시 동일한 데이터 타입이어야 한다.

 

- 201번 학과 교수의 급여, 보직수당, 급여와 보직수당의 합계를 출력 ( 보직수당이 NULL이면 0으로 바꾸어 계산 )

SELECT sal, comm, sal+NVL(comm, 0)
    FROM professor
    WHERE deptno=201;

위의 sal+NVL(comm, 0)은 comm이 NULL인 경우 0으로 대체한 후 모든 행에 더하기 연산을 수행한다.

NVL(sal+comm, sal)는 comm이 NULL인 행을 제외하고 더하기 연산을 하기에 연산량이 감소함

NVL2 : NVL 확장 함수

 

NVL2(expression1, expression2, expression3)

expression1 : NULL을 포함하는 칼럼 또는 표현식

expression2 : expression1이 NULL이 아닐 때 반환되는 값

expression3 : expression1이 NULL일때 대체되는 값

 

- 102번 학과 교수중 보직수당을 받는 사람은 급여와 보직수당을 더한 값을 급여 총액으로 출력하라

( 보직 수당을 받지 않는 교수는 급여만 급여 총액으로 출력 )

SELECT sal, comm, NVL2(comm, sal+comm, sal)
    FROM profeossor
    WHERE deptno=102;

 

NULLIF 함수 : NVL 확장 함수

두 개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고, 일치하지 않으면 첫 번째 표현식 값을 반환

NULLIF(expression1, expression2)

 

- 교수 테이블에서 이름의 바이트 수와 사용자 아이디의 바이트 수를 비교해서 같으면 NULL을 반환하고, 같지 않으면 이름의 바이트 수를 반환하여라

SELECT name, userid, LENGTHB(name), LENGTHB(userid),
        NULLIF( LENGTHN(name), LENGTHN(userid) )
     FROM professor;

 

COALESCE 함수

인수중에서 NULL이 아닌 첫 번째 인수를 반환하는 함수.

N개의 인수중에서 1번째 인수값이 NULL이면 2번째 인수 값을 출력하는데 또 2번째 출력 값이 NULL이면

3번째 인수값을 출력한다.

COALESCE(expression-1, expression-2, ..., expression-n)

 

- 교수 테이블에서 보직수당이 NULL이 아니면 보직수당을 출력하고, 보직수당이 NULL이고 급여가 NULL이 아니면

급여를 출력, 보직수당과 급여가 NULL이면 0을 출력하라

SELECT COALESCE(comm, sal, 0)
    FROM professor;