Oracle 函数的写法,以及简单的调用

1：首先看创建一个函数

  给定一个日期,判断是否是休息日.

create or replace function test(date_in in date)
    return int
is
    num int;
    workflag varchar2(1);
begin
    select count(*) into num from Sys_Holiday
        where to_char(TODAY,'yyyy-MM-dd')=to_char(date_in,'yyyy-MM-dd');

    if (num>0) then
        select WORKFLAG into workflag from Sys_Holiday
            where to_char(TODAY,'yyyy-MM-dd')=to_char(date_in,'yyyy-MM-dd');
        if (workflag='W') then
            return 0;
        else
            return 1;
        end if;
    else
        select to_char(date_in,'D') into num from dual;
        if (num=7 or num=1) then
            return 1;
        else
            return 0;
        end if;
    end if;
end;

 

2：另外一个函数调用这个函数,一个日期增加或减少多少天对应的日期(工作日) (有点罗嗦)

 

CREATE OR REPLACE FUNCTION getWorkDayADD( datetime in date,workday in int) return date
is
    dayflag int; --初始值,写循环
    sumflag int; --判断值
    datetime1 date;
    workday1  int;
    sysdateadd date;
    daynum    int;
    sysdatelast date; --返回值
begin
    datetime1 :=datetime;
    workday1 := workday;
    dayflag :=0;
    sumflag :=1;
    if(workday1>0) --加减多少来决定
        then
            while (sumflag=workday1) loop
                 dayflag := dayflag+1;
                 select datetime1+dayflag into sysdateadd from dual;
                 daynum :=test(sysdateadd);
                       if(daynum=1)then
                         sumflag :=sumflag;
                       else
                         sumflag :=sumflag+1;
                       end if;
            end loop;    
         else
             while (sumflag=workday1) loop
                   dayflag := dayflag+1;
                 select datetime1-dayflag into sysdateadd from dual;
                 daynum :=test(sysdateadd);
                       if(daynum=1)then
                         sumflag :=sumflag;
                       else
                         sumflag :=sumflag+1;
                       end if;
              end loop;
           
      end if;
  if(workday1>0) then
        select datetime1+dayflag into sysdatelast  from dual;
       else
        select datetime1-dayflag into sysdatelast  from dual;
    end if;       
  
  return (sysdatelast);
end getWorkDayADD;

 

3：写一个简单存储过程测试一下

create or replace procedure x is
  c date;
  datetime date;
  begin
	select sysdate into datetime from dual;	
	dbms_output.put_line(datetime);
	dbms_output.put_line('1231');
   c := getWorkDayADD(datetime,3);
   dbms_output.put_line(c);
   end;

 

4: 执行这个存储过程

SQL> exec x