I have a stored procedure which returns several ref cursors. I have a
few requirements which I am trying to achieve.

1) Ensure SQL is easy to validate by inspection
2) Execute minimum number of SQL
3) 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

1) SQL is easy to validate by using nested queries (often exists) but
is it efficient?

2) 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

3) 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?