子程序和程序包-2

 

/*
注意：当建立函数时在函数头部必须要带有return子句，在函数体内至少要包含一个return子句。

*/
1.不带任何参数的函数

--案例01：新建不带参数的函数
create or replace function getuser
return varchar2
is
v_user varchar2(10);
begin
select username into v_user from user_users;
return v_user;
end;

--调用函数的方法1：
declare
v1 varchar2(10);
begin
v1:=getuser;
dbms_output.put_line('getuser函数的结果为:' ||' ' ||v1);
end;

--调用函数的方法2：
select getuser from dual;

----调用函数的方法3：
set serveroutput on
exec dbms_output.put_line('getuser函数的结果为:' ||getuser);

 

2.带有输入参数的函数

--案例02：新建带IN参数的函数
create or replace function getname(sno varchar2)
return varchar2  /*返回类型后面是不能使用精度值的，只是表示返回的数据类型*/
is 
name varchar2(12);
begin
select ename into name from emp where empno=sno;
return name;
exception
when too_many_rows then
dbms_output.put_line('返回值的行数太多！');
when others then
dbms_output.put_line('执行getname函数出现以外错误！');
end;

--调用函数的方法1：

declare
 nm varchar(12);
begin
nm:=getname('7369');
dbms_output.put_line('输出的值是：'||nm);
end;

--调用函数的方法2：
 select getname(7788) from dual;

--调用函数的方法3：

set serveroutput on
exec dbms_output.put_line('getname函数的结果为:' ||getname(7788));

 

3.带有out参数的函数

create or replace function get_info
(
name varchar2,
title out varchar2
)
return varchar2
as
deptname dept.dname%type;
begin
select a.job, b.dname into title, deptname from emp a, dept b
where a.deptno=b.deptno and upper(a.ename)=upper(name);
return deptname;
exception
when no_data_found then
raise_application_error(-20000, '这个员工不能存在！');
end;

--调用函数
/*

这里需要申明两个变量，一个用来接受out参数的结果；一个用来接受函数返回的结果。
*/

declare
job varchar2(20);
dname varchar2(20);  --用来接受函数的结果
begin
dname:=get_info('scott',job);
dbms_output.put_line('getinfo is : ' ||dname ||' ;' ||job );
end;

 

4.带有 IN OUT参数的函数

create or replace function in_out_fun
(
num01 number,
num02 in out number
)
return number
as
v_result number;
v_remain number;
begin
v_result:=num01/num02;
v_remain:=mod(num01,num02);
num02:=v_remain;
return v_result;
exception
when zero_divide then
raise_application_error(-20000, '不能排除0');
end;

--调用函数
declare
n02 number;
fun_result number;
begin
n02:=30;
fun_result:=in_out_fun(100, n02);
dbms_output.put_line(n02 ||';'||'   '||fun_result);
end;

 

/*
sql中调用函数的限制：
1.在sql语句中只能调用存储函数不能调用客户端的函数
2.在sql语句中只能调用带有输入参数的函数不能调用out和in out的函数
3.sql中调用的函数不能在insert、update、delete中调用
*/