我怎样才能创建一个“动态"的?WHERE 子句? [英] How can I create a "dynamic" WHERE clause?
问题描述
首先:谢谢！
我完成了我的另一个项目和一个大惊喜:现在一切正常:-)感谢 SO 的一些有帮助的思想家！
I finished my other project and the big surprise: now everything works as it should :-) Thanks to some helpful thinkers of SO!
所以我开始下一个项目.
我想得到这样的东西:
SELECT * FROM tablename WHERE field1=content AND field2=content2 ...
正如您所注意到的，这可能是一个很长的 where 子句.表名是一个不会改变的静态属性.field1, field2 , ... (!) 并且内容可以改变.
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.
所以我需要一个选项来在 PL/SQL 中的递归函数中构建 SQL 语句.我真的不知道要搜索什么，所以我在这里要求链接甚至要搜索的词..
请不要开始争论递归函数是否真的需要或者它的缺点是什么 - 这是不是的问题;-)
Please dont start to argue about wether the recursive function is really needed or what its disadvanteges - this is not in question ;-)
如果你能帮我创建一个类似 SQL-String 的东西，这样以后就可以成功执行 SELECT，那就太好了！
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!
我可以通过递归函数每次生成更长的字符串，但我无法从中生成 SQL 语句..
Iam able to go through the recursive function and make a longer string each time, but I cannot make an SQL statement from it..
哦，还有一件事:我通过 xmlType(xmldom.domdocument 等)获取字段和内容我可以从 xmltype 获取字段和内容，例如在 clob 中
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
推荐答案
目标是从 WHERE 子句中的可变数量的过滤器中动态组装一个语句.我不确定递归在哪里适合所有这些，所以我将只使用一个数组来处理参数:
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>
这个表被传递给一个函数，该函数在数组周围循环.对于数组中的每个条目，它以 <name> 格式将一行附加到 WHERE 子句中.= '<值>'.您可能需要更复杂的过滤 - 不同的运算符、显式数据类型转换、绑定变量 - 但这是总体思路.
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>
编辑
在他们问题的最后一段中，OP 说他们正在使用 XML 来通过标准.这个要求不会显着改变我的原始实现的形状.循环只需要驱动一个 XPath 查询而不是一个数组:
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>
这篇关于我怎样才能创建一个“动态"的?WHERE 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
