oracle函数生成周末过程

核心提示：首先创建程序包create or replace package pkg_workday isfunction proc_TurnYear(nyear in varchar2) return number;end ;接着创建程序包体部分create or replace package body pkg_workday

首先创建程序包

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>