---计算两个日期之间的天数
/*
参数解释:
p_isContain_Weekend: 是否包含周末(0: 不包含;
1: 包含;
2: 包含周六不包括周天;
3: 包括周天不包括周六)
p_scope_type:
1: [start_Date, end_Date] ;
2: [start_Date, end_Date) ;
3: (start_Date, end_Date];
4: (start_Date, end_Date);
使用:传输yyyy-MM-dd类型的开始/结束日期,然后通过组合p_isContain_Weekend与p_scope_type
得到想要的结果。
*/
CREATE OR REPLACE FUNCTION f_get_Days_Count (p_start_time
IN DATE,
p_end_time
IN DATE,
p_isContain_Weekend
IN INT,
p_scope_type
IN INT
)
RETURN NUMBER IS
v_count
NUMBER;
BEGIN
------------包含周末--------------
--包含周末,且区间为:[start_Date, end_Date]
IF (p_isContain_Weekend =1
AND p_scope_type=1 )
THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) +
LEVEL - 1
AS
dt
FROM dual
CONNECT BY (p_start_time) +
LEVEL < p_end_time + 2)
WHERE
trim(
to_char(
dt , 'day', '
NLS_DATE_LANGUAGE = American'))
IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
saturday','
sunday');
END IF ;
--包含周末,且区间为:[start_Date, end_Date)
IF (p_isContain_Weekend =1
AND p_scope_type=2 )
THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) +
LEVEL - 1
AS dt
FROM dual
CONNECT BY (p_start_time) +
LEVEL < p_end_time + 1)
WHERE trim(
to_char(dt, 'day', '
NLS_DATE_LANGUAGE = American'))
IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
saturday','
sunday');
END IF ;
--包含周末,且区间为:(start_Date, end_Date]
IF (p_isContain_Weekend =1
AND p_scope_type=3 )
THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) +
LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) +
LEVEL < p_end_time +1)
WHERE trim(
to_char(dt, 'day', '
NLS_DATE_LANGUAGE = American'))
IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
saturday','
sunday');
END IF ;
--包含周末,且区间为:(start_Date, end_Date)
IF (p_isContain_Weekend =1 AND p_scope_type=4 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time )
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday','sunday');
END IF ;
------------不包含周末--------------
--不包含周末,且区间为:[start_Date, end_Date]
IF (p_isContain_Weekend =0 AND p_scope_type=1 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL - 1 AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time + 2)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
END IF ;
--不包含周末,且区间为:[start_Date, end_Date)
IF (p_isContain_Weekend =0 AND p_scope_type=2 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL - 1 AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time + 1)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
END IF ;
--不包含周末,且区间为:(start_Date, end_Date]
IF (p_isContain_Weekend =0 AND p_scope_type=3 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time +1)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
END IF ;
--不包含周末,且区间为:(start_Date, end_Date)
IF (p_isContain_Weekend =0 AND p_scope_type=4 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time )
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
END IF ;
-------------包含周六不包括周天---------------
--包含周六不包括周天,且区间为:[start_Date, end_Date]
IF (p_isContain_Weekend =2 AND p_scope_type=1 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL - 1 AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time + 2)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday');
END IF ;
--包含周六不包括周天,且区间为:[start_Date, end_Date)
IF (p_isContain_Weekend =2 AND p_scope_type=2 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL - 1 AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time + 1)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday');
END IF ;
--包含周六不包括周天,且区间为:(start_Date, end_Date]
IF (p_isContain_Weekend =2 AND p_scope_type=3 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time +1)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday');
END IF ;
--包含周六不包括周天,且区间为:(start_Date, end_Date)
IF (p_isContain_Weekend =2 AND p_scope_type=4 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time )
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday');
END IF ;
-----------包含周天不包括周六-----------------
--包含周天不包括周六,且区间为:[start_Date, end_Date]
IF (p_isContain_Weekend =3 AND p_scope_type=1 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL - 1 AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time + 2)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
sunday');
END IF ;
--包含周天不包括周六,且区间为:[start_Date, end_Date)
IF (p_isContain_Weekend =3 AND p_scope_type=2 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL - 1 AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time + 1)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
sunday');
END IF ;
--包含周天不包括周六,且区间为:(start_Date, end_Date]
IF (p_isContain_Weekend =3 AND p_scope_type=3 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time +1)
WHERE trim(to_char(dt, 'day', 'NLS_DATE_LANGUAGE = American')) IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
sunday');
END IF ;
--包含周天不包括周六,且区间为:(start_Date, end_Date)
IF (p_isContain_Weekend =3 AND p_scope_type=4 ) THEN
SELECT COUNT(*) INTO v_count
FROM (
SELECT (p_start_time) + LEVEL AS dt
FROM dual
CONNECT BY (p_start_time) + LEVEL < p_end_time )
WHERE trim(
to_char(dt, 'day', '
NLS_DATE_LANGUAGE = American')) IN
('
monday', '
tuesday', '
wednesday', '
thursday', '
friday','
sunday');
END IF ;
--start
dbms_output.put_line('v_count:' || v_count);
--end
RETURN v_count;
END f_get_Days_Count;