oracle NO_DATA_FOUND、SELECT COUNT(*) INTO var 和 var IS NULL 对触发器的澄清
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6426495/
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
NO_DATA_FOUND, SELECT COUNT(*) INTO var and var IS NULL clarification on triggers
提问by Peter Lang
I was reading that a SELECTthat doesn't retrieve any data will generate NO_DATA_FOUND.
我读到SELECT不检索任何数据的 a 将生成NO_DATA_FOUND.
But for my experience this isn't true because I did a
但根据我的经验，这不是真的，因为我做了一个
SELECT COUNT(*) INTO mylocalvar FROM tbl;
on an empty table and it didn't launch NO_DATA_FOUND exception.
在一个空表上，它没有启动 NO_DATA_FOUND 异常。
Instead I found out that mylocalvarwas NULL.
I excepted it to be = 0but it was NULL.
相反，我发现那mylocalvar是NULL.
我除外它是= 0但它是NULL。
So basically if I need to check if that count(*)is = 0I have to:
所以基本上，如果我需要检查，如果这count(*)是= 0我必须：
IF mylocalvar IS NULL THEN
--do stuff
Is this right? Also why in this case NO_DATA_FOUND doesn't apply?
Can I assign mylocalvar a 0 value as default? Maybe in the declare:
这是正确的吗？另外为什么在这种情况下 NO_DATA_FOUND 不适用？
我可以将 mylocalvar 指定为默认值 0 吗？也许在声明中：
DECLARE
mylocalvar NUMBER := 0;
So I can just do IF mylocalvar = 0 THEN?
所以我能做IF mylocalvar = 0 THEN吗？
Thanks
谢谢
回答by Peter Lang
I don't see why your query would return NULL. It should return exactly one row, that's why the exception is not thrown.
我不明白为什么您的查询会返回NULL。它应该只返回一行，这就是不抛出异常的原因。
Since your table has no rows, COUNT(*)is 0. This is stored in mylocalvar.
由于您的表没有行，因此COUNT(*)是0. 这存储在mylocalvar.
Try the following code, it outputs0
试试下面的代码，它输出0
DECLARE
mylocalvar PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO mylocalvar
FROM dual
WHERE 1 = 2;
dbms_output.put_line(mylocalvar);
END;
