prog:plsql_dynamic_in_with_bind_sql
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:plsql_dynamic_in_with_bind_sql [2016/03/09 15:27] – [2. Lösung - Eine Typ Objekt verwenden] gpipperr | prog:plsql_dynamic_in_with_bind_sql [2016/03/12 16:00] (aktuell) – [1. Lösung - SQL zusammenbauen] gpipperr | ||
---|---|---|---|
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 (' | ||
+ | select * from emp where deptno in (' | ||
+ | select * from emp where deptno in (' | ||
+ | </ | ||
+ | |||
+ | 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 " | ||
+ | |||
+ | Nachteil: Nicht immer 100% SQL Injection Save, Keine echte Bind Variablen Verwendung, umständlicher Code | ||
+ | |||
+ | Vorteil: Performance, | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ===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); | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | Im eigentlichen Code wird der Typ dann mit den notwendigen Werten gefüllt und als inner Select mit dem TABLE Operator **" | ||
+ | |||
+ | 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) := ' | ||
+ | v_names.extend; | ||
+ | v_names (2) := ' | ||
+ | -- mal so probieren | ||
+ | -- Gleich Platz für zwei schaffen | ||
+ | v_names.extend(2); | ||
+ | v_names (3) : | ||
+ | v_names (4) : | ||
+ | | ||
+ | -- Objekt übergeben und abfragen | ||
+ | for rec in all_emps (p_names => v_names) | ||
+ | loop | ||
+ | dbms_output.put_line (' | ||
+ | end loop; | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | **<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" | ||
+ | |||
+ | 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:// | ||
+ | |||
+ | Oracle Table Expression: | ||
+ | |||
+ | * https:// | ||
+ | * http:// | ||
+ | |||
+ | |||
+ | |||
prog/plsql_dynamic_in_with_bind_sql.txt · Zuletzt geändert: 2016/03/12 16:00 von gpipperr