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;