--创建存储过程，返回SYS_REFCURSOR

CREATE OR REPLACE PROCEDURE P_GET_AGENT(V_AGENT_CATE IN VARCHAR2,

                                        V_PAGE       IN INTEGER,

                                        O_CURSOR     OUT SYS_REFCURSOR) IS

BEGIN

  INSERT INTO TMP_TAB_T_AGENT(AGENT_ID)

    SELECT AGENT_ID FROM T_AGENT TA WHERE TA.AGENT_CATE = V_AGENT_CATE;

  OPEN O_CURSOR FOR

    SELECT AGENT_ID

      FROM (SELECT ROWNUM AS RN, AGENT_ID

              FROM (SELECT AGENT_ID FROM TMP_TAB_T_AGENT ORDER BY AGENT_ID)

             WHERE ROWNUM < V_PAGE * 10)

     WHERE RN >= (V_PAGE - 1) * 10;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('wrong');

END;

--调用存储过程，

DECLARE

  V_CURSOR   SYS_REFCURSOR;

  V_AGENT_ID T_AGENT.AGENT_ID%TYPE;

BEGIN

  -- Call the procedure

  P_GET_AGENT(V_AGENT_CATE => '5', V_PAGE => 4, O_CURSOR => V_CURSOR);

  LOOP

    FETCH V_CURSOR

      INTO V_AGENT_ID;

    EXIT WHEN V_CURSOR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(V_AGENT_ID);

  END LOOP;

  CLOSE V_CURSOR;

  COMMIT;

END;