How can I create a quot;dynamicquot; WHERE clause?(我怎样才能创建一个“动态的?WHERE 子句?)

本文介绍了我怎样才能创建一个“动态"的?WHERE 子句?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

首先:谢谢！

我完成了我的另一个项目和一个大惊喜:现在一切正常:-)感谢 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 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！