문자 및 날짜 기본 함수

/* 문자 함수 */

SELECT SYSDATE 
        , TRUNC(SYSDATE, 'YEAR') AS CUR_YEAR    /*년의 시작일*/
        , TRUNC(SYSDATE, 'MONTH') AS CUR_MON   /*월의 시작읷*/
        , TRUNC(SYSDATE, 'DAY') AS CUR_DAY     /*주의 시작읷*/
        , TRUNC(SYSDATE, 'HH24') AS CUR_HH    /*현재 시작시간*/
        , TRUNC(SYSDATE, 'MI') AS CUR_MI      /*현재 분의시간*/
         , TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS CUR_TIME 
        , TO_CHAR(123456,'999,999') AS NUM_TO_CHAR       
        , TO_DATE('20161204', 'YYYYMMDD') AS CHAR_TO_DATE
        , TO_DATE('20161204123000', 'YYYYMMDDHH24MISS') AS CHAR_TIME1
        , TO_DATE('2016/12/04 12:30:00','YYYY/MM/DD HH24:MI:SS') AS CHAR_TIME2
FROM DUAL;

SELECT TRUNC(123.4567, 2) AS TRUNC_NUM1  -- 2째 자리까지
     , TRUNC(123.4567) AS TRUNC_NUM2  -- 정수만 출력
     , TRUNC(123.4567,1) AS TRUNC_NUM3
     , ROUND(123.456,2) AS ROUND_NUM1  -- 지정 자리 반올림
     , ROUND(123.456) AS ROUND_NUM2  -- 반올림
     , ROUND(123.4567,1) AS ROUND_NUM3
     , CEIL(123.456) AS CEIL_NUM1
     , CEIL(-123.4567) AS CEIL_NUM2
     , FLOOR( 123.456) AS FLOOR_NUM1   
     , FLOOR(-123.4567) AS FLOOR_NUM2
     ,MOD(10,3 ) AS MOD_NUM -- 나머지 
FROM DUAL;


SELECT LAST_DAY(SYSDATE)
     , NEXT_DAY(SYSDATE, 1)  -- 기준일 다음날부터 찾고자 하는 첫번째 요일
     , NEXT_DAY(SYSDATE, '일요일')
     , ADD_MONTHS(SYSDATE, 1)
FROM DUAL;

SELECT rowid, A.* FROM EMPLOYEES A

/* NVL */
SELECT NVL(COMMISSION_PCT, 0) FROM EMPLOYEES;  /* NULL이면 0으로 변환 */
SELECT NVL2(COMMISSION_PCT, 'Y', 'N') FROM EMPLOYEES;


/* DECODE VS CASE WHEN */
SELECT DECODE(TO_CHAR(SYSDATE, 'D'), 1,'일', 2,'월',3,'화', 4,'수',5,'금',6,'토') AS WEEKDAY
FROM DUAL;

SELECT CASE TO_CHAR(SYSDATE, 'D') 
          WHEN '1' THEN '일'
          WHEN '2' THEN '월'
          WHEN '3' THEN '화'
          WHEN '4' THEN '수'
          WHEN '5' THEN '목'
          WHEN '6' THEN '금'
          ELSE '토' END AS WEEKDAY
FROM DUAL;

/* EXISTS IN */
SELECT * FROM EMPLOYEES E
WHERE EXISTS
( SELECT 'X' FROM JOB_HISTORY JH
WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID );

SELECT * FROM EMPLOYEES E 
WHERE NOT EXISTS 
( SELECT 'X' FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID );