X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

Parametrisierte Views mit SQL Macros

Kann man relationale Views auch parametrisieren? Diese Frage stellen sich Oracle Datenbank Entwickler und DBAs schon seit langer Zeit. Die Idee ist beim Anlegen einer relationalen View Argumente mitgeben zu können, um eine höhere Flexibilität zu haben. Die Antwort war bisher nein bzw. wenn ja, dann mit nicht ganz einfachen Mitteln. Mit 18c sind nämlich die sogenannten "polymorphic Views" eingeführt worden: Die Implementierung ist komplex, da die Kenntnis von speziellen PL/SQL Funktionen zur Implementierung notwendig sind. Mehr dazu findet sich beispielsweise auch in unserem deutschsprachigen Beitrag 18c: Polymorphic Table.

Mit der Einführung von SQL Macros (SQM) gibt es jetzt auch eine einfache Lösung. Wie der Name schon sagt, geht es in erster Linie um eine Vereinfachung und Modularisierung von komplexem SQL-Code. Gängige SQL-Ausdrücke und -Anweisungen sollen damit in wiederverwendbare, parametrisierte Konstrukte zerlegt werden, die dann in beliebigen SQL-Anweisungen Verwendung finden.

SQL-Makros haben zwei Verwendungsarten - eine skalare (SCALAR) und eine für Tabellen (TABLE).

  • Skalare Ausdrücke werden typischerweise in SELECT-Listen, WHERE-, GROUP BY- und HAVING-Klauseln verwendet, um Berechnungen und Geschäftslogik zu kapseln.
  • Tabellenausdrücke hingegen ermöglichen die Verwendung in einer FROM Klausel.
     

Basis Funktionalität

Das Erzeugen von SQL Macros ist für den Oracle Datenbank User ganz einfach: SQL Macros sind Datenbankobjekte. Sie werden also in der Datenbank zur Wiederverwendung gespeichert und mit einer erweiterten CREATE FUNCTION Syntax  erzeugt.

Nehmen wir folgendes SQL Statement, das die Demotabelle EMP verwendet. Über die Spalte HIREDATE soll die Anstellung in Jahren berechnet werden. Die Anfrage und Ausgabe könnte dann folgendermassen aussehen.

select ename, extract(year from sysdate) - extract(year from hiredate) jahre
from scott.emp
where ename like 'S%';


Die Ausgabe sieht dann folgendermaßen aus:

SCOTT                     33
SMITH                      40

Erzeugen wir nun ein passendes skalares SQL Macro dazu, das die Berechnung der Jahre enthält. 

CREATE OR REPLACE FUNCTION jobyears RETURN VARCHAR2
SQL_MACRO(SCALAR)
AS
BEGIN
   RETURN 'extract(year from sysdate) - extract(year from hiredate)';
END;
/

Nun können wir das SQL Macro JOBYEARS in SELECT Abfragen an beliebigen Stellen  - außer in der FROM Klausel -verwenden.

select ename, job, jobyears from scott.emp;
select ename from scott.emp where jobyears>30;

Eine Parametrisierung ist zusätzlich möglich, zum Beispiel in Abhängigkeit eines Startjahres.

CREATE OR REPLACE FUNCTION jobyearsfrom(startjahr number) RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
   RETURN 'startjahr - extract(year from hiredate)';
END;
/

Die Anwendung sieht dann beispielsweise so aus:

select ename, jobyearsfrom(2000) from scott.emp;

Dabei können mehrere SQL Macros gleichzeitig in einem Statement verwendet werden. Auch die Anwendung im UPDATE, DELETE, MERGE, INSERT oder CREATE Statements ist uneingeschränkt möglich. Einzig die Verwendung in der FROM Klausel ist mit dieser Art von SQL Macro nicht möglich.
 

Folgendes Beispiel zeigt die Verwendung von mehreren SQL Macros in einer Abfrage.

select ename, jobyearsfrom(2000) from scott.emp where jobyears>38;

Was ist zu beachten? SQL Macros sind immer deterministisch. Der RETURN Typ muss immer vom Typ Character sein -  also VARCHAR2, CHAR oder CLOB. RESULT_CACHE, PARALLEL_ENABLE und PIPELINED können in der CREATE FUNCTION Syntax nicht verwendet werden. SQL Macros können im Moment auch nicht in virtuellen Spalten, in Function Based Indizes, in Editioning Views und Materialiazed Views vorkommen.

Parametrisierte Views

Nun soll die Abfrage mit folgenden Anforderungen erweitert werden: Gruppiert nach DEPTNO und JOB werden die Angestellten mit dem berechneten Jahren in Anstellung ausgegeben. Dies soll über einen View erfolgen, der zusätzlich die Möglichkeit bietet, den JOB als Übergabe Parameter zu verwenden. SQL Macros vom Typ TABLE erfüllen diese Anforderung ganz einfach.

