如何处理SQL查询中的可选参数? [英] How to handle optional parameters in SQL query?

问题描述

说我有一个样本表:

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

我有一个示例PL/SQL块，该块具有一个查询，该查询当前选择数组中的一行:

And I have a sample PL/SQL block, which has a query that currently selects a single row into an array:

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;

我需要做的是实现选择单个行到数组中的功能，如上面的块所示， OR ，如果选择n_RequiredID，则可以选择所有行到数组中，实际上是用户输入的参数，设置为null.

What I need to do is to implement an ability to select a single row into an array, as shown in the block above, OR to select all rows into an array, if n_RequiredID, which is actually a user-input parameter, is set to null.

问题是，处理这种情况的最佳实践是什么?

And, the question is, what's the best practice to handle such situation?

我可以考虑将查询的where子句修改为以下内容:

I can think of modifying where clause of my query to something like this:

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

但是我想如果参数不为null将会减慢查询速度，我记得Kyte对此方法说的很不好.

but I suppose that's going to slow down the query if the parameter won't be null, and I remember Kyte said something really bad about this approach.

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

I can also think of implementing the following PL/SQL logic:

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;

但是如果我遇到多个这样的参数，那就太复杂了.

But would become too complex if I encounter more than one parameter of this kind.

您对我有什么建议?

推荐答案

是，使用以下任意一种方式:

Yes, using any of the following:

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

... 不可靠.它们可以工作，但是执行最差的可用选项.

...are not sargable. They will work, but perform the worst of the available options.

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

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

此后的下一个选项是动态SQL .但是，如果您在第一个示例中使用了不可保留的谓词，则对动态SQL进行编码是没有用的.动态SQL允许您在容纳众多路径的同时定制查询.但这也有SQL注入的风险，因此应在参数化查询后执行(最好在程序包中的存储过程/函数内进行.)

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.

这篇关于如何处理SQL查询中的可选参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！