======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
=== Wie sieht aber das erzeugte SQL aus? ===
Anzeigen über **DBMS_UTILITY.expand_sql_text**
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/