在动态PL/SQL中绑定变量 [英] Binding variables in dynamic PL/SQL

问题描述

我有一个动态的PL/SQL，它将根据用户输入的搜索条件来构造SELECT语句，例如:

I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes:

     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;

为了减少硬解析开销，我考虑使用绑定变量.但是，将实际值提供给绑定变量时，由于存在太多的绑定变量和生成的SELECT语句的组合，因此很难进行管理.我不能使用在 http://www.dba上介绍的DBMS_SESSION.set_context()方法. -oracle.com/plsql/t_plsql_dynamic_binds.htm ，因为我的帐户无权使用此软件包.此外，我希望生成的SQL仅包含用户没有留空的字段上的条件.因此，我无法将动态SQL更改为

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.htm because 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 *  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)

所以，我想尝试使用DBMS_SQL方法.谁能举一个有关如何使用DBMS_SQL调用带有绑定变量的动态SQL的示例吗?特别是，如何将执行结果从DBMS_SQL.execute()传递到SYS_REFCURSOR，如:

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 :

open refcursor for select .... from

我使用的oracle版本是10g，看来oracle 10g没有DBMS_Sql.To_Refcursor()

The oracle version that I use is 10g and it seems that the oracle 10g does not have DBMS_Sql.To_Refcursor()

推荐答案

在您的Oracle版本中，您可以对查询应用一些技巧，以实现此目的.想法是使用以下形式的查询:

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:

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

然后执行:

open c for query using param1, ..., paramN;

通过使用DUAL生成具有每个参数的伪行，然后仅使用要应用的过滤器将该伪行内部连接到真实查询(没有任何过滤器)，即可工作.这样，您在params子查询的SELECT列表中有固定的绑定变量列表，但是可以通过修改params与实际查询之间的联接条件来控制应用哪些过滤器.

It works by using DUAL to 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 SELECT list of the params subquery, but can control which filters are applied by modifying the join condition between params and your real query.

因此，如果您有类似的话，请说:

So, if you have something like, say:

create table people (
    first_name varchar2(20)
    last_name varchar2(20)
);

如果您只想对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;

，如果您想同时过滤first_name和last_name

and this if you want to filter on both first_name and 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;

对于where rownum > 0来说，将where rownum > 0与DUAL结合使用对于性能非常重要，因为它会迫使Oracle具体化"子查询.这通常会使DUAL停止干扰其余查询.无论如何，您应该检查执行计划以确保Oracle没有做错任何事情.

It is important for performance to use the where rownum > 0 with DUAL as it forces Oracle to "materialize" the subquery. This usually makes DUAL stop interfering with the rest of the query. Anyway, you should check the execution plans to be sure Oracle is not doing anything wrong.

这篇关于在动态PL/SQL中绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！