PL/SQL - ref cursor
From: Tim Smith <timasmith_at_hotmail.com>
Date: 25 Jul 2004 22:18:42 -0700
Message-ID: <a7234bb1.0407252118.3d2a1c41_at_posting.google.com>
I have a stored procedure which returns several ref cursors. I have a few requirements which I am trying to achieve.
BEGIN
OPEN ctable1 FOR
select * from table1 where primarykey = key;
Date: 25 Jul 2004 22:18:42 -0700
Message-ID: <a7234bb1.0407252118.3d2a1c41_at_posting.google.com>
I have a stored procedure which returns several ref cursors. I have a few requirements which I am trying to achieve.
- Ensure SQL is easy to validate by inspection
- Execute minimum number of SQL
- Ensure all refCursors are valid
Sample Code
PROCEDURE GetStuff (key varchar2, ctable1 OUT refCursor, ctable2 OUT refCursor,
ctable3 OUT refCursor)IS
BEGIN
OPEN ctable1 FOR
select * from table1 where primarykey = key;
OPEN ctable2 FOR
select * from table2 where key = (
select pkey from table1 where primarykey = key);
OPEN ctable2 FOR
select * from table3 where key in (
select field from table2 where key = (select pkey from table3 where primarykey = key));
EXCEPTION
when no_data_found then
null;
END GetStuff;
Problems
- SQL is easy to validate by using nested queries (often exists) but is it efficient?
- I dont know if there is something else I can set the refcursor to. It seems poor practise to do something like IF (missing(pkey)) THEN select * from table3 where rownum<1; END IF;I would need to do an extra select to get the pkey of course which may be wasteful
- I tried wrapping IF statements around child tables but unless the refcursor is valid (i.e. executed I guess) the client code errors - 0 rows are fine but not executing the select causes issues.
Any ideas? Received on Mon Jul 26 2004 - 07:18:42 CEST