SQL Oracle：获取查询以始终只返回一行，即使找不到数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4682821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Get a query to always return exactly one row, even when there's no data to be found
提问by FrustratedWithFormsDesigner
I have a query like this:
我有一个这样的查询：
select data_name
into v_name
from data_table
where data_table.type = 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.
通常，这个查询应该只返回一行。当 上没有匹配项时v_t_id，程序将失败并显示“未找到数据”异常。
I know I couldhandle 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:
我知道我可以在 PL/SQL 中处理这个，但我想知道是否有办法只在查询中做到这一点。作为测试，我尝试过：
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;
...but this will obviously not work (because subqbeing 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?
...但这显然不起作用（因为subq空与 不同subq.data_name is null）。这甚至是可能的还是我应该检查我的 PL/SQL 解决方案？
(oracle 10g)
(甲骨文 10g)
回答by Cade Roux
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
回答by Tony Andrews
I would prefer to handle the exception. However, this would work as you specify:
我更愿意处理异常。但是，这将按照您的指定工作：
select min(data_name) data_name
into v_name
from data_table
where data_table.type = v_t_id
Note that this also "works" if the query returns morethan 1 row - i.e. TOO_MANY_ROWS is not raised.
请注意，这也是“作品”如果查询返回多比1点的行-即TOO_MANY_ROWS不提高。
回答by Conrad Frix
select coalesce(data_table.data_name, d.data_name) data_name
into v_name
from
(SELECT 'UNKNOWN ' as data_name FROM DUAL) d
LEFT JOIN data_table
ON data_table.type = v_t_id
or a.data_table.data_name is null
回答by Félix Hernández
Here is my simple solution using LEFT OUTER JOIN:
这是我使用 LEFT OUTER JOIN 的简单解决方案：
CREATE TABLE data_table(data_name VARCHAR2(20), data_type NUMBER(2));
INSERT INTO data_table(data_name, data_type) VALUES('fifty-one', 51);
SELECT coalesce(data_name, 'unknown')
FROM dual
LEFT OUTER JOIN (SELECT data_name FROM data_table WHERE data_type = 53) o
ON 1 = 1;
SELECT coalesce(data_name, 'unknown')
FROM dual
LEFT OUTER JOIN (SELECT data_name FROM data_table WHERE data_type = 51) o
ON 1 = 1;
回答by michael nesterenko
https://stackoverflow.com/a/4683045/471149answer is nice, but there is shorter solution
https://stackoverflow.com/a/4683045/471149答案很好，但有更短的解决方案
select * from my_table ce, (select 150 as id from dual) d
where d.id = ce.entry_id (+)
回答by tawman
If you always expect zero or one row then you can use a group function i.e.:
如果您总是期望零或一行，那么您可以使用组功能，即：
select dump(max(dummy)) from dual
where dummy = 'Not Found'
You will always get at least one row and a value of NULL in the case where the record is not found.
在找不到记录的情况下，您将始终获得至少一行和 NULL 值。
