CODEWITHSUNDEEP
oracle-databaseplsql
Gumowy Kaczak
Gumowy Kaczak

Reputation: 1499

PLSQL: Procedure outputting multiple cursors

I'd like to return multiple cursor in one procedure, one based on other.

My currently code is:

TYPE REFCURSOR IS REF CURSOR; 

PROCEDURE GETCARS(oCARS OUT REFCURSOR)
BEGIN
    OPEN oCARS FOR SELECT * FROM CARS;
END GETCARS;

I'm not sure if that's posible but I want to make something like:

PROCEDURE GETCARS(oCARS OUT REFCURSOR, oREPAIRS OUT REFCURSOR)
BEGIN
    OPEN oCARS FOR SELECT * FROM CARS;
    ..??..
END GETCARS;

which would return as a second parameter all repairs connected with currently fetched oCARS row. (Table repairs has a FK for an id_car from cars)

Now I do that on C# side, when I fetch one row from oCARS cursor I call second procedure which gives me list of repairs, but maybe it's somehow possible to do that in one procedure (which would give me performance gain? - I don't want to use join, cause it returns multiplied cars for each repair)

Upvotes: 2

Views: 8330

Answers (2)

Allan
Allan

Reputation: 17429

The simple answer is that you can't do what you are attempting.

Cursors are basically just pointers to the start of the result set that contains the query results. Until you fetch a row, there is no way to know what that row will contain. Because your application, rather than the PL/SQL code, is doing the fetching, the PL/SQL portion has no knowledge of the values being returned.

To do what you're attempting, the database would have to detect the fetch from the first query, create a new result set using the second query, then place the new result set at the address that the procedure originally returned for the second cursor. Databases just aren't designed to handle this kind of operation.

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

How about

PROCEDURE GETCARS(oCARS OUT SYS_REFCURSOR, oREPAIRS OUT SYS_REFCURSOR, oCHARGES OUT SYS_REFCURSOR) 
BEGIN 
    OPEN oCARS FOR SELECT * FROM CARS; 
    OPEN oREPAIRS FOR SELECT * FROM REPAIRS;
    OPEN oCHARGES FOR SELECT * FROM CHARGES;
END GETCARS;

Share and enjoy.

Upvotes: 2

Related Questions