oracle函数生成周末过程

本文介绍了oracle函数生成周末过程。

首先创建程序包

create or replace package pkg_workday is

function proc_TurnYear(nyear in varchar2) return number;

end ;

接着创建程序包体部分

create or replace package body pkg_workday is

function proc_TurnYear(nyear in varchar2) return number

as

firstDay date;

lastDay date;

currDate date;

strDay varchar2(1);

strDesc varchar2(20);

numReturn number(2) := 0;

begin

delete from holiday where year = nyear;

select to_date(nyear||'/01/01','yyyy/mm/dd') into firstDay from dual;

select to_date(nyear||'/12/31','yyyy/mm/dd') into lastDay from dual;

currDate := firstDay;

loop

select to_char(currDate,'d') into strDay from dual;

if strDay = '1' then strDesc :='星期日';

insert into holiday values(nyear,to_char(currDate,'yyyymmdd'),strDesc,sysdate);

elsif strDay = '7' then strDesc := '星期六';

insert into holiday values(nyear,to_char(currDate,'yyyymmdd'),strDesc,sysdate);

end if;

currDate := currDate+1;

numReturn := 1;

exit when currDate>lastDay;

end loop;

commit;

return numReturn;

end ;

end pkg_workday ;

测试部分

SQL> declare

2 num number;

3 begin

4 num:= pkg_workday.proc_TurnYear('2008');

5 if num = 1 then

6 dbms_output.put_line('生成假日成功!');

7 else

8 dbms_output.put_line('生成假日失败!');

9 end if;

10* end;

SQL> /

生成假日成功!

PL/SQL 过程已成功完成。

SQL> select count(1) from holiday;

COUNT(1)

----------

104

SQL>