PL/SQL-->存储过程

PL/SQL-->存储过程

        存储过程子程序的一种类型，能够完成一些任务，作为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    --此过程中指定了缺省的输入值，即部门号为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 comp;
                          /

                          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 comp;
                  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.

转自：http://blog.csdn.net/robinson_0612/article/details/6063434