오라클 형변환함수 + 선택함수(단일함수)

오라클 자료형
오라클에서 자주 사용하는 자료형에 대해서 먼저 알아보자

• (1) 문자 : CHAR, VARCHAR2, NCHAR, NVARCHAR2 -> 자바의 String과 동일
• (2) 숫자 : NUMBER
• (3) 날짜 : DATE, TIMESTAMP

CHAR(N) : N 크기의 고정 길이 문자 형식 저장 (최대 2000 byte)
VARCHAR2(N) : N 크기의 가변 길이 문자 형식 저장(최대 4000 byte)

차이점
-> VARCHAR2의 경우 문자열이 갖고있는 최대 크기만큼만 가변적으로 저장한다
CHAR이 고정길이이므로 좀 더 빠르게 문자열을 읽어 올 수 있지만 용량은 계속 사용하게 됨

NUMBER(p,s) : 숫자 형식 저장(p : 정수자리수, s : 소수 자리수)
DATE : 날짜 형식 저장

---

형변환 함수

✅ TO_CHAR

• 숫자, 날짜를 문자형으로 변경해주는 함수
• 날짜값을 기호로 표시해서 문자형으로 변경
• Y : 년, M : 월, D : 일, H : 시, MI : 분, SS : 초
• 밑 예제는 날짜를 문자형으로 변경하는 예제!

// 날짜를 문자형으로 변경!
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD'), // 오늘날짜를 'YYYY-MM-DD' 형식으로 출력
              TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') // 오늘날짜를 'YYYY-MM-DD HH:MI:SS' 형식으로 출력
FROM DUAL;

• 밑 예제는 숫자를 문자형으로 변경하는 예제!
  -> 0 : 변환대상값의 자리수가 지정한 자리수와 일치하지 않을때, 값이 없는 자리에 0을 표시하는 패턴
  -> 9 : 변환대상값의 자리수가 지정한 자리수와 일치하지 않을때, 값이 없는 자리에 생략하는 패턴 (앞에 생기는 공백은 FM으로 제거해야함)
  -> 통화(₩)를 표시하고 싶을때는 L을 표시

SELECT 1234567, TO_CHAR(1234567,'000,000,000'), // 001,234,567 이 출력됨
                TO_CHAR(1234567,'999,999,999'), // 1,234,567 출력됨 (앞 두자리 생략됨)
                TO_CHAR(500,'L999,999,999') // 앞에 L 쓰면은 달러 표시됨 (₩500출력)
FROM DUAL;


SELECT 180.5, TO_CHAR(180.5,'000,000.00'), // 000,180.50으로 출력됨
          TO_CHAR(180.5,'FM999,999.00') AS 키 // 180.50이 출력됨 (FM써야 공백제거됨)
FROM DUAL;

SELECT TO_CHAR(TO_DATE(20201225), 'DAY') FROM DUAL; // 2020년 12월 25일 요일 구하기

---

✅ TO_NUMBER

• 문자를 숫자형으로 변경

SELECT 1000000+1000000, TO_NUMBER('1,000,000','999,999,999')+1000000,  
// 문자열 1,000,000이 패턴에맞쳐서 숫자로 변환된 후에 + 1000000 -> 2000000 출력
    TO_CHAR(TO_NUMBER('1,000,000','999,999,999')+1000000,'FML999,999,999') 
// 숫자로 변환된 값을 다시 패턴에맞춰서 표기
FROM DUAL;

---

✅ TO_DATE

• 숫자를 날짜로변경
• 문자열을 날짜로 변경

SELECT TO_DATE('23/12/25','YY/MM/DD')-SYSDATE, // 문자열을 날짜로 변경함
        TO_DATE('241225','YYMMDD'), // 문자열을 날짜로 변경함
        TO_DATE('25-12-25','YY-MM-DD') // 문자열을 날짜로 변경함
FROM DUAL;


