Oracle中的存储过程与存储函数

刚开始接触存储过程和存储函数的时候可能会产生误解，这里的“存储”并不是指这种语句是数据库用来存储数据的，“存储”的真正含义是存储预编译的语句，可以简单地把它理解成类似其他编程语言里的函数。

• 存储过程：存储过程就是提前编译好的一段pl/sql语言，放置在数据库端，可以直接被调用，这一段pl/sql一般都是固定步骤的业务。
• 存储函数：在存储过程的基础上加上返回值。

我们以scott用户下的表为例：

1. 基本语法

-- 通过存储过程给指定员工涨100块钱
create or replace 
procedure p1(eno emp.empno%type) -- 默认in 类型参数
is

begin
  update emp set sal = sal+100 where empno = eno;
  commit;
end;
-- 测试procedure p1
select * from emp where empno=7782;
declare
begin
  p1(7782);
end;

-- 通过存储函数实现计算指定员工的年薪
create or replace 
function f_yearsal(eno emp.empno%type) return number -- 注意这个参数number不能带长度
is
  s number(10);
begin
  select sal*12+nvl(comm,0) into s from emp where empno=eno;
  return s;
end;
-- 测试f_yearsal
-- 存储函数在调用的时候，返回值必须接收
declare
  s number(10);
begin
  s := f_yearsal(7782);
  dbms_output.put_line(s);
end;

2. in、out、in out参数

in和out类型参数的区别是什么?
在存储过程中，凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰，其余都用in（可以缺省）。

• in：用于接收参数，传入存储过程或存储函数内部不能进行修改，默认的参数模式为in；
• out：用于修改参数，必须先在外部定义变量（一般不赋值），然后传入存储过程或存储函数内部进行修改，存储过程或存储函数并不care在外部有没有赋值，即使有赋值也会忽略掉；
• in out：既能用于接收参数，又能用于修改参数，能接收传入的实参值；在存储过程或存储函数内部可以修改，也可以输出。

参考：Oracle 存储过程 in、out、in out

参数的使用方法

-- out类型参数如何使用
-- 使用存储过程来算年薪，存储过程想输出不能用return，只能借助out参数
create or replace 
procedure p_yearsal(eno emp.empno%type, yearsal out number) -- 注意这个参数number不能带长度
is
  s number(10);
  c emp.comm%type;
begin
  select sal*12, nvl(comm,0) into s, c from emp where empno=eno;
  yearsal := s+c;
end;
-- 测试p_yearsal
declare
  yearsal number(10);
begin
  p_yearsal(7782,yearsal); -- 这里其实相当于给yearsal赋值
  dbms_output.put_line(yearsal);
end;

3. 存储过程和存储函数的区别

过程和函数的区别在于函数必须有返回值,而过程没有返回值，存储过程比存储函数多了两个返回值;但过程和函数都可以通过out指定一个或多个输出参数,我们可以利用out参数，在过程和函数中实现返回多个值。

其中有一个点必须要理解清楚，当我们想要在存储过程中“返回值”时，必须借助out类型的参数，但其本质上并不返回任何值，本质上是在存储过程内部对out类型参数进行赋值操作，如上例“测试p_yearsal”中要先传入参数，我们才能拿到输出类型参数的值。

我们可以使用存储函数有返回值的特性，自定义函数，而存储过程不能用来自定义函数。

-- 案例需求：查询出员工姓名，员工所在部门名称。
-- 传统方式查询
select emp.ename, dept.dname
from emp, dept
where emp.deptno=dept.deptno;

-- 使用存储函数来实现提供一个部门编号，输出一个部门名称
create or replace 
function fDeptIdToName(dno dept.deptno%type) return dept.dname%type 
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno=dno;
  return dna;
end;
-- 测试fDeptIdToName
declare
  dnam dept.dname%type;
begin
  dnam := fDeptIdToName(10);
  dbms_output.put_line(dnam);
end;
-- 使用fdna存储函数来实现案例需求：查询出员工名字，员工所在部门名称
select ename, fDeptIdToName(deptno)
from emp;