动态 PL/SQL 中的绑定变量

Question

我有一个动态 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;

为了减少硬解析开销，我考虑使用绑定变量。然而，向绑定变量提供实际值时管理起来很困难，因为绑定变量和生成的 SELECT 语句的组合太多。我无法使用 http://www.dba 引入的 DBMS_SESSION.set_context() 方法-oracle.com/plsql/t_plsql_dynamic_binds.htm 因为我的帐户无权使用这个包。此外，我希望生成的 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)

所以，我想尝试使用 DBMS_SQL 方法。任何人都可以举一个关于如何使用 DBMS_SQL 调用带有绑定变量的动态 SQL 的示例吗？特别是，我怎样才能获得从 DBMS_SQL.execute() 执行到 SYS_REFCURSOR 的结果，例如：

open refcursor for select .... from

我使用的 oracle 版本是 10g，并且 oracle 10g 似乎没有 DBMS_Sql.To_Refcursor()

Answer 1

在您的 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

然后执行它：

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

它的工作原理是使用 DUAL 为每个参数生成一个假行，然后将该假行内部连接到您的真实查询（没有任何过滤器）仅使用您想要应用的过滤器。这样，您就可以在 params 子查询的 SELECT 列表中拥有固定的绑定变量列表，但可以通过修改 之间的联接条件来控制应用哪些过滤器params 和您的真实查询。

因此，如果您有这样的情况：

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

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;

并且在每种情况下您都会执行

open c for query using filterFirstName, filterLastName;

使用 where rownum > 对于性能非常重要0 与 DUAL ，因为它强制 Oracle“具体化”子查询。这通常会使 DUAL 停止干扰查询的其余部分。无论如何，您应该检查执行计划以确保 Oracle 没有做错任何事情。

Answer 2

在 10g 中，DBMS_SQL 游标无法更改为引用游标。通过 DBMS_SQL 遍历结果集是曲折的，因为除了循环行之外，您还必须循环遍历行中的列。

我希望生成的 SQL 只包含
字段上的条件
用户未留空

这纯粹是出于性能原因吗？如果是这样，我建议您弄清楚实际的执行计划是什么，并对它们使用单​​独的查询。

例如，假设我正在搜索人员，参数是名字、姓氏。性别、出生日期。该表在 (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;