Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_dynamic_in_with_bind_sql

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:plsql_dynamic_in_with_bind_sql [2016/03/09 15:27]
gpipperr [2. Lösung - Eine Typ Objekt verwenden]
prog:plsql_dynamic_in_with_bind_sql [2016/03/12 16:00] (aktuell)
gpipperr [1. Lösung - SQL zusammenbauen]
Zeile 1: Zeile 1:
 +=====In PL/SQL in einer SQL IN Anweisung mit einer Bind Variable verwenden=====
 +
 +**getestet in 10g** (auch in 2016 immer noch hoch aktuell ..... .-( )
 +
 +
 +**Problemstellung:**
 +
 +In einer PL/SQL Routine soll in einem SQL Statement dynamisch eine Werte Liste als Bind Variable dem SQL **IN** Operator übergeben werden.
 +
 +Beispiel Abfragen von diesen Typ:
 +<code sql>
 +select * from emp where deptno in ('10');
 +select * from emp where deptno in ('10','20');
 +select * from emp where deptno in ('10','20','30');
 +</code>
 +
 +Wie kann nun so eine Werte Liste im **IN** Operator für die //deptno// je nach Bedarf in PL/SQL als Bind Variable dynamisch übergeben werden?
 +
 +----
 +
 +===1. Lösung - SQL zusammenbauen===
 +
 +Verzicht auf Bind Variable, dynamisches SQL mit String Verknüpfung.
 +
 +D.h. zuerst wird der SQL String aufgebaut und dann je nach Bedarf mit "execute immediate" oder als Ref Cursor ausgeführt.
 +
 +Nachteil: Nicht immer 100% SQL Injection Save, Keine echte Bind Variablen Verwendung, umständlicher Code
 +
 +Vorteil: Performance, das Innerselect scheint recht viel CPU zu verbrauchen, unter 10.2.0.5 auf HP UX war das Ergebniss 150 fach langsamer als die harte Angabe der Werte im in Operator (und das bei zwei Werten!).
 +
 +----
 +
 +===2. Lösung - Eine Typ Objekt verwenden ===
 +
 +Dynamischer ist die Verwendung eines Typ Objektes, der dann wiederum in SQL referenziert wird. 
 +
 +Allerdings muss der Typ, um unter SQL verwendbar zu sein, als eigenes Objekt in der DB definiert werden!
 +
 +Über PL/SQL können wir aber das Objekt füllen und übergeben.
 +
 +
 +**Ablauf**
 +
 +Anlegen des Types als eigenes Object im aktuellen Schema:
 +<code sql>
 +CREATE OR REPLACE TYPE in_str_tab IS TABLE OF VARCHAR2(200);
 +/
 +</code>
 +
 +
 +Im eigentlichen Code wird der Typ dann mit den notwendigen Werten gefüllt und als inner Select mit dem TABLE Operator **"select [[https://docs.oracle.com/database/121/SQLRF/pseudocolumns004.htm#SQLRF50950|column_value]] from [[http://docs.oracle.com/database/121/ADOBJ/adobjbas.htm#ADOBJ7163|table]] (<type Name>)"** ausgewertet.
 +
 +Beispiel:
 +
 +<code sql>
 +
 +declare
 + -- Cursor anlegen und gefülltes Object für den inner select in der IN Abfrage übergeben
 +  cursor all_emps (p_names in_str_tab)
 +  is
 +  select *
 +  from EMPLOYEES
 +  where LAST_NAME in (select column_value from table (p_names));
 +
 +  -- locale Variable um das Typ Object verwenden zu können
 +  v_names in_str_tab;
 +begin
 +  -- Objekt initaliseren - je nach Bedarf dann über ein Schleife .-)
 +  v_names := in_str_tab ();
 +  -- Platz schaffen
 +  v_names.extend;
 +  -- Element einfügen
 +  v_names (1) := 'Atkinson';
 +  v_names.extend;
 +  v_names (2) := 'Bernstein';
 +  -- mal so probieren 
 +  -- Gleich Platz für zwei schaffen
 +  v_names.extend(2);
 +  v_names (3) :='Tucker';
 +  v_names (4) :='Pataballa';
 +      
 +  -- Objekt übergeben und abfragen
 +  for rec in all_emps (p_names => v_names)
 +  loop
 + dbms_output.put_line ('--Info - read ::' || rec.LAST_NAME);
 +  end loop;
 +end;
 +/
 +</code>
 +
 +**<fc #800000>Problem</fc>**
 +
 +Auf einer 10.2.0.5 Umgebung mit älterer Hardware führt aber die Verwendung des Typen im Ausführungsplan zu einem **" COLLECTION ITERATOR PICKLER FETCH"** bei einer echten komplexen Abfragen zu sehr hohen CPU Kosten!
 +
 +Wird das gleiche komplexe Statment mit festen IN Operator Werten aufgerufen sanken die Kosten dramatisch (über 150 mal bessser)!
 +
 +Daher ist das wohl mit Vorsicht einzusetzen und genau zu prüfen ob das auf einer aktuellen Umgebung (dann hoffentlich 12c) besser skaliert!
 +
 +
 +
 +----
 +
 +==== Quellen ====
 +
 +Web:
 +
 +  * http://stackoverflow.com/questions/8967652/how-do-you-specify-in-clause-in-a-dynamic-query-using-a-variable
 +
 +Oracle Table Expression:
 +
 +  * https://docs.oracle.com/database/121/ADOBJ/adobjcol.htm#BHBFDFDJ
 +  * http://docs.oracle.com/database/121/ADOBJ/adobjbas.htm#ADOBJ7163
 +
 +
 +
  
"Autor: Gunther Pipperr"
prog/plsql_dynamic_in_with_bind_sql.txt · Zuletzt geändert: 2016/03/12 16:00 von gpipperr