Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve the recordset from PROCEDURE????
How to retrieve the recordset from PROCEDURE???? [message #36268] Wed, 14 November 2001 00:31 Go to next message
liyou
Messages: 2
Registered: November 2001
Junior Member
Who can give me a sample?

----------------------------------------------------------------------
Re: How to retrieve the recordset from PROCEDURE???? [message #36292 is a reply to message #36268] Wed, 14 November 2001 10:40 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
CREATE TABLE ABC (A NUMBER);

insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);

CREATE OR REPLACE PACKAGE ref_cur_tst
IS
-- dummy cursor to get %rowtype below
CURSOR c1 IS SELECT a FROM abc;
TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;
-- or just use abc%rowtype to match whole table structure.
-- TYPE t_cur IS REF CURSOR RETURN abc%ROWTYPE;

PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/

CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
PROCEDURE get_abc (cv_cur IN OUT t_cur)
IS
BEGIN
OPEN cv_cur FOR SELECT a FROM abc;
END get_abc;
END ref_cur_tst;
/

set serveroutput on;

DECLARE
a abc.a%TYPE;
cv_c1 ref_cur_tst.t_cur;
BEGIN
ref_cur_tst.get_abc (cv_c1);

LOOP
FETCH cv_c1 INTO a;
EXIT WHEN cv_c1%NOTFOUND;
DBMS_OUTPUT.put_line (a);
END LOOP;
CLOSE cv_c1;
END;
/

----------------------------------------------------------------------
Previous Topic: Embedded PL/SQL: Cursor Loops
Next Topic: Something weird is happening...
Goto Forum:
  


Current Time: Fri Jun 05 15:17:53 CDT 2020