PL/SQL3—— 存储过程

原文转自  一沙弥的世界  的博客·

存储过程子程序的一种类型，能够完成一些任务，作为schema对象存储于数据库。是一个有名字的PL/SQL代码块，支持接收或不接受参数

，同时也支持参数输出。一个存储过程通常包含定于部分，执行部分，Exception部分，可以被其他子程序调用，也可以被重用。

 

一、过程定义

     CREATE ［OR REPLACE］PROCEDURE procedure_name

     [(argument_name [IN | OUT | IN OUT] argument_type)]

     AS | IS

     BEGIN

         procedure_body;

     END [procedure_name];

    

     存储过程中参数的类型

         IN：表示是一个输入参数，可以指定缺省值。如省略参数类型，则缺省为in类型

         OUT：表示是一个输出参数

         IN OUT：既可以作为一个输入参数，也可以作为一个输出参数来输出结果

        

二、过程调用

         EXECUTE ｜CALL procedure_name [(argument_list)]

 

     --例：定义一个过程，以JOB为参数，查询该JOB的最高工资、最低工资、平均工资。

 

         CREATE OR REPLACE PROCEDURE display_sal(v_job emp.job%TYPE)   --该形参缺省为in类型，数据类型为emp.job%TYPE

         AS

              v_avg_sal emp.sal%TYPE;

              v_max_sal emp.sal%TYPE;

              v_min_sal emp.sal%TYPE;

         BEGIN

              SELECT avg(sal) INTO v_avg_sal FROM emp WHERE job=v_job;

              SELECT max(sal) INTO v_max_sal FROM emp WHERE Job=v_job;

              SELECT min(sal) INTO v_min_sal FROM emp WHERE job=v_job;

 

              DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||' avg sal:'||v_avg_sal);

              DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||' max sal:'||v_max_sal);

              DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||' min sal:'||v_min_sal);

             

         EXCEPTION

              WHEN NO_DATA_FOUND THEN

                   DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');

         END display_sal;

         /

 

         scott@ORCL> set serveroutput on;

         scott@ORCL> exec display_sal('SALESMAN');

         DEPT SALESMAN avg sal:1400

         DEPT SALESMAN max sal:1600

         DEPT SALESMAN min sal:1250

 

         PL/SQL procedure successfully completed.

 

三、参数及其传递方式：

     在建立过程时，传递的参数为可选项，如果省略参数选项，则过程为无参过程（定义时不指定参数，调用时也不需要参数）。

     如果指定参数选项，则过程为有参过程（定义时需要指定参数名字、模式、数据类型，调时时需要给出对应的参数值），定义时的参数，

         称为形参，调用时的参数称为实参。

 

     1.无参过程

    

         CREATE OR REPLACE PROCEDURE display_systime

         AS

         BEGIN

              DBMS_OUTPUT.PUT_LINE('CURRENT TIME IS '||sysdate);

         END display_systime;

         /

 

         execute display_systime;     --调用

 

     2.有参过程

         定义时需要指定参数的名字、模式、数据类型

 

         --例：定义一个添加记录的过程（全部为输入参数）

 

              CREATE OR REPLACE PROCEDURE add_emp

              (

              v_no IN emp.empno%TYPE,

              v_name IN emp.ename%TYPE,

              v_dept IN emp.deptno%TYPE default 20   --此过程中指定了缺省的输入值，即部门号为

              )

              AS

              BEGIN

                   INSERT INTO emp (empno,ename,deptno) VALUES (v_no,v_name,v_dept);

              EXCEPTION

                   WHEN DUP_VAL_ON_INDEX THEN

                       DBMS_OUTPUT.PUT_LINE('Record Is Exist!');

              END add_emp;

              /

 

              execute add_emp(8000,'TEST2',20);   --调用

 

         --例：定义一个输入员工编号，修改记录，再返回修改后的结果（姓名和工资）。

              CREATE OR REPLACE PROCEDURE ed_emp

              (

              v_no IN emp.empno%TYPE,      --定义了一个in类型，二个out类型的参数

              v_name OUT emp.ename%TYPE,

              v_sal OUT emp.sal%TYPE

              )

              AS

              BEGIN

                   UPDATE emp SET sal=sal+100 WHERE empno=v_no;

                   SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;

              EXCEPTION

                   WHEN NO_DATA_FOUND THEN

                       DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');

              END ed_emp;

              /

 

              scott@ORCL> VARIABLE t_name varchar2(20);

              scott@ORCL> VARIABLE t_sal number;

              scott@ORCL> call ed_emp(7788,:t_name,:t_sal);

 

              Call completed.

 

              scott@ORCL> print t_name t_sal;

 

              T_NAME

              --------------------------------

              SCOTT

 

 

                    T_SAL

              ----------

                     3100

        

         --例：IN OUT类型参数的使用

              CREATE OR REPLACE PROCEDURE comp

              (num1 IN OUT NUMBER,num2 IN OUT NUMBER)

              AS

                   v1 NUMBER;

                   v2 NUMBER;

              BEGIN

                   v1:=num1+num2;

                   v2:=num1*num2;

                   num1:=v1;

                   num2:=v2;

              END;

              /

             

              scott@ORCL> var n1 number;

              scott@ORCL> var n2 number;

              scott@ORCL> exec :n1:=5;

              scott@ORCL> exec :n2:=3;

              scott@ORCL> exec comp(:n1,:n2);     

              scott@ORCL> print n1 n2;

 

                       N1

              ----------

                        8

 

 

                       N2

              ----------

                       15  

                      

         存储过程参数的传递方式：

              按位置传递：

                   实参按顺序将值传给形参

                   EXECUTE ED_EMP(7900,:t_name,:t_sal);

                   EXECUTE ED_EMP(8000,'TEST2',20);

 

              按名字传递

                   EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003); 

 

              混合传递

                   EXECUTE ED_EMP(8005,v_dept=>20,v_name=>'TEST5');

 

         注意host variable 的使用

              host 变量指的是一个绑定变量，也称之为全局变量

              host 变量通常在存储过程之外被声明，如SQL*Plus使用variable来声明或使用Java来声明

              host 变量在声明是使用variable关键字声明，如VARIABLE t_name varchar2(20)

              host 变量在引用时使用:variable_name来引用该全局变量,如上面的引用为:t_name

              可以被任意的匿名块调用并传入或传出数据值

             

             

