Oracle存储过程、包、方法使用总结(推荐)

/**

  *@author：zhengwei

  *@ date :2017-04-28

  *@ desc :存储过程用法总结

  */

  CREATE OR REPLACE PROCEDURE MYPROCEDURE(P_ID   IN VARCHAR ,

                      P_STATUS OUT VARCHAR ) --P_ID为输入参数 ,P_STATUS为输出参数

  AS

  ---变量声明

  T_STATUS   VARCHAR2(20);

  T_ID     NUMBER;

  V_POSTYPE   VARCHAR2(20);

  V_DESCRIPTION VARCHAR2(20);

  ---对象变量定义和声明

  TYPE XRECORD IS RECORD(

   FUND   VARCHAR2(50),

   BATCH_NO VARCHAR2(50),

   TRAN_AMT NUMBER,

   END_BAL  NUMBER,

   TRAN_DATE VARCHAR2(50),

   TRAN_TIME VARCHAR2(50),

   SUB_WATER NUMBER);

  XWATER XRECORD;

  ---游标声明，并填充数据

  CURSOR MY_CUR IS

   SELECT POS_TYPE, DESCRIPTION FROM VOTEMASTER;

BEGIN

  ---变量赋值(注意:in类型的参数不能直接赋值)

  T_STATUS := '1' ;

  P_STATUS := T_STATUS;

  DBMS_OUTPUT.put_line( 'P_STATUS:' || P_STATUS);

  BEGIN

   ---循环游标，使用游标

   FOR V_ROW IN MY_CUR LOOP

    BEGIN

     V_POSTYPE   := V_ROW.POS_TYPE;

     V_DESCRIPTION := V_ROW.DESCRIPTION;

     DBMS_OUTPUT.put_line( 'POSTYPE:' || V_POSTYPE || ',description:' ||

                V_DESCRIPTION);

    END ;

   END LOOP;

  END ;

  ---WHILE循环用法

  BEGIN

   WHILE i < 10 LOOP

    BEGIN

     i := i + 1;

    END ;

   END LOOP;

  END ;

  --将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

  BEGIN

   SELECT col1, col2 INTO 变量1, 变量2 FROM typestruct WHERE xxx;

  EXCEPTION

   WHEN NO_DATA_FOUND THEN

    xxxx;

  END ;

  ---IF判断语句用法

  BEGIN

   SELECT VOTETITLE, VATESUM

    INTO T_NAME, T_COUNT

    FROM VOTEMASTER

    WHERE ID = P_ID;

   IF T_COUNT <= 0 THEN

    P_STATUS := T_NAME || ':差' ;

   ELSIF T_COUNT > 0 AND T_COUNT < 3 THEN

    P_STATUS := T_NAME || ':良好' ;

   ELSE

    P_STATUS := T_NAME || ':优秀' ;

   END IF;

  END ;

  ---对象变量赋值

  BEGIN

   SELECT FUND,

       BATCH_NO,

       TRAN_AMT,

       END_BAL,

       TRAN_DATE,

       TRAN_TIME,

       SUB_WATER

    INTO XRECORD

    FROM ACCT_WATER

    WHERE FUND = P_ID;

   --对象变量的使用

   DBMS_OUTPUT.put_line(XRECORD.BATCH_NO || XRECORD.FUND);

  END ;

  ---索引表

  ---我们在使用存储过程的时候经常需要处理记录集，也就是多条数据记录。分为单列多行和多列多行，这些类型都可以称为集合类型。索引表就是集合类型中的一种。

  ---索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。

  ---使用场景：如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。(也可以通过创建临时表替代，但就不那么科学了，而且后期还得维护临时表)

  ---索引表对象使用方案1:

  BEGIN

   ---索引表对象声明、定义、使用

   DECLARE

    TYPE acct_table_type IS TABLE OF ACCT%ROWTYPE INDEX BY BINARY_INTEGER;

    ---定义了一个索引表v_acct_table，其表中的每行记录是ACCT表中的一行记录

    v_acct_table acct_table_type;

   BEGIN

    SELECT * BULK COLLECT ---BULK COLLECT INTO指是一个成批聚合类型, 简单的来说 , 它可以存储一个多行多列存储类型

     INTO v_acct_table

     FROM ACCT

     WHERE acct_type = '570'

      AND ROWNUM < 5;

    FOR i IN 1 .. v_acct_table. COUNT LOOP

     DBMS_OUTPUT.put_line( 'ACCT:' || v_acct_table(i).fund || ',' || v_acct_table(i).bal || ',' || v_acct_table(i)

                .real_nmbr);

    END LOOP;

   END ;

  END ;

  ---索引表对象使用方案2:

  BEGIN

   --例子：利用记录RECORD可用整体赋值的特性来填充PL/SQL表

   DECLARE

    TYPE RECTYPE IS RECORD(

     FUND   ACCT.FUND%TYPE,, ---表示定义的变量的类型为表Acct的fund字段的同样数据类型

     BAL    ACCT.BAL%TYPE,

     OWNER   ACCT.OWNER%TYPE,

     REAL_NMBR VARCHAR (30));

    ---定义了一个索引表MYTAB，其表中的每行记录是RECORD

    TYPE TABTYPE IS TABLE OF RECTYPE INDEX BY BINARY_INTEGER;

    MYTAB TABTYPE;

    VN  NUMBER;

   BEGIN

    --填充

    VN := 1;

    FOR VARR IN ( SELECT FUND, BAL, OWNER, REAL_NMBR

            FROM ACCT

           WHERE ROWNUM <= 15

           ORDER BY FUND ASC ) LOOP

     MYTAB(VN) := VARR; --记录整体赋值 

     VN := VN + 1;

    END LOOP;

    --访问

    VN := MYTAB. FIRST ;

    FOR VARR IN VN .. MYTAB. COUNT LOOP

     DBMS_OUTPUT.PUT_LINE(VN || '  ' || MYTAB(VN).FUND || '  ' || MYTAB(VN).BAL ||

                '  ' || MYTAB(VN).OWNER || '  ' || MYTAB(VN)

                .REAL_NMBR);

     VN := MYTAB. NEXT (VN);

    END LOOP;

   END ;

  END ;