CREATE or replace FUNCTION jobyearstable(job_var varchar2) RETURN VARCHAR2
SQL_MACRO(TABLE)
AS 
BEGIN RETURN q'!select deptno, job, listagg(ename || ':'||jobyears,',') 
               from scott.emp where job=job_var group by deptno, job!';
END;
/

Hinweis: Bei der RETURN Ausgabe wird der Oracle Quote Operator q verwendet. Die Funktionsweise ist wie folgt: 
q'c text-to-be-quoted c'. Der Buchstabe c ist dabei der Quote Delimiter und kann ein beliebiges Zeichen sein. In unserem Fall ist dies das Zeichen "!".

Nun wird das SQL Macro in der  FROM-Klausel verwendet.

select * from jobyearstable('CLERK');

Das Ergebnis sieht dann folgendermaßen aus:

10    CLERK    MILLER:38
20    CLERK    SMITH:40,ADAMS:33
30    CLERK    JAMES:39                                                                                                                                                   

Es kann natürlich auch in Verbindung mit anderen Tabellen wie der Tabelle DEPT verwendet werden.

select d.dname, m.*
from jobyearstable('CLERK') m, scott.dept d
where d.deptno=m.deptno;   

Die Ausgabe sieht dann wie folgt aus

ACCOUNTING    10    CLERK    MILLER:38
RESEARCH        20    CLERK    SMITH:40,ADAMS:33
SALES                30    CLERK    JAMES:39
                                                                                                 

SQL Macros verwalten und monitoren

SQL Macros lassen sich mit einem CREATE (OR REPLACE) FUNCTION Kommando erzeugen, die Kommandos ALTER und DROP können auch verwendet werden. Zum Monitoren eignen sich die Standard Data Dictionary Views wie USER_OBJECTS, USER_PROCEDURES und USER_SOURCE in den üblichen Varianten.

Folgendes Beispiel gibt einen Überblick über die Objekte in der Datenbank.

select object_name, object_type from user_objects where object_name like 'JOB%';

Die Ausgabe sieht dann folgendermaßen aus:

JOBYEARS                     FUNCTION
JOBYEARSFROM          FUNCTION
JOBYEARSTABLE          FUNCTION

Folgendes Beispiel gibt an, welche SQL Macros gespeichert sind.

select object_name, sql_macro  from user_procedures where object_name like 'JOB%';
 

Die Ausgabe sieht dann folgendermaßen aus:

JOBYEARS                SCALAR 
JOBYEARSTABLE     TABLE 
JOBYEARSFROM     SCALAR

Der entsprechende Code findet sich in USER_SOURCE - beispielsweise für JOBYEARS.

select line, text from user_source where name like 'JOBYEARS';

LINE   TEXT
------   ------------------------------------------------------------
     1     FUNCTION jobyears RETURN VARCHAR2
     2     SQL_MACRO(SCALAR)
     3     AS
     4     BEGIN 
     5     RETURN 'extract(year from sysdate) - extract(year from hiredate)'; 
     6     END;

 

SQL Macros ausprobieren

SQL Macros gehören zu der Basis- Funktion der Oracle Datenbank und können ohne zusätzliche Installation oder Lizenzierung verwendet werden. Im Moment steht die Funktion in der Version 20c im Cloud Preview und in der aktuellen Oracle Autonomous Database (ADW oder ATP) zur Verfügung. 
Ein Backport von parameterisierten Views ist sogar ab 19.7 auf On-Premises mit einer leicht abgewandelten Syntax nutzbar. Statt SQL_MACRO(TABLE) wird einfach nur SQL_MACRO verwendet. Ohne Oracle Software Installation ist die Verwendung dann auch über Live SQL möglich. 

Warum SQL Macros einsetzen?

SQL Macros eignen sich hervorragend, um übersichtlichen SQL-Code zu schreiben. Besonders bei wachsenden komplexen Anforderungen ist dies eine Möglichkeit modulare Funktionen zur Verfügung zu stellen. Overloading von Funktionen und einfachere Code-Migrationen sind weitere typische Anwendungsfälle. So können beispielsweise Änderungen am Code zentral verwaltet werden. Wichtig zu wissen ist auch, dass es sich um reinen SQL-Code handelt, es sind keine Context Switches zwischen PL/SQL und SQL erforderlich. Der gesamte Code ist dabei für den SQL-Optimizer transparent und kann mit den üblichen Mitteln optimiert werden. 

Handbücher

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.