Oracle存儲(chǔ)過程、包、方法使用總結(jié)(推薦)

/**
 *@author：zhengwei
 *@date:2017-04-28
 *@desc:存儲(chǔ)過程用法總結(jié)
 */
 CREATE OR REPLACE PROCEDURE MYPROCEDURE(P_ID   IN VARCHAR,
                     P_STATUS OUT VARCHAR) --P_ID為輸入?yún)?shù) ,P_STATUS為輸出參數(shù)
 AS
 ---變量聲明
 T_STATUS   VARCHAR2(20);
 T_ID     NUMBER;
 V_POSTYPE   VARCHAR2(20);
 V_DESCRIPTION VARCHAR2(20);
 ---對(duì)象變量定義和聲明
 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;
 ---游標(biāo)聲明，并填充數(shù)據(jù)
 CURSOR MY_CUR IS
  SELECT POS_TYPE, DESCRIPTION FROM VOTEMASTER;
BEGIN
 ---變量賦值(注意:in類型的參數(shù)不能直接賦值)
 T_STATUS := '1';
 P_STATUS := T_STATUS;
 DBMS_OUTPUT.put_line('P_STATUS:' || P_STATUS);
 BEGIN
  ---循環(huán)游標(biāo)，使用游標(biāo)
  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循環(huán)用法
 BEGIN
  WHILE i  10 LOOP
   BEGIN
    i := i + 1;
   END;
  END LOOP;
 END;
 --將select查詢的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲(chǔ)多個(gè)變量中，必須有一條記錄，否則拋出異常(如果沒有記錄拋出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 || ':優(yōu)秀';
  END IF;
 END;
 ---對(duì)象變量賦值
 BEGIN
  SELECT FUND,
      BATCH_NO,
      TRAN_AMT,
      END_BAL,
      TRAN_DATE,
      TRAN_TIME,
      SUB_WATER
   INTO XRECORD
   FROM ACCT_WATER
   WHERE FUND = P_ID;
  --對(duì)象變量的使用
  DBMS_OUTPUT.put_line(XRECORD.BATCH_NO || XRECORD.FUND);
 END;
 ---索引表
 ---我們?cè)谑褂么鎯?chǔ)過程的時(shí)候經(jīng)常需要處理記錄集，也就是多條數(shù)據(jù)記錄。分為單列多行和多列多行，這些類型都可以稱為集合類型。索引表就是集合類型中的一種。 
 ---索引表,也稱為pl/sql表，不能存儲(chǔ)于數(shù)據(jù)庫中，元素的個(gè)數(shù)沒有限制,下標(biāo)可以為負(fù)值。
 ---使用場(chǎng)景：如果僅僅是在存儲(chǔ)過程中當(dāng)作集合變量使用，索引表是最好的選擇。(也可以通過創(chuàng)建臨時(shí)表替代，但就不那么科學(xué)了，而且后期還得維護(hù)臨時(shí)表)
 ---索引表對(duì)象使用方案1:
 BEGIN
  ---索引表對(duì)象聲明、定義、使用
  DECLARE
   TYPE acct_table_type IS TABLE OF ACCT%ROWTYPE INDEX BY BINARY_INTEGER;
   ---定義了一個(gè)索引表v_acct_table，其表中的每行記錄是ACCT表中的一行記錄
   v_acct_table acct_table_type;
  BEGIN
   SELECT * BULK COLLECT ---BULK COLLECT INTO指是一個(gè)成批聚合類型, 簡(jiǎn)單的來說 , 它可以存儲(chǔ)一個(gè)多行多列存儲(chǔ)類型
    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;
 ---索引表對(duì)象使用方案2:
 BEGIN
  --例子：利用記錄RECORD可用整體賦值的特性來填充PL/SQL表
  DECLARE
   TYPE RECTYPE IS RECORD(
    FUND   ACCT.FUND%TYPE,, ---表示定義的變量的類型為表Acct的fund字段的同樣數(shù)據(jù)類型
    BAL    ACCT.BAL%TYPE,
    OWNER   ACCT.OWNER%TYPE,
    REAL_NMBR VARCHAR(30));
   ---定義了一個(gè)索引表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;