2022-11-23 데이터 베이스 오라클 SQL : NULL처리 함수
- NVL, NVL2, NULLIF 등이 제공됨
1. IS NULL, IS NOT NULL
. NULL값은 '='연산자로 동등성 평가가 불가능함
. NULL의 평가를 위한 연산자로 IS, IS NOT 연산자가 제공됨
사용예) 상품테이블에서 상품의 색상(PROD_COLOR)이
NULL인 제품의 제품코드,제품명,매입단가를 조회하시오
SELECT PROD_ID AS 제품코드,
PROD_NAME AS 제품명,
PROD_COST AS 매입단가
FROM PROD
WHERE PROD_COLOR IS NULL;
2. NVL(expr, val)
. 'expr' 값이 NULL이면 'val'를 반환하고 NULL이 아니면 'expr'값을 반환
. 'expr'과 'val'은 같은 데이터 타입 이어야 함
사용예) 2020년 7월 모든(=OUTTER JOIN 외부조인) 회원별 구매현황을 조회하시오
-- '모든' 이라는 수식어가 붙으면 OUTTER JOIN 외부조인이다.
Alias는 회원번호,회원명,구매수량합계
SELECT B.MEM_ID AS 회원번호,
B.MEM_NAME AS 회원명,
SUM(A.CART_QTY) AS 구매수량합계
FROM CART A
RIGHT OUTER JOIN MEMBER B ON(A.CART_MEMBER=B.MEM_ID
AND A.CART_NO LIKE '202007%')
GROUP BY B.MEM_ID,B.MEM_NAME
ORDER BY 1;
※ NVL로 null 값을 0으로
SELECT B.MEM_ID AS 회원번호,
B.MEM_NAME AS 회원명,
NVL(SUM(A.CART_QTY),0) AS 구매수량합계
FROM CART A
RIGHT OUTER JOIN MEMBER B ON(A.CART_MEMBER=B.MEM_ID
AND A.CART_NO LIKE '202007%')
GROUP BY B.MEM_ID,B.MEM_NAME
ORDER BY 1;
사용예) 2020년 7월 모든(=OUTTER JOIN 외부조인) 회원별 구매현황을 조회하시오
Alias는 회원번호,회원명,구매수량합계 이며 구매수량이 없으면 '구매정보없음'을 출력
※ 하지만 '구매수량합계'의 값은 문자열이다.(TO_CHAR)
SELECT B.MEM_ID AS 회원번호,
B.MEM_NAME AS 회원명,
NVL(SUM(A.CART_QTY),'구매정보없음') AS 구매수량합계
-- 오류 :'expr'과 'val'은 같은 데이터 타입 이어야 함
FROM CART A
RIGHT OUTER JOIN MEMBER B ON(A.CART_MEMBER=B.MEM_ID
AND A.CART_NO LIKE '202007%')
GROUP BY B.MEM_ID,B.MEM_NAME
ORDER BY 1;
-------------------------------------------------------------
SELECT B.MEM_ID AS 회원번호,
B.MEM_NAME AS 회원명,
NVL(TO_CHAR(SUM(A.CART_QTY),'9,999,999'),
'구매정보없음') AS 구매수량합계
-- 오류 :'expr'과 'val'은 같은 데이터 타입 이어야 함
-- 'expr' 을 문자열로 바꿔줘야함
FROM CART A
RIGHT OUTER JOIN MEMBER B ON(A.CART_MEMBER=B.MEM_ID
AND A.CART_NO LIKE '202007%')
GROUP BY B.MEM_ID,B.MEM_NAME
ORDER BY 1;
3. NVL2(expr, v1,v2)
. 'expr' 값이 NULL이면 v2를 반환하고, NULL이 아니면 v1을 반환
. NVL을 확장한 개념(NVL을 NVL2로 변환가능)
. v1과 v2는 같은 데이터 타입이어야 함
사용예) 상품테이블에서 제품의 제품코드,제품명,매입단가,색상을 조회하시오
색상이 NULL이면 '색상정보 없음'을 출력하시오. NVL2와 NVL을 적용
(NVL 사용)
SELECT PROD_ID AS 제품코드,
PROD_NAME AS 제품명,
PROD_COST AS 매입단가,
NVL(PROD_COLOR,'색상정보 없음') AS 색상
FROM PROD;
(NVL2 사용)
SELECT PROD_ID AS 제품코드,
PROD_NAME AS 제품명,
PROD_COST AS 매입단가,
NVL2(PROD_COLOR,PROD_COLOR,
'색상정보 없음') AS 색상
FROM PROD;
사용예) 사원테이블에서 사원들의 보너스를 계산하시오.
보너스는 영업실적이 있으면 기본급여(SALARY)*영업실적이고, 영업실적이 없으면
기본급여(SALARY)*10%이다.
영업실적이 없으면 영업실적에 '실적없음'을 출력할 것.
Alias는 사원번호,사원명,영업실적,기본급,보너스
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
NVL(TO_CHAR(COMMISSION_PCT,'0.99'),'실적없음') AS 영업실적,
SALARY AS 기본급,
NVL2(COMMISSION_PCT,SALARY*COMMISSION_PCT,SALARY*0.1) AS 보너스
FROM HR.EMPLOYEES
4. NULLIF(col1, col2)
. col1과 col2를 비교하여 서로 같은 값이면 NULL을 반환하고 다른값이면 'col1'을 반환함
** 상품테이블에서 분류코드 'P301' 자료의 매출가격을
매입가격으로 조정하시오
UPDATE PROD
SET PROD_PRICE=PROD_COST
WHERE PROD_LGU= 'P301';
-- 4개 행 이(가) 업데이트되었습니다.
: P301이 포함한 제품이 4가지
사용예) 상품테이블에서 상품들의 매입가와 매출가를 조회하여 같은 값이면 비고란에 '단종예정상품'을,
서로 다른 가격이면 판매이익을 조회하시오.
Alias는 상품코드, 상품명,매입가격, 매출가격, 비고
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_COST AS 매입가격,
PROD_PRICE AS 매출가격,
-- 같은 값이면 '단종예정상품' /다른 값이면 판매이익
NVL2(NULLIF(PROD_COST,PROD_PRICE),
TO_CHAR(PROD_PRICE-PROD_COST,'9,999,999'), '단종예정상품') AS 비고
FROM PROD;