函数语法:
create or replace function function_name(argu1 datatype,argu2 datatype...)
return datatype
is|as
PL/SQL Block;
自定义函数调用限制
1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数
函数必须是保存在数据库中。一个在客户端的PL/SQL环境中定义的函数没有办法在SQL中使用;因为SQL没有办法解析对这个函数的引用。
2、SQL只能调用带有输入参数,不能带有输出,输入输出函数
3、函数的参数以及返回值的数据类型,必须是Oracle数据库可以识别的。尽管Oracle数据库的所有数据类型在PL/SQL中都是有效的,但是PL/SQL却有一些Oracle数据库所不支持的全新的数据类型。这些数据类型包括BOOLEAN、BINARY_INTEGER、table,关联数组、PL/SQL的记录类型,以及程序员自定义的子类型。
4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句
查看函数院源代码
oracle会将函数名及其源代码信息存放到数据字典中user_source
select text from user_source where name='GET_EMPNAME';
删除函数
drop function get_empname;
在建立函数时,在函数头部必须要带有RETURN语句,在函数体内至少要包含一条RETURN语句。
1.不带任何参数
create or replace function fun_user
return varchar2
is
v_user varchar2(50);
begin
select username into v_user from user_users;
return v_user;
end;
调试:
方法一
select fun_user from dual;
方法二
SQL> var v_res varchar2(100);
SQL> exec :v_res :=fun_user;
SQL> print v_res;
2.带IN参数
//通过雇员名获取员工薪水
create or replace function fun_get_sal(v_ename varchar2)
return number
is
v_error_code number;
v_error_message varchar2(100);
v_sal number;
begin
select sal into v_sal from emp where upper(ename) = upper(v_ename);
return v_sal;
exception
when others then
v_error_code := sqlcode;
v_error_message := substr(sqlerrm, 1, 100);
insert into errors
(error_id, program_name, error_code, error_message)
values
(seq_errors.nextval, 'fun_get_sal', v_error_code, v_error_message);
commit;
end;
3.带OUT参数
如果要同时返回多个数据,需要使用输出参数
//返回雇员名所在的部门名和岗位
create or replace function fun_ename_info(v_ename varchar2,v_job out varchar2)
return varchar2
is
v_deptname dept.dname%type;
begin
select b.dname,a.job into v_deptname,v_job from emp a
join dept b on a.deptno=b.deptno
where upper(a.ename) = upper(v_ename);
return v_deptname;
end;
//调试
SQL> var job varchar2(50);
SQL> var dname varchar2(50);
SQL> exec :dname :=fun_ename_info('scott',:job);
SQL> print dname job;