Oracle数据库开发之存储过程和自定义函数

表、视图、索引、序列、同义词、存储过程和存储函数都是数据库对象

存储过程和存储函数指在数据库中供所有用户程序调用的子程序，相同点：都是完成特定功能的程序

                                                                                                                    不同点：是否用return语句返回值

用CREATE  PROCEDURE命令建立存储过程和存储函数

语法：create [or  replace] PROCEDURE  过程名（参数列表）AS  PLSQL子程序体;

--第一个存储过程：打印Hello World
create or replace procedure sayhelloworld
as
--说明部分
begin
dbms_output.put_line('Hello World');
end;
/
/*
调用存储过程：
1.exec sayhelloworld();
2.begin 
    sayhelloworld();
	sayhelloworld();
  end;
  /  
*/

--创建一个带参数的存储过程：
--给指定的员工涨100块钱的工资，并且打印涨前和涨后的工资
create or replace procedure raisesalary(eno in number)
as
--定义一个变量保存涨前的工资
psal emp.sal%type;
begin
--得到员工涨前的薪水
select sal into psal from emp where empno = eno;
--给该员工涨100
update emp set sal=sal+100 where empno = eno;

--需不需要commit？
--注意：一般不在存储过程或者存储函数中commit和rollback

--打印
dbms_output.put_line('涨前：'||psal||'涨后'||(psal+100));

end;
/ 

/*
如何调用：
begin
   raisesalary(7839);
   raisesalary(7566);
   commit;
end;   
*/

函数（Function）为一命名的存储程序，可带参数，并返回一计算值

函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值

创建存储函数的语法：

create [or  replace] Function 函数名(参数列表)

return  函数值类型

AS

PLSQL子程序体;

--存储函数：查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as 
--定义变量保存员工的薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
--得到该员工的月薪和奖金
select sal,comm into psal,pcomm from emp where empno=eno;
--直接返回年收入
return psal*12+nvl(pcomm,0);
end;
/
--调用存储函数：
set serveroutput on
declare
ENO number;
v_Return number;
begin
ENO:=7839;
v_Return:=queryempincome(eno=>ENO);
dbms_output.put_line('v_Return='||v_Return);
v_Return:=v_Return;
end;
/

in和out参数：

一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值，而存储过程没有返回值。

过程和函数都可以通过out指定一个或多个输出参数，我们可以利用out参数，在过程和函数中实现返回多个值

存储过程和存储函数都可以有多个out参数

存储过程可以通过out参数来实现返回值

什么时候用存储过程/存储函数

原则:
如果只有一返回值，用存储函数；否则，就用存储过程

create or replace procedure queryempinform(eno in number,
                                           pename out varchar2,
										   psal out varchar2,
										   pjob out varchar2)
as
begin
--得到该员工的姓名，月薪和职位
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
--调用存储过程
declare 
ENO number;
PENAME varchar2(200);
PSAL number;
PJOB varchar2(200);
begin
ENO:=7839;
queryempinform(
ENO=>ENO,
PENAME=>PENAME,
PSAL=>PSAL,
PJOB=>PJOB
);
dbms_output.put_line('PENAME='||PENAME);
PENAME:=PENAME;
dbms_output.put_line('PSAL='||PSAL);
PSAL:=PSAL;
dbms_output.put_line('PJOB='||PJOB);
PJOB:=PJOB;
end;
/