Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====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