Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
======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!
<code bash>
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"
</code>

Kann nicht im Group by verwendet werden!

<code bash>
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"
</code>


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:
<code sql>
create or replace function customerId
return varchar2
SQL_MACRO(SCALAR)
as

begin
  return 42;
end customerId;

</code>

Aufrufen:
<code sql>
select customerId from dual;

customerId 
---
42

</code>

Was für ein SQL wurde aufgerufen?
Versuchen wir mal ob  DBMS_UTILITY.expand_sql_text die Funktion auflösen kann:
<code>
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
---

</code>

Hmm, das ist nicht ganz das erwartete...


=== Ein komplexeres Bespiel ===

<code sql>
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;

</code>

Die Daten sollen so ausgewertet werden:
<code sql>

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

</code>


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

== Lösung PLSQL nativ ==


<code plsql>
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;
</code>


Aufrufen mit:
<code plsql>
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

</code>


== Lösung SQL Macro ==

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

Das SQL Macro:
<code sql>
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;
/
</code>


Aufrufen:
<code sql>

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

</code>

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

<code sql>

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 ...

</code>



----

==== Tabellen Macro =====

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

Ein erstes einfaches Beispiel, eine Filterbedingung einbauen:
<code sql>
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;

</code>

Abfragen mit:
<code sql>
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"


</code>

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!

<code sql>
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');

</code>

----

====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/