Ooracle：计算2个日期相差天数（除节假日周末）

create or replace function count_days_diff(start_time date ,end_time date)
RETURN NUMBER
is
TMP DATE ;
days number ;

begin
if end_time is null
then
return ceil(sysdate-start_time);
end if;
days:= ceil(end_time-start_time);
TMP:= TRUNC(start_time-1);

LOOP
TMP:=TMP+1;
IF TMP> end_time THEN
EXIT;

elsif to_char(TMP,'d') ='7'OR to_char(TMP,'d') ='1' THEN
days:=days-1;

end IF;
END LOOP;
DECLARE
CURSOR date_setting IS
SELECT * FROM JHMR_MRRECEIVESIGNHOLIDAY t
WHERE t.holidaydate>=start_time and t.holidaydate<=end_time;
begin
FOR oneday IN date_setting
LOOP
if (to_char(oneday.HOLIDAYDATE,'d') ='7'or to_char(oneday.HOLIDAYDATE,'d') ='1') and oneday.TYPE=1 then
days:=days+1;
elsif (to_char(oneday.HOLIDAYDATE,'d') !='7'and to_char(oneday.HOLIDAYDATE,'d') !='1'and oneday.TYPE=0) then
days:=days-1;
end if;
end loop;
end;

return days;
end;

 

------------------------------------------------------------------------------------

-- 新建法定节假日表格
create table JHMR_MRRECEIVESIGNHOLIDAY
(
holidaydate DATE not null,
type NUMBER(1) default 0 not null
)
tablespace TSP_JHEMR
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table JHMR_MRRECEIVESIGNHOLIDAY
is '休息日 、上班日设置 ，如2013.9.24';
-- Add comments to the columns
comment on column JHMR_MRRECEIVESIGNHOLIDAY.type
is '0为休息日，1为上班日。';
-- Create/Recreate primary, unique and foreign key constraints
alter table JHMR_MRRECEIVESIGNHOLIDAY
add constraint PK_JHMR_MRRECEIVESIGNHOLIDAY primary key (HOLIDAYDATE, TYPE)
using index
tablespace TSP_JHEMR
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 2M
next 1M
minextents 1
maxextents unlimited
);