1：首先看创建一个函数
给定一个日期,判断是否是休息日.
1 create or replace function test(date_in in date) 2 return int 3 is 4 num int; 5 workflag varchar2(1); 6 begin 7 select count(*) into num from Sys_Holiday 8 where to_char(TODAY,'yyyy-MM-dd')=to_char(date_in,'yyyy-MM-dd'); 9 10 if (num>0) then 11 select WORKFLAG into workflag from Sys_Holiday 12 where to_char(TODAY,'yyyy-MM-dd')=to_char(date_in,'yyyy-MM-dd'); 13 if (workflag='W') then 14 return 0; 15 else 16 return 1; 17 end if; 18 else 19 select to_char(date_in,'D') into num from dual; 20 if (num=7 or num=1) then 21 return 1; 22 else 23 return 0; 24 end if; 25 end if; 26 end;
2：另外一个函数调用这个函数,一个日期增加或减少多少天对应的日期(工作日)
1 CREATE OR REPLACE FUNCTION getWorkDayADD( datetime in date,workday in int) return date 2 is 3 dayflag int; --初始值,写循环 4 sumflag int; --判断值 5 datetime1 date; 6 workday1 int; 7 sysdateadd date; 8 daynum int; 9 sysdatelast date; --返回值 10 begin 11 datetime1 :=datetime; 12 workday1 := workday; 13 dayflag :=0; 14 sumflag :=1; 15 if(workday1>0) --加减多少来决定 16 then 17 while (sumflag=workday1) loop 18 dayflag := dayflag+1; 19 select datetime1+dayflag into sysdateadd from dual; 20 daynum :=test(sysdateadd); 21 if(daynum=1)then 22 sumflag :=sumflag; 23 else 24 sumflag :=sumflag+1; 25 end if; 26 end loop; 27 else 28 while (sumflag=workday1) loop 29 dayflag := dayflag+1; 30 select datetime1-dayflag into sysdateadd from dual; 31 daynum :=test(sysdateadd); 32 if(daynum=1)then 33 sumflag :=sumflag; 34 else 35 sumflag :=sumflag+1; 36 end if; 37 end loop; 38 39 end if; 40 if(workday1>0) then 41 select datetime1+dayflag into sysdatelast from dual; 42 else 43 select datetime1-dayflag into sysdatelast from dual; 44 end if; 45 46 return (sysdatelast); 47 end getWorkDayADD;
3 编写一个简单的存储过程调用以上的函数
1 create or replace procedure x is 2 c date; 3 datetime date; 4 begin 5 select sysdate into datetime from dual; 6 dbms_output.put_line(datetime); 7 dbms_output.put_line('1231'); 8 c := getWorkDayADD(datetime,3); 9 dbms_output.put_line(c); 10 end;
4 执行存储过程
sql>exec x;
