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

 
Verfügbarkeit und Download

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services