Binding variables in dynamic PL/SQL

问题:

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;

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)

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

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

回答1:

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

then execute it with:

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

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)
);

you can construct the following query if you just want to filter on 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_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;

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.

回答2:

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.

I want the generated SQL only contains the conditions on the fields that the user did not leave empty

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.

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;