How can I create a "dynamic" WHERE clause?

Question

First: Thanks!

I finished my other project and the big surprise: now everything works as it should :-) Thanks to some helpful thinkers of SO!

So here I go with the next project.

I'd like to get something like this:

SELECT * FROM tablename WHERE field1=content AND field2=content2 ...

As you noticed this can be a very long where-clause. tablename is a static property which does not change. field1, field2 , ... (!) and the contents can change.

So I need an option to build up a SQL statement in PL/SQL within a recursive function. I dont really know what to search for, so I ask here for links or even a word to search for..

Please dont start to argue about wether the recursive function is really needed or what its disadvanteges - this is not in question ;-)

If you could help me to create something like an SQL-String which will later be able to do a successful SELECT this would be very nice!

Iam able to go through the recursive function and make a longer string each time, but I cannot make an SQL statement from it..

Oh, one additional thing: I get the fields and contents by a xmlType (xmldom.domdocument etc) I can get the field and the content for example in a clob from the xmltype

Answer 1

The object is to dynamically assemble a statement from a variable number of filters in the WHERE clause. I'm not sure where recursion fits into all this, so I will just use an array to handle the parameters:

SQL> create type qry_param as object
  2      (col_name varchar2(30)
  3      , col_value varchar(20))
  4  /

Type created.

SQL> create type qry_params as table of qry_param
  2  /

Type created.

SQL> 

This table is passed to a function, which loops around the array. For each entry in the array it appends a line to the WHERE clause in the format <name> = '<value>'. Probably you will require more sophisticated filtering - different operators, explicit data type conversion, bind variables - but this is the general idea.

SQL> create or replace function get_emps
  2      (p_args in qry_params )
  3      return sys_refcursor
  4  as
  5      stmt varchar2(32767);
  6      rc sys_refcursor;
  7  begin
  8      stmt := ' select * from emp';
  9      for i in p_args.first()..p_args.last()
 10      loop
 11          if i = 1 then
 12              stmt := stmt || ' where ';
 13          else
 14              stmt := stmt || ' and ';
 15          end if;
 16          stmt := stmt || p_args(i).col_name
 17                       ||' = '''||p_args(i).col_value||'''';
 18      end loop;
 19      open rc for stmt;
 20      return rc;
 21  end get_emps;
 22  /

Function created.

SQL> 

Finally to execute this query we need to populate a local variable of the array type and return the result to a ref cursor.

SQL> var l_rc refcursor
SQL> declare
  2      l_args qry_params := qry_params
  3                             (qry_param('DEPTNO', '50')
  4                                     , qry_param('HIREDATE', '23-MAR-2010'));
  5  begin
  6      :l_rc := get_emps(l_args);
  7  end;
  8  /

PL/SQL procedure successfully completed.


SQL> print l_rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      8041 FEUERSTEIN PLUMBER         7839 23-MAR-10       4250                    50
      8040 VERREYNNE  PLUMBER         7839 23-MAR-10       4500                    50

SQL>    

edit

In the last paragraph of their question the OP says they are using XML to pass the criteria. This requirement doesn't dramatically change the shape of my original implementation. The loop simply needs to drive off an XPath query instead of an array:

SQL> create or replace function get_emps
  2      (p_args in xmltype )
  3      return sys_refcursor
  4  as
  5      stmt varchar2(32767);
  6      rc sys_refcursor;
  7  begin
  8      stmt := ' select * from emp';
  9      for i in (select * from xmltable (
 10                       '/params/param'
 11                       passing p_args
 12                       columns
 13                           position for ordinality
 14                           , col_name varchar2(30) path '/param/col_name'
 15                           , col_value varchar2(30) path '/param/col_value'
 16                       )
 17               )
 18      loop
 19          if i.position = 1 then
 20            stmt := stmt || ' where ';
 21          else
 22            stmt := stmt || ' and ';
 23          end if;
 24          stmt := stmt || i.col_name
 25                     ||' = '''||i.col_value||'''';
 26      end loop;
 27      open rc for stmt;
 28      return rc;
 29  end get_emps;
 30  /

Function created.

SQL>

As can be seen, this version returns the same results as before...

SQL> var l_rc refcursor
SQL> declare
  2      l_args xmltype := xmltype
  3                              ('<params>
  4                                  <param>
  5                                      <col_name>DEPTNO</col_name>
  6                                      <col_value>50</col_value>
  7                                  </param>
  8                                  <param>
  9                                      <col_name>HIREDATE</col_name>
 10                                      <col_value>23-MAR-2010</col_value>
 11                                  </param>
 12                              </params>');
 13  begin
 14    :l_rc := get_emps(l_args);
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> print l_rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      8041 FEUERSTEIN PLUMBER         7839 23-MAR-10       4250                    50
      8040 VERREYNNE  PLUMBER         7839 23-MAR-10       4500                    50

SQL>

Answer 2

A useful way to use the dynamic SQL as shown in the other answers and still use bind variables (which is a good practice) is to use a WITH clause to bind the variables. This serves two purposes: first, it lets you bind all of your variables every time, whether you're using them or not; second, it allows you to refer to your binds by name, so if you need to reference one more than once, you still only have to bind it once.

An example:

create or replace sample_function (
   v_field1 tablename.field1%type default 1,
   v_field2 tablename.field2%type default null,
   v_field3 tablename.field3%type default 'some value') is
   v_base_query varchar2(2000) := 
      'with binds as (
          select :bind1 as field1,
                 :bind2 as field2,
                 :bind3 as field3
            from dual)
       select t.field4, b.field3 from tablename t, binds b
       where 1=1 ';
   v_where varchar2(2000);
   cur_tablename sys_refcursor;
begin
   if v_field1 is not null then
      v_where := v_where || ' and t.field1 = b.field1';
   end if;
   if v_field2 is not null then
      v_where := v_where || ' and t.field2 = b.field2';
   end if;
   if v_field3 is not null then
      v_where := v_where || ' and t.field3 <= b.field3';
   end if;
   open cur_tablename for v_base_query || v_where
      using v_field1, v_field2, v_field3;
   return cur_tablename;
end sample_function;