Search Results for '오라클'


1 POSTS

  1. 2008.10.24 [펌]NVL2 함수

[펌]NVL2 함수

Posted 2008. 10. 24. 09:56 by maxmini MAXMINI

NVL2라는 함수 DECODE랑 조금 비슷한 놈 같기도 하고요..
참 편한놈이네용..

문법
NVL2(expr,expr1,expr2);


expr의 값이 null이 아닐 경우에는  expr1의 값을 반환 하고요 null일 경우에는 expr2의 값을 반환 합니다.



예제)

-- 보통 SQL문을 실행 했을 경우
SQL>  SELECT ename, comm FROM emp;

ENAME                      COMM
---------------- ----------
SMITH              
ALLEN                        300
WARD                        500
JONES              
MARTIN                     1400
BLAKE               
CLARK               
SCOTT              
KING                
TURNER                        0
ADAMS            



-- NVL함수를 사용 했을 경우

SQL>SELECT ename, NVL(comm, 0) comm  FROM emp;

ENAME                      COMM
---------------- ----------
SMITH                           0
ALLEN                        300
WARD                         500
JONES                           0
MARTIN                     1400
BLAKE                            0
CLARK                            0
SCOTT                           0
KING                               0
TURNER                         0
ADAMS                           0



-- NVL2함수를 사용 했을 경우

SQL>SELECT ename, NVL2(comm, 1, 0) FROM emp;
ENAME                      COMM
--------------- ----------
SMITH                          0
ALLEN                          1
WARD                          1
JONES                          0
MARTIN                        1
BLAKE                          0
CLARK                          0
SCOTT                         0
KING                             0
TURNER                        1
ADAMS                         0


http://www.oracleclub.com/article/2869

Tag : , , ,

Write your message and submit