Oracle 12강 - 날짜타입, Null처리 함수

TO_NUMBER : 문자열에 있는 숫자가 연산에 사용되어야 할 때 사용됨.
TO_DATE : 날짜형으로 연산을 해야할 때 사용됨.
TO_CHAR : 원하는 형식으로 출력하기 위해 사용됨.(출력 포맷지정)

(2)숫자타입 형식지정 문자열

-------------------------------------------------------------
형식지정문자열          의미                    사용예
-------------------------------------------------------------  
9(나인모드)  유효의 숫자는 숫자를 출력하고 무효의 0은 공백을 출력 
SELECT TO_CHAR(2345,'99,999') FROM DUAL;

0(제로모드)  유효의 숫자는 숫자를 출력하고 무효의 0은 0을 출력
  SELECT TO_CHAR(2345,'00,000') FROM DUAL;

$,L         화폐기호를 숫자왼쪽에 출력    
SELECT TO_CHAR(2345,'L99,999') FROM DUAL;

PR          음수를 '<>'안에 출력        
SELECT TO_CHAR(-2345,'99,999PR') FROM DUAL;

,(Comma)    자리점                      

.(Dot)      소숫점                    
SELECT TO_CHAR(2345,'99,999.00') FROM DUAL;
---------------------------------------------------------------

3)TO_DATE(data[,fmt])

• 문자열 자료 c를 날짜형으로 명시적 형변환 --숫자가 아닌 문자열, 숫자는 날짜로 바꿀 수 없음
• 'fmt'는 TO_CHAR에 사용된 형식지정문자열과 같음. 단, 날짜형과 대응되지 못하는 형식지정문자열은 무시되거나 오류를 발생시킴

사용예)장바구니테이블에서 2005년 6월 날짜별 판매자료를 조회하시오
Alias는 날짜,상품코드,수량합계,금액합계이며, 날짜순으로 출력하시오

  SELECT TO_CHAR(TO_DATE(SUBSTR(A.CART_NO,1,8)),'YYYY-MM-DD') AS 날짜,
         SUM(A.CART_QTY) AS 수량합계,
         SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계
    FROM CART A, PROD B
   WHERE A.CART_PROD=B.PROD_ID --(조인은 조건)
     AND TO_DATE(SUBSTR(A.CART_NO,1,8)) BETWEEN TO_DATE('20050601') 
         AND TO_DATE('20050630')
   GROUP BY SUBSTR(A.CART_NO,1,8)
   ORDER BY 1;
   

사용예)오늘날짜의 장바구니번호를생성하시오

DECLARE
    V_CNT NUMBER:=0;
    V_DATE CHAR(8):=TO_CHAR(SYSDATE,'YYYYMMDD'); --오늘날짜를 8자의 글자로 만들어줌
    V_CART_NO CHAR(13); --만들어진 카트번호를 보관할 방
  BEGIN
    SELECT COUNT(*) INTO V_CNT -- V_CNT에 넣어줌
      FROM CART
     WHERE CART_NO LIKE V_DATE||'%'; 
     
     IF V_CNT=0 THEN
        V_CART_NO:=V_DATE||TRIM(TO_CHAR(1,'00000')); 
        --00001로 만들어주고 공백을 제거한 것을 V_DATE와 합침
     ELSE
        SELECT MAX(CART_NO)+1 INTO V_CART_NO
          FROM CART
         WHERE CART_NO LIKE V_DATE||'%';
     END IF;
     
     DBMS_OUTPUT.PUT_LINE('CART 번호 : '||V_CART_NO);
 END;
   
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||'00001' 
-- TO_DATE(SYSDATE)는 안됨 : TO_DATE는 문자열을 날짜로 바꾸는 거라서
   FROM DUAL;
   
 SELECT TO_DATE('20210924','YYYYMMDD') --사이에 /가 생김(다른 기호를 넣으면 무시되고 /만 출력됨)
   FROM DUAL; 
   

NULL 처리 함수

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

1)IS NULL, IS NOT NULL

• 특정 자료가 NULL인지 여부 판단
• NULL은 '='연산자로 판단 불가
  (사용형식)
  expr IS[NOT] NULL
  

사용예) 사원테이블에서 50번부서에 속하지 않고 영업실적코드(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;
      

OUTER JOIN
FROM 다음에 기술되는 자료가 적을 때 RIGHT 사용
양쪽이 모두 부족할 때 FULL사용

2)NVL(col,val)

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

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

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

사용예)2005년 2월 모든 제품별 매입현황을 조회하시오
--모든 ,전부, 전체 : OUTER JOIN / OO별 : GROUP BY
Alias는 제품코드,제품명,매입수량합계,매입금액합계

  SELECT B.PROD_ID AS 제품코드,
         B.PROD_NAME AS 제품명,
         NVL(SUM(A.BUY_QTY),0) AS 매입수량합계,
         NVL(SUM(A.BUY_QTY*B.PROD_COST),0) AS 매입금액합계
    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) --DISTINCT : 중복없이
     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 상품명,
         NVL2(PROD_SIZE,PROD_SIZE,'크기정보없음') AS 상품크기,
         --위와 같음 NVL(PROD_SIZE,'크기정보없음')
         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;