SQL Macros

Des Öfteren kommt es vor, dass man ein und denselben Ausdruck über mehrere SELECT Befehle oder WHERE Bedingungen benötigt.
Mit den SQL Macros gibt es nun ein Werkzeug, um solche SQLs lesbarer und performanter zu gestalten.

Ein SQL Macro ist eine ausgelagerte Funktion, welche innerhalb eines SQL-Befehls aufgerufen wird. Dieses Konstrukt wird verwendet, um gängige SQL-Ausdrücke auszulagern und wiederzuverwenden. Überall, wo man normalerweise Funktionen aufrufen kann, können SQL Macros verwendet werden. In Oracle 20c nur als preview in der Cloud vorhanden, sind SQL Macros ab Oracle 21c fixer Bestandteil von Oracle Datenbanken, jedoch gibt es schon ab der Version 19.6 SQL TABLE Macros.

Generell gibt es zwei Arten von SQL Macros:

SCALAR: Liefert einen Ausdruck und kann im SELECT, WHERE, HAVING, GROUP BY und ORDER BY Teil des Befehls verwendet werden.

TABLE: Liefert einen Ausdruck und darf ausschließlich im FROM Teil des Befehls verwendet werden.

Aufbau:

Wird der Macro Typ nicht angegeben, wird das Macro defaultmäßig als TABLE Macro erstellt. Da in der Oracle Version 19.6 nur TABLE Macros zur Verfügung stehen, muss der Typ nicht angegeben werden.

Das SQL Macro wird nicht erst während der runtime ausgeführt, sondern bereits während dem parsen der Abfrage und liefert im Anschluss einen String (VARCHAR2, CHAR, CLOB) in die Hauptabfrage zurück. Dem Optimizer stehen daher schon alle nötigen Informationen zur Optimierung des SQLs zur Verfügung. Da das SQL Macro nur einmal während der parse time ausgeführt wird, entfällt daher auch der context switch zwischen SQL und PL/SQL engine. Des Weiteren können Parameter einem Macro mitübergeben werden. Dies ist besonders bei VIEWs interessant, denn bis dato konnte man VIEWs nicht parametrisieren.

Beispiele

Beispiel (Scalar SQL Macro): Wir wollen aus der Tabelle „emp“ alle Mitarbeiter ausgeben, welche schon mehr als 38 Jahre im Unternehmen angestellt sind.

Der Ausdruck „FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), t1.hiredate)/12)” wird in diesem Statement zwei Mal verwendet, diesen können wir also auch in ein SQL Macro auslagern.

Die Funktion „job_duration“ wurde als SCALAR SQL Macro angelegt und ist nun verwendbar.

Beispiel (TABLE SQL Macro): Wir wollen aus der Tabelle „emp“ alle Mitarbeiter mit dem Jobtitel „ANALYST“ ausgeben.

Wir können den Ausdruck in der FROM Klausel auch als Macro auslagern.

Die Funktion „analyst_emp“ wurde als TABLE SQL Macro angelegt und ist nun verwendbar.

Folgendes Macro liefert zwar beim Kompilieren keinen Fehler, jedoch beim Aufruf im SQL Statement:

Fehler Nummer 1: Zwei Ergebnisspalten in einem Macro. Dies ist nicht möglich, da ein Macro immer nur eine Ergebnisspalte zurückliefert.

Fehler Nummer 2: Spalten Alias in einem Macro. Da Macros unter Anderem auch in der WHERE Klausel verwendet werden können und diese mit Spalten Aliase nicht arbeiten können, wird hier ein weiterer Fehler erzeugt.

Parameter

Um mehr Flexibilität in SQL Macros zu erlangen, können Parameter verwendet werden. Beispiel: Wir wollen anhand eines beliebig gewählten Jahres wissen, welche Mitarbeiter schon länger als 38 Jahre im Unternehmen tätig sind.