NULL 처리 함수

• 오라클의 모든 타입의 자료형의 Default 값은 NULL임
• NULL 값과 연산이 실행되면 모든 결과가 NULL 임
• NVL, NVL2, NULLIF 등의 함수와 NULL 값의 판단을 위한 연산자
  IS NULL, IS NOT NULL) 제공됨

1) IS NULL, IS NOT NULL

• 특정 자료가 NULL인지 여부 판단
• NULL은 '='연산자로 판단 불가

(사용형식)

  expr IS [NOT] NULL

사용예) 사원테이블에서 80번 부서(영업부)에 속하지않고
영업실적코드(COMMISSION_PCT)가 NULL 이 아닌 사원을 조회하시오.
Alias는 사원번호, 사원명, 부서코드, 직무코드, 영업실적코드

   SELECT EMPLOYEE_ID AS 사원번호,
              EMP_NAME AS 사원명,
              DEPARTMENT_ID AS 부서코드,
              JOB_ID AS 직무코드,
              COMMISSION_PCT AS 영업실적코드
         FROM EMP
        WHERE DEPARTMENT_ID IS NULL
          AND COMMISSION_PCT IS NOT NULL;

2) NVL(col,val)

• 'col' 값이 NULL이면 'val'값을 반환하고 NULL이 아니면 자신의 값을 반환
• 'col'과 'val' 데이터 타입은 반드시 일치해야함
• 외부조인(OUTER JOIN)의 숫자결과 항목에 주로 사용

사용예) 상품테이블에서 색상(PROD_COLOR) 값이 NULL인 상품의 색상컬럼에 '색상정보없음'을 출력하시오.
Alias는 상품코드, 상품명, 색상정보

 SELECT PROD_ID AS 상품코드,
          PROD_NAME AS 상품명,
          NVL(PROD_COLOR,'색상정보없음') AS 색상정보
     FROM PROD;

사용예) 2005년 2월 모든제품별 매입현황을 조회하시오 --모든, 전부, 전체 라는 말 나오면 외부조인. ~~별하면 집계함수. Alias는 제품코드, 제품명, 매입수량합계, 매입금액합계

(ANSI OUTER JOIN)
   SELECT B.PROD_ID AS 제품코드,
          B.PROD_NAME AS 제품명,
          NVL(SUM(A.BUY_QTY),0) AS 매입수량합계,
          NVL(TO_CHAR(SUM(A.BUY_QTY*B.PROD_COST),'99,999,999'),'매출없음') 
          AS 매입금액합계 --NVL안의 앞뒤내용 데이터타입 일치해야함~
     FROM BUYPROD A 
    RIGHT OUTER JOIN PROD B ON(A.BUY_PROD=B.PROD_ID AND
          A.BUY_DATE BETWEEN TO_DATE('20050201') AND
          LAST_DAY(TO_DATE('20050201')))
    GROUP BY B.PROD_ID, B.PROD_NAME
    ORDER BY 1;

   (2005년 2월 매입상품종류)
   SELECT COUNT(DISTINCT BUY_PROD)
     FROM BUYPROD
    WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND
         LAST_DAY(TO_DATE('20050201'));

         

3) NVL2(col,val1,val2)

• 'col'값이 NULL이면 'val2'를 NULL이 아니면 'val1'을 반환
• 'val1'과 'val2'의 데이터 타입은 같아야 한다.

사용예) 상품테이블에서 상품의 크기(PROD_SIZE)가 NULL이면 '상품의 크기정보 없음'을 비고난에 출력하고
NULL이 아니면 상품의 할인 판매가를 비고난에 출력하시오
Alias는 상품코드, 상품명, 상품크기, 비고

 SELECT PROD_ID AS 상품코드,
          PROD_NAME AS 상품명,
          NVL(PROD_SIZE,'크기없음') AS 상품크기,
          NVL2(PROD_SIZE,TO_CHAR(PROD_SALE,'99,999,999'),
          '상품의 크기정보없음') 
          AS 비고
     FROM PROD;

4) NULLIF(c1,c2)

• c1과 c2를 비교하여 같은 값이면 NULL 을 반환하고 같은값이 아니면 c1값을 반환한다.

** PROD테이블에서 분류코드 'P301'에 속한 상품의 할인판매가를 매입가로 조정하시오

 UPDATE PROD
   SET PROD_SALE=PROD_COST
 WHERE PROD_LGU='P301';

사용예) 상품테이블에서 매입가격과 할인판매가가 비교하여 동일한 제품은 비고난에 '단종예정상품',
동일하지 않은 제품은 '정상상품'을 출력
Alias는 상품코드, 상품명, 매입가, 할인판매가, 비고

  SELECT PROD_ID AS 상품코드, 
              PROD_NAME AS 상품명,
              PROD_COST AS 매입가, 
              PROD_SALE AS 할인판매가, 
              NVL2(NULLIF(PROD_SALE, PROD_COST),'정상상품', '단종예정상품') AS 비고
    FROM PROD;