[오라클] MOD, TRUNC, EXTRACT

WITH DATA AS(         

SELECT 'AAA' AS NAME, '810519-1234567' AS JUMIN FROM DUAL

UNION ALL

SELECT 'BBB' AS NAME, '790519-2234567' AS JUMIN FROM DUAL

UNION ALL

SELECT 'CCC' AS NAME, '020519-3234567' AS JUMIN FROM DUAL

UNION ALL

SELECT 'DDD' AS NAME, '840519-2234567' AS JUMIN FROM DUAL

)

SELECT NAME

      ,JUMIN

      ,CASE WHEN MOD(SUBSTR(JUMIN,8,1),2) = 0 THEN '여'

            ELSE '남' 

        END AS 성별

      ,CASE WHEN SUBSTR(JUMIN,8,1) IN (1,2) THEN (EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUMIN,1,2)+1900))+1

            ELSE                                 (EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUMIN,1,2)+2000))+1

        END AS 나이

      ,TRUNC(CASE WHEN SUBSTR(JUMIN,8,1) IN (1,2) THEN (EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUMIN,1,2)+1900))+1

                  ELSE                                 (EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUMIN,1,2)+2000))+1

              END ,-1 ) AS 연령대

  FROM DATA

■ MOD : 나머지를 구하는 함수

- 주민번호 뒤첫자리를 가져와 2로 나누어 [나머지]가 1이면 '여' 아니면 '남' 

CASE WHEN MOD(SUBSTR(JUMIN,8,1),2) = 0 THEN '여'

     ELSE '남' 

 END AS 성별

다른예시)

SELECT MOD('11', 4) FROM DUAL 

SELECT MOD('11', 3) FROM DUAL 

SELECT MOD('11', 2) FROM DUAL 

■ EXTRACT : 특정 날짜,시간 값이나 날짜 값 표현식으로부터 지정된 날짜 영역의 값을 추출하여 반환

- [SYSDATE의 YEAR를 가져와서] 생일연도를 뺀 다음(만 나이) 1살을 더해주면 나이

CASE WHEN SUBSTR(JUMIN,8,1) IN (1,2) 

     THEN (EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUMIN,1,2)+1900))+1

     ELSE (EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUMIN,1,2)+2000))+1

 END AS 나이

-- 뒤첫자리가 1또는 2일때는 1900년대생 3, 4이후는 2000년대생

다른예시)

SELECT SYSDATE                      AS TODAY

      ,EXTRACT(YEAR   FROM SYSDATE) AS YY

      ,EXTRACT(MONTH  FROM SYSDATE) AS MM

      ,EXTRACT(DAY    FROM SYSDATE) AS DD

      ,SYSTIMESTAMP

      ,EXTRACT(HOUR   FROM SYSTIMESTAMP) AS HH --▷ 대한민국 시차 차이(9시간)

      ,EXTRACT(MINUTE FROM SYSTIMESTAMP) AS MI

      ,EXTRACT(SECOND FROM SYSTIMESTAMP) AS SS

  FROM DUAL

■ TRUNC : 원하는 소수점 자리에서 절삭

- 위에서 구한 나이에서 일의 자리 [잘라내면(-1)] 연령대 

다른예시)

SELECT TRUNC('123.45',  2) FROM DUAL 

SELECT TRUNC('123.45',  1) FROM DUAL 

SELECT TRUNC('123.45',  0) FROM DUAL 

SELECT TRUNC('123.45', -1) FROM DUAL 

SELECT TRUNC('123.45', -2) FROM DUAL 

-- RR(00~49까지는 2000년대, 50~99까지는 1900년대)
-- CC(세기)
SELECT TO_CHAR(TO_DATE('860326','RRMMDD'), 'YYYY/MM/DD') FROM DUAL
SELECT TO_CHAR(TO_DATE('020326','RRMMDD'), 'YYYY/MM/DD') FROM DUAL
SELECT TO_CHAR(TO_DATE('020326','RRMMDD'), 'CC YYYY/MM/DD') FROM DUAL

 
SELECT * FROM nls_session_parameters
 
 
SELECT SYSTIMESTAMP A, EXTRACT(HOUR FROM SYSTIMESTAMP) B, TO_CHAR(SYSTIMESTAMP,'HH24') C
  FROM DUAL;

SELECT
       SYSTIMESTAMP
     , TO_CHAR(SYSTIMESTAMP, 'HH24') ch
     , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) etzh
     , EXTRACT(HOUR FROM SYSTIMESTAMP) eh
     , EXTRACT(HOUR FROM CAST(SYSTIMESTAMP AS TIMESTAMP)) ehc
  FROM DUAL;

SELECT SYSTIMESTAMP
     , EXTRACT(hour FROM SYSTIMESTAMP) h1
     , EXTRACT(hour FROM SYSTIMESTAMP)
     + EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) h2
  FROM DUAL;