四、过程管理

    查看系统过程信息

     DBA_OBJECTS

     DBA_PROCEDURES

     DBA_SOURCE

 

     --使用desc procedure_name 查看存储过程的参数信息

         scott@ORCL> desc ed_emp;

         PROCEDURE ed_emp

          Argument Name                  Type                    In/Out Default?

          ------------------------------ ----------------------- ------ --------

          V_NO                           NUMBER(4)                      IN

          V_NAME                         VARCHAR2(10)            OUT

          V_SAL                          NUMBER(7,2)                  OUT 

    

     --从dba_objects获得存储过程的信息

         idle> select owner,object_name,object_type,status from dba_objects where object_name = 'ED_EMP';

 

         OWNER                          OBJECT_NAME          OBJECT_TYPE     STATUS

         ------------------------------ -------------------- --------------- -------

         SCOTT                          ED_EMP               PROCEDURE       VALID

        

         scott@ORCL> select object_name,procedure_name,interface,authid from user_procedures;

 

         OBJECT_NAME          PROCEDURE_NAME                 INT AUTHID

         -------------------- ------------------------------ --- ------------

         DISPLAY_SAL                                         NO  DEFINER

         ED_EMP                                              NO  DEFINER

 

     --查看存储过程的源代码

         scott@ORCL> select line, text from user_source where name='ED_EMP';

 

                LINE TEXT

         ---------- --------------------------------------------------------------------------------

                    1 PROCEDURE ed_emp

                    2       (

                    3       v_no IN emp.empno%TYPE,   

                    4       v_name OUT emp.ename%TYPE,

                    5       v_sal OUT emp.sal%TYPE

                    6       )

                    7       AS

                    8       BEGIN

                    9         UPDATE emp SET sal=sal+100 WHERE empno=v_no;

                   10         SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;

                   11       EXCEPTION

                  12         WHEN NO_DATA_FOUND THEN

                   13           DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');

                   14       END ed_emp;

        

    --查看错误信息

         SHOW ERRORS

 

         scott@ORCL> CREATE OR REPLACE PROCEDURE comp

           2  (num1 IN OUT NUMBER,num2 IN OUT NUMBER)

           3  AS

           4  v1 NUMBER;

           5  v2 NUMMBER;

           6  BEGIN

           7    v1:=num1+num2;

           8    v2:=num1*num2;

           9    num1:=v1;

          10    num2:=v2;

          11  END;

          12  /

 

         Warning: Procedure created with compilation errors.

 

         scott@ORCL> show errors;

         Errors for PROCEDURE COMP:

 

         LINE/COL ERROR

         -------- -----------------------------------------------------------------

         5/4      PL/SQL: Item ignored

         5/4      PLS-00201: identifier 'NUMMBER' must be declared

         8/3      PL/SQL: Statement ignored

         8/3      PLS-00320: the declaration of the type of this expression is

                    incomplete or malformed

 

         10/3     PL/SQL: Statement ignored

         10/9     PLS-00320: the declaration of the type of this expression is

                    incomplete or malformed   

   

     删除过程

         DROP PROCEDURE procedure_name

 

         scott@ORCL> drop procedure comp;

 

         Procedure dropped.