sql - 如何处理SQL查询中的可选参数？

Question

假设我有一个示例表:

 id_pk  value
------------
 1       a
 2       b
 3       c

我有一个示例 PL/SQL block ，其中有一个查询当前将单行选择到数组中:

declare

  type t_table is table of myTable%rowtype;

  n_RequiredId myTable.id_pk%type := 1;  
  t_Output t_table := t_table();

begin

  select m.id_pk, m.value
    bulk collect into t_Output
    from myTable m 
   where m.id_pk = n_RequiredId;

end;

我需要做的是实现将单行选择到数组中的功能，如上面的 block 所示，或将所有行选择到数组中，如果n_RequiredID ，实际上是一个用户输入参数，被设置为null。

问题是，处理这种情况的最佳做法是什么？

我可以考虑将查询的 where 子句修改为如下所示:

where m.id_pk = nvl(n_RequiredId, m.id_pk);

但我想如果参数不为空，查询就会变慢，而且我记得 Kyte 说过关于这种方法的一些非常糟糕的话。

我还可以考虑实现以下 PL/SQL 逻辑:

if n_RequiredId is null then 

  select m.id_pk, m.value bulk collect into t_Output from myTable m;

else

  select m.id_pk, m.value bulk collect
    into t_Output
    from myTable m
   where m.id_pk = n_RequiredId;

end if;

但是如果我遇到多个此类参数，就会变得太复杂。

你会给我什么建议？

Answer 1

是的，使用以下任意一种:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

...是not sargable 。它们可以工作，但执行的是可用选项中最差的。

如果只有一个参数，IF/ELSE 和单独的定制语句是更好的选择。

之后的下一个选项是 dynamic SQL 。但是，如果您继承第一个示例中的不可控制谓词，则编写动态 SQL 是没有用的。动态 SQL 允许您定制查询，同时容纳大量路径。但它也有 SQL 注入(inject)的风险，因此应该在参数化查询后面执行(最好在包中的存储过程/函数内执行。