Oracle PL/SQL (9) - 自定义函数functio

函数与存储过程相似，是数据库中存储的已命名PL-SQL程序块。函数的主要特征是它必须有一个返回值。通过return来指定函数的返回类型。在函数的任何地方可以通过return expression语句从函数返回，返回类型必须和声明的返回类型一致。

Oracle自定义函数的语法如下：

create or replace function 函数名(参数1 模式 参数类型)
return 返回值类型
as
变量1 变量类型;
变量2 变量类型;
begin
   函数体;
end 函数名;

参数的模式有3种:(如果没有注明, 参数默认的类型为 in.)
in: 为只读模式, 在函数中, 参数的值只能被引用, 不能被改变;
out: 为只写模式, 只能被赋值, 不能被引用;
in out: 可读可写。

实例1，没有参数的函数

create or replace function get_accidentno return t_claim.accident_no%TYPE
  is 
     v_accident_no t_claim.accident_no%TYPE;
  begin
        select accident_no into v_accident_no from t_claim where claim_id=521;
        return v_accident_no;
  end get_accidentno;

测试：

  select get_accidentno from dual;

输出结果：

image.png

实例2，带有IN参数的函数

create or replace function get_accidentno1(claimid in t_claim.claim_id%TYPE) return  t_claim.accident_no%TYPE is 
   v_accident_no t_claim.accident_no%TYPE;
   begin
     select accident_no into v_accident_no from t_claim where claim_id=claimid;
     return v_accident_no;
     
     exception 
      when no_data_found then
         raise_application_error(-20001,'你输入的ID无效！');
   end get_accidentno1;

测试：

select get_accidentno1(521) from dual;

输出结果：

image.png

查看函数院源代码
oracle会将函数名及其源代码信息存放到数据字典中user_source

select * from user_source where name='GET_ACCIDENTNO';

image.png

删除函数

drop function get_accidentno;

函数调用限制
1)、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数
2)、SQL只能调用带有输入参数,不能带有输出,输入输出函数
3)、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)
4)、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句
5)、可执行部分至少有一条return语句
6)、调用函数时，不能将函数作为单独的语句存在，可以作为表达式的一部分

实例3: 读入两个值, 返回比较大的值

create or replace function bijiao(para1 in number, para2 in number) 
return number 
as 
begin
 if para1 > para2 then
     return para1;
 else
     return para2; 
 end if;
end bijiao;

测试：

select bijiao(100, 200) from dual;

输出结果：

image.png

实例4: 读入date型日期, 大于15号的归入下一个月, 小于等于15号归入本月

create or replace function fn_mymonth(oridate  in date)
return varchar2
as
 nowday   number;
 nowmonth number;
 nowyear  number; 
begin
 nowday := to_number(to_char(oridate, 'dd'));
 nowmonth := to_number(to_char(oridate, 'mm'));
 nowyear := to_number(to_char(oridate, 'yyyy'));

 if nowday <= 15 then                              --少于等于15号属于本月
   return to_char(oridate, 'yyyymm');
 else
   if nowmonth <= 8 then                           -- 日 <=8 则+1后转char还要补0,单独出来作为一种情况
     return to_char(nowyear)||'0'||to_char(nowmonth + 1);
   elsif( nowmonth <= 11 ) then                    -- 日 <= 11则+1后不会跨年,转char不需要补零, 单独出来作为一种情况
     return to_char(nowyear)||to_char(nowmonth + 1);
   else                                            -- 最后一种情况就是跨年, 改变年份, 月份补零就行
     return to_char(nowyear + 1)||'0'||to_char(nowmonth - 11);
   end if;
 end if;
end fn_mymonth;

测试：

select fn_mymonth(to_date('2020-04-23', 'yyyy-mm-dd')) from dual; --输出：202005
select fn_mymonth(to_date('2019-12-15', 'yyyy-mm-dd')) from dual; --输出：201912
select fn_mymonth(to_date('2019-12-16', 'yyyy-mm-dd')) from dual; --输出：202001