PLSQL_游标_过程

CREATE OR REPLACE PACKAGE PKG_TEST AS

     -- 表类型

     -- TYPE KEY_TABLE_TYPE IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;

     -- PROCEDURE GET (P_VALUE IN VARCHAR2, P_KEY_TABLE OUT KEY_TABLE_TYPE);

     -- 参照游标

     TYPE KEY_REFCUR IS REF CURSOR;

     PROCEDURE GET (P_VALUE IN VARCHAR2, P_KEY_REFCUR OUT KEY_REFCUR); 

END PKG_TEST;

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS

    -- PROCEDURE GET(P_VALUE IN VARCHAR2, P_KEY_TABLE OUT KEY_TABLE_TYPE) IS

    PROCEDURE GET(P_VALUE IN VARCHAR2, P_KEY_REFCUR OUT KEY_REFCUR) IS

       CURSOR CUR_SF IS

       SELECT KEY FROM SYS_FILE;

    BEGIN

         DBMS_OUTPUT.PUT_LINE(P_VALUE);

         FOR V_C IN CUR_SF LOOP

             DBMS_OUTPUT.PUT_LINE('CUSR: ' || V_C.KEY);

         END LOOP;

         OPEN P_KEY_REFCUR FOR SELECT KEY FROM SYS_FILE;

         --SELECT KEY BULK COLLECT INTO P_KEY_TABLE FROM SYS_FILE;   

    END GET;

END PKG_TEST;

DECLARE

    --V_K PKG_TEST.KEY_TABLE_TYPE;

    V_VALUE VARCHAR2(10);

    V_K PKG_TEST.KEY_REFCUR;

BEGIN 

    V_VALUE := 'BJSXT';

    PKG_TEST.GET(V_VALUE, V_K);

    --

    --FOR I IN 1..V_K.COUNT LOOP

    --    DBMS_OUTPUT.PUT_LINE(V_K(I));

    --END LOOP;    

END;