[Oracle] NVL, NVL2, NULLIF

 

NVL과 NVL2

데이터 처리를 할 때 NULL 처리는 중요하며 오라클(oracle)에서는 이런 NULL 데이터 처리를 간편하게 하기 위해 NLV 함수를 제공한다.
오라클 외 다른 데이터베이스에서는 nvl 사용이 불가능하다.

 

NVL 함수

 

NVL( 대상 , null인 경우 값 )

NULL 값을 다른 값으로 바꿀 때 사용하며, 모든 데이터 타입에 적용이 가능하다.

 

-- 매니저가 없는 값을 0으로 바꾸어서 출력하는 예제.
SELECT empno, NVL(mgr, 0) mgr
  FROM emp  
 WHERE deptno = 10;
 
EMPNO      MGR
------- -------
  7782    7839
  7839       0
  7934    7782

 

NVL2 함수

NVL2라는 함수는 NVL함수의 DECODE 함수의 개념을 합쳤다고 생각하면 쉽다.

NVL2 함수는 DECODE함수와 비슷한 IF문의 기능을 가지고 있다.

 

NVL2( 대상, null 아닌경우 값, null인 경우 값 )

대상의 값이 NULL 인 경우와 NULL이 아닌 경우를 지정한 값으로 치환한다.

 

-- 매니저가 있는경우 1을 없는경우 0을 출력하는 예제이다.
SELECT empno, NVL2(mgr, 1, 0) mgr
  FROM emp  
 WHERE deptno = 10;
 
EMPNO        MGR
------- ----------
  7782          1
  7839          0
  7934          1

 

NULLIF

NULLIF(exp1, exp2)

exp1값과 exp2값이 동일하면 NULL을 그렇지 않으면 exp1을 반환한다.

NULLIF 함수는 아래와 같은 CASE문으로 변환 가능하다.

 

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

 

NULLIF 함수 내에 RAND()와 같은 시간에 종속적인 함수를 사용하지 않는 것이 좋다. 

이렇게 하면 함수가 두 번 평가되고 두 호출에서 다른 결과가 반환된다.

 

예제)  데이터가 포함되지 않은 예산 반환

budgets 테이블을 만들고, 데이터를 로드하고, NULLIF를 사용하여 current_year 또는 previous_year에 데이터가 포함되지 않는 경우 Null을 반환한다.

 

CREATE TABLE budgets (  
   dept           TINYINT,  
   current_year   DECIMAL(10,2),  
   previous_year  DECIMAL(10,2)  
);  
  
INSERT INTO budgets VALUES(1, 100000, 150000);  
INSERT INTO budgets VALUES(2, NULL, 300000);  
INSERT INTO budgets VALUES(3, 0, 100000);  
INSERT INTO budgets VALUES(4, NULL, 150000);  
INSERT INTO budgets VALUES(5, 300000, 300000);  
  
SELECT dept, NULLIF(current_year,  
   previous_year) AS LastBudget  
FROM budgets;
 
 
dept   LastBudget  
----   -----------  
1      100000.00  
2      null 
3      0.00  
4      null  
5      null