如何在SQL查询中处理可选参数？

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

我有一个示例PL / SQL块,它有一个查询,当前选择一行到一个数组：

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;

我需要做的是实现一个选择单个行到一个数组的能力,如上面的块所示,或者选择所有行到一个数组中,如果n_RequiredID,实际上是一个用户输入参数,设置为空值.

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

我可以考虑修改我的查询的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;

但如果遇到这种类型的多个参数,会变得太复杂.

你有什么建议我的？

解决方法

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.但是如果你在第一个例子中继承了非sargable谓词,那么编写动态SQL是没用的.动态SQL允许您在容纳多个路径的同时定制查询.但它也存在SQL注入的风险,因此应该在参数化查询后面执行(最好在包中的存储过程/函数内).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!