Dynamisch hinzufügen der where-Klausel, um einen cursor in oracle

Habe ich plsql-Verfahren, die akzeptiert, dass bestimmte Parameter, z.B. v_name, v_country, v_type.

Wünsche ich mir einen cursor mit einer select-Anweisung wie folgt aus:

select column from table1 t1, table2 t2
where t1.name = v_name
and t1.country = v_country
and t1.id = t2.id
and t2.type = v_type

Wenn bestimmte Parameter leer sind, kann ich nur hinzufügen, die entsprechenden where-Klauseln auf den cursor? Oder gibt es einen besseren Weg, dies zu erreichen?

InformationsquelleAutor Trigger | 2008-12-18

Der beste Weg, um dieses ist mit DBMS_SQL.

Erstellen Sie eine Zeichenfolge für die SQL-Anweisung. Sie weiterhin verwenden Sie bind-Variablen. Es ist ein schmerzhafter Prozess.

Es geht so etwas (habe ich noch nicht zusammengestellt, aber es sollte in der Nähe sein) :-

CREATE OR REPLACE FUNCTION find_country( v_name  t1.country%TYPE,
                                         v_type  t2.type%TYPE)  /* Hmm, column called type? */
DECLARE
  v_SQL         varchar2(2000);
  v_select          INTEGER;   /* "Pointer" to a DBMS_SQL select statement */
  v_execute         INTEGER;

BEGIN
  v_SQL := 'select column from table1 t1, table2 t2 ||
           'where t1.id = t2.id';

  IF v_name IS NOT NULL THEN
    v_SQL := v_SQL || ' AND t1.country = :v_name'
  END IF;

  IF v_type IS NOT NULL THEN
    v_SQL := v_SQL || ' AND t2.type = :v_type';
  END IF;

  /* Setup Cursor */
  v_select := dbms_sql.open_cursor;     
  dbms_sql.parse( v_select, v_SQL, DBMS_SQL.native);

  IF v_name IS NOT NULL THEN
    dbms_sql.bind_variable( v_select, ':v_name', v_name );
  END IF;

  IF v_type IS NOT NULL THEN
    dbms_sql.bind_variable( v_select, ':v_type', v_type );
  END IF;

  DBMS_SQL.DEFINE_COLUMN(v_select, 1, v_column);  /* This is what we have selected */

  /* Return value from EXECUTE is undefined for a SELECT */     
  v_execute := DBMS_SQL.EXECUTE( v_select );

  IF DBMS_SQL.FETCH_ROWS( v_select ) > 0 THEN

    /* A row was found  
    DBMS_SQL.COLUMN_VALUE( v_select, 1, v_column);

    /* Tidy Up */
    DBMS_SQL.CLOSE_CURSOR(v_select);

    RETURN v_ID_address;

  ELSE

     DBMS_SQL.CLOSE_CURSOR(v_select);

     /* No row */
     RETURN NULL;
  END IF;

  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_open(v_select) THEN
        DBMS_SQL.CLOSE_CURSOR(v_select);
      END IF;
      RAISE;
END;

Dieser Ansatz ist so schmerzhaft, im Vergleich zu einfach nur zu schreiben die SQL-inline ist, es sei denn, Sie haben haufenweise Spalten manchmal ist es einfach leichter, zu schreiben ein paar verschiedene Versionen die Verwendung dieser syntax:

FOR r IN (SELECT blah FROM blah WHERE t1 = v_t1) LOOP
   func( r.blah );
END LOOP;

eine Spalte namens Typ? ... frag nicht! 🙁 Vielen Dank für dieses, es sieht aus wie die Art, wie ich es zu tun habe.

InformationsquelleAutor WW.

3

Eine Möglichkeit wäre, Baue deine Abfrage als string, dann verwenden Sie execute immediate

Je nachdem, was meinen Sie mit "Bau deine Abfrage" dies kann eine SQL-injection-bug

InformationsquelleAutor hamishmcn
3

Ist es nicht direkt, was du fragst, aber es kann eine akzeptable Lösung:
```
select column from table1 t1, table2 t2
where
    (v_name is null or t1.name = v_name)
and (v_country is null or t1.country = v_country)
and t1.id = t2.id
and (v_type is null or t2.type = v_type)
```
Für eine begrenzte Anzahl von Parametern, ich denke, das ist die beste Lösung. Dynamische Methoden sind mehr allgemein, sondern auch erfordern mehr Rechenleistung und sind schwieriger zu pflegen.
Dies kann dazu führen, schlechte Pläne erzeugt durch Oracle. Denken Sie daran, es geht um einen plan für diese Aussage. Es geht um Probleme mit Indizes auf t2.Typ oder t1.name.

InformationsquelleAutor
2

Den besten Weg, dies zu tun wäre die Nutzung von Oracle ' s Applikations-Kontext-feature, am besten definiert als die beste Leistung und Sicherheit.

Den schneller Weg wäre, was hamishmcn vorgeschlagen, mit EXECUTE IMMEDIATE. Ich würde wählen, dass über der WW-Vorschlag von DBMS_SQL jedes mal.

Andere Art und Weise, dass Schnellste zu schreiben aber nicht so gut wäre so etwas wie dieses:
```
select column from table1 t1, table2 t2
where t1.name = nvl(v_name, t1.name)
and t1.country = nvl(v_country, t1.country)
and t1.id = t2.id
and t2.type = nvl(v_type, t2.type)
```
InformationsquelleAutor Stew S

Haben Sie nicht verwenden dbms_sql um dieses problem zu lösen
und dennoch kann man die normalen cursor über einen ref-cursor.

Beispiel:

DECLARE
  TYPE cursor_ref IS REF CURSOR;
  c1 cursor_ref;
  r1 table1.column%type;
BEGIN
  l_sql := 'select t1.column from table1 t1, table2 t2 where t1.id = t2.id ';
  if v_name is not null then
    l_sql := l_sql||' and t1.name = '||v_name ;
  end if;
  if v_country is not null then
    l_sql := l_sql||' and t1.country = '||v_country';
  end if;
  if v_type is not null then  
    l_sql := l_sql||' and t2.type = '||v_type';
  end if;
  open c1 for l_sql;
  loop
      fetch c1 into r1;
      exit when c1%notfound;
      -- do something
  end loop;
  close c1;
end;
/

Können Sie diese besser durch Bindung der Variablen mit dem Befehl 'verwenden' wie diese:

open c1 for l_sql using v_name, v_country;

InformationsquelleAutor Edwin

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.