Oracle 默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/286946/
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 Default Values
提问by Christopher McAtackney
I've got a quick question about default values in PL/SQL functions in Oracle. Take this program as an example;
我有一个关于 Oracle PL/SQL 函数中默认值的快速问题。以本程序为例；
create or replace
FUNCTION testFunction
(
varNumber IN NUMBER DEFAULT 0
)
RETURN NUMBER
AS
BEGIN
dbms_output.put_line(varNumber);
RETURN varNumber;
END;
The idea here being that if no value is specified for varNumber when this function is called, then it will take the value of 0.
这里的想法是，如果在调用此函数时没有为 varNumber 指定值，则它将取值为 0。
Now, my problem is that my functions are getting called from a web services layer that will always pass in NULL as the value for parameters which it doesn't have a value for. Oracle interprets NULL as a value, and so does not initialise varNumber to its default of 0.
现在，我的问题是我的函数是从 Web 服务层调用的，该层将始终传入 NULL 作为它没有值的参数的值。Oracle 将 NULL 解释为一个值，因此不会将 varNumber 初始化为其默认值 0。
I can see why this approach makes sense, but I was wondering if there was a way to override this behaviour, and make it so that if a NULL value is passed, that it causes Oracle to assign the explicit DEFAULT value that is specified in the function header?
我可以理解为什么这种方法有意义，但我想知道是否有一种方法可以覆盖此行为，并使其在传递 NULL 值时会导致 Oracle 分配在函数头？
I have considered the option of doing a manual check...
我已经考虑过手动检查的选项......
IF(varNumber IS NULL) THEN
varNumber := 0;
END IF;
However, there are hundreds of functions where this may be an issue, never mind the large number of parameters per function, and so I'd prefer it if I could find a more general solution to the problem.
但是，有数百个函数可能是一个问题，更不用说每个函数的大量参数了，所以如果我能找到更通用的解决方案，我会更喜欢它。
Cheers for any insight you can give.
为您提供的任何见解干杯。
回答by Turnkey
Use NVL to define the value.
使用 NVL 来定义值。
NVL( value_in, replace_with )
回答by Jim Hudson
You can't assign values to an IN parameter, but you could make them IN/OUT and then set them. That raises a big potential for misuse and confusion, though.
您不能为 IN 参数分配值，但您可以将它们输入/输出，然后设置它们。不过，这会带来很大的滥用和混淆的可能性。
So I think you'd do better with a local variable. But you can do it in the declaration. That is,
所以我认为你最好使用局部变量。但是你可以在声明中做到这一点。那是，
create or replace
FUNCTION testFunction
(
varNumber IN NUMBER DEFAULT 0
)
RETURN NUMBER
AS
vFix number := nvl(varNumber,0);
BEGIN
dbms_output.put_line(vFix);
RETURN vFix;
END;
回答by Rob Stevenson-Leggett
Your manual check is the only way to safely do what you want.
手动检查是安全执行所需操作的唯一方法。
You can write that in one line like this though:
你可以像这样写在一行中：
varNumber = NVL(varNumber,0);
Good luck!
祝你好运！
