|
create or replace package PG_PPM_COMMON_UTIL is
type commCur is ref cursor; -- 定义公用游标
-- Author : kangyong
-- Created : 2011-4-8
-- Purpose : 公共操作组件
-- 取某个时间最近的一个工作日
FUNCTION getLateWorkday(DESC_DATE IN DATE) RETURN date;
-- 判断某个时间是否是工作日
FUNCTION isWorkday(DESC_DATE IN DATE) RETURN number;
-- 查询两个时间段内的工作日（节假日除外,节假日表:prod_public_holiday）
-- 精确到小数点后1位
FUNCTION calcWorkdays(I_START_DATE IN DATE, I_END_DATE IN DATE) RETURN number;
end PG_PPM_COMMON_UTIL;
/
create or replace package body PG_PPM_COMMON_UTIL is
-- Author : kangyong
-- Created : 2011-4-8
-- Purpose : 公共操作组件
-- 取某个时间最近的一个工作日
FUNCTION getLateWorkday(DESC_DATE IN DATE) RETURN date IS
num number(3);
temp_date date;
begin
temp_date := DESC_DATE;
loop
select count(1)
into num
from (select trunc(temp_date) dayList from dual) A
where not exists (select 1
from prod_public_holiday b
where b.ph_date = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
--dbms_output.put_line(num);
exit when(num > 0);
select (temp_date + 1) into temp_date from dual;
end loop;
return temp_date;
end;
-- 判断某个时间是否是工作日
FUNCTION isWorkday(DESC_DATE IN DATE) RETURN number Is
num number(3);
temp_date date;
begin
temp_date := DESC_DATE;
select count(1)
into num
from (select trunc(temp_date) dayList from dual) A
where not exists (select 1
from prod_public_holiday b
where b.ph_date = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
--dbms_output.put_line(num);
return num;
end;
-- 查询两个时间段内的工作日（节假日除外,节假日表:prod_public_holiday）
-- 精确到小数点后1位
FUNCTION calcWorkdays(I_START_DATE IN DATE, I_END_DATE IN DATE)
RETURN number IS
NO_OF_DAYS number;
START_DATE date;
END_DATE date;
hour number := 0;
mi number := 0;
mis number := 0;
days number(12,1) := 0.0;
m_days number := 0;
isworkday_flag number := -1;
BEGIN
if I_START_DATE is not null and I_END_DATE is not null then
START_DATE := getLateWorkday(I_START_DATE);
END_DATE := getLateWorkday(I_END_DATE);
isworkday_flag := isWorkday(I_END_DATE);
if isworkday_flag = 0 THEN
m_days := m_days + 1;
end if;
IF START_DATE < END_DATE THEN
if(START_DATE = I_START_DATE) then
m_days := m_days + 1;
select (24-to_number(to_char(START_DATE,'HH24'))) into hour from dual;
select (60-to_number(to_char(START_DATE,'MI'))) into mi from dual;
mis := mis + (hour * 60) - mi;
end if;
if(END_DATE = I_END_DATE) then
m_days := m_days + 1;
select to_number(to_char(END_DATE,'HH24')) into hour from dual;
select to_number(to_char(END_DATE,'MI')) into mi from dual;
mis := mis + (hour * 60) + mi;
end if;
SELECT count(1) days
INTO NO_OF_DAYS
FROM (SELECT DISTINCT trunc(START_DATE) + level - 1 dayList
FROM dual
connect BY trunc(START_DATE) + level - 1 < = trunc(END_DATE)) A
where not exists (select 1
from prod_public_holiday b
where b.ph_date = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
--大于半个小时，按1小时算 round(mis/60)
days := ( (NO_OF_DAYS - m_days) * 24 + round(mis/60) ) / 24;
ELSE
if(END_DATE = I_END_DATE) then
m_days := m_days + 1;
select (24-to_number(to_char(END_DATE,'HH24'))) into hour from dual;
select (60-to_number(to_char(END_DATE,'MI'))) into mi from dual;
mis := mis + (hour * 60) - mi;
end if;
if(START_DATE = I_START_DATE) then
m_days := m_days + 1;
select to_number(to_char(START_DATE,'HH24')) into hour from dual;
select to_number(to_char(START_DATE,'MI')) into mi from dual;
mis := mis + (hour * 60) + mi;
end if;
SELECT count(1) days
INTO NO_OF_DAYS
FROM (SELECT DISTINCT trunc(END_DATE) + level - 1 dayList
FROM dual
connect BY trunc(END_DATE) + level - 1 < = trunc(START_DATE)) A
where not exists (select 1
from prod_public_holiday b
where b.ph_date = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
--大于半个小时，按1小时算 round(mis/60)
days := 0 - ( ( (NO_OF_DAYS - m_days) * 24 + round(mis/60) ) / 24 );
END IF;
else
--开始时间或结束时间有为空的
dbms_output.PUT_LINE('I_START_DATE:' || I_START_DATE || 'I_END_DATE:' || I_END_DATE ||'开始时间或结束时间有为空的');
RAISE_APPLICATION_ERROR(-20001, '开始时间或结束时间有为空的');
end if;
Return days;
END;
end PG_PPM_COMMON_UTIL;
/
|
