CODEWITHSUNDEEP
sql-serveroracle-databasestored-proceduresplsql
Nelson Melendez
Nelson Melendez

Reputation: 43

Oracle DB: Return second query if first query is empty

I am writing an Oracle stored procedure to return the results of a database query. If the query does not produce any results, a second query must be run in its place.

In SQL Server, I can accomplish this using something similar to the following:

INSERT INTO @TableVar
SELECT <joinQuery1>;

IF (SELECT COUNT(*) FROM @TableVar) > 0
BEGIN
    SELECT * FROM @TableVar;  -- returns <joinQuery1>
END
ELSE
    SELECT <joinQuery2>;  --returns <joinQuery2>
END

However, I can not wrap my head around how to accomplish the same task in Oracle.

Upvotes: 3

Views: 3837

Answers (2)

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

The answer depends very much how are you going to use results of query further. So you should either use pipelened functions, insert into GTT or return ref cursor.

At any case I would recommend you to do it in 1 SQL statement to achieve read consistency.

So please consider something like

create procedure test (pCursor out sys_refcursor) is
begin
 open pCursor for
 select <joinQuery1>
 union all
 SELECT <joinQuery2>
 where not exists (select 1 from joinquery1)
 ;
end;

Upvotes: 0

Craig
Craig

Reputation: 5820

You can utilize WITH to make this perform better (and easier to maintain):

WITH query1 as (
    select 1, 2
    from dual
    where 1=0
    connect by level <= 10
),
query2 as (
    select 3, 4
    from dual
    connect by level <= 10
)
select *
from query1
union all
select *
from query2
where not exists (
    select null
    from query1
);

As is this should return the 10 rows from query2. If you remove the where 1=0 from query1 (causing it to actually return rows), you should get the 10 rows from query1.

Upvotes: 8

Related Questions