|
18c: Polymorphic Table Function
Table Functions gibt es schon seit langer Zeit in der Oracle Datenbank. Sie werden meist dazu verwendet eine Ansammlung
von Daten beispielsweise Nested Tables oder Collections wie eine ganz normale Datenbank Tabelle in der FROM Klausel abzufragen.
Interessant ist dabei auch die Möglichkeit eine Table Function "pipelined" zu verwenden - ähnlich wie bei einer Unix Pipe.
Seit 12c ist es übrigens nicht mehr notwendig, dass Schlüsselwort TABLE nach der FROM Klausel mitanzugeben.
Statt
SELECT * FROM TABLE(table_function_name(parameter_list))
reicht nun folgendes aus
SELECT * FROM table_function_name(parameter_list)
In Oracle Database 18c gibt es nun einen neuen Table Function Typ - die Polymorphic Table Function (kurz PFT). Der Begriff "polymorph" (griech.: poly=viele und morph=Form)
wird auch häufig in der Objektorientierten Programmierung benutzt und bezeichnet die Eigenschaft von Objekten mehrere Formen anzunehmen.
Polymorphic Table Functions in Oracle Database 18c sollen generische Anwendungen mit beliebigen Eingabetabellen unterstützen, die zur Definitionszeit noch nicht bekannt sein müssen.
PFTs sind übrigens auch Teil von
ANSI 2016.
Hier lautet die Definition:
"Polymorphic Table Functions (PTF) are user-defined functions that can be invoked in the FROM clause.
They are capable of processing tables whose row type is not declared at definition time and producing a
result table whose row type may or may not be declared at definition time. Polymorphic Table Functions
allow application developers to leverage the long-defined dynamic SQL capabilities to create powerful
and complex custom functions."
Um Polymorphic Table Functions (PTF) in Oracle zu verwenden, wird eine Infrastruktur aus vordefinierten Unterprogrammen in PL/SQL zur Verfügung gestellt. Diese Infrastruktur wird vom Entwickler verwendet, um die Funktionalität festzulegen. Die Anwendung selbst bzw.
der Anwendungsprogrammierer muss bei der Ausführung nichts von der darunterliegenden Programmierung wissen.
Da die Funktionsumfang recht groß ist, beschränken wir den Tipp auf das Beispiel 18c polymorphic table function TAB2KEYVAL aus dem Live SQL Tutorial, das jeder auch selbst nachvollziehen kann.
Die Aufgabe und das Ergebnis
Nehmen wir an, dass die Aufgabe darin besteht, aus Spalten (Inhalt und Name der Spalten) einer beliebigen Tabelle eine Liste von Key (Spaltenname)/Value (Spalteninhalt) Tupelpaaren auszugeben. Die Spaltennamen in der Ausgabe lauten dabei KEY_NAME für den Spaltennamen und KEY_VAL_CHAR, KEY_VAL_NUMBER und KEY_NAME_DATE für die Spalteninhalte. Beim Aufruf wird zusätzlich zum Tabellennamen eine Spalte zur Identifikation der Tupelliste mitgegeben. Das Ergebnis sieht dann beispielsweise folgendermassen aus:
SQL> SELECT *
FROM tab2keyval(scott.emp, COLUMNS(empno))
EMPNO KEY_NAME KEY_VAL_CHAR KEY_VAL_NUMBER KEY_NAME_DATE
---------- ---------- -------------------- -------------- --------------------
7369 ENAME SMITH
7369 JOB CLERK
7369 MGR 7902
7369 HIREDATE 17-DEC-80
7369 SAL 800
7369 COMM
7369 DEPTNO 20
7499 ENAME ALLEN
7499 JOB SALESMAN
7499 MGR 7698
7499 HIREDATE 20-FEB-81
7499 SAL 1600
...
Im ersten Aufruf wurde die Tabelle EMP und die Spalte EMPNO verwendet; die PTF heißt tab2keyval. In der Spalte KEY_NAME steht der jeweilige Spaltenname (z.B. ENAME, JOB) und
in der Spalte KEY_VAL_CHAR usw. der zugehörige Spalteninhalt (z.B. SMITH).
Da die EMP Tabelle aus 7 Spalten (ohne EMPNO) besteht, werden für jede EMPNO genau 7 Zeilen generiert.
Die Funktion kann natürlich auf beliebige Tabellen und Spalten angewendet werden, wie das folgende Beispiel zeigt.
Im Ausführungsplan wird dann die Operation POLYMORPHIC TABLE FUNCTION angezeigt.
SQL> SELECT * FROM tab2keyval(scott.dept, COLUMNS(Deptno));
DEPTNO KEY_NAME KEY_VAL_CHAR KEY_VAL_NUMBER KEY_NAME_
---------- ---------- -------------------- -------------- ---------
10 DNAME ACCOUNTING
10 LOC NEW YORK
20 DNAME RESEARCH
20 LOC DALLAS
30 DNAME SALES
30 LOC CHICAGO
40 DNAME OPERATIONS
40 LOC BOSTON
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 16212 | 2 (0)| 00:00:01 |
| 1 | POLYMORPHIC TABLE FUNCTION | TAB2KEYVAL | 4 | 120 | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Die Funktion lässt sich dann beispielsweise mit Window Funktionen kombinieren, erlaubt JOINS oder auch die Verwendung der WITH Klausel.
SQL> SELECT deptno, listagg(key_name||':'||key_val_char,',') within group (order by deptno) a
FROM tab2keyval(TAB=>scott.dept, ID_COLS=>COLUMNS(Deptno))
WHERE deptno=30 GROUP BY deptno;
DEPTNO A
---------- ------------------------------
30 DNAME:SALES,LOC:CHICAGO
SQL> WITH e AS (select * from emp natural join dept)
select * from tab2keyval(e, COLUMNS(empno)) where empno=7934;
EMPNO KEY_NAME KEY_VAL_CHAR KEY_VAL_NUMBER KEY_NAME_
---------- ---------- -------------------- -------------- ---------
7934 DEPTNO 10
7934 ENAME MILLER
7934 JOB CLERK
7934 MGR 7782
7934 HIREDATE 23-JAN-82
7934 SAL 1300
7934 COMM
7934 DNAME ACCOUNTING
7934 LOC NEW YORK
9 rows selected.
Die Definition der PFT
Zum besseren Verständnis beginnen wir mit der Definition der PFT und setzen die Implementierung des zugehörigen Package voraus. Die Funktionsdefinition einer PFT besteht aus folgenden Schlüsselbestandteilen:
- Name der PFT
- ein Argument vom Typ TABLE und weitere Argumente
- Art der PFT - ROW oder TABLE Semantik
- Name des Implementierungs Package
PFTs können auf einer ROW oder TABLE Semantik basieren, je nachdem wie die neuen Zeilen berechnet werden. Die ROW Semantik verwendet
einzelne Zeilen, die TABLE Semantik bezieht sich immer auf mehrere Zeilen oder Berechnungen.
PFTs besitzen allerdings keine Funktionsdefinition (FUNCTION BODY), da die Definition im zugehörigen Implementierungspaket gekapselt ist.
Im Beispiel heißt die Funktion tab2keyval und das Implementierungspackage tab2keyval_pkg.
Unsere PFT verwendet ROW Semantik und gibt das Ergebnis "pipelined" zurück.
Als Eingabe ist die Angabe der Tabelle (mit Operator TABLE) und einer oder mehrerer Spalte(n) (mit Operator COLUMNS) erforderlich.
CREATE OR REPLACE FUNCTION tab2keyval(tab TABLE, id_cols COLUMNS)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING tab2keyval_pkg;
Mit dem DESCRIBE Kommando läßt sich die Funktion dann einfach beschreiben und somit vom Anwendungsentwickler wie im ersten Abschnitt schon gezeigt verwenden.
SQL> desc tab2keyval FUNCTION tab2keyval RETURNS TABLE_COLUMNS_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TAB TABLE_COLUMNS_T IN ID_COLS COLUMNS_T IN
Die Implementierung
Die Hauptarbeit steckt natürlich in der Implementierung der Funktionalität selbst. Das neue Package DBMS_TF liefert dazu die
Schnittstelle, die für die Implementierung von PTFs zum Lesen und Schreiben von Informationen in die Datenbank benötigt wird. Folgende Tabelle zeigt
einen Auschnitt aus den verfügbaren Unterprogrammen und Datenstrukturen. Gute Beispiele finden sich neben den Live SQL Tutorials auch im
PL/SQL Packages and Types Reference Handbuch .
Folgende Unterprogramme (bis zu 4) von DBMS_TF können vom Programmierer verwendet werden:
- DESCRIBE Funktion (obligatorisch)
- OPEN Prozedur (optional)
- FETCH_ROWS Prozedur (optionaL)
- CLOSE Prozedur (optional)
Die Funktion DESCRIBE beschreibt die Daten und gibt einen Deskriptor zurück, der die Informationen zu den neuen Spalten enthält.
Die Funktion DESCRIBE muss während der SQL-Cursor-Kompilierung aufgerufen werden. Die Prozedur FETCH_ROWS hingegen liest die Daten zu einem
Rowset (Sammlung von Zeilen) aus und erzeugt ein Ausgabe Rowset. Die Prozeduren OPEN, FETCH_ROWS und CLOSE werden
während der Ausführung der Query aufgerufen.
Aber zurück zum Beispiel: Im Package tab2keyval_pkg wird die DESCRIBE und die FETCH_ROWS Funktion definiert.
Die DESCRIBE Funktion benötigt dabei die Eingabeinformationen der PFT - hier die Tabelle als Tabellen Recordtyp (DBMS_TF.TABLE_T) und
die Spalte als Spalten Recordtyp (DBMS_TF.COLUMNS_T). Die Rückgabeinformation ist im Deskriptor DBMS_TF.DESCRIBE_T enthalten.
SQL> CREATE OR REPLACE PACKAGE tab2keyval_pkg
AS
FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T,
id_cols IN DBMS_TF.COLUMNS_T)
RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE fetch_rows;
END tab2keyval_pkg;
Der erste Teil der Package Body Implementierung sieht folgendermaßen aus:
CREATE OR REPLACE PACKAGE BODY tab2keyval_pkg AS
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
id_cols IN DBMS_TF.COLUMNS_T)
RETURN DBMS_TF.DESCRIBE_T
AS
new_cols DBMS_TF.COLUMNS_NEW_T;
col_id PLS_INTEGER := 1;
BEGIN
-- alle Spalten werden gelesen ausser die ID Spalte
FOR I IN 1 .. tab.COLUMN.COUNT LOOP
tab.column(i).pass_through := FALSE;
TAB.COLUMN(i).for_read := TRUE;
-- Unless this is one of ID-columns
FOR J IN 1 .. id_cols.COUNT LOOP
IF (tab.COLUMN(i).description.name = id_cols(j) ) THEN
tab.column(i).pass_through := TRUE;
TAB.COLUMN(i).for_read := FALSE;
END IF;
END LOOP;
END LOOP;
-- Folgende neue Spalten werden generiert: key_name, key_val_char, key_val_num, key_val_date
NEW_COLS(col_id) := dbms_tf.column_metadata_t(name => 'KEY_NAME',
TYPE => dbms_tf.type_varchar2);
NEW_COLS(col_id + 1) := dbms_tf.column_metadata_t(name => 'KEY_VAL_CHAR',
TYPE => dbms_tf.type_varchar2);
NEW_COLS(col_id + 2) := dbms_tf.column_metadata_t(name => 'KEY_VAL_NUMBER',
TYPE => dbms_tf.type_number);
NEW_COLS(col_id + 3) := dbms_tf.column_metadata_t(name => 'KEY_VAL_DATE',
TYPE => dbms_tf.type_date);
RETURN DBMS_TF.DESCRIBE_T(new_columns => new_cols, row_replication=>true);
END;
In der FOR Schleife wird die mitgegebene Spalte (im ersten Beispiel EMPNO) als PASS_THROUGH deklariert - sie
soll nicht weiterverarbeitet werden. Alle anderen Spalten werden mit der Eigenschaft FOR_READ markiert, da sie
weiter verwendet werden. Die Variable NEW_COLS definiert die neuen Spalten für die finale Ausgabe der Tabelle.
Mit DBMS_TF.COLUMN_METADATA_T lassen sich die Metadaten von existierenden oder neuen Spalten definieren. In unserem Beispiel
wird damit der Name und der Datentyp der neuen Spalten festlegt. Der Returnwert enthält die neuen Spalten und gibt mit "ROW_REPLICATION" an,
ob Zeilen repliziert werden können.
Im nächsten und letzten Abschnitt werden die Zeilen mithilfe der Prozedur FETCH_ROWS generiert.
PROCEDURE fetch_rows IS
inp_rs DBMS_TF.row_set_t;
-- Metadaten zum Ausfuehrungszeitpunkt der PFT
env dbms_tf.env_t := dbms_tf.get_env();
colcnt PLS_INTEGER;
rowcnt PLS_INTEGER;
repfac dbms_tf.tab_naturaln_t;
namecol dbms_tf.tab_varchar2_t;
valnumcol dbms_tf.tab_number_t;
valcharcol dbms_tf.tab_varchar2_t;
valdatecol dbms_tf.tab_date_t;
BEGIN
DBMS_TF.get_row_set(inp_rs, rowcnt, colcnt);
FOR i IN 1 .. rowcnt LOOP
repfac(i) := 0;
END LOOP;
FOR r IN 1 .. rowcnt LOOP
FOR c IN 1 .. colcnt LOOP
IF env.get_columns(c).type = dbms_tf.type_number then
repfac(r) := repfac(r) + 1;
namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"');
valnumcol(NVL(valnumcol.last+1,1)) := inp_rs(c).tab_number(r);
valcharcol(NVL(valcharcol.last+1,1)) := NULL;
valdatecol(NVL(valdatecol.last+1,1)) := NULL;
ELSIF env.get_columns(c).type = dbms_tf.type_varchar2 then
repfac(r) := repfac(r) + 1;
namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"');
valcharcol(NVL(valcharcol.last+1,1)) := inp_rs(c).tab_varchar2(r);
valnumcol(NVL(valnumcol.last+1,1)) := NULL;
valdatecol(NVL(valdatecol.last+1,1)) := NULL;
ELSIF env.get_columns(c).type = dbms_tf.type_date then
repfac(r) := repfac(r) + 1;
namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"');
valdatecol(NVL(valdatecol.last+1,1)) := inp_rs(c).tab_date(r);
valcharcol(NVL(valcharcol.last+1,1)) := NULL;
valnumcol(NVL(valnumcol.last+1,1)) := NULL;
END IF;
END LOOP;
END LOOP;
dbms_tf.row_replication(replication_factor => repfac);
dbms_tf.put_col(1, namecol);
dbms_tf.put_col(2, valcharcol);
dbms_tf.put_col(3, valnumcol);
dbms_tf.put_col(4, valdatecol);
END;
END tab2keyval_pkg;
Mit der Prozedur GET_ROW_SET werden die Zeilen zur Verfügung gestellt. Um die Daten auszulesen und
die die richtige Ausgabespalte zu definieren, werden 2 FOR-LOOP Schleifen (für Zeilen und Spalten) benötigt.
Für jeder Spalte wird der Datentyp überprüft und danach der Spaltenwert in den entsprechenden PL/SQL Tabellen VALCHARCOL, VALDATECOL oder
VALNUMCOL gespeichert. Der zugehörige Spaltenname wird in der PL/SQL Tabelle NAMECOL abgelegt. Die Information über den aktuellen Spaltenname wird dabei
über dem Aufruf ENV.GET_COLUMNS bereitgestellt. Mit DBMS_TF.DBMS_TF.ROW_REPLICATION wird definiert, ob und wie häufig die Inhalte repliziert werden sollen.
In unserem Fall bleibt es bei einer einmaligen Ausgabe der Informationen. DBMS_TF.PUT_COL stellt dann abschließend die Werte in den entsprechenden neuen Spalten zur Verfügung.
Nun kann das Package abgespeichert und die PFT verwendet werden.
Das vollständige Code Beispiel findet sich im Live SQL Tutorial unter
folgendem Link. Hier finden sich auch noch weitere Beispiele zum Thema PFT.
Lizenzierung
Das Feature Polymorphic Table Function steht in allen Editionen zur Verfügung.
Weitere Informationen
- Oracle Live SQL: Tutorials (Suchwort Polymorphic verwenden)
- SQL Standard 2016
- PL/SQL Language Reference Guide Handbuch: Overview of Polymorphic Table Functions
Zurück zur Community-Seite