oracle 如何确定 PL/SQL 参数值是否为默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/882466/
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
How do I determine if a PL/SQL parameter value was defaulted?
提问by Adam Paynter
Suppose I have a PL/SQL stored procedure as follows:
假设我有一个 PL/SQL 存储过程，如下所示：
PROCEDURE do_something(foo VARCHAR2 DEFAULT NULL) IS
BEGIN
/* Do something */
END;
Now, suppose do_somethingis invoked two different ways:
现在，假设do_something以两种不同的方式被调用：
/* Scenario 1: The 'foo' parameter defaults to NULL */
do_something();
/* Scenario 2: The 'foo' parameter is explicitly set to NULL */
do_something(foo => NULL)
How can I define the do_somethingprocedure to determine which scenario is calling it?
我如何定义do_something程序来确定调用它的场景？
Edit: Clarifying my intentions for this procedure:
编辑：澄清我对这个程序的意图：
FUNCTION find_customer(name VARCHAR2 DEFAULT NULL, number VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
BEGIN
/* Query the "customer" table using only those parameters provided */
END;
Below are example uses of this procedure with the associated SQL clauses desired:
以下是此过程与所需的关联 SQL 子句的示例用法：
/* SELECT * FROM customer WHERE customer.name = 'Sam' */
find_customer(name => 'Sam')
/* SELECT * FROM customer WHERE customer.name = 'Sam' AND customer.number = '1588Z' */
find_customer(name => 'Sam', number => '1588Z')
/* SELECT * FROM customer WHERE customer.name = 'Sam' AND customer.number IS NULL */
find_customer(name => 'Sam', number => NULL)
/* SELECT * FROM customer WHERE customer.name IS NULL */
find_customer(name => NULL)
/* SELECT * FROM customer WHERE customer.name IS NULL AND customer.number IS NULL */
find_customer(name => NULL, number => NULL)
回答by Greg
How about instead of defaulting to null, default the omitted parameter values to something you will never use in the real world? The values you use should belong to some domain so choose values outside that domain.
与其默认为 null，不如将省略的参数值默认为您在现实世界中永远不会使用的值？您使用的值应该属于某个域，因此请选择该域之外的值。
eg
例如
PROCEDURE do_something(foo VARCHAR2 DEFAULT '*#@') IS
PROCEDURE do_something(foo VARCHAR2 DEFAULT '*#@') 是
l_foo VARCHAR2(32000); -- local copy of foo parm
BEGIN
开始
IF foo = '*#@' THEN
-- I know the parm was omitted
l_foo := NULL;
ELSE
l_foo := foo;
END IF;
END;
结尾;
回答by Stefan Steinegger
You could overload the procedure instead of using a default value:
您可以重载该过程而不是使用默认值：
PROCEDURE do_something(foo VARCHAR2) IS
BEGIN
/* Do something */
END;
PROCEDURE do_something IS
BEGIN
/* here you know: no argument. Then call do_something(null) */
END;
