ORACLE PL/SQL：函数和可选参数，如何？

Question

我正在寻找创建一个函数的最佳方法，该函数可以不接受任何参数并返回所有结果，但也可以接受参数并返回这些结果。

我在工作中一直在处理的标准是这样的：

FUNCTION get_records (
  i_code                                 IN records.code%type := NULL,
  i_type                                 IN records.type%type := NULL
) RETURN results

问题是我也想返回具有 NULL 类型的记录，并使用：

WHERE type = nvl(i_type, type)

它只返回具有实际类型的记录，而不是空记录。明显的原因。我只是想知道是否有一种标准方法可以在我们使用的所有功能中实现这一点。巧合的是，如果我提供一个参数...我不想要该字段的 NULL 值。

Answer 1

为什么不简单地添加这种

 type = i_type OR (i_type IS NULL AND type IS NULL)

方式，当传入的参数为空时，它会查找所有内容（包括空值）或指定的值。现在，如果您只想要 null...

示例（将值从 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) ;

而 :n = 6
结果将是

DATAID                 
---------------------- 
6

（删除了新帖子，误读了参数）
但我喜欢这种新方法，并且避免 NVL

如果您不反对动态 sql

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

Answer 2

解决此问题的标准方法是重载该函数，而不是使用默认值：

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;

注意：如果您还需要一个版本 i_type 本身，那么如果它具有与以下版本相同的基础类型，您可能会遇到麻烦i_code - 在这种情况下，您需要为该函数使用不同的名称。

Answer 3

我突然想到，我猜想使用 DEFAULT 关键字就可以达到目的，不是吗？ （以下链接将提供更多详细信息。）

1. 使用 DEFAULT 关键字。

   创建或替换函数 get_records (
       i_code IN 记录.code%type DEFAULT NULL,
       i_type IN 记录.type%type DEFAULT NULL
   ) 返回结果
   

编辑 #1

如果我正确理解问题，您希望在 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

尽管函数体在下面进行了注释，但这就是我对所提供的信息所能做的全部。

编辑 #2

我对 Oracle 有点生疏，所以我查阅了下面链接的一些文档：

Oracle/PLSQL：NVL 函数

正如我所读到的，您最好在 SELECT< 中使用 NVL 函数/code> 指令，而不是您的 WHERE 子句。

最后，你的问题到底是什么？你能说得一清二楚吗？

Answer 4

重申我对这个问题的理解：您不能将 NULL 的默认值表示“返回所有记录”，因为当前的预期行为是它将仅返回值为实际上是NULL。

一种可能性是添加与每个查找参数相对应的布尔参数，该参数指示是否应该实际使用它来过滤结果。这样做的一个潜在问题是调用者可能会指定查找值但无法将标志设置为 true，从而产生意外结果。如果该标志对于 NULL 以外的任何查找值都为 false，则可以在运行时通过引发异常来防止这种情况。

另一种可能性是为每个查找列定义一个域外值——例如，如果为参数传递字符串“ANY”，则它不会过滤该列上的值。只要您能为每列找到一个有价值的哨兵值，这应该可以正常工作。我建议将哨兵值声明为某个包中的常量，以便对该函数的调用看起来像 get_records( PKG.ALL_CODES, 'type1' )。

Answer 5

我一直在使用以下 hack（假设 p_product 是可选的过程输入）：

where 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);
...

这将创建 'where 1=1'不传入产品的场景，否则使用该产品。一个潜在的主要缺点是，无论参数如何，它都会解析为相同的执行计划。