2023.01.02(Oracle Fuction)

ORACLE 오라클 TO_CHAR 함수 사용법 완벽한 정리 (날짜포맷, 소수점, 천단위 콤마)

참고 : https://gent.tistory.com/331

1.TO_CHAR (숫자 혹은 날짜, format)

숫자나 날짜를 문자로 변환해 주는 함수가 바로 TO_CHAR로, 매개변수로는 숫자나 날짜가 올 수 있고 반환 결과를 특정 형식에 맞게 출력할 수 있다.

날짜 포맷 변경 (YYYY-MM-DD)

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')              --20200723
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD')            --2020/07/23
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD')            --2020-07-23
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') --2020-07-23 11:10:52
  FROM dual

• YYYY: 년, MM: 월, DD: 일, HH24: 24시간, HH: 12시간, MI: 분, SS:초

소수점 변경

SELECT TO_CHAR(3, 'FM999.999') --3.
     , TO_CHAR(6, 'FM0000.00') --0006.00
     , TO_CHAR(0.12345, 'FM9990.99') --0.12
FROM dual

• FM: 문자열의 공백제거

  • 숫자의 최대 길이만큰 9999... 형식을 지정한다. (9 : 값이 없으면 표시안함, 0: 값이 없으면 "0"으로 처리)

  • 정수은 지정한 형식보다 값의 길이가 길면 오류, 소수 지정한 길이보다 길면 반올림

숫자의 천 단위 콤마 찍기

SELECT TO_CHAR(123467, 'FM999,999')        --123,467
     , TO_CHAR(123467890, 'FM999,999,999') --123,467,890
     , TO_CHAR(123467, 'FML999,999')       --￦123,467
FROM dual

• 숫자의 최대 길이만큰 9999... 형식을 지정한다.

지정한 길이 만큼 "0"으로 채우기

SELECT TO_CHAR(123)            --123
     , TO_CHAR(123, 'FM00000') --00123
FROM dual

날짜의 "0" 없애기

SELECT TO_CHAR(SYSDATE, 'MM/DD')   --01/02
     , TO_CHAR(SYSDATE, 'FMMM/DD') --1/2
FROM dual

임의의 구분자로 날짜 형식 만들기

SELECT TO_CHAR(SYSDATE, '""YYYY"년 "MM"월 "DD"일"') --2023년 01월 02일
     , TO_CHAR(SYSDATE, '""HH24"시 "MI"분 "SS"초"') --20시 37분 54초
FROM dual

시간의 오전, 오후 값 반환

SELECT TO_CHAR(SYSDATE, 'AM')                    --오전
     , TO_CHAR(SYSDATE, 'AM HH:MI:SS')           --오전 11:44:31
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD AMHH:MI:SS') --2020-07-23 오전11:44:31
FROM dual

날짜의 요일 반환

SELECT TO_CHAR(SYSDATE, 'D')   --2 : 1(일)~7(토)
     , TO_CHAR(SYSDATE, 'DY')  --월
     , TO_CHAR(SYSDATE, 'DAY') --월요일
FROM dual

1년기준 몇일, 몇주, 분기 반환

SELECT TO_CHAR(SYSDATE, 'DDD') --365일 기준 002일
     , TO_CHAR(SYSDATE, 'WW')  --1년 기준 01주
     , TO_CHAR(SYSDATE, 'Q')   --1분기
FROM dual

간편한 날짜 변환

SELECT TO_CHAR(SYSDATE, 'MON') --1월
     , TO_CHAR(SYSDATE, 'DL')  --2023년 1월 2일 월요일
FROM dual

2.TO_NUMBER, CAST

참고 : https://gent.tistory.com/424
오라클에서 문자열을 숫자형으로 변환하기 위해서는 TO_NUMBER 함수와 CAST 함수를 사용할 수 있다.

1) TO_NUMBER 함수(문자열 숫자 변환)

SELECT TO_NUMBER('1123231') AS NUM1
     , TO_NUMBER('1238908') AS NUM2
FROM dual

2) CAST 함수

SELECT CAST('12345' AS NUMBER(10))
     , CAST('12345.56' AS NUMBER(10))
     , CAST('12345.56' AS NUMBER(10,2))
  FROM dual

