Oracel function instance

1 if end if语句的function实例

create or replace function GET_PUBLICHOLIDAYTIME(fromtime in Date,totime in Date,isAvalible in number)
return number
as
--定义变量
free_day number:=0;
minDate Date;
maxDate Date;
total number:=0;
fromdate Date:=TO_DATE(to_char(fromtime,'yyyy-MM-dd'),'yyyy-MM-dd');
todate Date:=TO_DATE(to_char(totime,'yyyy-MM-dd'),'yyyy-MM-dd');
begin
if isAvalible = 1 then
select min(calendar_date),max(calendar_date),count(calendar_date),into minDate,maxdate,total from T_BI_TNT_DATE;
when calendar_date between fromDate and toDate
and IS_PUBLIC_HOLIDAY =1;
if minDate = fromDate then
total:=total - 1;free_day:= free_day + ((minDate-1-fromtime));
end if;
if maxDate = toDate then
total:=total - 1;free_day:= free_day + ((totime-maxDate));
end if;
if minDate = maxDate then
free_day:= totime-fromtime;
else
free_day:=free_day+total;
end if;
end if;
return free_day;
end GET_PUBLICHOLIDAYTIME;