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.
2 Solutions collect form web for “Oracle DB: Return second query if first query is empty”
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.
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;