Oracle:获取查询以始终仅返回一行，即使找不到数据也是如此 [英] Oracle: Get a query to always return exactly one row, even when there's no data to be found

问题描述

我有这样的查询:

   select data_name
   into v_name
   from data_table
   where data_table.type = v_t_id

通常，此查询应仅返回一行.当v_t_id上没有匹配项时，程序将失败，并显示找不到数据"异常.

Normally, this query should return exactly one row. When there's no match on v_t_id, the program fails with a "No data found" exception.

我知道我可以在PL/SQL中处理此问题，但我想知道是否有办法仅在查询中执行此操作.作为测试，我尝试了:

I know I could handle this in PL/SQL, but I was wondering if there's a way to do this only in a query. As a test, I've tried:

select case
           when subq.data_name is null then
            'UNKNOWN'
           else
            subq.data_name
       end
from (select data_name
       from data_table
       where data_table.type = '53' /*53 does not exist, will result in 0 rows. Need fix this...*/
       ) subq;

...但这显然不起作用(因为subq为空与subq.data_name is null不同).甚至有可能还是我应该只检查我的PL/SQL解决方案?

...but this will obviously not work (because subq being empty is not the same as subq.data_name is null). Is this even possible or should I just check in my PL/SQL solution?

(oracle 10克)

(oracle 10g)

推荐答案

有一些方法可以使此方法更简单，更简洁，但这基本上说明了该技术:

There's ways to make this simpler and cleaner, but this basically spells out the technique:

SELECT data_name
FROM data_table
WHERE data_table.type = v_t_id

UNION ALL

SELECT NULL AS data_name
FROM dual
WHERE NOT EXISTS (
    SELECT data_name
    FROM data_table
    WHERE data_table.type = v_t_id
)

当联合的第一部分为空时，第二部分将包含一行，当第一部分不为空时，第二部分将不包含任何行.

When the first part of the union is empty the second will contain a row, when the first part is not empty, the second will contain no rows.

如果查询需要花费很多时间，请使用以下命令:

If the query is takes to much time, use this one:

SELECT * FROM (  
    SELECT data_name
    FROM data_table
    WHERE data_table.type = v_t_id

    UNION ALL

    SELECT NULL AS data_name
    FROM dual
  ) WHERE data_name is not null or ROWNUM = 1

这篇关于Oracle:获取查询以始终仅返回一行，即使找不到数据也是如此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！