Inhaltsverzeichnis

Oracle SQL Macro SQM - SQL Code Fragmente - in SQL verwenden ab 19.7c(Nur Table Typ) / 21c / 23c

Oracle SQL Macro SQM - SQL Code Fragmente - in SQL verwenden ab 19.7c(Nur Table Typ) / 21c / 23c

Mit dem DB Release Update 19.7 ist das Oracle SQL Macro SQM für Tabellen Macros verfügbar. ⇒ https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/sql-macros-sqm.html .

Das Feature das uns PL/SQL Aufrufe gut ersetzten kann ist aber leider erst ab 21c verfügbar, der Typ SKALAR für die Prädikate einer SQL Abfrage.

Was ist ein Oracle SQL Macro SQM?

Eine PL/SQL Funktion gibt ein Stück SQL Code zurück (Text!).

Dieser Text wird vom Parser ausgewertet und das SQL Statement wird um diese Stück SQL erweitert.

Da diese Erweiterung des SQLs vor der Ausführung erfolgt (Nur beim ersten Hardparse!) kann verhindert werden, das bei der Ausführung ständig zwischen PL/SQL Engine und SQL Engine hin und her gesprungen werden muss.

Das kann am Ende eine deutliche Laufzeitverbesserung erzielen.

Typen:

Funktion kann in der From Klausel verwendet werden (Table SQL Macro) - Parametrisierbare Inline View ( ab 19.7)
Funktion kann in der SELECT-Listen, WHERE-, GROUP BY- und HAVING-Klausel verwendet werden ( Scalar SQL Macro) - Vermeidung für PL/SQL Aufrufe ( ab 21c)

Einsatzzweck:

Wiederverwendbarer SQL Code
Parametrisierbare InLine View Konstrukte
Kapseln von komplexen Case Konstrukten
Performance Optimierung von PL/SQL Funktionen in SQL

Ein SQL Macro kann auch parametrisiert aufgerufen werden, hier ist das Handling der Parameter zu beachten, das nicht unbedingt auf den ersten Blick der normalen PL/SQL Logik folgt!

Aktuell (23c Free Edition, 12.2023) starke Einschränkungen

Kann nicht in der With Klausel verwendet werden!

SQL Error: ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported
64630. 00000 -  "unsupported use of SQL macro: %s"

Kann nicht im Group by verwendet werden!

SQL Error: ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported
64630. 00000 -  "unsupported use of SQL macro: %s"

Aktuelle schränkt das doch die Verwendung des Features stark ein.

Beispiel Performance Optimierung - Scalar SQL Macro

Eine interne Kundennummer wird durch eine einfache Funktion gebildet, die in einer bestimmten Art Eigenschaften eines Kundens zu einer internen Kunden Kennung verknüpft.

In der 19c wurde dazu eine einfache PLSQL Routine verwendet, in der 23c soll nun dieser Pl/SQL Aufruf im SQL vermieden werden.

Prüfen ob ein SQL Macro machbar ist

Lässt sich die Logik auch in SQL Schreiben? zum Bespiel über einen komplexen Case Ausdruck?

Nur dann macht ein SQL Macro Sinn, da wir ja sonst doch wieder permanent PL/SQL aufrufen würden.

Einfaches erstes Beispiel

Ein sehr simples Macro:

CREATE OR REPLACE FUNCTION customerId
RETURN varchar2
SQL_MACRO(SCALAR)
AS
 
BEGIN
  RETURN 42;
END customerId;

Aufrufen:

SELECT customerId FROM dual;
 
customerId 
---
42

Was für ein SQL wurde aufgerufen? Versuchen wir mal ob DBMS_UTILITY.expand_sql_text die Funktion auflösen kann:

SET SERVEROUTPUT ON 
DECLARE
  v_full_sql CLOB;
BEGIN
 
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => q'[select customerId from dual ]',
    output_sql_text => v_full_sql 
  );
 
  DBMS_OUTPUT.put_line(v_full_sql );
 
END;
/

---
SELECT "GPI"."CUSTOMERID"() "CUSTOMERID" FROM "SYS"."DUAL" "A1" WHERE "GPI"."CUSTOMERID"()=101
---

Hmm, das ist nicht ganz das erwartete…

Ein komplexeres Bespiel

CREATE TABLE Kunden(
  SK NUMBER(11)  GENERATED BY DEFAULT ON NULL AS IDENTITY
, institut    varchar2(4)
, kundennummerInstitut  NUMBER(8)
, ZentralInstitut varchar2(4)
, ZentraleKundennummer NUMBER(9)
, Kundenname varchar2(256)
, Eigenschaften varchar2(4000)
)
;
 
