Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_dynamic_in_with_bind_sql

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:

SELECT * FROM emp WHERE deptno IN ('10');
SELECT * FROM emp WHERE deptno IN ('10','20');
SELECT * FROM emp WHERE deptno IN ('10','20','30');

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:

CREATE OR REPLACE TYPE in_str_tab IS TABLE OF VARCHAR2(200);
/

Im eigentlichen Code wird der Typ dann mit den notwendigen Werten gefüllt und als inner Select mit dem TABLE Operator „select column_value from table (<type Name>)„ ausgewertet.

Beispiel:

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;
/

Problem

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

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/plsql_dynamic_in_with_bind_sql.txt · Zuletzt geändert: 2016/03/12 16:00 von Gunther Pippèrr