함수 #1

2010/05/19

예제 위주...


SELECT CEIL(15.7) "Ceiling" FROM DUAL;

SELECT FLOOR(15.7) "Floor" FROM DUAL;

SELECT MOD(11,4) "Modulus" FROM DUAL;

SELECT ROUND(15.193,1) "Round" FROM DUAL;

SELECT WIDTH_BUCKET(92,0,100,10) "Score" FROM dual;

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 30), 'YYYY-MM-DD') FROM dual;

SELECT LAST_DAY(SYSDATE) - SYSDATE "REMAIN DAYS" FROM dual;

SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) "REMAIN MONTHS" FROM DUAL;

SELECT SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;

-- 주민등록번호 123456-7890123
-- 123456-7******

SELECT RPAD(SUBSTR('123456-1234567',0,8),14,'*') "NUMBER"
FROM dual;

-- 월별 입사인원을 구해보자
-- 출력결과 > 월, 인원
SELECT EXTRACT(MONTH FROM hire_date), count(*)
FROM employees
GROUP BY EXTRACT(MONTH FROM hire_date)
ORDER BY EXTRACT(MONTH FROM hire_date);

-- 분기별 입사인원을 구해보자
-- 출력결과 > 분기, 인원
SELECT hire_date, WIDTH_BUCKET(EXTRACT(MONTH FROM hire_date),1,12,4)
FROM employees;

SELECT CEIL(EXTRACT(MONTH FROM hire_date)/3), COUNT(*)
FROM employees
GROUP BY CEIL(EXTRACT(MONTH FROM hire_date)/3)
ORDER BY CEIL(EXTRACT(MONTH FROM hire_date)/3);

-- 월(1년) 더하기
SELECT ADD_MONTHS(TO_DATE('2008/01/01','YYYY/MM/DD'),12)
FROM dual;