[Oracle SQL Developer] 기초 VIII - 변환 함수

1. 변환함수

---

  . 오라클은 수식에 사용된 자료형이 자동변환 기능을 제공한다.
  . 정수형이 문자열보다 우선 적용된다.
  EX) SELECT '23' + 75 FROM DUAL; -- 정수형 98로 출력된다.
  
  
  1.1. CAST(exp1 AS type)
    1.1.1. 'exp1'을 type으로 변환한다.
    
    예) 회원테이블에서 주민번호 앞자리를 날짜형으로 변환한다.
    
    SELECT MEM_NAME 회원명,
           MEM_REGNO1 원본,
           CAST('19' || MEM_REGNO1 AS DATE) AS DATE타입변환,
           CAST(MEM_REGNO1 AS NUMBER) AS NUMBER타입변환
      FROM MEMBER;
      
      
    SELECT EMP_NAME 원본,
           LENGTH(EMP_NAME) "EMP_NAME의 길이",
           CAST(EMP_NAME AS CHAR(30)) 변환,
           LENGTH(CAST(EMP_NAME AS CHAR(30))) "변환된 EMP_NAME의 길이"
      FROM EMPLOYEES;
      
      
    SELECT CAST('1234' AS NUMBER) + 50 FROM DUAL;  
    SELECT CAST('50' AS CHAR) + '50' FROM DUAL;
      
      
      
  1.2. TO_CHAR(expr[,fmt])
    1.2.1. 주어진 자료 'expr'값을 형식문자열 'fmt' 형식의 문자열로 변환한다.
    1.2.2. 'expr'은 숫자, 날짜, 문자 타입의 자료이다.
    1.2.3. 형식지정 문자열

