Oracle 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。如果在应用程序中，经常需要执行特定的操作，可以基于这些操作建立一个特定的过程。通过使用过程，不仅可以简化客户端应用程序的开发和维护，而且可以提高应用程序的运行性能。

建立存储过程的语法如下所示：

CREATE OR REPLACE PROCEDURE procedure_name 
 (argument1 [mode1] datatype1,argument2 [mode2] datatype2,...)
IS [AS]
PL/SQL 块；

其中，procedure_name ：存储过程名； argument1,argument2等则用于指定过程的参数；mode1,mode2 用于指定参数模式，如IN、 OUT、IN OUT，IS 或AS用于开始一个PL/SQL块. 当指定参数类型时，不能指定其长度。当建立过程时，可以指定输入参数（IN），输出参数（OUT），输入输出参数（IN OUT）。如果不指定参数模式，默认是IN。

 下面通过示例说明建立过程和使用各种参数模式的方法：

1、建立过程，不带任何参数.

create or replace procedure out_time
is
begin 
	dbms_output.put_line(systimestamp);
end;

建立过程out_time后，就可以调用了，一般使用execute(简写exec)调用：

SQL>  set serveroutput on
SQL>  exec out_time
 
27-6月 -13 04.47.34.086000000 下午 +08:00
 
PL/SQL procedure successfully completed
 
SQL> /

2、建立过程，带有IN参数 .

下面以建立雇员插入数据的过程add_employee为例，说明建立带有输入参数过程的方法：

CREATE OR REPLACE PROCEDURE add_employee
 (eno NUMBER,
  name VARCHAR2,
  sal NUMBER,
  job VARCHAR2 default 'CLERK',
  dno NUMBER)
IS 
  e_integrity EXCEPTION;
  PRAGMA  EXCEPTION_INIT(e_integrity,-2291);
BEGIN 
    INSERT INTO emp (empno,ename,sal,job,deptno)  
      VALUES(eno,name,sal,job,dno);    
EXCEPTION
    WHEN DUP_VAL_ON_INDEX  THEN
    RAISE_APPLICATION_ERROR(-2000,'雇员号不能重复');
    WHEN e_integrity then
    RAISE_APPLICATION_ERROR(-2000,'部门号不存在');
 END;
    

上面程序没有指定参数模式，所以全部默认都是输入（IN）参数。在调用该过程时，除了具有默认值得参数之外，其它参数必须要提供参数值。调用示例如下：

SQL> exec add_employee(1111,'MARY',2000,'MANAGER',10);
 
PL/SQL procedure successfully completed

2,、建立过程，带有OUT参数.

过程不仅可以执行特定的操作，而且可以输出数据，在过程中输出数据时使用OUT或IN OUT 参数来完成的。当定义输出参数时，必须要提供OUT关键字，下面用于建立输出雇员名及其工资的过程为例：

CREATE OR REPLACE PROCEDURE query_employee
 (eno NUMBER,
  name OUT VARCHAR2,
  salary OUT VARCHAR2)
IS 
BEGIN 
	  SELECT ename,sal INTO  name ,salary 
	      from emp 
	      WHERE empno=eno;
 EXCEPTION
    WHEN NO_DATA_FOUND  THEN
     RAISE_APPLICATION_ERROR(-2000,'该雇员不存在');
END;   

下面是在SQL*Plus中调用该过程的实例：

SQL> var name varchar2(10);
SQL> var salary number
SQL> exec query_employee(7788,:name,:salary);
 
PL/SQL procedure successfully completed
name
---------
SCOTT
salary
---------
1000

其中:name,:salary，前面为什么有冒号：个人理解，原因是他们不是PL/SQL变量，所谓PL/SQL变量就是在PL/SQL块中定义的变量，呵呵.....

 

3、建立过程，带有IN OUT 参数

  当使用这种参数时，在调用过程之前，需要通过变量给该种参数传递数据，在调用结束之后，Oracle会通过该变量将过程结果传递给应用程序。

 下面以计算两个数值相除结果的过程compute为例，说明在过程中使用IN OUT参数的方法。示例如下：

