oracle 如何处理 SQL 查询中的可选参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3649036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to handle optional parameters in SQL query?
提问by Kirill Leontev
Say I have a sample table:
假设我有一个示例表：
id_pk value
------------
1 a
2 b
3 c
And I have a sample PL/SQL block, which has a query that currently selects a single row into an array:
我有一个示例 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;
What I need to do is to implement an ability to select a single row into an array, as shown in the block above, ORto select all rows into an array, if n_RequiredID, which is actually a user-input parameter, is set to null.
我需要做的是实现将单行选择到数组中的能力，如上面的块所示，或者将所有行选择到数组中，如果n_RequiredID，它实际上是一个用户输入参数，设置为null。
And, the question is, what's the best practice to handle such situation?
而且，问题是，处理这种情况的最佳做法是什么？
I can think of modifying whereclause of my query to something like this:
我可以考虑将where查询的子句修改为这样的：
where m.id_pk = nvl(n_RequiredId, m.id_pk);
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.
但我想如果参数不为空，那会减慢查询速度，而且我记得 Kyte 说这种方法非常糟糕。
I can also think of implementing the following PL/SQL logic:
我还可以考虑实现以下 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;
But would become too complex if I encounter more than one parameter of this kind.
但是如果我遇到不止一个这样的参数，那就太复杂了。
What would you advice me?
你会给我什么建议？
回答by OMG Ponies
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.
......不是 sargable。它们会起作用，但会执行可用选项中最差的一个。
If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.
如果您只有一个参数，IF/ELSE 和单独的定制语句是更好的选择。
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。但是，如果您继承第一个示例中的不可 sargable 谓词，那么编写动态 SQL 将毫无用处。动态 SQL 允许您在适应众多路径的同时定制查询。但它也有 SQL 注入的风险，因此它应该在参数化查询之后执行（最好在包中的存储过程/函数中）。
回答by Allan
OMG_Ponies' and Rob van Wijk's answers are entirely correct, this is just supplemental.
OMG_Ponis' 和 Rob van Wijk 的回答完全正确，这只是补充。
There's a nice trick to make it easy to use bind variables and still use dynamic SQL. If you put all of the binds in a with clause at the beginning, you can always bind the same set of variables, whether or not you're going to use them.
有一个很好的技巧可以使使用绑定变量变得容易并且仍然使用动态 SQL。如果将所有绑定放在开头的 with 子句中，则始终可以绑定同一组变量，无论是否要使用它们。
For instance, say you have three parameters, representing a date range and an ID. If you want to just search on the ID, you could put the query together like this:
例如，假设您有三个参数，分别代表一个日期范围和一个 ID。如果您只想搜索 ID，您可以将查询放在一起，如下所示：
with parameters as (
select :start_date as start_date,
:end_date as end_date,
:search_id as search_id
from dual)
select *
from your_table
inner join parameters
on parameters.search_id = your_table.id;
On the other hand, if you need to search on the ID and date range, it could look like this:
另一方面，如果您需要搜索 ID 和日期范围，它可能如下所示：
with parameters as (
select :start_date as start_date,
:end_date as end_date,
:search_id as search_id
from dual)
select *
from your_table
inner join parameters
on parameters.search_id = your_table.id
and your_table.create_date between (parameters.start_date
and parameters.end_date);
This may seem like an round-about way of handling this, but the end result is that no matter how you complicated your dynamic SQL gets, as long as it only needs those three parameters, the PL/SQL call is always something like:
这似乎是一种迂回的处理方式，但最终的结果是，无论您的动态 SQL 变得多么复杂，只要它只需要这三个参数，PL/SQL 调用总是类似于：
execute immediate v_SQL using v_start_date, v_end_date, v_search_id;
In my experience it's better to make the SQL construction slightly more complicated in order to ensure that there's only one line where it actually gets executed.
根据我的经验，最好使 SQL 构造稍微复杂一些，以确保只有一行实际执行它。
回答by Jon Heller
The NVL approach will usually work fine. The optimizer recognizes this pattern and will build a dynamic plan. The plan uses an index for a single value and a full table scan for a NULL.
NVL 方法通常可以正常工作。优化器识别此模式并将构建动态计划。该计划对单个值使用索引，对 NULL 使用全表扫描。
Sample table and data
示例表和数据
drop table myTable;
create table myTable(
id_pk number,
value varchar2(100),
constraint myTable_pk primary key (id_pk)
);
insert into myTable select level, level from dual connect by level <= 100000;
commit;
Execute with different predicates
使用不同的谓词执行
--Execute predicates that return one row if the ID is set, or all rows if ID is null.
declare
type t_table is table of myTable%rowtype;
n_RequiredId myTable.id_pk%type := 1;
t_Output t_table := t_table();
begin
select /*+ SO_QUERY_1 */ m.id_pk, m.value
bulk collect into t_Output
from myTable m
where m.id_pk = nvl(n_RequiredId, m.id_pk);
select /*+ SO_QUERY_2 */ m.id_pk, m.value
bulk collect into t_Output
from myTable m
where m.id_pk = COALESCE(n_RequiredId, m.id_pk);
select /*+ SO_QUERY_3 */ m.id_pk, m.value
bulk collect into t_Output
from myTable m
where (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);
end;
/
Get execution plans
获取执行计划
select sql_id, child_number
from gv$sql
where lower(sql_text) like '%so_query_%'
and sql_text not like '%QUINE%'
and sql_text not like 'declare%';
select * from table(dbms_xplan.display_cursor(sql_id => '76ucq3bkgt0qa', cursor_child_no => 1, format => 'basic'));
select * from table(dbms_xplan.display_cursor(sql_id => '4vxf8yy5xd6qv', cursor_child_no => 1, format => 'basic'));
select * from table(dbms_xplan.display_cursor(sql_id => '457ypz0jpk3np', cursor_child_no => 1, format => 'basic'));
Bad plans for COALESCE and IS NULL OR
COALESCE 和 IS NULL 的错误计划或
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_2 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE M.ID_PK
= COALESCE(:B1 , M.ID_PK)
Plan hash value: 1229213413
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| MYTABLE |
-------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_3 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE (:B1 IS
NULL OR M.ID_PK = :B1 )
Plan hash value: 1229213413
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| MYTABLE |
-------------------------------------
Good plan for NVL
NVL的好计划
The FILTERoperations allow the optimizer to choose a different plan at run time, depending on the input values.
这些FILTER操作允许优化器在运行时根据输入值选择不同的计划。
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_1 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE M.ID_PK
= NVL(:B1 , M.ID_PK)
Plan hash value: 730481884
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | FILTER | |
| 3 | TABLE ACCESS FULL | MYTABLE |
| 4 | FILTER | |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE |
| 6 | INDEX UNIQUE SCAN | MYTABLE_PK |
----------------------------------------------------
Warnings
警告
FILTERoperations and this NVLtrick are not well documented. I'm not sure what version introduced these features but it works with 11g. I've had problems getting the FILTERto work correctly with some complicated queries, but for simple queries like these it is reliable.
FILTER操作和这个NVL技巧没有很好的记录。我不确定哪个版本引入了这些功能，但它适用于 11g。我在FILTER处理一些复杂的查询时遇到问题，但对于像这样的简单查询，它是可靠的。
