ORACLE PL/SQL：函数和可选参数，如何？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3971001/
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 PL/SQL: Functions and Optional Parameters, How?
提问by jlrolin
I'm looking for the optimal way to create a function that can accept no parameters and return all results, but also accepts parameters and return those results.
我正在寻找创建一个函数的最佳方法，该函数可以不接受任何参数并返回所有结果，但也接受参数并返回这些结果。
The standard I've been dealing with at my job is this:
我在工作中处理的标准是：
FUNCTION get_records (
i_code IN records.code%type := NULL,
i_type IN records.type%type := NULL
) RETURN results
The problem is that I want to return records that have a type of NULL as well, and using:
问题是我想返回类型为 NULL 的记录，并使用：
WHERE type = nvl(i_type, type)
It only returns records with actual types and not the null records.. for obvious reasons. I was just wondering if there is a standard way of doing this that could be implemented across all functions we use. Coincidentally, if I provide a parameter... I don't want the NULL values of that field.
出于显而易见的原因，它只返回具有实际类型的记录而不是空记录。我只是想知道是否有一种标准方法可以在我们使用的所有功能中实现。巧合的是，如果我提供一个参数......我不想要该字段的 NULL 值。
采纳答案by Harrison
why not simply what you have with the addition of
为什么不简单地加上你所拥有的
type = i_type OR (i_type IS NULL AND type IS NULL)
that way, when the passed in param is null it looks for everything (including nulls) or the specified value. Now, if you just want the nulls...
这样，当传入的参数为空时，它会查找所有内容（包括空值）或指定的值。现在，如果你只想要空值......
example (change the value from null to 5 and you will see the output)
示例（将值从 null 更改为 5，您将看到输出）
WITH TESTDATA AS (
SELECT LEVEL dataId
FROM DUAL
CONNECT BY LEVEL <= 100
UNION
SELECT NULL
from dual
)
SELECT *
FROM TESTDATA
where dataId = :n or (:n is null AND dataId is null) ;
Whereas :n = 6 the results will be
而 :n = 6 结果将是
DATAID
----------------------
6
(deleted a new post, misread a param) But I like the new approach and avoiding the NVL
（删除了一个新帖子，误读了一个参数）但我喜欢新方法并避免使用 NVL
here's a nifty way to do it if you are not adverse to dynamic sql
如果您不反对动态 sql，这是一个很好的方法
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html
回答by Jeffrey Kemp
The standard way to solve this issue is to overload the function, instead of using default values:
解决这个问题的标准方法是重载函数，而不是使用默认值：
FUNCTION get_records (
i_code IN records.code%type,
i_type IN records.type%type
) RETURN results;
FUNCTION get_records (
i_code IN records.code%type
) RETURN results;
FUNCTION get_records RETURN results;
Note: If you also need a version i_typeby itself, you might have trouble if it has the same underlying type as i_code- in which case you'd need to use a different name for the function.
注意：如果您还需要一个单独的版本i_type，如果它具有相同的基础类型，您可能会遇到问题i_code- 在这种情况下，您需要为函数使用不同的名称。
回答by Will Marcouiller
Right off the top of my head, I would guess the use of the DEFAULTkeyword would do the trick, doesn't it? (The following link will give some further details.)
就在我的头顶上，我猜想使用DEFAULT关键字可以解决问题，不是吗？（以下链接将提供更多详细信息。）
CREATE OR REPLACE FUNCTION get_records ( i_code IN records.code%type DEFAULT NULL, i_type IN records.type%type DEFAULT NULL ) RETURN results
CREATE OR REPLACE FUNCTION get_records ( i_code IN records.code%type DEFAULT NULL, i_type IN records.type%type DEFAULT NULL ) RETURN results
EDIT #1
编辑#1
If I understand the question correctly, you want to return all of the records when the i_typeparameter is NULL. In absence of further details, my guess would be the following.
如果我正确理解了这个问题，您希望在i_type参数为 NULL时返回所有记录。在没有更多细节的情况下，我的猜测如下。
CREATE OR REPLACE FUNCTION get_records (
i_code IN records.code%TYPE DEFAULT NULL,
i_type IN records.type%TYPE DEFAULT NULL
) RETURN results
BEGIN
IF (i_type IS NULL) THEN
select *
from table
ELSE
select *
from table
where type = NVL(i_type, type)
END IF
EXCEPTION
WHEN OTHERS THEN
NULL
END
That is all I can do with the information provided, although the function body is commented below.
这就是我对提供的信息所能做的所有事情，尽管下面对函数体进行了注释。
EDIT #2
编辑#2
I am a bit rusty from Oracle, so I consulted some documentation as linked below:
我对 Oracle 有点生疏，所以我查阅了一些文档，链接如下：
As I have read it, you had better use the NVLfunction within your SELECTinstruction, and not your WHEREclause.
正如我所读到的，你最好NVL在你的SELECT指令中使用这个函数，而不是你的WHERE子句。
In the end, what is your question exactly? Could you make it crystal clear?
最后，你的问题究竟是什么？你能说清楚吗？
回答by Dave Costa
To reiterate my understanding of the issue: You can't take the default value of NULLto mean "return all records", because the current expected behavior is that it will return only those records where the value is actually NULL.
重申我对这个问题的理解：您不能将默认值取NULL为“返回所有记录”，因为当前的预期行为是它只会返回值实际上为 NULL 的那些记录。
One possibility is to add a boolean parameter corresponding to each lookup parameter, which indicates whether it should actually be used to filter results. A potential issue with this is that a caller might specify a lookup value but fail to set the flag to true, producing unexpected results. You can guard against this at runtime by raising an exception if the flag is false for any lookup value other than NULL.
一种可能性是为每个查找参数添加一个布尔参数，指示是否应该实际使用它来过滤结果。一个潜在的问题是调用者可能指定了一个查找值，但未能将该标志设置为 true，从而产生意外的结果。您可以在运行时通过引发异常来防止这种情况发生，如果除NULL.
Another possibility is to define an out-of-domain value for each lookup column -- e.g. if the string 'ANY' is passed for the parameter, it will not filter values on that column. This should work fine as long as you can find a worthwhile sentry value for each column. I'd suggest declaring the sentry values as constants in some package, so that calls to the function could look like get_records( PKG.ALL_CODES, 'type1' ).
另一种可能性是为每个查找列定义域外值——例如，如果为参数传递字符串“ANY”，则不会过滤该列上的值。只要您能为每一列找到有价值的哨兵值，这应该可以正常工作。我建议将哨兵值声明为某个包中的常量，以便对函数的调用看起来像get_records( PKG.ALL_CODES, 'type1' ).
回答by Don.Mega
I have been using the following hack (assuming p_product is an optional procedure input):
我一直在使用以下技巧（假设 p_product 是一个可选的过程输入）：
where t.product = decode(p_product, null, t.product, p_product)
其中 t.product = decode(p_product, null, t.product, p_product)
procedure get_data(p_product in number := null)
...
select *
from tbl t
where t.product = decode(p_product, null, t.product, p_product);
...
This will create 'where 1=1' scenario when the product is not passed in, and use the product otherwise. One potentially major drawback is that this parses to the same execution plan regardless of parameters.
这将在产品未传入时创建“where 1=1”场景，否则使用产品。一个潜在的主要缺点是，无论参数如何，这都会解析为相同的执行计划。