CREATE OR REPLACE PROCEDURE  compute 
(num1 IN OUT NUMBER,
 num2 IN OUT NUMBER)
IS 
 v1 NUMBER;
 v2 NUMBER;
BEGIN
  v1:=num1/num2;
  v2:=MOD(num1,num2);
  num1:=v1;
  num2:=v2;
END;

':=' 是PL/SQL程序中的赋值操作符. 接下来在SQL*Plus调用该过程：

SQL> var n1 number;
SQL> var n2 number;
SQL> exec :n1:=100;
 
PL/SQL procedure successfully completed
n1
---------
100
 
SQL> exec :n2:=30;
 
PL/SQL procedure successfully completed
n2
---------
30
 
SQL> exec compute(:n1,:n2);
 
PL/SQL procedure successfully completed
n1
---------
3.33333333333333
n2
---------
10

关于语句中的冒号（：），参考前面的讲解.因为当时我也是很困扰，呵呵，如果你知道，就当我啰嗦一下吧.

4、接下来介绍一下在调用带参数的存储过程时，需要为参数传递变量和数据的方式，主要有三种：位置传递、名称传递和组合传递。

在介绍传递方法之前，先建立用于DEPT表增加数据的过程.

CREATE OR REPLACE PROCEDURE add_dept
 (dno NUMBER,
  dname VARCHAR2 DEFAULT NULL,
  loc VARCHAR2 DEFAULT NULL
  )
 IS 
 BEGIN 
 	  INSERT INTO dept 
 	     VALUES(dno,dname,loc); 
 EXCEPTION
     WHEN DUP_VAL_ON_INDEX THEN 
     RAISE_APPLICATION_ERROR(-2000,'部门号不能重复');   
 END;    	 

 4.1 位置传递

      位置传递是指在调用子程序时按照参数定义的顺序依次为参数指定相应变量或数值.示例如下：

 exec add_dept(50,'SALES','NEWYORK')
 exec add_dept(60)
 exec add_dept(70,'ADMIN');  

 第二次、第二次没有提供数据的参数时因为他们都具有默认值NULL.

4.2 名称传递

      名称传递是指在调用子程序时指定参数名，并使用关联符号“=>”为其提供相应的数值或变量,使用这种方法时，不需要考虑位置关系。示例如下：

 exec add_dept(loc=>'NEW YORK'，dno=>50);
 exec add_dept(loc=>'NEW YORK',dname=>'SALES',dno=>60)

4.3组合传递

    组合传递是指在调用子程序的同时使用位置传递和名称传递.示例如下：

 exec add_dept(50,loc=>'NEW YORK');
 exec add_dept(60,loc=>'NEW YORK',dname=>'SALES')
 

5.查询存储过程的源代码

 当建立了过程之后，Oracle会将过程名、源代码、执行代码存放到数据字典中。当调用过程时，应用程序会按照其执行代码直接执行，而不需要重新解析过程代码，所以使用子程序的性能优于直接执行SQL语句。通过查询数据字典USER_SOURCE,可以显示当前用户的所有子程序及其源代码。示例如下 ：

SQL> SELECT text FROM user_source where name='ADD_DEPT';
 
TEXT
--------------------------------------------------------------------------------
PROCEDURE add_dept
 (dno NUMBER,
  dname VARCHAR2 DEFAULT NULL,
  loc VARCHAR2 DEFAULT NULL
  )
 IS
 BEGIN
     INSERT INTO dept
        VALUES(dno,dname,loc);
 EXCEPTION
     WHEN DUP_VAL_ON_INDEX THEN
     RAISE_APPLICATION_ERROR(-2000,'部门号不能重复');
 END;
 
13 rows selected

当然使用PL/SQL工具也可以查看源码.

6、删除存储过程

 使用PL/SQL工具可以图形化直接删除 ，也可以 DROP PROCEDURE add_dept 来删除.

关于存储过程的知识，就介绍这么多了，关于存储过程的调试，以后再补充。

附录： 

    1.查看数据库的用户下有哪些存储过程

                 select * from dba_source where OWNER='用户名' AND TYPE='PROCEDURE'