用table类型作为参数

用table类型作为参数，首先要建立一个可公开调用的table类型才可以。为了便于维护，可放在package里

CREATE OR REPLACE PACKAGE pkg_test IS
  TYPE t_temp IS RECORD(
    tid   VARCHAR2(50),
    tname VARCHAR2(50),
    cou   VARCHAR2(50));
  TYPE tab IS TABLE OF t_temp INDEX BY BINARY_INTEGER;
  PROCEDURE p_test(t IN tab);
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test IS
  PROCEDURE p_test(t IN tab) AS
  BEGIN
    FOR i IN 1 .. t.count LOOP
      dbms_output.put_line(t(i).tid || t(i).tname || t(i).cou);
    END LOOP;
  END p_test;
END pkg_test;
/

下面调用就可以了:

SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2    CURSOR cr IS
  3      SELECT *
  4        FROM (SELECT '1TEST' tid, '1TEST' tname, '1TEST' cou
  5                FROM dual
  6              UNION ALL
  7              SELECT '2TEST' tid, '2TEST' tname, '2TEST' cou
  8                FROM dual
  9              UNION ALL
 10              SELECT '3TEST' tid, '3TEST' tname, '3TEST' cou
 11                FROM dual
 12              UNION ALL
 13              SELECT '4TEST' tid, '4TEST' tname, '4TEST' cou
 14                FROM dual) e;
 15    t pkg_test.tab;
 16  BEGIN
 17    OPEN cr;
 18    FETCH cr BULK COLLECT INTO t;
 19    CLOSE cr;
 20    pkg_test.p_test(t);
 21  END;
 22  /
1TEST1TEST1TEST
2TEST2TEST2TEST
3TEST3TEST3TEST
4TEST4TEST4TEST
PL/SQL procedure successfully completed