날짜함수
* SYSDATE
SYSDATE
--현재 시스템의 날짜 데이터를 반환
SQL> SELECT TO_CHAR(sysdate, 'yyyymmddhh24mmss') FROM dual
결과 : 20091106201101
* ADD_MONTHS
ADD_MONTHS(date, integer)
지정한 날짜에서 해당월에 일정한 정수(혹은 마이너스 정수)값을 더한 날짜를 반환
SQL> SELECT TO_CHAR(ADD_MONTHS(sysdate,-2), 'YYYY-MM-DD') FROM dual
결과 : 2009-09-06
* LAST_DAY
LAST_DAY(date)
해당월의 마지막 날짜를 반환한다.
SQL> SELECT LAST_DAY(sysdate) FROM dual
결과: 2009/11/30 20:57:43 (현재일자는 2009/11/06)
* MONTHS_BETWEEN
MONTHS_BETWEEN(Date_1, Date_2)
Date_1 과 Date_2 사이의 기간을 월로 나타내 준다.
(한달 이내일 경우나 정확한 달로 맞아떨어지지 않을 경우는 소수점으로 표시)
SQL> SELECT MONTHS_BETWEEN('20091101','20090801') FROM dual
결과 : 3
SQL> SELECT MONTHS_BETWEEN('20091115','20090830') FROM dual
결과 : 2.51612903225806451612903225806451612903
변환함수
* TO_CHAR 함수
TO_CHAR(NUMBER, "Format")
-- NUMBER형 데이타 타입을 문자열로 변환
SQL> SELECT TO_CHAR(233900, '$999,999') FROM dual
결과: $233,900
SQL> SELECT TO_CHAR(to_date(20090112), 'YYYY-MM-DD') FROM dual
결과: 2009-01-12
* NVL 함수
NVL(ex1, ex2)
-- ex1 값이 NULL이 아니라면 ex1의 값, NULL이라면 ex2의 값을 반환
SELECT NVL( 20, 0 ) tup_c FROM dual
결과: 20
SELECT NVL( '', 0 ) tup_c FROM dual
결과: 0
레벨업 쿼리문 1
** 날짜함수 및 변환함수 사용 예제
(입력값으로 YYYYMMDD를 받아 YYYY(MM-1)25 ~ YYYY(MM-1)31 일자의 범위내의 해당 데이터를 읽어오는 쿼리문)
SELECT
count(apv_tr_rqr_am) AS io_un_byng_cn
,sum(apv_tr_rqr_am) AS io_un_byng_am
FROM tb_cs_az_cm_dom_crd_az
WHERE cd_no IN (
SELECT cd_no
FROM tb_cs_ae_cm_gencd_bsc
WHERE cd_natv_no =
( SELECT cd_natv_no
FROM tb_cs_ae_cm_gencd_bsc
WHERE cd_no = :cd_no
)
)
-- 전월의 25~31 일 범위
AND tr_dt BETWEEN TO_CHAR(ADD_MONTHS(to_date(substr(:bas_dt,1,6),'yyyymm'), -1 ),'YYYYMM') ||'25' AND TO_CHAR(ADD_MONTHS(to_date(substr(:bas_dt,1,6),'yyyymm'), -1 ),'YYYYMM') ||'31'
레벨업 쿼리문 2
** DBIO 속도튜닝 예제
(일정데이터 기준일자 이후의 모든건 및 최신건을 가져오며 서로 다른 테이블에서 각각 명을 참조하는 쿼리문)
DB의 풀 조회 횟수를 최대한 줄이고 가능한 조건내에서 선택적으로 값을 가져옴으로서 속도면에서 최적화
SELECT
x1.mcht_grp_dsc
, x1.mcht_mbdc
, CASE
WHEN x1.mcht_mbdc_length < 11 THEN x2.mcht_mtalnm
ELSE x3.mcht_mtalnm
END AS mcht_mtalnm
, x1.apl_st_dt
, x1.apl_ed_dt
, x1.wkr_mbdy_no || ' ' || x4.empnm AS io_wkr_nm
, x1.wk_dtm
FROM (
SELECT t2.mcht_grp_dsc
, t2.mcht_mbdc
, t2.apl_st_dt
, t2.apl_ed_dt
, t2.wkr_mbdy_no
, t2.wk_dtm
, length(t2.mcht_mbdc) mcht_mbdc_length
FROM ( SELECT *
FROM( -- 기준일자 적용건
SELECT mcht_grp_dsc
, mcht_mbdc
, apl_st_dt
FROM tb_cs_jh_ch_mchtgrp
WHERE mcht_grp_no = :mcht_grp_no
AND mcht_grp_dsc = :mcht_grp_dsc
AND apl_ed_dt >= :apl_st_dt /* 기준일자 :apl_st_dt */
UNION
-- 최신건
SELECT mcht_grp_dsc
, mcht_mbdc
, max(apl_st_dt) apl_st_dt
FROM tb_cs_jh_ch_mchtgrp
WHERE mcht_grp_no = :mcht_grp_no
AND mcht_grp_dsc = :mcht_grp_dsc
GROUP BY mcht_grp_dsc, mcht_mbdc
)
ORDER BY mcht_mbdc
) t1,
tb_cs_jh_ch_mchtgrp t2
WHERE t1.mcht_grp_dsc = t2.mcht_grp_dsc
AND t1.mcht_mbdc = t2.mcht_mbdc
AND t1.apl_st_dt = t2.apl_st_dt
AND ((t1.mcht_mbdc > :mcht_mbdc)
OR (t1.mcht_mbdc = :mcht_mbdc AND t1.apl_st_dt >= :io_nx_apl_st_dt))
GROUP BY t2.mcht_grp_dsc, t2.mcht_mbdc, t2.apl_st_dt, t2.apl_ed_dt
, t2.wkr_mbdy_no, t2.wk_dtm
ORDER BY t2.mcht_grp_dsc, t2.mcht_mbdc
) x1
, tb_cs_mc_cm_bsc x2
, tb_cs_mc_cm_tup_bsc x3
, tb_cs_ui_cm_emp x4 -- 공통_직원정보
WHERE x1.mcht_mbdc = x2.mcht_no(+)
AND x1.mcht_mbdc = x3.aflcn_mcht_no(+)
AND x1.wkr_mbdy_no = x4.eno(+)
