Oracle(2022.02.16)2-NVL,NVL2,NULLIF

카테고리 없음

Oracle(2022.02.16)2-NVL,NVL2,NULLIF

AIN99 2022. 2. 16. 19:16

728x90

NULL 처리 함수

-NULL자료는 길이를 갖지않는 자료
-연산에 참여하면 결과가 모두 NULL임
-NVL, NVL2, NULLIF등이 지원

  1)NVL(expr, val)
  .expr의 값이 NULL이면 'val' 값을 변환하고 NULL이 아니면 'expr'값을 반환
  .'expr'과 'val'은 같은 데이터 타입이어야 함

**상품테이블에서 상품의 분류코드가 'P301'인 상품의 할인판매가를 NULL값으로 변경하시오..
  --NULL 대신에 ''를 써도 된다.
  UPDATE PROD
  SET PROD_SALE=NULL
  WHERE PROD_LGU='P301';

사용예)상품테이블의 할인판매가를 조회하여 그 값이 NULL이면 '단종상품'을, 
      NULL이 아니면 할인판매가를 비고난에 출력해라
       SELECT PROD_ID AS 상품번호, 
             PROD_NAME AS 상품명,
             PROD_PRICE AS 판매가격, 
             PROD_SALE AS 할인가격,
             NVL(PROD_SALE,'단종상품') AS 비고
      FROM PROD;

-->INVAILD NUMBER 오류,데이터타입이 안맞아서 그래서 PROD_SALE를 문자열로 바꿔준다.

-->수정 후

SELECT PROD_ID AS 상품번호, 
             PROD_NAME AS 상품명,
             PROD_PRICE AS 판매가격, 
             NVL(PROD_SALE,0) AS 할인가격,
             NVL(TO_CHAR(PROD_SALE,'99,999,999'),LPAD('단종상품',12)) AS 비고
      FROM PROD;

사용예)2005년도 6월 판매된 상품종류는?
    SELECT  DISTINCT CART_PROD  
      FROM CART
      WHERE CART_NO LIKE '200506%';

사용예) 2005년 6월 모든 상품별 판매 집계를 조회하시오   
        Alias는 상품코드, 상품명, 판매수량, 판매금액
     SELECT B.PROD_ID AS 상품코드,
            B.PROD_NAME AS 상품명, 
            SUM(A.CART_QTY) AS 판매수량, 
            SUM(A.CART_QTY * B.PROD_PRICE) AS 판매금액
      FROM CART A
      RIGHT OUTER JOIN PROD B ON( A.CART_PROD=B.PROD_ID AND
            A.CART_NO LIKE '200506%')
    GROUP BY B.PROD_ID,B.PROD_NAME
    ORDER BY 1;

-->모든:외부조인 , 상품별: GROUP ,상품코드는 CART,PROD 모두다 존재 하지만 출력은 많은쪽을 써준다.

많은쪽 74줄 적은쪽 15개니까 59줄 NULL값행 추가

-->수정 후(NULL값을 0으로바꾸기 위해)

SELECT B.PROD_ID AS 상품코드,
            B.PROD_NAME AS 상품명, 
            NVL(SUM(A.CART_QTY),0) AS 판매수량, 
            NVL(SUM(A.CART_QTY * B.PROD_PRICE),0) AS 판매금액
      FROM CART A
      RIGHT OUTER JOIN PROD B ON( A.CART_PROD=B.PROD_ID AND
            A.CART_NO LIKE '200506%')
    GROUP BY B.PROD_ID,B.PROD_NAME
    ORDER BY 1;

2)NVL2(expr,val1,val2)
  .expr의 값이 NULL이면 'val2'값을 반환하고 NULL이 아니면 'val1'값을 반환
  .NVL 함수를 확장한 결과 반환
  .'vall'과 'val2'는 같은 데이터 타입이어야 함
  .NVL2>NVL 이라서 NVL2가 NVL이 되는경우는 거의 드물다.
  .'expr' 얘와 val1,val2 같은데이터타입이 아니여도 된다. expr은 출력되지는 것이 아니기 때문이다.

사용예)사원테이블에서 영업실적코드가 NULL이면 '영업 이외 부서(영업실적 없음)'을
      NULL이 아니면 '영업부서'라는 메세지를 비고난에 출력
      Alias는 사원번호, 사원명, 부서코드, 영업실적코드, 비고
      SELECT EMPLOYEE_ID AS 사원번호, 
             EMP_NAME AS 사원명, 
             DEPARTMENT_ID AS 부서코드,
             COMMISSION_PCT AS 영업실적코드,
             NVL2(COMMISSION_PCT,'영업부서','영업 이외 부서(영업실적 없음)') AS 비고
        FROM HR.EMPLOYEES;

사용예)상품테이블의 할인판매가를 조회하여 그 값이 NULL이면 '단종상품'을, 
      NULL이 아니면 할인판매가격을 비고난에 출력(NVL2 사용)
      
       SELECT PROD_ID AS 상품번호, 
             PROD_NAME AS 상품명,
             PROD_PRICE AS 판매가격, 
             NVL(PROD_SALE,0) AS 할인가격,
             NVL2(PROD_SALE,TO_CHAR(PROD_SALE,'99,999,999')
             ,LPAD('단종상품',12,' ')) AS 비고
      FROM PROD;

사용예)상품테이블의 할인판매가를 조회하여 그 값이 NULL이면 '단종상품'을, 
      NULL이 아니면 할인률을 출력하되 할인률은
      정수로 비고난에 출력
      Alias는 상품번호, 상품명,판매가격, 할인가격,비고
      --할인금액=판매가*할인율
        구매금액=판매가-(판매가*할인율)
      SELECT PROD_ID AS 상품번호, 
             PROD_NAME AS 상품명,
             PROD_PRICE AS 판매가격, 
             PROD_SALE AS 할인가격,
             NVL2(PROD_SALE ,TO_CHAR(100-(PROD_SALE/PROD_PRICE*100),'99999')||'%' ,'단종상품') AS 비고
      FROM PROD;

3)NULLIF(expr1, expr2)
    .'expr1'과 'expr2'를 비교하여 같은 값이면 NULL을 반환하고, 같은 값이 아니면
      expr1을 반환 함

**상품테이블에서 할인판매가가 판매가보다 큰 상품의 할인판매가를 매입가로 변경하시오
    UPDATE PROD
        SET PROD_SALE=PROD_COST
    WHERE PROD_SALE>PROD_PRICE;
    COMMIT;

사용예)상품테이블에서 할인판매가와 매입가격을 비교하여 같은 가격이면 '1+1상품'을
      서로 같지 않으면 '정상상품'을 출력하시오
      Alias는 상품코드, 상품명,매입가,판매가,할인판매가,비고
      SELECT PROD_ID AS 상품코드,
      PROD_NAME AS 상품명,
      PROD_COST AS 매입가,
      PROD_PRICE AS 판매가,
      PROD_SALE AS 할인판매가,
      NVL2(NULLIF(PROD_COST,PROD_SALE),'정상상품','1+1상품') AS 비고
    FROM PROD;

728x90