SELECT TO_DATE(20230405,'YYYYMMDD'), // 숫자를 날짜로 변경
        TO_DATE(230505,'YYMMDD'), // 숫자를 날짜로 변경
        TO_DATE(TO_CHAR(000224,'000000'),'YYMMDD') 
        // 숫자앞에 000은 없어지는값이라서 문자열로 바꾼후에 그 값을 날짜로 바꿈 (출력 00/02/04)
FROM DUAL;

✅ 날짜 지정형식

✅ 시간 지정형식

✅ 숫자 지정형식

---

선택함수

• NULL값을 처리해주는 함수
  -> NVL 함수 : NVL(컬럼,대체값)
  -> NVL2 함수 : NVL2(컬럼,NULL이 아닐때, NULL일때)

SELECT EMP_NAME, DEPT_CODE, NVL(DEPT_CODE,'인턴'), // DEPT_CODE가 NULL값이면 인턴으로 대체출력
              NVL2(DEPT_CODE,'있음','없음') //DEPT_CODE가 NULL값이 아니면 있음 출력, NULL값이면 없음 출력
FROM EMPLOYEE;

• DECODE
  -> 조건에 따라 출력할 값을 변경해주는 함수 (자바의 스위치문과 비슷)
  -> DECODE(컬럼명||문자열,'예상값','대체값','예상값2',.....)
  -> 쌍으로 된 값이 마지막에 한개 존재한다면 ELSE 문임

// 각 직책코드의 명칭을 출력하기
// J1 대표, J2 부사장, J3 부장, J4 과장
// 쌍으로 값을 이루면 IF 조건문이며, 마지막 하나의 값 사원은 ELSE문이라고 생각하면된다.
SELECT EMP_NAME, JOB_CODE, DECODE(JOB_CODE,'J4','과장','J3','부장','J2','부사장','J1','대표','사원') 
FROM EMPLOYEE;

---

✅ CASE WHEN THEN ELSE

CASE
WHEN 조건식 THEN 실행내용
WHEN 조건식 THEN 실행내용
WHEN 조건식 THEN 실행내용
ELSE 실행
END

SELECT EMP_NAME, JOB_CODE,
        CASE
            WHEN JOB_CODE = 'J1' THEN '대표'  
            WHEN JOB_CODE = 'J2' THEN '부사장'
            WHEN JOB_CODE = 'J3' THEN '부장'
            WHEN JOB_CODE = 'J4' THEN '과장'
            ELSE '사원'  // 위 조건이 없을 경우 사원으로 출력
        END AS 직책,
        CASE JOB_CODE
            WHEN 'J1' THEN '대표'
            WHEN 'J2' THEN '부사장'
            // 위조건이 없을경우 다 null값으로 출력
        END AS 조건문예제
FROM EMPLOYEE;

---

SELECT EMP_NAME, EMP_NO,
    EXTRACT(YEAR FROM SYSDATE)-(
    TO_NUMBER(SUBSTR(EMP_NO,1,2))+
    CASE
        WHEN SUBSTR(EMP_NO,8,1) IN (1,2) THEN 1900  // 주민번호 뒷자리 앞이 1,2
        WHEN SUBSTR(EMP_NO,8,1) IN (3,4) THEN 2000 // 주민번호 뒷자리 앞이 3,4
    END
    ) AS 살 
FROM EMPLOYEE;

---

✅ RANK(), DENSE_RANK(), ROW_NUMBER()

• RANK() : 공통순위는 건너뛰어 다음 순위를 출력하는 순위함수
• DENSE_RANK() : 공통 순위를 건너뛰지 않고, 다음 순위를 출력하는 순위 함수
• ROW_NUMBER() : 공통 순위 없이 순위를 출력하는 순위 함수

SELECT first_name, salary,
RANK() OVER(ORDER BY salary DESC) AS RANK,
DENSE_RANK() OVER(ORDER BY salary DESC) AS DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS ROW_NUMBER
FROM EMPLOYEES;