Oracle Null 치환 함수 - NVL, NVL2, NULLIF, COALESCE

Oracle Null 치환 함수

오라클 조회 시 컬럼의 값이 NULL값을 체크하여 다른 값으로 치환하는 함수에는 NVL, NVL2, NULLIF, COALESCE 함수 등이 있습니다.

 

NVL

• NVL 함수는 NULL 값을 다른 값으로 변경할때 사용하며 모든 데이터 타입에 적용이 가능하다.
• NVL(expr1, expr2) : expr1값이 Null인 경우 expr2 값으로 변경되어 출력된다.
• expr1이 컬럼인 경우 expr2는 expr1과 데이터 타입이 같아야 한다.
  ex) expr1 : Number --> expr2 : Number로 변환 가능해야함.

SELECT NVL(NULL, 'Null 이면 출력') AS NVL_1
     , NVL('Null 아니면 출력', NULL) AS NVL_2
  FROM DUAL

 

 

 

SELECT EMPNO, ENAME, JOB, MGR
	 , NVL(MGR, '999') AS MGR -- 정상출력
	 -- MGR 컬럼이 Number Type으로 'ORA-01722 수치가 부적합' 오류 발생
	 , NVL(MGR, 'AAA') AS MGR
  FROM EMP A
 WHERE MGR IS NULL

 

 NVL2

• NVL2(expr, expr1, expr2)
• expr의 값이 NULL이 아닐 경우에는 expr1의 값을 반환 하고, NULL일 경우에는 expr2의 값을 반환 한다.

-- 매니저가 있는 경우 'Y', 없는 경우 'N' 출력
SELECT MGR
     , NVL2(MGR, 'Y', 'N') AS MGR_YN
     , A.*
  FROM EMP A
 WHERE DEPTNO = 10

 

NULLIF

• NULLIF(exp1, exp2)
• exp1값과 exp2값이 동일하면 NULL을 그렇지 않으면 exp1을 반환
• CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

-- JOB이 'SALESMAN' 이면 Null 반환
SELECT JOB
     , NULLIF(JOB, 'SALESMAN') AS JOB2
     , A.*
  FROM EMP A

 

COALESCE

• COALESCE(expr1,expr2,expr3,…)
• expr1이 NULL이 아니면 expr1값을 그렇지 않으면 COALESCE(expr2,expr3,…)값을 반환.
• NVL 함수와 비슷하다.

SELECT MGR
     , COALESCE(MGR, 111) AS COAL_MGR
     , COMM
     , COALESCE(COMM, 222) AS COAL_COMM
  FROM EMP A

 

관련글

 구루비 Oracle 강좌 : http://www.gurubee.net/lecture/1880