오라클 변환함수

변환함수(형변환)

- 정의된 자료의 형을 일시적으로 변환하여 반환
- CAST, TO_NUMBER, TO_DATE, TO_CHAR이 제공됨

1) CAST(exper AS 타입)

 - 'exper'로 정의된 데이터 또는 컬럼의 값을 '타입'형으로 변환(일시적)
 - '타입'은 오라클에서 사용될수 있는 데이터 타입 
 

사용예) SELECT PROD_ID,
PROD_NAME,
PROD_COST,
CAST(PROD_PRICE AS VARCHAR2(10)) --12345
-- 매출단가는 숫자를 문자열(VARCHAR2(10))로 변환하여 왼쪽정렬이다.
PROD_COST -- AAAA
FROM PROD
WHERE PROD_COST >= 100000; -- 매입단가가 10만원 이상

2)TO_CHAR(data[,fmt]) --fmt 생략될때 형식이 없는 문자열로 변환

        - data는 문자열(CHAR(고정길이),CLOB(대용량) => VARCHAR2), 숫자, 날짜 타입의 자료를 문자열로 바꿀때
        - 'fmt'는 변환하려는 형식지정 문자열(포맷의 약자)
        

(1) 날자타입 형식지정 문자열

        ----------------------------------------------------------------------------------------------------
        형식지정문자열                 의미                 사용예   
        ----------------------------------------------------------------------------------------------------
        AD,BC,CC                서기, 세기                SELECT TO_CHAR(SYSDATE, 'BC CC') FROM DUAL;
        YYYY,YYY,YY,Y              년도                 SELECT TO_CHAR(SYSDATE, 'BC YYYY') FROM DUAL; 
                                                        SELECT TO_CHAR(SYSDATE, 'BC YYY') FROM DUAL; --오른쪽에서 3자리
                                                        SELECT TO_CHAR(SYSDATE, 'BC Y') FROM DUAL; --오른쪽에서 1자리
        Q                          분기                  SELECT TO_CHAR(SYSDATE, 'Q')||'분기' FROM DUAL;
                                                         -- = SELECT TO_CHAR(SYSDATE, 'Q"분기"') FROM DUAL;
        MM(월을 두자리 숫자로 표현)
        RM (로마식 표현)             월                   SELECT TO_CHAR(SYSDATE, 'YYYYMM RM') FROM DUAL;
        MONTH, MON (한글 월이 추가되어 출력)               SELECT TO_CHAR(SYSDATE, 'MON MONTH') FROM DUAL;
        W,WW,IW                    주차                  SELECT TO_CHAR(SYSDATE, 'W WW IW') FROM DUAL;
       -- W는 요번주가 몇번째 날인지 WW는 요번주가 몇번째 주인지 
       DD,DDD,J                    일                    SELECT TO_CHAR(SYSDATE, 'DD DDD J') FROM DUAL;
       --DD(월 기준)는 해당 달 1일부터 지금까지 며칠이 경과 되었는지 
       --DDD(연 기준)는 2021년에서 지금까지 몇일이 경과되었는가 J는 기원전으로 부터 오늘까지 지난 일수 
       D,DY,DAY                 주의 요일                 SELECT TO_CHAR(SYSDATE, 'D DY DAY') FROM DUAL;
       --D(주 기준)는 일요일을 기준으로 오늘이 몇번째 날인지 DY와 DAY는 요일을 나타냄
       AM,PM,A.M.,P.M.          오전, 오후                SELECT TO_CHAR(SYSDATE, 'AM PM') FROM DUAL;
       HH,HH12,HH24             시                       SELECT TO_CHAR(SYSDATE, 'HH HH24') FROM DUAL;
       MT                       분                        SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM DUAL;
       SS,SSSSS                 초                        SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS SSSSS') FROM DUAL;
      --SSSSS 는 지금까지 경과된 시간을 초로 환산한 것
       " "                     기타 사용자 정의 , 컬럼의 별칭에도 사용됨            
           SELECT TO_CHAR(SYSDATE, 'YYYY "년" MM "월" DD "일" HH24:MI:SS') --" "값이 문자열로 취급되어 그대로 출력
             FROM DUAL;
      

(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; --L은 원화 기호 / $는 달라 사인(위치: 왼쪽)
      PR       음수를 '<>'안에 출력                                  SELECT TO_CHAR(-2345,'99,999PR') FROM DUAL; --PR은 오른쪽 위치
      ,(Comma) 자리점     --컴퓨터에서는 알수 없는 기호라 숫자로 변환하기 힘듬                                         
      .(Dot)   소숫점                                              SELECT TO_CHAR(2345,'99,999.99') FROM DUAL;
      -----------------------------------------------------------------------------------------------------
      

3) TO_DATE(data[,fmt]) --문자열을 날짜로 바꿈

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

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

             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 TO_CHAR(TO_DATE(SUBSTR(A.CART_NO,1,8)),'YYYY-MM-DD')
             ORDER BY 1;
      

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

        DECLARE
        V_CNT NUMBER:=0; -- V_CNT오늘날짜에 자료가 있는지 확인
        V_DATE CHAR(8):= TO_CHAR(SYSDATE,'YYYYMMDD');
        V_CART_NO CHAR(13);
        BEGIN
         SELECT COUNT(*) INTO V_CNT--자료의 개수를 V_CNT에 넣어줌 / 자료가 없을 경우 0
          FROM CART
          WHERE CART_NO LIKE V_DATE||'%';--V_DATE는 구분자 없이 날짜가 들어감
      
      IF V_CNT = 0 THEN
          V_CART_NO:=V_DATE||TRIM(TO_CHAR(1,'00000'));--TRIM는 공백제거
      ELSE
          SELECT MAX(CART_NO)+1 INTO V_CART_NO -- 가장큰 값을 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') -- SYSDATE는 이미 날짜 이기 때문에 숫자를 숫자로 바꿀수는 없어서 TO_DATE는 올 수 없다
       FROM DUAL;                       -- SELECT TO_DATE('20210924', 'YYYYMMDD') FROM DUAL;는 가능하다
      

4)TO_NUMBER(C[,fmt])

  - 숫자로 변형될 수 있는 문자열 c를 'fmt'형식에 맞추어 숫자형으로 변환
  - 'fmt'는 숫자로 변환가능한 형식문자열('9','0','.')만 사용 가능 
      
사용예) 2005년 5월 7일 CART번호 생성
       SELECT '20050507'||
                TRIM(TO_CHAR(MAX(TO_NUMBER(SUBSTR(CART_NO,9)))+1,'00000')) --TRIM은 공백을 없애는 것이다.
         FROM CART
        WHERE CART_NO LIKE '20050507%';