의미	날짜 및 시간 형식의 문자열	사용 예시	비고
세기 및 년도	AD, BC, CC, YEAR, YYYY, YYY, YY, Y	SELECT TO_CHAR(SYSDATE,'CC'),              TO_CHAR(SYSDATE,'BC'),              TO_CHAR(SYSDATE,'AD'),              TO_CHAR(SYSDATE,'BC CC')        FROM DUAL;            SELECT TO_CHAR(SYSDATE,'YYYY'),              TO_CHAR(SYSDATE,'YEAR'),    -- 해당 년도를 영문으로 표현              TO_CHAR(SYSDATE,'YYY'),              TO_CHAR(SYSDATE,'YY'),             TO_CHAR(SYSDATE,'Y')        FROM DUAL;
분기	Q	SELECT TO_CHAR(SYSDATE,'Q')        FROM DUAL;
월	MONTH, MON, MM, RM	SELECT TO_CHAR(SYSDATE,'RM')  -- 로마자로 출력됨.        FROM DUAL;
주차	W, WW, IW	SELECT TO_CHAR(SYSDATE,'W'),  -- 해당 월의 주차             TO_CHAR(SYSDATE,'WW'),  -- 1월 1일부터의 주차             TO_CHAR(SYSDATE,'IW')          FROM DUAL;
일	DD, DDD, J, D, DY, DL, DS	SELECT TO_CHAR(SYSDATE,'DD'),              TO_CHAR(SYSDATE,'DDD'),              TO_CHAR(SYSDATE,'D'),              TO_CHAR(SYSDATE,'DL'),  -- 자세한 날짜를 표시             TO_CHAR(SYSDATE,'DS'),  -- 날짜를 간략하게 표시             TO_CHAR(SYSDATE,'DY'),  -- 요일만 표시된다.             TO_CHAR(SYSDATE,'DAY'),  -- 요일을 붙여서 표시된다.             TO_CHAR(SYSDATE,'J')  -- 율리우스 적일(Julian Date)은 시점 : B.C. 4713년 1월 1월 세계시 12시부터 세어진 날의 수          FROM DUAL;
오전/오후	AM, PM, A.M., P.M.	SELECT TO_CHAR(SYSDATE,'AM'), -- AM             TO_CHAR(SYSDATE,'PM'), -- PM             TO_CHAR(SYSDATE,'A.M.'),             TO_CHAR(SYSDATE,'P.M.')        FROM DUAL;
시간	HH, HH12, HH24	SELECT TO_CHAR(SYSDATE,'HH'),              TO_CHAR(SYSDATE,'HH12'),  -- 해당 시간을 12시 표기법으로 표현             TO_CHAR(SYSDATE,'HH24')   -- 해당 시간을 24시 표기법으로 표현        FROM DUAL;
분	MI
초	SS, SSSSS	SELECT TO_CHAR(SYSDATE,'SSSSS')  -- 0시 0분 0초부터 현재 까지의 지난 초       FROM DUAL;           SELECT TO_CHAR(SYSDATE,'SS')          FROM DUAL;
공백 처리	9	SELECT TO_CHAR(PROD_PRICE, '999,999,999')  -- 자리수를 적게 설정하면 엑셀의 숫자 출력 초과처럼 #### 이 출력된다.        FROM PROD;        SELECT TO_CHAR(0000000000001234567890, '999,999,999,999')        FROM DUAL;	유효한 숫자는 데이터 자신을 출력하고, 무효의 0은 공백 처리한다.
'0' 출력	0	SELECT TO_CHAR(PROD_PRICE, '000,000,000')         FROM PROD;              SELECT TO_CHAR(PROD_SALE, '9,999,990')  -- 뒤에 공백이 있어도 0으로 처리된다.        FROM PROD;	유효한 숫자는 데이터 자신을 출력하고, 무효의 0은 '0'이 출력된다.
화폐 기호	$,L	SELECT TO_CHAR(PROD_COST, 'L9,999,990')        FROM PROD;	화폐기호를 정의한다.
음수 표현 1	MI	SELECT TO_CHAR(PROD_COST-PROD_PRICE, 'L9,999,990PR')         FROM PROD;	음수 출력시 우측에 '-' 기호 출력한다.
음수 표현 2	PR		음수 출력시 우측에 '<>' 기호로 출력한다.
소수점 및 자리수	, .
16 진수의 표현	X	SELECT TO_CHAR(64,'XXXX')         FROM DUAL;	16진수로 변환한 자료를 출력한다.
기타	" " 안에 사용자 지정 문자열 정의	SELECT TO_CHAR(SYSDATE,'YYYY"년"')        FROM DUAL;

  
  예) 상품분류테이블에서 신규 분류 코드를 삽입하고자 한다.
        분류코드를 자동으로 생성하는 쿼리를 작성하시오.
          . 분류코드 : P3로 시작
          . 분류명 : 농산물
          . LPROD_ID : MAX(LPROD_ID)+1 로 설정.
        
    -- 데이터가 숫자일 경우    
    SELECT MAX(CART_NO)+1 
      FROM CART
     WHERE CART_NO LIKE '20050401%';


    -- 데이터에 문자가 포함되어 있는 경우 
    SELECT 'P' || TO_CHAR(MAX(SUBSTR(LPROD_GU, 2))+1)  -- MAX(SUBSTR(LPROD_GU, 2))+1는 정수형이기 때문에 'P'와 더하려면 변환함수 사용
      FROM LPROD
     WHERE LPROD_GU LIKE 'P3%';
    
    -- 데이터에 문자가 포함되어 있는 경우 데이터를 삽입하는 방법
    INSERT INTO LPROD VALUES
          (LPROD_SEQ.NEXTVAL, (SELECT 'P' || TO_CHAR(MAX(SUBSTR(LPROD_GU, 2)) + 1)   
             FROM LPROD
            WHERE LPROD_GU LIKE 'P3%'),'농산물');
    
    CREATE SEQUENCE LPROD_SEQ -- 자동으로 증가되는 객체 생성
     START WITH 10;
    
    SELECT * FROM LPROD;         
    


    
  1.3. TO_NUMBER(expr[,fmt])
    1.3.1. 숫자형식의 문자열을 숫자로 변환한다.
    1.3.2. 주어진 자료 'expr'값을 형식숫자열 'fmt' 형식의 숫자열로 변환한다. 'fmt'의 형식은 같다.
      : 'expr'은 숫자형으로 변형될 수 있는 값만 변형이 가능하다.
    
    
    예) 회원테이블에서 첫 번째 주민번호를 이용하여 출생년도를 4자리 형식으로 출력하시오.
    
    SELECT MEM_NAME 회원명,
           TO_NUMBER('19' || SUBSTR(MEM_REGNO1,1,2)) 출생년도1,
           1900 + TO_NUMBER(SUBSTR(MEM_REGNO1,1,2)) 출생년도2
      FROM MEMBER;
    
    
    
  1.4. TO_DATE(expr[,fmt])
    1.4.1. 문자열을 날짜형으로 변환한다.
    
    예) 회원테이블의 첫번째 주민번호를 'YY-MM-DD'형식의 날짜형으로 변환하시오.
    
    SELECT MEM_NAME 회원명,
          /* TO_DATE('19' || MEM_REGNO1, 'YY-MM-DD') 출생년도,*/
           TO_DATE(MEM_REGNO1, 'RR-MM-DD') 출생년도1,
           TO_CHAR(TO_DATE('19' ||MEM_REGNO1,'YYYYMMDD'),'YYYY-MM-DD')
      FROM MEMBER;
     /*WHERE SUBSTR(MEM_REGNO1,1,2) > 50
       AND TO_DATE(MEM_REGNO1, 'YY-MM-DD');*/