我有这样的查询：
select data_name into v_name from data_table where data_table.type = v_t_id
通常,此查询应该只返回一行.当v_t_id上没有匹配项时,程序将失败并显示“No data found”异常.
我知道我可以在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;
…但这显然不起作用(因为subq为空与subq.data_name不相同).这甚至是可能的还是我应该检查我的PL / sql解决方案？
(oracle 10g)
解决方法
有办法使这更简单,更清洁,但这基本上说明了技术：
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
)
当union的第一部分为空时,第二部分将包含一行,当第一部分不为空时,第二部分将不包含任何行.
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
