oracle pl/sql - 空行是否有 NVL()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3891829/
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
pl/sql - Is there a NVL() for empty rows
提问by Topera
With this,
有了这个，
set serveroutput on size 900000;
DECLARE
test VARCHAR(255):=0;
BEGIN
SELECT id INTO test FROM sku WHERE id = 1515;
dbms_output.put_line('Result:' || test);
END;
I'm having the error
我有错误
"no data found"
When ID doesn't exist in database.
当数据库中不存在 ID 时。
How can I use something like nvl()here, so I can get a default value instead of get an error?
我怎样才能使用像nvl()这里这样的东西，所以我可以获得默认值而不是错误？
回答by Tony Andrews
Ifappropriate you could do this as an alternative to the more usual NO_DATA_FOUND exception handling:
如果合适，您可以将其作为更常见的 NO_DATA_FOUND 异常处理的替代方法：
DECLARE
test VARCHAR(255):=0;
BEGIN
SELECT NVL(MAX(id),'default') INTO test FROM sku WHERE id = 1515;
dbms_output.put_line('Result:' || test);
END;
By using MAX(ID) you are sure to get one row back, which will have a NULL when there is no data; you then use NVL in the usual way.
通过使用 MAX(ID) 你肯定会得到一行，当没有数据时它会有一个 NULL；然后您以通常的方式使用 NVL。
回答by FrustratedWithFormsDesigner
You could catch the NO_DATA_FOUND Exception
您可以捕获 NO_DATA_FOUND 异常
DECLARE
test VARCHAR(255):=0;
BEGIN
SELECT id INTO test FROM sku WHERE id = 1515;
dbms_output.put_line('Result:' || test);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('sorry no data found :(' );
END;
More on PL/SQL error handling here: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm
有关 PL/SQL 错误处理的更多信息，请访问：http: //download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm
To my knowledge, there is no such thing as nvl()for an empty row in PL/SQL, the NO_DATA_FOUNDexception is probably the closest you'll get.
据我所知，nvl()在 PL/SQL 中没有空行这样的事情，NO_DATA_FOUND异常可能是你得到的最接近的。
回答by Bob Jarvis - Reinstate Monica
You can't use NVL (or NVL2 or COALESCE, which are similar) here because there's no data on which these functions can operate. The following will do what you've asked:
你不能在这里使用 NVL（或 NVL2 或 COALESCE，它们是类似的），因为没有这些函数可以运行的数据。以下将执行您的要求：
DECLARE
test VARCHAR2(255):=0;
BEGIN
BEGIN
SELECT id INTO test FROM sku WHERE id = 1515;
EXCEPTION
WHEN NO_DATA_FOUND THEN
test := '0';
END;
dbms_output.put_line('Result:' || test);
END;
but just because something is possible doesn't mean it's a good idea. The database is trying to warn about something important (specifically, that there's no data which matches your criteria) and this code ignores that warning.
但仅仅因为某些事情是可能的并不意味着它是一个好主意。数据库试图警告一些重要的事情（特别是，没有符合您的条件的数据）并且此代码忽略该警告。
回答by ninesided
Depending on what you're doing, you could move your query to a cursor and fetch from it:
根据您在做什么，您可以将查询移动到游标并从中获取：
DECLARE
test VARCHAR(255):=0;
CURSOR c_test IS
SELECT id
FROM sku
WHERE id = 1515
;
BEGIN
OPEN c_test;
FETCH c_test INTO test;
IF c_test%NOTFOUND THEN
dbms_output.put_line('no data found');
ELSE
dbms_output.put_line('result:' || test);
END IF;
CLOSE c_test;
END;