• CAST 함수를 사용하면 숫자의 크기(자릿수)를 정의하여 변환이 가능하며, 변환하려고 하는 수치 값의 자릿수보다 정의된 자릿수가 작으면 오류가 발생한다.
• 소수점 이하의 자리수는 자릿수가 맞지 않아도 오류가 발생하지 않으며, 정의된 자릿수 이하의 값은 반올림되어서 변환된다. (예시 쿼리의 두 번째 변환 반올림 부분 확인)

번외 REPLACE

SELECT REPLACE('$123,456', '$')         AS result1
FROM dual

3.ROWNUM, ROW_NUMBER()

1) ROWNUM

SELECT ROWNUM
     , a.*   
FROM V_WPPIMINFO a

• 조회된 순서되로 순번을 매긴다.

SELECT ROWNUM      
     , x.*   
  FROM ( SELECT a.*            
         FROM ZZANMUSER a           
         ORDER BY a.USR_ID        
        )x

• ORDER BY 를 사용하면 순번이 뒤섞이므로 정렬된 서브쿼리 결과에 ROWNUM 을 매겨야 한다.

2) ROW_NUMBER()

• ORDER BY 된 결과에 순번을 매길때에는 ROWNUM 보다 ROW_NUMBER() 함수가 더 편하다.

  SELECT ROW_NUMBER() OVER(ORDER BY a.USR_ID, a.KOR_NM) row_num      
      , a.*   
   FROM ZZANMUSER a  
  ORDER BY a.USR_ID, a.KOR_NM
  

4.DISTINCT

• 데이터 조회 시 데이터 중복을 제거
• 오라클 쿼리문에서 데이터의 중복을 제거할 때는 DISTINCT 키워드를 사용할 것을 권장한다. DISTINCT를 사용하면 쿼리문을 분석할 때 중복을 제거한 결과가 반환된다는 것을 직관적으로 파악이 가능하다. 그러나 상황에 따라서 GROUP BY 절을 사용하여 중복을 제거할 수도 있다.

SELECT DISTINCT
       a.PATNO
     , b.PAT_NM
  FROM GXEXRGEXM a
     , V_WPPIMINFO b
 WHERE a.PATNO= b.PATNO

5.MAX, MIN

• 오라클에서 최댓값과 최솟값을 구하기 위해서는 집계 함수 MAX(최대값), MIN(최소값)를 사용하면 된다.
• 집계 함수는 주로 GROUP BY 절과 함께 많이 사용하며, SELECT 절에 집계 함수만 있다면 GROUP BY 절이 없어도 사용이 가능하다.
• 집계함수(MAX, MIN)는 WHERE 절에 직접 사용은 할 수 없으며, 서브 쿼리와 같은 방법을 사용해야 한다.

 SELECT 
 PAT_NM
 ,MAX(FINL_VIST_SN) AS MAX1
 ,MIN(FINL_VIST_SN) AS MIN1
  FROM V_WPPIMINFO
  GROUP BY PAT_NM
  ORDER BY MAX1 DESC, MIN1 

6.CASE WHEN

• 오라클에서 if 문과 비슷한 기능을 하는 DECODE 함수가 있다. 그러나 DECODE 함수는 조건이 많아지면 가독성이 떨어지고 복잡해지며, 가장 큰 문제는 오라클 SQL에서만 사용할 수 있는 비표준 함수이다.

• 오라클에서 DECODE 함수를 대체할 수 있는 기능이 CASE 표현식이며 가독성이 좋고 더 많은 기능을 제공한다. 조건이 복잡한 경우 DECODE 함수 보다 CASE 표현식을 사용할 것을 권장한다.

1) IF문 방식

 SELECT PATNO
     , ROAD_NM_ADDR_YN   
     , CASE WHEN ROAD_NM_ADDR_YN = 'Y' THEN '도로명 주소 있음'
            WHEN ROAD_NM_ADDR_YN = 'N' THEN '도로명 주소 없음'
            ELSE null
            END AS ROAD_ADDR
  FROM V_WPPIMINFO

2) Switch문 방식

  SELECT PAT_NM
     , FINL_VIST_SN
     , CASE WHEN FINL_VIST_SN >= 100  THEN '아픈환자'
            WHEN FINL_VIST_SN >= 50 THEN '중간환자'
            WHEN FINL_VIST_SN >= 10 THEN '안아픈 환자'
            WHEN FINL_VIST_SN >= 0 THEN '방문한 사람'
            ELSE NULL
       END AS PAT_GRADE
  FROM V_WPPIMINFO