SQL Macros sind eine Neuerung, die mit 20c in Oracle dazugekommen sind. Ihr Aufbau und ihre Erstellung entsprechen klassischen PL/SQL-Funktionen. Sie werden aber schon in der Parse-Phase des SQLs "aufgelöst" und führen damit nicht zu Kontext Switchen. SQL Macros gibt es in zwei Varianten: als Scalar Macro und als Table Macro. Beide Marco-Arten gleichen am ehesten einer Art Text-Substitution. Am besten versteht man das an einem einfachen Beispiel.
Vorab Informationen:
Das Folgende ist ein einfaches statisches Beispiel für ein Scalares SQL Macro. Dabei stellen wir die prozentuale Verteilung zwischen Gehalt und Kommission dar.
Normales SQL:
SELECT
EMPNO,
ENAME,
JOB,
SAL,
COMM,
ROUND((SAL/(SAL+NVL(COMM,0)))*100) PCT_SAL,
ROUND(DECODE(NVL(COMM,0),0,0,(COMM/(SAL+COMM)))*100) PCT_COMM
FROM
EMP;
EMPNO ENAME JOB SAL COMM PCT_SAL PCT_COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 800 100 0
7499 ALLEN SALESMAN 1600 300 84 16
7521 WARD SALESMAN 1250 500 71 29
7566 JONES MANAGER 2975 100 0
7654 MARTIN SALESMAN 1250 1400 47 53
7698 BLAKE MANAGER 2850 100 0
7782 CLARK MANAGER 2450 100 0
7788 SCOTT ANALYST 3000 100 0
7839 KING PRESIDENT 5000 100 0
7844 TURNER SALESMAN 1500 0 100 0
7876 ADAMS CLERK 1100 100 0
7900 JAMES CLERK 950 100 0
7902 FORD ANALYST 3000 100 0
7934 MILLER CLERK 1300 100 0
14 rows selected.
SQL Macro Variante:
CREATE OR REPLACE FUNCTION PCT_SAL RETURN VARCHAR2
SQL_MACRO(SCALAR) IS BEGIN
RETURN 'ROUND((SAL/(SAL+NVL(COMM,0)))*100)';
END;
/
CREATE OR REPLACE FUNCTION PCT_COMM RETURN VARCHAR2
SQL_MACRO(SCALAR) IS BEGIN
RETURN 'ROUND(DECODE(NVL(COMM,0),0,0,(COMM/(SAL+COMM)))*100)';
END;
/
SELECT
EMPNO,
ENAME,
JOB,
SAL,
COMM,
PCT_SAL,
PCT_COMM
FROM
EMP;
EMPNO ENAME JOB SAL COMM PCT_SAL PCT_COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 800 100 0
7499 ALLEN SALESMAN 1600 300 84 16
7521 WARD SALESMAN 1250 500 71 29
7566 JONES MANAGER 2975 100 0
7654 MARTIN SALESMAN 1250 1400 47 53
7698 BLAKE MANAGER 2850 100 0
7782 CLARK MANAGER 2450 100 0
7788 SCOTT ANALYST 3000 100 0
7839 KING PRESIDENT 5000 100 0
7844 TURNER SALESMAN 1500 0 100 0
7876 ADAMS CLERK 1100 100 0
7900 JAMES CLERK 950 100 0
7902 FORD ANALYST 3000 100 0
7934 MILLER CLERK 1300 100 0
14 rows selected.
Man kann in diesem Beispiel sehr gut sehen, dass sich der Text im eigentlichen SELECT Statment sehr vereinfacht hat.
Neben der Art von Scalaren Macros gibt es auch Table Macros. In diesem Beispiel wird ein klassischer Self-Join auf die EMP Tabelle gemacht, um die Vorgesetzten der Mitarbeiter anzuzeigen.
Normales SQL:
SELECT
E.ENAME ENAME,
E.JOB EJOB,
M.ENAME MNAME,
M.JOB MJOB
FROM
EMP E,
EMP M
WHERE
E.MGR=M.EMPNO
ORDER
BY M.JOB;
ENAME EJOB MNAME MJOB
---------- --------- ---------- ---------
ADAMS CLERK SCOTT ANALYST
SMITH CLERK FORD ANALYST
TURNER SALESMAN BLAKE MANAGER
ALLEN SALESMAN BLAKE MANAGER
WARD SALESMAN BLAKE MANAGER
JAMES CLERK BLAKE MANAGER
MILLER CLERK CLARK MANAGER
SCOTT ANALYST JONES MANAGER
FORD ANALYST JONES MANAGER
MARTIN SALESMAN BLAKE MANAGER
JONES MANAGER KING PRESIDENT
CLARK MANAGER KING PRESIDENT
BLAKE MANAGER KING PRESIDENT
13 rows selected.
SQL Macro Variante:
CREATE OR REPLACE FUNCTION BOSS_TABLE RETURN VARCHAR2
SQL_MACRO(TABLE) IS BEGIN
RETURN 'SELECT E.ENAME ENAME, E.JOB EJOB, M.ENAME MNAME, M.JOB MJOB FROM EMP E, EMP M WHERE E.MGR=M.EMPNO ORDER BY M.JOB';
END;
/
select * from BOSS_TABLE();
ENAME EJOB MNAME MJOB
---------- --------- ---------- ---------
ADAMS CLERK SCOTT ANALYST
SMITH CLERK FORD ANALYST
TURNER SALESMAN BLAKE MANAGER
ALLEN SALESMAN BLAKE MANAGER
WARD SALESMAN BLAKE MANAGER
JAMES CLERK BLAKE MANAGER
MILLER CLERK CLARK MANAGER
SCOTT ANALYST JONES MANAGER
FORD ANALYST JONES MANAGER
MARTIN SALESMAN BLAKE MANAGER
JONES MANAGER KING PRESIDENT
CLARK MANAGER KING PRESIDENT
BLAKE MANAGER KING PRESIDENT
13 rows selected.
Im Prinzip verhält es sich wie ein Sub-Select, der hier über einen Funktionsaufruf im eigentlich SELECT-Statement eingebunden wird.
Dieses erste Beispiel ist aber leider nur auf diese Tabelle anwendbar. Nachdem es sich bei dem Macro um eine PL/SQL-Funktion handelt, ist es hier natürlich möglich, sie mit Parametern zu versehen und damit die Verwendung dynamischer zu gestalten.
Im Folgenden wird ein neues SQL Macro geschrieben, was nun über Parameter gesteuert werden kann. Es werden in dieser Funktion Spaltennamen übergeben und der entsprechende Code als String zusammengesetzt.
CREATE OR REPLACE FUNCTION PCT_DIST (P_FIRSTCOL IN VARCHAR2, P_SECONDCOL in VARCHAR2) RETURN VARCHAR2
SQL_MACRO(SCALAR) IS BEGIN
RETURN 'ROUND(DECODE(NVL(P_FIRSTCOL,0),0,0,(NVL(P_FIRSTCOL,0)/(NVL(P_SECONDCOL,0)+NVL(P_FIRSTCOL,0))))*100)';
END;
/
SELECT
EMPNO,
ENAME,
JOB,
SAL,
COMM,
PCT_DIST(sal,comm),
PCT_DIST(comm,sal)
FROM
EMP;
EMPNO ENAME JOB SAL COMM PCT_DIST(SAL,COMM) PCT_DIST(COMM,SAL)
---------- ---------- --------- ---------- ---------- ------------------ ------------------
7369 SMITH CLERK 800 100 0
7499 ALLEN SALESMAN 1600 300 84 16
7521 WARD SALESMAN 1250 500 71 29
7566 JONES MANAGER 2975 100 0
7654 MARTIN SALESMAN 1250 1400 47 53
7698 BLAKE MANAGER 2850 100 0
7782 CLARK MANAGER 2450 100 0
7788 SCOTT ANALYST 3000 100 0
7839 KING PRESIDENT 5000 100 0
7844 TURNER SALESMAN 1500 0 100 0
7876 ADAMS CLERK 1100 100 0
7900 JAMES CLERK 950 100 0
7902 FORD ANALYST 3000 100 0
7934 MILLER CLERK 1300 100 0
14 rows selected.
Dadurch ist das Macro wesentlich dynamischer und kann auch für andere Tabellen eingesetzt werden.
Table Macros kann man auch mit Parameter versehen, man erhält damit eine Art parametrisierbare View. Hier wird das ursprüngliche Beispiel um die Möglichkeit erweitert, den Job des Vorgesetzten vorzugeben.
CREATE OR REPLACE FUNCTION BOSS_TABLE (P_BOSS_JOB IN VARCHAR2) RETURN VARCHAR2
SQL_MACRO(TABLE) IS BEGIN
RETURN 'SELECT E.ENAME ENAME, E.JOB EJOB, M.ENAME MNAME, M.JOB MJOB FROM EMP E, EMP M WHERE E.MGR=M.EMPNO AND M.JOB = P_BOSS_JOB ORDER BY M.JOB';
END;
/
select * from BOSS_TABLE('MANAGER');
ENAME EJOB MNAME MJOB
---------- --------- ---------- ---------
FORD ANALYST JONES MANAGER
SCOTT ANALYST JONES MANAGER
TURNER SALESMAN BLAKE MANAGER
ALLEN SALESMAN BLAKE MANAGER
WARD SALESMAN BLAKE MANAGER
JAMES CLERK BLAKE MANAGER
MARTIN SALESMAN BLAKE MANAGER
MILLER CLERK CLARK MANAGER
8 rows selected.
Bei sehr großen Abfragen mit gleichen Unterabfragen auf verschiedene Tabellen kann ein Table Macro sehr viel Text sparen und die Redundanz reduzieren.
Mit SQL Macros lässt sich ein Teil der Abfrage-Logik auslagern und modularisieren. Das sorgt dafür, dass Abfragen übersichtlicher werden. Dadurch, dass die Macros schon in der Parse-Phase „aufgelöst“ werden, gibt es auch keine Kontextswitches während der Ausführung des SQL-Statements.
In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den
Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.
