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