Polymorphic Table Functions
Einführung, Grundlagen
Mit der Version 18c hat Oracle ein spannendes neues Datenbank-Feature eingeführt, die Polymorphic Table Functions, kurz PTF. Das Prinzip der Pipelined Table Functions gibt es schon seit geraumer Zeit. Mit den Pipelined Functions war es möglich, Ergebnismengen dynamisch per PL/SQL zu generieren. Die Funktion wird dann einfach in der FROM-Klausel von SQL-Statements anstatt einer Tabelle verwendet. Allerdings war die Struktur, also die Spalten und deren Typen fest vorgegeben. Die neuen PTFs heben dieses Limit auf. Denn mit ihnen ist es möglich, ein komplett dynamisches Ergebnisset per PL/SQL generiert. Im Prinzip kann damit die Ausführungslogik komplett in PL/SQL abgebildet werden. Das sind also die Phasen DESCRIBE, OPEN, FETCH und CLOSE. Diese vier Funktionen werden in einem PL/SQL-Package definiert. Maßgeblich und für die meisten Fälle ausreichend sind die Funktionen DESCRIBE und FETCH. Das DESCRIBE definiert die Struktur der Ergebnismenge und das FETCH generiert die eigentlichen Daten. Als Basis dient aber immer eine Tabelle, die den Funktionen als Parameter übermittelt wird. Das macht es möglich, ein komplett dynamisches Ergebnis mit ein und demselben SQL zu generieren. Es können Spalten entfernt und hinzugefügt werden aber es kann genauso auch das Ergebnis verändert werden indem Zeilen hinzugefügt oder entfernt werden. Typische Anwendungsfälle können z.B. Pivot- und Unpivot-Operationen sein, es können neue Spalten mit komplexeren Berechnungen hinzugefügt werden oder oder oder. Im Folgenden zeigen wir Ihnen anhand von zwei Beispielen die Implementierung und die Wirkungsweise von PTFs, im ersten Beispiel werden wir aus beliebigen Tabellen ein CSV generieren und im zweiten Beispiel umgekehrt eine Tabelle aus einem CSV erstellen. Genauere technische Details dazu finden sie unter anderem in der Dokumentation des Package DBMS_TF und in diesem Blog-Beitrag. Auch auf Live-SQL gibt es einige Beispiele.
Beispiel 1: CSV aus Tabelle
Im ersten Beispiel werden wir ein Package erstellen, das aus einer beliebigen Tabelle alle Spalten zu einer Komma-separierten Liste zusammenfügt und diese dann als eine Spalte ausgibt. Wir müssen also in der DESCRIBE-Funktion die vorhandenen Spalten ausblenden und eine neue Spalte hinzufügen. In der FETCH-Funktion, die im übrigen FETCH_ROW heißen muss, wird dann mit zwei geschachtelten Schleifen über alle Zeilen und Spalten das Ergebnis generiert. Die Funktionen müssen in einem Package definiert werden. Die Erklärung, warum das so ist, folgt noch.
Zuerst erstellen wir die Package-Definition.
create or replace package pkg_tab2csv as
/* Die DESCRIBE Funktion erzeugt die gewünschte Struktur */
function describe (
p_tab in out dbms_tf.table_t
) return dbms_tf.describe_t;
/* FETCH_ROW generiert die Ergebnismenge */
procedure fetch_rows;
end pkg_tab2csv;
/
Das eigentlich Spannende passiert nun aber in der Implementierung.
create or replace package body pkg_tab2csv as
/* Die DESCRIBE Funktion erzeugt die gewünschte Struktur */
function describe (
p_tab in out dbms_tf.table_t
) return dbms_tf.describe_t
as
v_new_cols dbms_tf.columns_new_t;
begin
-- mark all columns as non-passthrough and read
for i in 1..p_tab.column.count() loop
p_tab.column(i).pass_through := false;
p_tab.column(i).for_read := true;
end loop;
v_new_cols(1) := dbms_tf.column_metadata_t(
name => 'CSV',
type => dbms_tf.type_varchar2
);
return dbms_tf.describe_t(new_columns => v_new_cols);
end;
/* FETCH_ROW generiert die Ergebnismenge */
procedure fetch_rows
as
v_row_set dbms_tf.row_set_t;
v_new_col dbms_tf.tab_varchar2_t;
v_row_count pls_integer;
v_col_count pls_integer;
v_data varchar2(32000);
begin
dbms_tf.get_row_set(v_row_set, v_row_count, v_col_count);
--dbms_tf.trace(v_row_set);
dbms_tf.trace(msg=>'Rows: ' || v_row_count);
dbms_tf.trace(msg=>'Cols: ' || v_col_count);
-- erstelle eine CSV-Liste aus den Spaltenwerten für alle Zeilen
-- Das Rowset ist eine Liste aller Spalten
-- Die Spalten wiederum enthalten Datentyp-spezifische Listen aller Spaltenwerte
for row_num in 1..v_row_count loop
v_data := '';
for col_num in 1..v_col_count loop
if length(v_data) > 0 then
v_data := v_data || ',';
end if;
if v_row_set(col_num).description.type = dbms_tf.type_varchar2 then
v_data := v_data || v_row_set(col_num).tab_varchar2(row_num);
dbms_tf.trace(msg=>'Row: ' || row_num || ' ; Col: ' || col_num || '; VC2; ' || v_row_set(col_num).tab_varchar2(row_num));
elsif v_row_set(col_num).description.type = dbms_tf.type_number then
v_data := v_data || to_char(v_row_set(col_num).tab_number(row_num));
dbms_tf.trace(msg=>'Row: ' || row_num || ' ; Col: ' || col_num || '; NMB; ' || v_row_set(col_num).tab_number(row_num));
elsif v_row_set(col_num).description.type = dbms_tf.type_date then
v_data := v_data || to_char(v_row_set(col_num).tab_date(row_num));
dbms_tf.trace(msg=>'Row: ' || row_num || ' ; Col: ' || col_num || '; DAT; ' || v_row_set(col_num).tab_date(row_num));
else
v_data := v_data || ',<unsupported>';
dbms_tf.trace(msg=>'Row: ' || row_num || ' ; Col: ' || col_num || '; N/A; ' );
end if;
end loop;
-- Der generierte Wert wird der neuen Spalte hinzugefügt
v_new_col(row_num) := v_data;
dbms_tf.trace(msg=>'Val: ' || row_num || ' = ' || v_data);
end loop;
-- Die komplett generierte neue Spalte wird dem Ergebnis hinzugefügt
dbms_tf.put_col(1, v_new_col);
end;
end pkg_tab2csv;
/
Man sieht auch in der Implementierung, wie ein Debugging funktionieren kann. Mittels DBMS_TF.TRACE können Ausgaben generiert werden, die wie DBMS_OUTPUT funktionieren. Soweit die Theorie. Aber wie wird diese PTF nun aufgerufen und verwendet? Dafür benötigen wir eine weitere Funktion, die unser neues Package als PTF verwendet.
create or replace function columns_to_csv(tab in table)
return table pipelined
row polymorphic
using pkg_tab2csv;
/
Es ist also eine Art spezielle Pipelined Function. Die Angabe "row polymorphic" bedeutet, wir benutzen Row-Semantik und interessieren uns nur für einzelne Zeilen. Mit der Table-Semantik ist es dann z.B. noch möglich, zu sortieren. das "using <Package Name>" verweist auf unsere Implementierung.
Aufgerufen wird das Ganze nun wie folgt, hier am Beispiel des HR-Schemas:
SQL> select * from columns_to_csv(employees) where rownum <11;
CSV
--------------------------------------------------------------------------------
100,Steven,King,SKING,515.123.4567,17.06.03,AD_PRES,24000,,,90
101,Neena,Kochhar,NKOCHHAR,515.123.4568,21.09.05,AD_VP,17000,,100,90
102,Lex,De Haan,LDEHAAN,515.123.4569,13.01.01,AD_VP,17000,,100,90
103,Alexander,Hunold,AHUNOLD,590.423.4567,03.01.06,IT_PROG,9000,,102,60
104,Bruce,Ernst,BERNST,590.423.4568,21.05.07,IT_PROG,6000,,103,60
105,David,Austin,DAUSTIN,590.423.4569,25.06.05,IT_PROG,4800,,103,60
106,Valli,Pataballa,VPATABAL,590.423.4560,05.02.06,IT_PROG,4800,,103,60
107,Diana,Lorentz,DLORENTZ,590.423.5567,07.02.07,IT_PROG,4200,,103,60
108,Nancy,Greenberg,NGREENBE,515.124.4569,17.08.02,FI_MGR,12008,,101,100
109,Daniel,Faviet,DFAVIET,515.124.4169,16.08.02,FI_ACCOUNT,9000,,108,100
10 Zeilen ausgewählt.
SQL> select * from columns_to_csv(departments) where rownum <11;
CSV
--------------------------------------------------------------------------------
10,Administration,200,1700
20,Marketing,201,1800
30,Purchasing,114,1700
40,Human Resources,203,2400
50,Shipping,121,1500
60,IT,103,1400
70,Public Relations,204,2700
80,Sales,145,2500
90,Executive,100,1700
100,Finance,108,1700
10 Zeilen ausgewählt.
Beispiel 2: Tabelle aus CSV
Beispiel 2 wird nun den umgekehrten Weg beschreiten. Wir erstellen ein Package, das aus einer Spalte mit CSV-Inhalten eine Tabelle mit mehreren Spalten erstellt. Dazu wird der DESCRIBE-Funktion diesmal eine Spaltenliste in einem zusätzlichen Parameter ebenfalls als kommaseparierter Text übergeben um die Spalten entsprechend benennen zu können.
create or replace package pkg_csv2tab as
/* Die DESCRIBE Funktion erzeugt die gewünschte Struktur */
function describe (
p_tab in out dbms_tf.table_t,
p_colnames varchar2
) return dbms_tf.describe_t;
/* FETCH_ROW generiert die Ergebnismenge */
procedure fetch_rows (p_colnames varchar2);
end pkg_csv2tab;
/
Im Package Body passiert dann wieder die komplette Logik. Die Aufsplittung wird mit einem regulären Ausdruck erledigt, denn damit können die Texte an den Kommas in einzelne Token unterteilt und dann quasi per Index angesprochen werden.
create or replace package body pkg_csv2tab as
/* Die DESCRIBE Funktion erzeugt die gewünschte Struktur */
function describe(
p_tab in out dbms_tf.table_t,
p_colnames varchar2
)
return dbms_tf.describe_t as
v_new_cols dbms_tf.columns_new_t;
begin
/* Die Quellspalte wird ausgeblendet, bleibt aber lesbar */
p_tab.column(1).pass_through := FALSE;
p_tab.column(1).for_read := TRUE;
v_new_cols(1) := p_tab.column(1).description;
/* Spaltennamen aus dem Parameter extrahieren und neue Spalten anlegen */
for cols in (
select rownum, regexp_substr(p_colnames,'[^,]+', 1, level) colname from dual
connect by regexp_substr(p_colnames, '[^,]+', 1, level) is not null
) loop
--dbms_tf.trace(msg => 'Col#: ' || cols.rownum || ', Col: ' || cols.colname);
v_new_cols(1+cols.rownum) := dbms_tf.column_metadata_t(
name => cols.colname,
type => dbms_tf.type_varchar2
);
end loop;
return dbms_tf.describe_t( new_columns => v_new_cols );
end;
/* FETCH_ROW generiert die Ergebnismenge */
procedure fetch_rows (p_colnames varchar2)
as
v_rowset dbms_tf.row_set_t;
v_row_count pls_integer;
begin
/* die Eingabe-Datenbmenge einlesen */
dbms_tf.get_row_set(v_rowset, row_count => v_row_count);
--dbms_tf.trace(env => dbms_tf.get_env);
/* Schleife über Eingabe-Datensätze... */
for i in 1 .. v_row_count loop
for vals in (
select rownum, regexp_substr(v_rowset(1).tab_varchar2(i),'[^,]+', 1, level) colval from dual
connect by regexp_substr(v_rowset(1).tab_varchar2(i), '[^,]+', 1, level) is not null
) loop
-- Ergebnisse ab Spalte 2 anhängen, Spalte 1 ist das Original
v_rowset(vals.rownum+1).tab_varchar2(i) := vals.colval;
end loop;
end loop;
/* Neue Spalten und deren Werte ausgeben */
dbms_tf.put_row_set(v_rowset);
end;
end pkg_csv2tab;
/
Um das Package nun wieder als PTF nutzen zu können, benötigen wir wieder eine separate Funktion zur Kapselung. Diese Funktion hat entsprechend ebenfalls den zusätzlichen Parameter für die Spaltenliste, die dann einfach durchgereicht wird.
create or replace function csv_to_columns(
tab table, col_names varchar2
)
return table pipelined
row polymorphic
using pkg_csv2tab;
/
Die Beispielabfrage zum Test zeigt gleich einen weiteren Trick. Denn die Eingabe für eine PTF muss zwingend eine Tabelle sein, (Sub-)Selects funktionieren nicht. Zum Glück kann man mit einer WITH-Klausel auch eine virtuelle Tabelle erzeugen, das genügt der PTF dann.
SQL> with csv as (
2 select 'Erkan,Alles' from dual union all
3 select 'Frank,Reich' from dual union all
4 select 'Ismir,Schnuppe' from dual union all
5 select 'Marion,Nette' from dual union all
6 select 'Theo,Rist' from dual union all
7 select 'Marta,Pfahl' from dual union all
8 select 'Ann,Hänger' from dual
9 )
10 select * from csv_to_columns( csv, 'Vorname,Nachname' )
11 ;
'ERKAN,ALLES' VORNAME NACHNAME
-------------- -------------------- --------------------
Erkan,Alles Erkan Alles
Frank,Reich Frank Reich
Ismir,Schnuppe Ismir Schnuppe
Marion,Nette Marion Nette
Theo,Rist Theo Rist
Marta,Pfahl Marta Pfahl
Ann,Hänger Ann Hänger
7 Zeilen ausgewählt.
Zusammenfassung
Man sieht schon an diesen recht einfachen Beispielen, wie mächtig dieses neue Feature sein kann. Der persönlichen Kreativität sind demnach kaum Grenzen gesetzt. Natürlich kann dieser Beitrag nur einen sehr groben Überblick über die neue Technologie und deren Möglichkeiten geben. Die technischen Grundlagen und komplexere Beispiele würden den Rahmen sprengen. Für Fragen stehen wir natürlich gerne zur Verfügung.
Kommentare
Keine Kommentare