存储过程以及函数

1.概述

ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

2.存储过程

存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。

创建存储过程的语法

create [or replace] procedure 存储过程名[(参数 in|out|in out 参数数据类型,...)]

is|as

   声名部分;

begin

   plsql代码块;

   exception

     异常处理;

end;

如：

--无参数存储过程

create or replace procedure p1

is

begin

   for v in (select * from emp) loop

      dbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);

   end loop;

end;

select * from user_objects where object_type='PROCEDURE';

存储过程的调用

 1.在plsql块中调用

 2.call命令调用

 3.execute命令调用--但是要先设置属性set serverout on;

删除存储过程

drop procedure p1;

存储过程参数的三种模式

IN  用于接受调用程序的值。默认的参数模式

OUT  用于向调用程序返回值

IN OUT  用于接受调用程序的值，并向调用程序返回更新的值

--写一个存储过程，根据传入部门编号，查询该部门下的所有员工，并打印员工信息

create or replace procedure p1(v_deptno in number)

is

begin

   for v in (select * from emp where deptno=v_deptno) loop

      dbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);

   end loop;

end;

declare

   v_dno number(10):=20;

begin

  p1(10);   --传值

  dbms_output.put_line('______________________________________________________');

  p1(v_dno);  --传变量

  dbms_output.put_line('______________________________________________________');

  p1(v_deptno=>30);  --按位传值

end;

-- create or replace procedure p2(v_empno in number,v_emp out emp%rowtype)

is

begin

  select * into v_emp from emp where empno=v_empno;

end;

declare

  v emp%rowtype;

begin

  p2(7369,v);

  dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.deptno);

end;

create or replace procedure p3(v_emp in out emp%rowtype)

is

begin

  select * into v_emp from emp where empno=v_emp.empno;

end;

declare

   v_e emp%rowtype;

begin

   --要给v_e里的empno赋值

   v_e.empno:=7369;

   p3(v_e);

   end;

注：将过程的执行权限授予其他用户

grant EXECUTE ON 存储过程名 to 用户名;

3.函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。

创建函数的语法

create [or replace] function 函数名称[(形式参数 参数类型,...)]

return 返回值类型

is

   声名变量;

begin

  plsql代码块;

  return 返回值;

end;

如:

create or replace function f1

return varchar2

is

begin

   return 'hello';

end;

访问函数的方式

使用 SQL 语句

select f1 from dual;

使用 PL/SQL 块

declare

   v varchar2(30);

begin

   v:=f1;

  dbms_output.put_line(v);

end;

--求二个数的和

create or replace function f1(n number,n2 number)

return number

is

  

begin 

   return n + n2;

end;

--求一个数的阶乘

create or replace function f1(n number)

return number

is

   --声名一个变量保存返回值

   res number(10):=1;

begin

   for i in 1..n loop

      res:=res*i;

   end loop;

   return res;

end;