oracle 动态 PL/SQL 中的绑定变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3606352/
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
Binding variables in dynamic PL/SQL
提问by Ken Chan
I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes:
我有一个动态 PL/SQL，它将根据用户输入的搜索条件构建 SELECT 语句，例如：
l_sql := 'SELECT * INTO FROM TABLEA WHERE 1=1 ';
IF in_param1 IS NOT NULL THEN
l_sql := l_sql || 'AND column1 = in_param1 ';
END IF;
IF in_param2 IS NOT NULL THEN
l_sql := l_sql || 'AND column2 = in_param2 ';
END IF;
...................................
IF in_paramXX IS NOT NULL THEN
l_sql := l_sql || 'AND columnXX = in_paramXX ';
END IF;
To reduce the hard parse overhead , I consider to use the binding variables. However , it is difficult to manage when supplying the actual values to the binding variables as there are so many binding variables and combination of the generated SELECT statement . I cannot use the method of DBMS_SESSION.set_context() introduced at http://www.dba-oracle.com/plsql/t_plsql_dynamic_binds.htmbecause my account has no right to use this package. Besides , I want the generated SQL only contains the conditions on the fields that the user did not leave empty. So I cannot change the dynamic SQL to something likes
为了减少硬解析开销，我考虑使用绑定变量。但是，在为绑定变量提供实际值时很难管理，因为绑定变量和生成的 SELECT 语句的组合太多。我无法使用http://www.dba-oracle.com/plsql/t_plsql_dynamic_binds.htm 中介绍的 DBMS_SESSION.set_context() 方法，因为我的帐户无权使用此包。此外，我希望生成的 SQL 只包含用户没有留空的字段的条件。所以我不能将动态 SQL 更改为类似的东西
SELECT * INTO FROM TABLEA WHERE 1=1
and ( in_param1 is NULL or column1 = in_param1)
and ( in_param2 is NULL or column2 = in_param2)
...............................................
and ( in_paramXX is NULL or columnXX = in_paramXX)
So , I want to try to use the DBMS_SQL method .Can anyone give an example about how to use DBMS_SQL to call dynamic SQL with binding variables? Especially , how can I get the result executed from DBMS_SQL.execute() to the SYS_REFCURSOR , something like :
所以，我想尝试使用DBMS_SQL方法。谁能举例说明如何使用DBMS_SQL调用带有绑定变量的动态SQL？特别是，我怎样才能得到从 DBMS_SQL.execute() 到 SYS_REFCURSOR 执行的结果，例如：
open refcursor for select .... from
The oracle version that I use is 10g and it seems that the oracle 10g does not have DBMS_Sql.To_Refcursor()
我用的oracle版本是10g，好像oracle 10g没有DBMS_Sql.To_Refcursor()
回答by gpeche
In your Oracle version you can apply some tricks to your query in order to do this. The idea is to use a query in the following form:
在您的 Oracle 版本中，您可以将一些技巧应用到您的查询中以执行此操作。这个想法是使用以下形式的查询：
select *
from
(select
:possibleParam1 as param1
-- do the same for every possible param in your query
:possibleParamN as paramN
from dual
where rownum > 0) params
inner join
-- join your tables here
on
-- concatenate your filters here
where
-- fixed conditions
then execute it with:
然后执行它：
open c for query using param1, ..., paramN;
It works by using DUALto generate a fake row with every single param, then inner joining this fake row to your real query (without any filters) using only the filters you want to apply. This way, you have a fixed list of bind variables in the SELECTlist of the paramssubquery, but can control which filters are applied by modifying the join condition between paramsand your real query.
它的工作原理是使用DUAL每个参数生成一个假行，然后仅使用您要应用的过滤器将这个假行内部连接到您的真实查询（没有任何过滤器）。这样，您SELECT在params子查询列表中有一个固定的绑定变量列表，但可以通过修改params与实际查询之间的连接条件来控制应用哪些过滤器。
So, if you have something like, say:
所以，如果你有类似的话，说：
create table people (
first_name varchar2(20)
last_name varchar2(20)
);
you can construct the following query if you just want to filter on first name
如果您只想过滤，则可以构建以下查询 first name
select *
from
(select
:first_name as first_name,
:last_name as last_name
from dual
where rownum > 0) params
inner join
people
on
people.first_name = params.first_name;
and this if you want to filter on both first_nameand last_name
如果你想同时过滤first_name和last_name
select *
from
(select
:first_name as first_name,
:last_name as last_name
from dual
where rownum > 0) params
inner join
people
on
people.first_name = params.first_name and
people.last_name = params.last_name;
and in every case you would execute with
在每种情况下你都会执行
open c for query using filterFirstName, filterLastName;
It is important for performance to use the where rownum > 0with DUALas it forces Oracle to "materialize" the subquery. This usually makes DUALstop interfering with the rest of the query. Anyway, you should check the execution plans to be sure Oracle is not doing anything wrong.
使用where rownum > 0with对性能很重要，DUAL因为它会强制 Oracle“具体化”子查询。这通常会DUAL停止干扰查询的其余部分。无论如何，您应该检查执行计划以确保 Oracle 没有做错任何事情。
回答by Gary Myers
In 10g a DBMS_SQL cursor can't be changed into a Ref Cursor. Going through a result set through DBMS_SQL is tortuous since, as well as looping through the rows, you also have to loop through the columns in a row.
在 10g 中，不能将 DBMS_SQL 游标更改为引用游标。通过 DBMS_SQL 遍历结果集是曲折的，因为除了遍历行之外，还必须遍历行中的列。
I want the generated SQL only contains the conditions on the fields that the user did not leave empty
我希望生成的 SQL 只包含用户没有留空的字段上的条件
Is that purely for performance reasons ? If so, I suggest you work out what the practical execution plans are and use separate queries for them.
这纯粹是出于性能原因吗？如果是这样，我建议您弄清楚实际的执行计划是什么，并对它们使用单独的查询。
For example, say I'm searching on people and the parameters are first_name, last_name. gender, date_of_birth. The table has indexes on (last_name,first_name) and (date_of_birth), so I only want to allow a query if it specifies either last_name or date_of_birth.
例如，假设我正在搜索人员并且参数是 first_name、last_name。性别，出生日期。该表在 (last_name,first_name) 和 (date_of_birth) 上有索引，所以我只想允许查询，如果它指定了 last_name 或 date_of_birth。
IF :p_firstname IS NOT NULL and :p_lastname IS NOT NULL THEN
OPEN cur FOR
'SELECT * FROM PEOPLE WHERE last_name=:a AND first_name=:b AND
(date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_lastname IS NOT NULL THEN
OPEN cur FOR
'SELECT * FROM PEOPLE WHERE last_name=:a AND
(date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_dateofbirth IS NOT NULL THEN
OPEN cur FOR
'SELECT * FROM PEOPLE WHERE date_of_birth=:a AND
(first_name=:b OR :b IS NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSE
RAISE_APPLICATION_ERROR(-20001,'Last Name or Date of Birth MUST be supplied);
END IF;
