Using Ref Cursors To Return Recordsets.

CREATE TABLE STHOLTAB
(
  BRANCD  VARCHAR2(5 BYTE)                      NOT NULL,
  HOLDTE  DATE                                  NOT NULL,
  HOLDES  VARCHAR2(500 BYTE)                    NOT NULL
)

CREATE TABLE TEST_CURSOR
(
  BRANCD  VARCHAR2(5 BYTE)                      NOT NULL,
  HOLDTE  DATE                                  NOT NULL,
  HOLDES  VARCHAR2(500 BYTE)                    NOT NULL
)

CREATE OR REPLACE PROCEDURE DPR_CURSOR_RETURN(p_BRANCD    IN  VARCHAR2,
                                              p_recordset OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_recordset FOR
    SELECT BRANCD, HOLDTE, HOLDES
    FROM   STHOLTAB
    WHERE BRANCD=P_BRANCD
    ORDER BY BRANCD;
END DPR_CURSOR_RETURN;

DECLARE
C_RECORDSET SYS_REFCURSOR;
V_BRANCD VARCHAR2(100);
V_HOLDTE DATE;
V_HOLDES VARCHAR2(1000);
BEGIN
DPR_CURSOR_RETURN('003',C_RECORDSET);

LOOP
FETCH C_RECORDSET  INTO  V_BRANCD, V_HOLDTE, V_HOLDES;

    INSERT INTO TEST_CURSOR VALUES(V_BRANCD, V_HOLDTE, V_HOLDES);
   
    COMMIT;
    EXIT WHEN C_RECORDSET%NOTFOUND;
END LOOP;

END;