Comment puis-je créer une “dynamique” clause where?

D'Abord: Merci!

J'ai fini mon autre projet et la grosse surprise: maintenant, tout fonctionne comme il se doit 🙂
Grâce à quelques penseurs de la SORTE!

Donc ici, je aller avec le prochain projet.

Je voudrais obtenir quelque chose comme ceci:

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

Comme vous l'avez remarqué, cela peut être une très longue clause where.
tablename est une propriété statique qui ne change pas.
field1, field2 , ... (!) et le contenu peut changer.

J'ai donc besoin d'une option pour créer une instruction SQL PL/SQL à l'intérieur d'une fonction récursive.
Je n'ai pas vraiment savoir quoi chercher, donc je demande ici pour des liens ou même un mot pour rechercher..

Veuillez ne pas commencer à argumenter sur que la fonction récursive est vraiment nécessaire, ou ce que ses disadvanteges - c'est pas en question 😉

Si vous pouviez m'aider à créer quelque chose comme un SQL-Chaîne, qui sera ensuite en mesure de réussir à SÉLECTIONNER ce serait très gentil!

Iam en mesure de passer par la fonction récursive et de faire une plus longue chaîne à chaque fois, mais je ne peux pas faire une instruction SQL à partir d'elle..

Oh, encore une chose supplémentaire:
Je reçois les champs et les matières par une xmlType (xmldom.domdocument etc) je peux obtenir le champ et le contenu par exemple dans un clob de la xmltype

OriginalL'auteur TheChange | 2010-03-25

  1. 4

    L'objet est d'assembler dynamiquement une déclaration à partir d'un nombre variable de filtres dans la clause where. Je ne suis pas sûr de l'endroit où la récursivité s'inscrit dans tout cela, donc je vais juste utiliser un tableau pour gérer les paramètres: 

    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>

    Ce tableau est transmis à une fonction qui boucle autour de la matrice. Pour chaque entrée dans le tableau, il ajoute une ligne à la clause where dans le format <nom> = '<valeur>'. Probablement, vous aurez besoin de plus sophistiqué, filtrage des différents opérateurs, explicite conversion de types de données, variables de liaison - mais c'est l'idée générale.

    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>

    Enfin pour exécuter cette requête nous avons besoin pour remplir une variable locale de type tableau et de retourner le résultat d'un curseur ref.

    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>

    modifier

    Dans le dernier paragraphe de la question de l'OP dit qu'ils sont l'utilisation de XML pour transmettre les critères. Cette exigence n'est pas radicalement changer la forme de mon original de mise en œuvre. La boucle doit simplement pour chasser une requête XPath au lieu d'un tableau:

    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>

    Comme on peut le voir, cette version renvoie les mêmes résultats qu'avant...

    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>

    OriginalL'auteur APC

  2. 3

    Un moyen utile d'utiliser le SQL dynamique, comme indiqué dans les autres réponses et toujours à lier les variables (ce qui est une bonne pratique est d'utiliser une clause de lier les variables. Cela sert à deux fins: d'abord, il permet de lier l'ensemble de vos variables à chaque fois, que vous les utilisiez ou non; en second lieu, il permet de vous référer à votre lie par nom, donc si vous avez besoin de faire référence à l'un plus d'une fois, vous n'avez qu'à lier qu'une seule fois.

    Un exemple:

    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;

    OriginalL'auteur Allan

  3. 2

    Vous pouvez créer un curseur, puis de créer une chaîne sql dynamique et ensuite utiliser

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

    ou

    vous pouvez utiliser

    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;

    OriginalL'auteur HamoriZ

  4. 2

    SELECT * from emp

    OÙ (1 = 1 OU job = 'VENDEUR')

    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

    découvrez ce merveilleux article.

    OriginalL'auteur Awais

  5. 0

    En PLSQL vous pouvez faire quelque chose comme ceci:

    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;
    +1 si vous montrer comment obtenir le SÉLECTIONNER résultat en retour.

    OriginalL'auteur Rene