INSERT INTO Kunden (INSTITUT, KUNDENNUMMERINSTITUT, ZENTRALINSTITUT, ZENTRALEKUNDENNUMMER, KUNDENNAME, EIGENSCHAFTEN) 
VALUES ('0100','4711','0000','909090','Müller','Zentralkunde')
;
INSERT INTO Kunden (INSTITUT, KUNDENNUMMERINSTITUT, ZENTRALINSTITUT, ZENTRALEKUNDENNUMMER, KUNDENNAME, EIGENSCHAFTEN) 
VALUES ('0400','4812','0000','909090','Müller','Zentralkunde')
;
INSERT INTO Kunden (INSTITUT, KUNDENNUMMERINSTITUT, ZENTRALINSTITUT, ZENTRALEKUNDENNUMMER, KUNDENNAME, EIGENSCHAFTEN) 
VALUES ('0100','4242','0000',NULL,'Maier','Nur regional Kunde')
;
 
commit;

Die Daten sollen so ausgewertet werden:

SELECT k.Kundenname
    ,  CASE
        WHEN k.ZENTRALEKUNDENNUMMER IS NULL THEN
           k.INSTITUT||'-'||k.KUNDENNUMMERINSTITUT||'-1'
        WHEN  k.ZENTRALEKUNDENNUMMER IS NOT NULL AND k.ZENTRALINSTITUT IS NOT NULL THEN
          k.ZENTRALINSTITUT||'-1-'||k.ZENTRALEKUNDENNUMMER
      END AS InteralKDNr 
   FROM Kunden k
GROUP BY Kundenname
        ,InteralKDNr; -- < -- 23c Feature, group by über den Alias Namen!
 
 
Kundenname  InteralKDNr
Müller      0000-1-909090
Maier	    0100-4242-1

Das ganze muss nun in zig Tabellen mit ähnlicher Struktur zigfach in verschiedenen SQL's verwendet werden.

Lösung PLSQL nativ

CREATE OR REPLACE FUNCTION getInteralKDNr(    p_institut   VARCHAR2
                                            , p_kundennummerInstitut  NUMBER
                                            , p_ZentralInstitut VARCHAR2
                                            , p_ZentraleKundennummer NUMBER
                                        )
RETURN VARCHAR2
IS
v_return VARCHAR2(4000);
BEGIN
 
v_return := CASE
        WHEN p_ZENTRALEKUNDENNUMMER IS NULL THEN
           p_INSTITUT||'-'||p_KUNDENNUMMERINSTITUT||'-1'
        WHEN  p_ZENTRALEKUNDENNUMMER IS NOT NULL AND p_ZENTRALINSTITUT IS NOT NULL THEN
              p_ZENTRALINSTITUT||'-1-'||p_ZENTRALEKUNDENNUMMER
      END;
 
RETURN v_return;
 
END getInteralKDNr;

Aufrufen mit:

SELECT k.Kundenname,
     getInteralKDNr(institut, kundennummerInstitut, ZentralInstitut, ZentraleKundennummer) AS InteralKDNr 
 FROM Kunden k
GROUP BY Kundenname,InteralKDNr;    
 
 
Kundenname  InteralKDNr
 
Müller      0000-1-909090
Maier	    0100-4242-1

Lösung SQL Macro

Die gleiche Logik läßt sich einfach auch ein SQL Macro umwandeln

Das SQL Macro:

CREATE OR REPLACE FUNCTION getInteralKDNrSQM
RETURN varchar2
SQL_MACRO(SCALAR)
IS
v_return varchar2(4000);
BEGIN
 
v_return := q'[case
        when k.ZENTRALEKUNDENNUMMER is null then
           k.INSTITUT||'-'||k.KUNDENNUMMERINSTITUT||'-1'
        when  k.ZENTRALEKUNDENNUMMER is not null and k.ZENTRALINSTITUT is not null then
          k.ZENTRALINSTITUT||'-1-'||k.ZENTRALEKUNDENNUMMER
      end]';
 
RETURN v_return;
 
END getInteralKDNrSQM;
/

Aufrufen:

SELECT k.Kundenname,
      getInteralKDNrSQM AS InteralKDNr 
 FROM Kunden k
 
Müller	0000-1-909090
Müller	0000-1-909090
Maier	0100-4242-1
 
-- Als Bedingung
 
SELECT k.Kundenname,
      getInteralKDNrSQM AS InteralKDNr 
 FROM Kunden k
 WHERE getInteralKDNrSQM = '0100-4242-1';
 
Maier	0100-4242-1

Mít simplen SQL sieht das ja schon mal sehr gut aus ABER was ist mit Group by und With Clausel?

SELECT k.Kundenname,
       getInteralKDNrSQM AS InteralKDNr 
  FROM Kunden k
 GROUP BY k.Kundenname
        , InteralKDNr;
 
SQL Error: ORA-22818: subquery expressions NOT allowed here
22818. 00000 -  "subquery expressions not allowed here"
 
