如何处理 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);

但我想如果参数不为空的话，这会减慢查询速度，而且我记得 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);

...不可sargable.它们会起作用，但会执行可用选项中最差的一个.

...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.但是，如果您继承第一个示例中的不可 sargable 谓词，那么编写动态 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屋！