CODEWITHSUNDEEP
sqloracle-databaseplsql
Dan
Dan

Reputation: 1083

PLSQL Execute another Select statement if First is Empty with ref cursor

I am trying to do something similar to the following:

procedure some_name ( p_ref_cursor in out nocopy sys_refcursor )

is

begin

open p_ref_cursor for
select item1, item2 from (some complicated join query);

fetch p_ref_cursor into xx;
if p_ref_cursor%NOTFOUND
then
select item1, item2 from table2 where item3='y';
end if;

end

If the first select statement returns nothing it should execute the second select statement to retrieve values. The problem I am running into is when the cursor is fetched, the result set is cleared out. So regardless if the first select statement found anything, it acts as if it was not found.

I have tried the following:

procedure some_name ( p_ref_cursor in out nocopy sys_refcursor )

is
local_var1 number
local_var2 number
begin

open p_ref_cursor for
select item1, item2 
into local_var1, local_var2
from (some complicated join query);

if local_var1 is null
then
select item1, item2 from table2 where item3='y';
end if;

end

However it looks like the INTO statement does not work with ref cursor.

Thanks, Dan

Upvotes: 2

Views: 572

Answers (1)

xQbert
xQbert

Reputation: 35333

This seems like overkill for something that I think should work. But I believe it would work around the problem and eliminate the need for your if statements. Additionally it may be slightly slower since it has to do both queries. So if performance is of the utmost concern, then I wouldn't use this.

WITH CTE AS
(SELECT item1, item2, '1' as Src 
FROM (some complicated join query)
UNION
SELECT item1, item2, '2' as SRC
FROM table2 where item3='y')
SELECT item1, item2
FROM CTE
WHERE src = (Select min(src) from cte)

Upvotes: 1

Related Questions