Als PDF Downloaden!

Tipps & Tricks: September 2006
Bereich:
PL/SQL
Erstellung:
09/2006 HA
Versionsinfo:
9.2, 10.2, 11.1
Letzte Überarbeitung:
06/2009 HA
Verarbeitung eines dynamischen Selects
Dynamische Selects zu schreiben ist dank Ref Cursoren mittlerweile ziemlich einfach geworden. Man muss
jedoch wissen, wie viele Spalten man ausliest, und welchen Datentyp sie haben. In der Regel ist das kein
Problem, wenn man über die Dynamik nur erreichen will, dass je nach übergebenen Parametern auf
unterschiedliche Tabellen zugegriffen wird oder nach unterschiedlichen Spalten sortiert werden soll.
Was aber tut man, wenn man die Spaltenliste des Select-Befehls nicht kennt?
In diesem Fall kann man auf das altbewährte und teilweise bereits als veraltet belächelte DBMS_SQL-Package
zurückgreifen. Es ermöglicht, über die Prozedur DESCRIBE_COLUMNS die Spaltenliste zu ermitteln. Diese
Prozedur liefert die Anzahl der Spalten zurück und eine Beschreibung jeder Spalte in Form einer INDEX
BY-Tabelle, die auf einem Record-Datentyp (DBMS_SQL.DESC_REC) beruht. Ausgelesen werden u. a. Name
und Datentyp der Spalten.
Um auch die Anzahl der Spalten komplett dynamisch halten zu können, wurde im unten gezeigten Beispiel mit
INDEX BY-Tabellen der wichtigsten Datentypen als Variablen gearbeitet.
Beispiel:
CREATE or replace PROCEDURE GETROWS (p_select IN VARCHAR2) AS
v_desc
DBMS_SQL.DESC_TAB;
v_id
INTEGER;
v_count
INTEGER;
v_ret
INTEGER;
Type t_ntype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_col_n
t_ntype;
Type t_vtype IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_col_v
t_vtype;
Type t_dtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;
v_col_d
t_dtype;
v_output VARCHAR2(2000);
BEGIN
v_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_id, p_select, DBMS_SQL.v7);
DBMS_SQL.DESCRIBE_COLUMNS(v_id, v_count, v_desc);
FOR i in 1..v_count LOOP
v_col_n(i) := NULL;
v_col_v(i) := NULL;
v_col_d(i) := NULL;
END LOOP;
FOR i in 1..v_count LOOP
CASE v_desc(i).col_type
WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 2
DBMS_SQL.DEFINE_COLUMN(v_id, i, v_col_v(i), 2000);
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.DEFINE_COLUMN(v_id, i, v_col_n(i));
WHEN DBMS_TYPES.TYPECODE_DATE THEN
DBMS_SQL.DEFINE_COLUMN(v_id, i, v_col_d(i));
END CASE;
END LOOP;
v_ret := DBMS_SQL.EXECUTE(v_id);
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(v_id);
EXIT WHEN v_ret IS NULL OR v_ret <=0;
FOR i IN 1..v_count LOOP
v_output := 'Spalteninhalt Spalte '||v_desc(i).col_name ||': ';
CASE v_desc(i).col_type
WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
DBMS_SQL.COLUMN_VALUE(v_id, i, v_col_v(i));
v_output := v_output||v_col_v(i);
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.COLUMN_VALUE(v_id, i, v_col_n(i));
v_output := v_output||v_col_n(i);
WHEN DBMS_TYPES.TYPECODE_DATE THEN
DBMS_SQL.COLUMN_VALUE(v_id, i, v_col_d(i));
v_output := v_output||v_col_d(i);
END CASE;
DBMS_OUTPUT.PUT_LINE(v_output);
END LOOP; END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_id);
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Unbekannter Datentyp einer Spalte');
DBMS_SQL.CLOSE_CURSOR(v_id);
END;
/
SET SERVEROUTPUT ON SIZE 1000000
EXEC GETROWS('SELECT * FROM EMP')
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 2