Newsletter
DBConcepts Logo Original

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;
				
			
SQL Macros-Abb.12

DBConcepts

Weitere Beiträge

DBConcepts Adventpunsch

Am Dienstag, den 03.12 findet heuer unser allseits beliebter Adventpunsch statt. Dieses Jahr treffen wir uns wieder im Weihnachtsdorf im Alten AKH (Universitätscampus).Wir haben einen eigens für uns

DBConcepts

Newsletter abonnieren

Wir freuen uns, dass wir Ihr Interesse für den Newsletter geweckt haben! Mit dem Versand dieser Zustimmung erhalten Sie regelmäßig alle aktuellen Informationen!

Vielen Dank für Ihr Interesse an unserem Unternehmen. Derzeit suchen wir niemanden für diese Stelle. Aber wir sind immer an talentierten Menschen interessiert und freuen uns von Ihnen zu hören! Schicken Sie uns einfach Ihren Lebenslauf und eine kurze Nachricht und schreiben Sie an welcher Stelle Sie interessiert sind: recruitment@dbconcepts.com. Wir freuen usn von Ihnen zu hören!