-- Sieht mir noch nach eine BUG in 23c aus, sonst macht das irgendwie keinen Sinn ...
 
-- nächster Versuch:
 
WITH data_set AS (
SELECT k.Kundenname,
      getInteralKDNrSQM AS InteralKDNr 
 FROM Kunden k
)
SELECT * 
  FROM data_set
  GROUP BY Kundenname
       ,  InteralKDNr
 
 
SQL Error: ORA-64630: unsupported USE OF SQL macro: USE OF SQL macro inside WITH clause IS NOT supported
64630. 00000 -  "unsupported use of SQL macro: %s"
 
-- Das schränkt es nun aber doch sehr stark in der Verwendung ein ...

Tabellen Macro

Mit dem Tabellen Macro läßt sich eine parametrisierte Views aufbauen.

Ein erstes einfaches Beispiel, eine Filterbedingung einbauen:

CREATE OR REPLACE FUNCTION getZentralKundenTabSQM
RETURN varchar2
SQL_MACRO(TABLE)
IS
v_return varchar2(4000);
BEGIN
v_return := q'[select * 
                 from Kunden 
               where ZentraleKundennummer is not null
            ]';
 
RETURN v_return;
END getZentralKundenTabSQM;

Abfragen mit:

SELECT * FROM getZentralKundenTabSQM();
 
0100	4711	0000	909090	Müller	Zentralkunde
0400	4812	0000	909090	Müller	Zentralkunde
</code >
 
 
=== Wie sieht aber das erzeugte SQL aus? ===
 
Anzeigen über **DBMS_UTILITY.expand_sql_text**
<code sql>
 
SET SERVEROUTPUT ON 
DECLARE
  v_full_sql CLOB;
BEGIN
   DBMS_UTILITY.expand_sql_text (
    input_sql_text  => q'[select * from getZentralKundenTabSQM() ]',
    output_sql_text => v_full_sql 
  );
   DBMS_OUTPUT.put_line(v_full_sql );
 END;
/
 
SELECT  "A1"."SK" "SK"
       ,"A1"."INSTITUT" "INSTITUT","A1"."KUNDENNUMMERINSTITUT" "KUNDENNUMMERINSTITUT"
	   ,"A1"."ZENTRALINSTITUT" "ZENTRALINSTITUT","A1"."ZENTRALEKUNDENNUMMER" "ZENTRALEKUNDENNUMMER"
	   ,"A1"."KUNDENNAME" "KUNDENNAME"
	   ,"A1"."EIGENSCHAFTEN" "EIGENSCHAFTEN" 
FROM  
    ( SELECT "A2"."SK" "SK"
            ,"A2"."INSTITUT" "INSTITUT","A2"."KUNDENNUMMERINSTITUT" "KUNDENNUMMERINSTITUT","A2"."ZENTRALINSTITUT" "ZENTRALINSTITUT"
			,"A2"."ZENTRALEKUNDENNUMMER" "ZENTRALEKUNDENNUMMER","A2"."KUNDENNAME" "KUNDENNAME","A2"."EIGENSCHAFTEN" "EIGENSCHAFTEN" 
       FROM  
	        (  SELECT "A3"."SK" "SK"
			         ,"A3"."INSTITUT" "INSTITUT","A3"."KUNDENNUMMERINSTITUT" "KUNDENNUMMERINSTITUT","A3"."ZENTRALINSTITUT" "ZENTRALINSTITUT"
					 ,"A3"."ZENTRALEKUNDENNUMMER" "ZENTRALEKUNDENNUMMER","A3"."KUNDENNAME" "KUNDENNAME","A3"."EIGENSCHAFTEN" "EIGENSCHAFTEN" 
                 FROM "GPI"."KUNDEN" "A3" 
                 WHERE "A3"."ZENTRALEKUNDENNUMMER" IS NOT NULL 
	) "A2"
    ) "A1"

Bei einem Table SQL Macro wird der Text komplett aufgelöst dargestellt.

Parameter übergeben

Wichtig bei einer SQM ist es das die Parameter Text Ersetzungen sind!

CREATE OR REPLACE FUNCTION getZentralKundenTabSQM(p_institut varchar2)
RETURN varchar2
SQL_MACRO(TABLE)
IS
v_return varchar2(4000);
BEGIN
 
v_return := q'[select * 
                 from Kunden 
               where institut =  p_institut
            ]';
 
RETURN v_return;
END getZentralKundenTabSQM;
/
 
--abfragen mit:
SELECT * 
  FROM getZentralKundenTabSQM('0100');

Quellen

Oracle:

https://blogs.oracle.com/coretec/post/parametrisierte_views_sql_macros

Zu Problem ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported

https://paulzipblog.wordpress.com/2022/11/08/sql-macros-with-clauses-prohibited/