Oracle DB:如果第一个查询为空，则返回第二个查询 [英] Oracle DB: Return second query if first query is empty

问题描述

我正在编写一个Oracle存储过程以返回数据库查询的结果.如果查询未产生任何结果，则必须在其位置运行第二个查询.

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.

在SQL Server中，我可以使用类似于以下内容的方法来实现此目的:

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

但是，我不能全神贯注于如何在Oracle中完成相同的任务.

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

推荐答案

您可以利用WITH使其表现更好(并且更易于维护):

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

这应该返回query2的10行.如果从query1中删除where 1 = 0(使它实际上返回行)，则应该从query1中获得10行.

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.

这篇关于Oracle DB:如果第一个查询为空，则返回第二个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！