Oracle 创建函数实例
Oracle 创建函数实例
1.Oracle 创建函数
- create [or replace] function function_name
- [(parameter_name [in | out | in out] type [,...])]
- retutn type
- is|as
- begin
- function_body;
- end;
说明:函数必须有返回值。
or replace:如果函数已经存在,则替换现有的函数;
function_name:函数名;
parameter_name :参数名;
in | out | in out:指定参数的模式;
function_body:包含执行任务函数的SQL和PL/SQL语句。
2、函数实例
- create or replace function get_publicholidaytime(fromtime in Date, totime in Date, isAvailable 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语句
- if isAvailable=1 then
- --sql语句
- select min(calendar_date) ,max(calendar_date),count(calendar_date) into mindate,maxdate,total
- from T_BI_TNT_DATE
- where 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;
3、调用函数
select get_publicholidaytime(sysdate,sysdate + 1,1) from dual;
4、删除函数
drop function get_publicholidaytime;
评论暂时关闭