SQL 检查plsql中的变量是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1984141/
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
Check if a variable is null in plsql
提问by user223541
I want to check if a variable is null. If it is null, then I want to set a value to that variable:
我想检查一个变量是否为空。如果它为空，那么我想为该变量设置一个值：
//data type of var is number
if Var = null then
var :=5;
endif
But I am geting error in it. How can I check if a variable is null?
但我遇到了错误。如何检查变量是否为空？
I am using oracle data type
我正在使用 oracle 数据类型
回答by Priyank
if var is NULL then
var :=5;
end if;
回答by OMG Ponies
回答by Bob Jarvis - Reinstate Monica
In PL/SQL you can't use operators such as '=' or '<>' to test for NULLbecause all comparisons to NULLreturn NULL. To compare something against NULLyou need to use the special operators IS NULLor IS NOT NULLwhich are there for precisely this purpose. Thus, instead of writing
在 PL/SQL 中，您不能使用诸如 '=' 或 '<>' 之类的运算符来测试，NULL因为所有比较都NULL返回NULL. 要将某些内容与NULL您进行比较，您需要使用特殊运算符IS NULL或专门IS NOT NULL用于此目的的运算符。因此，而不是写
IF var = NULL THEN...
you should write
你应该写
IF VAR IS NULL THEN...
In the case you've given you also have the option of using the NVLbuilt-in function. NVLtakes two arguments, the first being a variable and the second being a value (constant or computed). NVLlooks at its first argument and, if it finds that the first argument is NULL, returns the second argument. If the first argument to NVLis not NULL, the first argument is returned. So you could rewrite
在您提供的情况下，您还可以选择使用NVL内置函数。 NVL接受两个参数，第一个是变量，第二个是值（常量或计算值）。 NVL查看它的第一个参数，如果发现第一个参数是NULL，则返回第二个参数。如果第一个参数NVL不是NULL，则返回第一个参数。所以你可以重写
IF var IS NULL THEN
var := 5;
END IF;
as
作为
var := NVL(var, 5);
I hope this helps.
我希望这有帮助。
EDIT
编辑
And because it's nearly ten years since I wrote this answer, let's celebrate by expanding it just a bit.
因为我写这个答案已经快十年了，让我们稍微扩展一下来庆祝一下。
The COALESCEfunction is the ANSI equivalent of Oracle's NVL. It differs from NVLin a couple of IMO good ways:
该COALESCE函数是 ANSI 等效的 Oracle 的NVL. 它与NVLIMO 的几个好方法不同：
It takes any number of arguments, and returns the first one which is not NULL. If all the arguments passed to
COALESCEare NULL, it returns NULL.In contrast to
NVL,COALESCEonly evaluates arguments if it must, whileNVLevaluates both of its arguments and then determines if the first one is NULL, etc. SoCOALESCEcan be more efficient, because it doesn't spend time evaluating things which won't be used (and which can potentially cause unwanted side effects), but it also means thatCOALESCEis not a 100% straightforward drop-in replacement forNVL.
它接受任意数量的参数，并返回第一个不为 NULL 的参数。如果传递给的所有参数
COALESCE都是 NULL，则返回 NULL。与 相比
NVL，COALESCE仅在必须时评估参数，而NVL评估其两个参数，然后确定第一个参数是否为 NULL，等等。因此COALESCE效率更高，因为它不会花时间评估不会使用的东西（这可能会导致不必要的副作用），但这也意味着它COALESCE不是NVL.
回答by Arthur Thomas
回答by Andy White
回答by Calmar
Always remember to be careful with nulls in pl/sql conditional clauses as null is never greater, smaller, equal or unequal to anything. Best way to avoid them is to use nvl.
永远记住要小心 pl/sql 条件子句中的空值，因为空值永远不会大于、小于、等于或不等于任何东西。避免它们的最佳方法是使用 nvl。
For example
例如
declare
i integer;
begin
if i <> 1 then
i:=1;
foobar();
end if;
end;
/
Never goes inside the if clause.
永远不要进入 if 子句。
These would work.
这些会起作用。
if 1<>nvl(i,1) then
if i<> 1 or i is null then
回答by Tony Andrews
Another way:
其它的办法：
var := coalesce (var, 5);
COALESCE is the ANSI equivalent (more or less) of Oracle's NVL function.
COALESCE 是 Oracle 的 NVL 函数的 ANSI 等价物（或多或少）。
回答by Dapeng
use if var is null
如果 var 为空则使用
