函数与存储过程相似，是数据库中存储的已命名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
网友评论