NULL 처리함수

서현서현·2022년 2월 18일

DB, SQL

목록 보기

13/27

👀 NULL 처리함수

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

👀 1) NVL (expr,val)

expr의 값이 NULL이면 ‘val’값을 반환하고 NULL이 아니면 ‘expr’가 값을 반환
‘expr’과 ‘val’은 같은 데이터 타입이어야 함

EX1) 상품테이블에서 상품의 분류코드가 ‘P301’인 상품의 할인판매가를 NULL값으로 변경하시오

UPDATE PROD
SET PROD_SALE = NULL
WHERE PROD_LGU ='P301'

EX2) 상품테이블의 할인판매가를 조회하여 그값이 NULL이면 ‘단종상품’을, NULL이 아니면 할인판매 가격을 비고란에 출력하라

Alias는 상품번호, 상품명, 판매가격, 할인가격, 비고

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

ERROR : ORA-01722:invalid number
왜 에러메세지가 뜰까? >> PROD_SALE과 단종상품의 데이터타입이 안맞음. 단종상품이라는 문자열은 숫자로 바뀔수가 없는 자료 = INVALID NUMBER

해결방법 : PROD_SALE을 문자열로 바꿔준다

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

보기 좀 안좋으니까 정렬

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

EX3) 2005년도 6월에 판매된 상품의 종류?

SELECT DISTINCT CART_PROD
FROM CART
WHERE CART_NO LIKE '200506%'

EX4) 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 BY B.PROD_ID,B.PROD_NAME

둘중하나만 써도 분류는 되지만 둘다써줘야 되는게 규칙 안그럼 오류나

NULL을 SUM해봐야 NULL이니까 팔리지 않은 품목은 다 NULL뜸
이때 NULL말고 0 쓰고싶으니까 NVL 쓰는거!

👀 2)NVL2(expr, val1 ,val2)

expr값이 NULL이면 ‘val2’ 값을 반환하고 NULL이 아니면 ‘val1’값을 반환
NVL함수를 확장한 결과 반환
‘val1’과 ‘val2’는 같은 데이터 타입이어야 함

EX1) 사원테이블에서 영업실적코드가 NULL이면 ‘영업 이외 부서(영업 실적 없음)’을 NULL이 아니면 ‘영업부서’라는 메세지를 비고란에 출력

Alias는 사원번호, 사원명, 부서코드, 영업실적코드, 비고

SELECT EMPLOYEE_ID AS 사원번호, 
       EMP_NAME AS 사원명, 
       DEPARTMENT_ID AS 부서코드, 
       COMMISSION_PCT AS 영업실적코드, 
       NVL2(COMMISSION_PCT,'영업부서','영업 이외 부서(영업 실적 없음)') AS 비고
FROM HR.EMPLOYEES;

🚨 모든 NVL은 NVL2로 표현할 수 있지만 그반대는 불가능

EX2) 상품테이블의 할인판매가를 조회하여 그값이 NULL이면 ‘단종상품’을, NULL이 아니면 할인판매 가격을 비고란에 출력하라 (NVL2 사용)

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

EX3) 상품테이블의 할인판매가를 조회하여 그값이 NULL이면 ‘단종상품’을, NULL이 아니면 할인율(할인판매가/매출가격*100)을 출력하되 할인률은 정수로 비고란에 출력하라

SELECT PROD_ID AS 상품번호, 
       PROD_NAME AS 상품명, 
       PROD_PRICE AS 판매가격, 
       PROD_SALE AS 할인가격, 
       NVL2(PROD_SALE,TO_CHAR(100-(PROD_SALE/PROD_PRICE*100),'99,999,999')||'%','단종상품') AS 비고
FROM PROD;

👀 3) NULLIF (expr1, expr2)

‘expr1’과 ‘expr2’를 비교하여 같은값이면 NULL을 반환하고, 같은값이 아니면 expr1을 반환함

* 상품테이블에서 할인판매가가 판매가보다 큰 상품의 판매가를 매입가로 변경하시오

UPDATE PROD
SET PROD_SALE = PROD_COST
WHERE PROD_SALE>PROD_PRICE;
COMMIT;

EX1) 상품테이블에서 할인판매가와 매입가격을 비교하여 같은 가격이면 비고란에 ‘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;