Application Express Community
Application Express Community
| Erscheinungsmonat | APEX-Version | Datenbankversion |
| September 2015 | alle | ab 10.1 |
Viele APEX-Komponenten haben eine SQL-Anfrage als Basis. Ob es ein klassischer oder interaktiver Bericht ist, ein Kalender oder ein Diagramm; immer ist eine SQL-Abfrage die Datenquelle. Das bedeutet auch, dass in diesen Komponenten der ganze SQL-Funktionsumfang genutzt werden kann - und das ist einiges ...
In diesem Tipp geht es um die SQL MODEL-Klausel, die es, kurz gesagt, erlaubt, Spreadsheet-Formeln in einer SQL-Abfrage zu verwenden. Damit wird es möglich, einzelne Spalten und Zeilen in einer SQL-Ergebnismenge wie in einer Excel-Formel anzusprechen und ggfs. zu verändern. Das sei am besten anhand eines Beispiels erläutert - Ausgangspunkt ist eine einfache SQL-Abfrage auf die Tabelle EMP.
select * from emp
Die Syntax der SQL MODEL-Klausel ist gewöhnungsbedürftig und erfordert etwas Einarbeitungszeit - belohnt wird man aber mit völlig neuen Möglichkeiten, die ansonsten mit reinen SQL-Queries gar nicht möglich wären. Zuerst ein ganz einfaches Beispiel - was anschließend erläutert wird.
select * from emp
MODEL
DIMENSION BY (empno)
MEASURES (ename, job, sal, comm, hiredate, deptno, mgr)
RULES (
job[7844] = 'APEXDEV'
,sal[7902] = 9876
,comm[7839] = sum(comm)[ANY]
,ename[9000] = 'NOBODY'
);
EMPNO ENAME JOB SAL COMM HIREDATE DEPTNO MGR
----- ---------- --------- ----- ----- ------------------- ------ -----
7369 SMITH CLERK 800 17.12.1980 00:00:00 20 7902
7499 ALLEN SALESMAN 1600 300 20.02.1981 00:00:00 30 7698
7521 WARD SALESMAN 1250 500 22.02.1981 00:00:00 30 7698
7566 JONES MANAGER 2975 02.04.1981 00:00:00 20 7839
7654 MARTIN SALESMAN 1250 1400 28.09.1981 00:00:00 30 7698
7698 BLAKE MANAGER 2850 01.05.1981 00:00:00 30 7839
7782 CLARK MANAGER 2450 09.06.1981 00:00:00 10 7839
7788 SCOTT ANALYST 3000 19.04.1987 00:00:00 20 7566
7839 KING PRESIDENT 5000 2200 17.11.1981 00:00:00 10
7844 TURNER APEXDEV 1500 0 08.09.1981 00:00:00 30 7698
7876 ADAMS CLERK 1100 23.05.1987 00:00:00 20 7788
7900 JAMES CLERK 950 03.12.1981 00:00:00 30 7698
7902 FORD ANALYST 9876 03.12.1981 00:00:00 20 7566
7934 MILLER CLERK 1300 23.01.1982 00:00:00 10 7782
9000 NOBODY
Eine solche Abfrage lässt sich schon als Datenquelle für einen interaktiven oder klassischen APEX-Bericht hernehmen (Abbildung 1). Damit können die Möglichkeiten der MODEL-Klausel vom Entwickler genutzt werden - aufwändiger PL/SQL-Code zum finalen Aufbereiten von Abfrageergebnissen können so mitunter entfallen. Für den Endanwender bliebe es jedoch bei einem statischen Bericht und den bekannten Möglichkeiten des interaktiven Reports.
Abbildung 1: Nutzung der SQL-Model-Klausel in einem APEX-Bericht
Möchte man die Möglichkeiten der SQL MODEL-Klausel auch dem Endanwender zur Verfügung stellen, so braucht es einen Bericht mit dynamischem SQL. Bei einem klassischen Bericht ist das einfach - wählen Sie als Regiontyp Classic Report (based on function) aus und setzen Sie als Regionsquelle einfach einen PL/SQL-Block, der eine SQL-Abfrage zurückliefert, ein. Zur Eingabe der Regeln können APEX-Formularelemente verwendet werden. Achten Sie bei solchen, sehr dynamischen, Abfragemöglichkeiten aber auf die Anwendungssicherheit (SQL Injection) - letztlich geben Sie ihrem Nutzer damit sehr viel Freiheit, auch komplexe (und vielleicht auch von Ihnen ungewollte) SQL-Anfragen zu formulieren.
Im folgenden beschreiben wir ein sehr einfaches Beispiel, das es auch dem Endanwender erlauben würde, die SQL MODEL-Klausel zu nutzen. Es basiert darauf, dass der Anwender die Formeln der SQL Model-Klausel in eine Textarea eingibt und diese dann in die SQL-Abfrage integriert werden. Als Report nehmen sie, wie gesagt, einen Classic Report (based on function). Tragen Sie als Regionsquelle des statischen Berichts folgende SQL-Abfrage ein.
declare
l_sql varchar2(32767) :=
'select * from emp
MODEL
DIMENSION BY (empno)
MEASURES (ename, job, sal, comm, hiredate, deptno, mgr)
RULES (#RULES#)';
begin
return replace(l_sql, '#RULES#', v('P2_RULES'));
end;
Legen Sie dann eine zweite Region mit einem Textarea-Element namens P2_RULES an. Erzeugen Sie dann einen Button; bei Klick soll die Berichtsregion per Dynamic Action aktualisiert werden (Refresh). Vergessen Sie nicht, P2_RULES in der Berichtsregion unter Page Items to Submit einzutragen. Ihre Seite sollte dann etwa wie in Abbildung 2 aussehen.
Abbildung 2: SQL MODEL-Klausel in APEX: Der Endanwender kann Formeln eingeben
In Abbildung 2 wurden folgende Formeln eingegeben.
ename[9000] = 'SMITH', sal[ANY] = sal[cv()] * 2, ename[ANY] = 'APEX'
Die zweite Formel in Abbildung 2, sal[ANY] = sal[cv()] * 2, zeigt, dass die MODEL-Klausel auch das relative Ansprechen von Werten erlaubt: SAL[ANY] spricht dabei alle Zeilen der Ergebnismenge an - zugewiesen wird das Gehalt der gleichen Zeile sal[cv()], multipliziert mit 2. Damit kann man, mit einer einzigen Formel, viele Werte auf einmal verändern.
Die Referenzierung kann auch etwas komplexer sein und "in andere Zeilen springen". Probieren Sie mal die folgende Formel aus - alle CLERK, SALESMAN und ANALYSTS bekommen das Gehalt ihrer Manager, die Gehälter der Manager und von KING bleiben unverändert. Man sieht schön, wie das Gehalt mit sal[mgr[cv()]] aus der Zeile des jeweiligen Managers angezogen wird. In den Zuweisungen kann der ganze SQL Sprachumfang genutzt werden.
sal[ANY] =
case
when job[cv()] in ('CLERK','ANALYST','SALESMAN') then sal[mgr[cv()]]
else sal[cv()]
end
Damit diese Formel funktioniert, muss der SQL-Query in der Berichtsdefiniton noch das Schlüsselwort AUTOMATIC ORDER hinzugefügt werden. Das bestimmt, dass die Datenbank die Abhängigkeiten der Formeln zueinander selbst bestimmt und die Regeln in der korrekten Reihenfolge ausführt. Alternativ dazu führt SEQUENTIAL ORDER (Default) die Regeln in der Reihenfolge aus, in der sie angegeben werden.
declare
l_sql varchar2(32767) :=
'select * from emp
MODEL
DIMENSION BY (empno)
MEASURES (ename, job, sal, comm, hiredate, deptno, mgr)
RULES AUTOMATIC ORDER (#RULES#)';
begin
return replace(l_sql, '#RULES#', v('P2_RULES'));
end;
Mit den Schlüsselwort ITERATE können Formeln auch mehrfach ausgeführt werden; dabei kann entweder eine feste Anzahl Iterationen hinterlegt werden oder die Formeln werden solange ausgeführt, bis eine bestimmte Bedingung erfüllt ist. Dies sei wiederum anhand eines Beispiels gezeigt. Erzeugen Sie die gleiche APEX-Anwenungsseite nochmals (am Besten als Kopie Ihrer bereits vorhandenen Seite) und nehmen Sie dann folgende Änderungen vor.
declare
l_sql varchar2(32767) :=
'select zeile, a, b, c, d, e, f from dual
MODEL
DIMENSION BY (rownum as zeile)
MEASURES (0 as a, 0 as b, 0 as c, 0 as d, 0 as e, 0 as f)
RULES SEQUENTIAL ORDER ITERATE (#ITERATE#) UNTIL (#ITERATE_UNTIL#) (#RULES#)';
begin
return
replace(
replace(
replace(
l_sql, '#RULES#', v('P3_RULES')
), '#ITERATE#', nvl(v('P3_ITERATE'),1)
), '#ITERATE_UNTIL#', nvl(v('P3_ITERATE_UNTIL'), '1=0')
);
end;
Wenn Sie die Seite dann starten, sollte sie in etwa wie in Abbildung 3 aussehen.
Abbildung 3: APEX-Seite für SQL MODEL Klausel mit Iterationen
Sie können entweder eine konkrete Zahl im Eingabefeld Iterations eintragen, dann werden die Formeln exakt so oft ausgeführt. Die Variable ITERATION_NUMBER sagt Ihnen, in der wievielten Iteration Sie sich befinden (beginnt bei Null). Alternativ können Sie eine Bedingung eintragen - ist diese erfüllt, so werden die Iterationen abgebrochen. Ein erster Test: Tragen Sie die folgende Formel bei Rules ein und nehmen Sie 10 Iterationen.
a[ITERATION_NUMBER + 2] = a[ITERATION_NUMBER + 1] + 10
Das Ergebnis sollte in etwa wie in Abbildung 4 aussehen.
Abbildung 4: Erster, einfacher Test der ITERATE-Klausel
Auf dieser Grundlage lassen sich Simulationen rechnen - mit APEX und einem SQL Report. Die folgenden Rules implementieren einen Tilgungsplan für einen Ratenkredit. Die Formeln würde man in einem Spreadsheet analog programmieren.
a[0] = 200000, b[ITERATION_NUMBER] = a[cv(zeile)] * 0.06 / 12, d[ITERATION_NUMBER] = least(2000, a[cv(zeile)] + b[cv(zeile)]), c[ITERATION_NUMBER] = d[cv(zeile)] - b[cv(zeile)], a[ITERATION_NUMBER + 1] = a[cv(zeile)-1] - c[cv(zeile) - 1]
Legen Sie maximal 400 Iterationen und a[ITERATION_NUMBER + 1] <= 0 als Abbruchbedingung fest. Abbildung 5 zeigt das Ergebnis.
Abbildung 5: Tilgungsplan rechnen mit der SQL Model Klausel
Diese Rechnung können Sie noch verfeinern - so ein Tilgungsplan ist nur ein Beispiel für beliebige Simulationen, die Sie nun - in einem APEX-Bericht - rechnen können. Die folgenden Regeln erweitern das Beispiel: Nach 24 Iterationen erhöht sich der Zinssatz von 6 auf 7 Prozent und bei der 36. Iteration findet ein "Sondertilgung" statt.
a[0] = 200000, e[zeile < 24] = 6, e[zeile > 23] = 7, f[36] = 20000, b[ITERATION_NUMBER] = a[cv(zeile)] * e[cv(zeile)] / 1200, d[ITERATION_NUMBER] = least(2000, a[cv(zeile)] + b[cv(zeile)]), c[ITERATION_NUMBER] = d[cv(zeile)] - b[cv(zeile)], a[ITERATION_NUMBER + 1] = a[cv(zeile)-1] - c[cv(zeile) - 1] - nvl(f[cv(zeile) - 1],0)
Auch mit dem Zufall lässt sich simulieren. Die Funktion DBMS_RANDOM.VALUE , welche eine Zufallszahl liefert, ist auch hier nutzbar. Wenn Sie mit der folgenden Formel bei 25 Iterationen durchführen, simulieren Sie quasi ein "Roulettespiel" ...
a[ITERATION_NUMBER ] = round(dbms_random.value(0,36))
Abbildung 6: Zufalls-Simulation mit der SQL MODEL Klausel
So werden "Monte-Carlo" Simulationen möglich - die "Fachlichkeit" kann mit weiteren Rules ergänzt werden - das alles in einer APEX-Anwendung mit einem APEX-Bericht. Hierzu ebenfalls ein Beispiel (ohne Anspruch auf fachliche Korrektheit). Es soll ein Geldautomat simuliert werden.
Eine einzige Abhebung lässt sich mit folgender Formel simulieren.
a[1] = case round(dbms_random.value(1,10)) when 1 then 100 when 2 then 100 when 3 then 200 when 4 then 200 when 5 then 200 when 6 then 300 when 7 then 300 when 8 then 400 when 9 then 500 when 10 then 50 end
Abbildung 7: Einmalige Simulation: Geldautomat
Wenn man 1.000 Iterationen festlegt und den Wert jeweils aufsummiert; dann ergibt sich die Simulation von 1.000 Abhebungen.
a[1] = nvl(a[1],0) + case round(dbms_random.value(1,10)) when 1 then 100 when 2 then 100 when 3 then 200 when 4 then 200 when 5 then 200 when 6 then 300 when 7 then 300 when 8 then 400 when 9 then 500 when 10 then 50 end
Abbildung 8: Simulation: 1000 Abhebungen am Geldautomat
Nun geht es daran, eine solche 1000er Simulation auch noch mehrfach durchzuführen. Zwar kann man Iterationen nicht schachteln, die MODEL-Klausel bietet uns aber noch ein zusätzliches Feature an: Auf der linken Seite einer Formel kann eine FOR-Schleife angegeben werden. Dann wird diese Formel - innerhalb einer Iteration - für jeden Schleifendurchlauf einmal ausgeführt - und mehrere Zellen können mit einer Formel verändert werden. Das wollen wir uns zunutze machen. Die 1000er Simulation soll 20 Mal ausgeführt werden - das funktioniert wie folgt:
a[for zeile from 1 to 20 increment 1] =
nvl(a[cv(zeile)],0) +
case round(dbms_random.value(1,10))
when 1 then 100 when 2 then 100
when 3 then 200 when 4 then 200 when 5 then 200
when 6 then 300 when 7 then 300
when 8 then 400 when 9 then 500
when 10 then 50
end
Abbildung 9: Simulation "1000 Abhebungen am Geldautomat" - 20 mal
Das letztes Beispiel führt dieses Szenario noch weiter. Angenommen, man möchte auch noch wissen, wie oft die einzelnen Beträge jeweils abgehoben wurden - dazu müssen die jeweiligen Ausprägungen der Zufallszahl gezählt werden. Die folgenden Regeln setzen das um - allerdings brauchen Sie nun noch eine Iteration - denn mit einer FOR-Loop werden zunächst alle 20.000 Werte in "Zellen" gespeichert und danach erfolgt die "Auswertung". Das alles findet im Rahmen der SQL-Ausführung statt.
a[for zeile from 100001 to 120000 increment 1] =
case round(dbms_random.value(1,10))
when 1 then 100 when 2 then 100
when 3 then 200 when 4 then 200 when 5 then 200
when 6 then 300 when 7 then 300
when 8 then 400 when 9 then 500
when 10 then 50
end,
a[for zeile from 1 to 20 increment 1] =
sum(a)[zeile between 99001+cv(zeile)*1000 and 100000 + cv(zeile) * 1000],
b[for zeile from 1 to 20 increment 1] =
sum(case when a=50 then 1 else 0 end)[zeile between 99001+cv(zeile)*1000 and 100000 + cv(zeile) * 1000],
c[for zeile from 1 to 20 increment 1] =
sum(case when a=100 then 1 else 0 end)[zeile between 99001+cv(zeile)*1000 and 100000 + cv(zeile) * 1000],
d[for zeile from 1 to 20 increment 1] =
sum(case when a=200 then 1 else 0 end)[zeile between 99001+cv(zeile)*1000 and 100000 + cv(zeile) * 1000],
e[for zeile from 1 to 20 increment 1] =
sum(case when a=300 then 1 else 0 end)[zeile between 99001+cv(zeile)*1000 and 100000 + cv(zeile) * 1000],
f[for zeile from 1 to 20 increment 1] =
sum(case when a=500 then 1 else 0 end)[zeile between 99001+cv(zeile)*1000 and 100000 + cv(zeile) * 1000]
Abbildung 10: Simulation - mit Detailaufstellung, welche Beträge abgehoben wurden
In den 20 Ergebniszeilen finden Sie letztlich die Ergebnisse der 20 Simulationen; dies wurde allein mit einer SQL-Abfrage realisiert - die SQL MODEL-Klausel bietet dem Anwender wirklich unglaubliche Möglichkeiten.
In der Praxis werden wohl die wenigsten Anwendungen so aussehen, wie dieses Beispiel - das einfache Übernehmen der Nutzereingabe in eine SQL-Query birgt ein nicht unerhebliches SQL Injection-Risiko: Die Nutzereingaben müssen auf jeden Fall validiert werden. Ein Ansatz dazu könnte so aussehen.
create or replace function validate_modelclause_rules(
p_rules_string in varchar2
) return varchar2 is
l_rules_array apex_application_global.vc_arr2;
l_valid_rules_array apex_application_global.vc_arr2;
l_b1 pls_integer := 0;
l_b2 pls_integer := 0;
l_q pls_integer := 0;
l_ok boolean := false;
l_valid_rules_cnt pls_integer := 0;
begin
l_rules_array := apex_util.string_to_table(p_rules_string, '##');
if l_rules_array.count != 0 then
for i in l_rules_array.first..l_rules_array.last loop
l_b1 := 0; l_b2 := 0; l_q := 0;
l_ok := true;
for p in 1..length(l_rules_array(i)) loop
if substr(l_rules_array(i),p,1) = '(' then l_b1 := l_b1 + 1;
elsif substr(l_rules_array(i),p,1) = ')' then l_b1 := l_b1 - 1;
elsif substr(l_rules_array(i),p,1) = '[' then l_b2 := l_b2 + 1;
elsif substr(l_rules_array(i),p,1) = ']' then l_b2 := l_b2 - 1;
elsif substr(l_rules_array(i),p,1) = '''' then l_q := l_q + 1;
end if;
if l_b1 < 0 or l_b2 < 0 then l_ok := false; end if;
end loop;
if l_b1 != 0 or l_b2 != 0 or remainder(l_q, 2) != 0 then l_ok := false; end if;
if l_ok then
l_valid_rules_cnt := l_valid_rules_cnt + 1;
l_valid_rules_array(l_valid_rules_cnt) := l_rules_array(i);
end if;
end loop;
return apex_util.table_to_string(l_valid_rules_array, ',');
else
return null;
end if;
end validate_modelclause_rules;
Diese Funktion müsste nun wie folgt in die SQL Query integriert werden - passen Sie den PL/SQL-Block in der Report-Quelle wie folgt an.
declare
l_sql varchar2(32767) :=
'select zeile, a, b, c, d, e, f from dual
MODEL
DIMENSION BY (rownum as zeile)
MEASURES (0 as a, 0 as b, 0 as c, 0 as d, 0 as e, 0 as f)
RULES UPSERT SEQUENTIAL ORDER ITERATE (#ITERATE#) UNTIL (#ITERATE_UNTIL#) (#RULES#)';
begin
return
replace(
replace(
replace(
l_sql, '#RULES#', validate_modelclause_rules(v('P3_RULES'))
), '#ITERATE#', nvl(v('P3_ITERATE'),1)
), '#ITERATE_UNTIL#', nvl(v('P3_ITERATE_UNTIL'), '1=0')
);
end;
In der APEX-Anwendung müssen Sie die einzelnen Regeln nun durch ## trennen.
Abbildung 11: Leicht veränderte Formeleingabe nach Einführung der Validierungsfunktion
Die eingegebenen Regeln werden nun daraufhin geprüft, ob jede geöffnete runde oder eckige Klammer auch geschlossen wird, ob nicht etwa eine Klammer zuerst geschlossen (und damit die MODEL-Klausel terminiert wird) und ob jedes Quote-Zeichen (') richtig maskiert wird. Das ist nicht zwingend eine vollständige Validierung; deckt aber einige Fälle ab. Dennoch bleibt die freie Eingabe von Formeln eher ein Fall für (wenige) Power-User. In den meisten Fällen dürfte es besser sein, nur Eckparameter für eine Simulation zu erfragen und die Formeln ansonsten schon von Seiten der Anwendungsentwicklung fest zu hinterlegen. Alles in allem bietet die SQL MODEL-Klausel sehr mächtige Möglichkeiten zur Arbeit mit den Daten einer SQL-Abfrage an - und ein APEX-Bericht kann so sehr viel mehr sein als die einfache Darstellung von Daten.