How can I create a “dynamic” WHERE clause?

Question

First: Thanks!

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

So here I go with the next project.

I would 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 that does not change. field1 , field2 , ... (!), and the contents may change.

Therefore, I need the option to create an SQL query in PL / SQL in a recursive function. I really do not know what to look for, so I ask here for links or even a word to search ..

Please do not start arguing about whether a recursive function is needed or what its disadvantages are not , -)

If you could help me create something like SQL-String, which later can make a successful SELECT, it will be very nice!

I can go through a recursive function and create a longer string every time, but I cannot make an SQL statement from it.

Oh, one more thing: I get the fields and contents using xmlType (xmldom.domdocument, etc.). I can get the field and content e.g. in clob from xmltype

Answer 1

The object is to dynamically assemble an instruction from a variable number of filters in a WHERE clause. I'm not sure where recursion fits into all of this, so I 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 that moves around the array. For each record in the array, it adds a line to the WHERE clause in the format <name> = '<value>'. You will probably need more complex filtering — different operators, explicit data type conversions, variable bindings — but this is a 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 array type and return the result to the 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> 

change

In the last paragraph of their question, OP says that they use XML to pass criteria. This requirement does not change the form of my initial implementation. The loop should just disable the XPath query instead of the 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 you can see, 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 dynamic SQL, as shown in other answers, and still use bind variables (which is good practice) is to use the WITH clause to bind variables. This serves two purposes: firstly, it allows you to bind all your variables every time, regardless of whether you use them or not; secondly, it allows you to reference your bindings by name, so if you need to reference only once, you only need to bind it once.

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; 

Answer 3

You can create a cursor and then create a sql row dynamically and then use

 mycur is ref cursor open mycur for 'select ... from ... where '||dynamic_string fetch mycur ... 

or

you can use

 execute immediate 'select id from ... where '||dynamic_string bulk collect into mylist where mytype is for example> Type Mytype is table of number mylist Mytype; 

Answer 4

SELECT * FROM emp

WHERE (1 = 1 OR job = 'SALESMAN')

 AND (1 = 1 OR TO_CHAR(hiredate,'YYYYMMDD') = '19810220') AND (1 = 0 OR TO_CHAR(hiredate,'YYYYMMDD') > '19820101') AND (1 = 1 OR sal = 1600); 

http://www.akadia.com/services/dyn_modify_where_clause.html
Check out this great article.

Answer 5

In PLSQL, you can do something like this:

 declare l_statement varchar2(32767); begin l_statement := 'SELECT * FROM tablename WHERE field1=:a AND field2=:b'; -- you now have you query. Put in the values that you like. execute immediate l_statement using 'value1','value2'; end;