[Oracle] NVL(), NVL2()

1. Null값 처리 함수

01) NVL()

NVL(expr1, expr2) : expr1이 null값일 경우, expr2를 반환한다.

SELECT NAME, SCROE FROM STUDENT_SCORE;

SELECT NAME, NVL(SCROE, '채점중') FROM STUDENT_SCORE;

02) NVL2()

NVL2(expr1, expr2, expr3) : expr1이 null값일 경우, expr3을 반환하고, expr1이 null이 아닐경우 expr2를 반환한다.

SELECT NAME, SCROE FROM STUDENT_SCORE;

SELECT NAME, NVL2(SCROE, '채점완료', '채점중') FROM STUDENT_SCORE;

2. 예시 및 소스 (복뿟)

01) 테이블 생성

CREATE TABLE STUDENT_SCORE (
        NAME VARCHAR2(20) NOT NULL,
       SCORE VARCHAR2(20) NULL
);

02) 데이터 삽입

INSERT INTO STUDENT_SCORE (NAME, SCORE)
SELECT '김철수', 100   FROM DUAL
UNION ALL
SELECT '이만수', 95    FROM DUAL
UNION ALL
SELECT '김지영', 95    FROM DUAL
UNION ALL
SELECT '양수민', null  FROM DUAL
UNION ALL
SELECT '꽈뚜룹', null  FROM DUAL
UNION ALL
SELECT '응우엔', 70    FROM DUAL
UNION ALL
SELECT '외계인', 5     FROM DUAL
UNION ALL
SELECT '민정수', 20    FROM DUAL
;

03) 실행 쿼리

SELECT NAME, SCROE FROM STUDENT_SCORE;
SELECT NAME, NVL(SCROE, '채점중') FROM STUDENT_SCORE;
SELECT NAME, NVL2(SCROE, '채점완료', '채점중') FROM STUDENT_SCORE;