利用Table變更來儲存資料

exsample:

CREATE OR REPLACE PACKAGE PKG_TEST AS
  --定義一個ima_file table的temp table變數
  type temp_ima_file is table of ima_file%ROWTYPE;

  PROCEDURE SP_TEST(p_user IN VARCHAR);
END PKG_TEST;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS
  PROCEDURE SP_TEST(p_user IN VARCHAR) AS
 
    l_temp_ima         temp_ima_file;
   
  BEGIN
 
    --利用 BULK COLLECT INO 語法將資料寫入 l_temp_ima Table
    SELECT *
      BULK COLLECT INTO l_temp_ima
    FROM ima_file
    WHERE ima01 IN ('A123','B123');
   
    FOR i in 1..l_temp_ima.COUNT LOOP
      DBMS_OUTPUT.put_line('ima01--->' || l_temp_ima(i).ima01);
    END LOOP;
  END;

END PKG_TEST;
/

也可以修改為只定義一個變數的TABLE

CREATE OR REPLACE PACKAGE PKG_TEST AS
  --定義一個欄位的 table變數
  type temp_ima_file IS TABLE OF VARCHAR2(100);

  PROCEDURE SP_TEST(p_user IN VARCHAR);
END PKG_TEST;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS
  PROCEDURE SP_TEST(p_user IN VARCHAR) AS
 
    l_temp_ima         temp_ima_file;
   
  BEGIN
 
    --利用 BULK COLLECT INO 語法將資料寫入 l_temp_ima Table
    SELECT *
      BULK COLLECT INTO l_temp_ima
    FROM ima_file
    WHERE ima01 IN ('A123','B123');
   
    FOR i in 1..l_temp_ima.COUNT LOOP
      DBMS_OUTPUT.put_line('ima01--->' || l_temp_ima(i).ima01);
    END LOOP;
  END;

END PKG_TEST;
/

reference web:
http://tomkuo139.blogspot.tw/2010/10/oracle-procedure-bulk-collect-table.html