如何创建“动态"广告素材? 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 ...
您已经注意到，这可能是一个很长的子句.
tablename是不会更改的静态属性.
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语句的选项. 我真的不知道要搜索什么，所以我在这里要求链接或什至要搜索一个词.
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 ;-)
如果您可以帮助我创建类似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>
最后要执行此查询，我们需要填充数组类型的局部变量并将结果返回给ref游标.
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屋！
