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.
CREATE or RREPLACE FUNCTION function_name (<optional parameters>)
RETURN VARCHAR2 | CHAR | CLOB
SQL_MACRO (SCALAR|TABLE)
AS
BEGIN
< CODE >;
END;
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.
SELECT tl.*,
FLOOR (MONTHS_BETWEEN(TRUNC(SYSDATE), tl.hiredate)/12) as years
FROM emp tl
WHERE FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), tl.hiredate)/12) > 38;
SELECT tl.*
FROM emp tl;
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.
CREATE OR REPLACE FUNCTION job_duration RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
RETURN 'FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), tl.hiredate)/12)';
END;
Die Funktion „job_duration“ wurde als SCALAR SQL Macro angelegt und ist nun verwendbar.
SELECT tl.ename,
job_duration as years
FROM emp tl
WHERE job_duration > 38;
Beispiel (TABLE SQL Macro): Wir wollen aus der Tabelle „emp“ alle Mitarbeiter mit dem Jobtitel „ANALYST“ ausgeben.
SELECT tl.ename,
tl.deptno
FROM (SELECT al.*
FROM emp al
WHERE al.job = 'ANALYST') tl;
Wir können den Ausdruck in der FROM Klausel auch als Macro auslagern.
CREATE OR REPLACE FUNCTION analyst_emp
RETURN VARCHAR2
SQL_MACRO
AS
BEGIN
RETURN 'SELECT * FROM emp WHERE job = ''ANALYST''';
END;
Die Funktion „analyst_emp“ wurde als TABLE SQL Macro angelegt und ist nun verwendbar.
SELECT tl.ename,
tl.deptno
FROM analyst_emp() tl;
Folgendes Macro liefert zwar beim Kompilieren keinen Fehler, jedoch beim Aufruf im SQL Statement:
CREATE OR REPLACE FUNCTION job_duration RETURN VARCHAR2
SQL_MACRO (SCALAR)
AS
BEGIN
RETURN 'FLOOR (MONTHS_BETWEEN (SYSDATE, hiredate)/12) AS YEARS,FLOOR (MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) AS MONTHS';
END;
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.
CREATE OR REPLACE FUNCTION duration (p_year IN NUMBER) RETURN VARCHAR2
SQL_MACRO (SCALAR)
AS
BEGIN
RETURN 'p_year - EXTRACT(year from hiredate)';
END;
Nun haben wir das SQL Macro erstellt und können es nun in unserer Abfrage verwenden.
SELECT tl.ename,
duration(p_year => 2021) AS years,
FROM emp tl
WHERE duration(p_year => 2021) > 38;
Data Dictionary
Da SQL Macros eigene Datenbankobjekte sind, sind diese auch im Data Dictionary ersichtlich.
SELECT tl.object_name,
tl.object_type,
tl.sql_macro
FROM user_procedures tl
ORDER
BY tl.object_name;
Manuel ist Software Developer bei DBConcepts.
