by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1. NULL 처리 함수 이해하기.




Null

  • 컬럼에 값이 존재하지 않는 것
  • NOT NULL제약이나 Primary Key 제약이 걸려있지 않는 컬럼은 NULL데이터를 포함
  • 다른값과 연산을 수행하면 NULL값을 리턴
    Ex) NULL + 100 = NULL
  • 연산을 하기 위해선 특정 내장 함수를 사용(NVL, NVL2)
    : NULL값을 숫자 또는 문자열로 변환

1. NULL처리 함수 이해하기

1) NVL()

---------------------------------------------
Syntax : NVL(expr1, expr2)
---------------------------------------------

  • NVL은 NULL 값을 다른 값으로 대체하기 위한 함수
  • expr1이 NULL이면 expr2를 리턴하고, expr1이 NOT NULL이면 expr1을 리턴
  • expr1과 expr2는 여러 데이터 타입을 가질 수 있는데, 서로 데이터 타입이 다르면 expr1의 데이터 타입을 리턴
  • 사용예제
    1-1). DATE 데이터 타입
    SQL> SELECT NVL(c2, '01/JAN/2011') as "Date"
         FROM   NULL T
         WHERE  c2 is null
         AND    rownum <= 1;
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    
    SQL> ALTER SESSION SET nls_date_format = 'DD/MON/YYYY';
    
    SQL> SELECT NVL(c2, '01/JAN/2011' ) as "Date"
         FROM   NULL_T
         WHERE  c2 is null
         AND    rownum <= 1;
    
    Date                                          
    ------------                                  
    01-JAN-11                                     
    
    1-2). CHARACTER 데이터 타입
    SQL> SELECT NVL(c3, 'Null Data') AS "Character"
         FROM   NULL_T
         WHERE  c3 iS NULL
         AND    ROWNUM <= 1;
    
    Character                                      
    --------------------
    Null Data                                      
    
    SQL> SELECT NVL(c3, 100) AS "Character"
         FROM   NULL_T
         WHERE  c3 iS NULL
         AND    ROWNUM <= 1;
    
    Character                              
    --------------------                   
    100                                    
    
    1-3). NUMBER 데이터 타입
    SQL> SELECT NVL(c4, 'Null Data') AS "Number"
         FROM   NULL_T
         WHERE  c4 iS NULL
         AND    ROWNUM <= 1;
    
    SELECT NVL(c4, 'Null Data') AS "Number"
                   *
    ERROR at line 1:
    ORA-01722: invalid number 
    
    SQL> SELECT NVL(c4, '10') AS "Number"
         FROM   NULL_T
         WHERE  c4 iS NULL
         AND    ROWNUM <= 1;
    
        Number                           
    ----------                           
            10                           
    
    SQL> SELECT NVL(c4, 10) AS "Number"
         FROM   NULL_T
         WHERE  c4 iS NULL
         AND    ROWNUM <= 1;
    
        Number                         
    ----------                         
            10                         
    

2) NVL2()

----------------------------------------------------------
Syntax : NVL2(expr1, expr2, expr3)
----------------------------------------------------------

  • NVL2는 expr1 컬럼의 데이터가 NULL or NOT NULL 여부에 따라 서로 다른 값을 추출하고자 할 때 사용되는 함수
  • NVL2를 통해 추출되는 데이터
    expr1 --> NOT NULL --> Return:expr2
    expr1 --> NULL --> Return:expr3
  • NVL은 expr1 컬럼의 NULL인 데이터만 expr2로 대체하고, NOT NULL인 경우에는 expr1 컬럼의 데이터 자체를 조회하나
    NVL2는 expr1 컬럼이 NOT NULL인 경우에도 컬럼의 데이터가 아닌 특정 값으로 대체를 할 수 있으므로 다방면으로 활용 가능
  • expr2와 expr3의 데이터 타입은 LONG 데이터 타입을 제외한 모든 데이터 타입은 가능
  • expr2와 expr3 데이터 타입이 다른 경우에는 expr2의 의해 데이터 타입이 결정되게 되는데 데이터 타입은 아래와 같다.
    expr2(CHAR) vs.expr3(CHAR) --> CHAR
    expr2(CHAR) vs.expr3(NUMBER) --> CHAR
    expr2(NUMBER) vs.expr3(NUMBER --> NUMBER
    expr2(NUMBER) vs.expr3(CHAR) --> ERROR(expr3가 '100'형태 제외)
Ex1>
SQL> WITH null_t_temp AS (SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NULL
                          AND    ROWNUM <= 1
                          UNION ALL
                          SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NOT NULL
                          AND    ROWNUM <= 1)
     SELECT c3
           ,NVL2(c3, 'Not Null', 'Null') AS Return_Data
     FROM  null_t_temp;

C3                   RETURN_DATA                       
-------------------- ----------------                  
                     Null                              
15-SEP-11            Not Null                          
Ex2>
SQL> WITH null_t_temp AS (SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NULL
                          AND    ROWNUM <= 1
                          UNION ALL
                          SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NOT NULL
                          AND    ROWNUM <= 1)
     SELECT c3
           ,NVL2(c3, 100, 100) AS Return_Data
     FROM  null_t_temp;

C3                   RETURN_DATA               
-------------------- -----------               
                             100               
15-SEP-11                    100      
Ex3>
SQL> WITH null_t_temp AS (SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NULL
                          AND    ROWNUM <= 1
                          UNION ALL
                          SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NOT NULL
                          AND    ROWNUM <= 1)
     SELECT c3
           ,NVL2(c3, 100, 'Null') AS Return_Data
     FROM  null_t_temp;
      ,NVL2(c3, 100, 'Null') AS Return_Data
                     *
ERROR at line 11:
ORA-01722: invalid number     
Ex4>
SQL> WITH null_t_temp AS (SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NULL
                          AND    ROWNUM <= 1
                          UNION ALL
                          SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NOT NULL
                          AND    ROWNUM <= 1)
     SELECT c3
           ,NVL2(c3, 100, '95') AS Return_Data
     FROM  null_t_temp;

C3                   RETURN_DATA               
-------------------- -----------               
                              95               
15-SEP-11                    100               
Ex5>
SQL> WITH null_t_temp AS (SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NULL
                          AND    ROWNUM <= 1
                          UNION ALL
                          SELECT c3
                          FROM   NULL_T
                          WHERE  c3 iS NOT NULL
                          AND    ROWNUM <= 1)
     SELECT c3
           ,NVL2(c3, 'Not Null', 100) AS Return_Data
     FROM  null_t_temp;

C3                   RETURN_DATA                    
-------------------- ----------------               
                     100                            
15-SEP-11            Not Null

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.