Application Express und die SQL Model Clause

Entwicklung
Application Express und die SQL Model
Clause – Tabellenkalkulation wie mit Excel
Carsten Czarski, ORACLE Deutschland GmbH
Bereits mit Oracle10g wurde die SQL Model Clause eingeführt – sie macht es möglich, im Ergebnis einer SQL-Abfrage so
mit Formeln zu rechnen, wie man es von einem Tabellenkalkulationsprogramm wie Excel gewohnt ist. Dieser Artikel
beschreibt, wie man die SQL Model Clause in einer Application-Express-Anwendung nutzt – und somit in einem Application-Express-Bericht – wie in Excel rechnen kann. Grundlage ist ein HowTo-Dokument aus der deutschsprachigen Seite der
Application-Express-Community.
Ausgangspunkt für das Anwenden der
SQL Model Clause ist ein einfacher
APEX-Bericht auf die bekannte Tabelle
EMP. Grundlage ist die einfache SQLAbfrage select * from emp.
Die SQL Model Clause betrachtet die Ergebnismenge einer Abfrage
wie ein Arbeitsblatt einer Tabellenkalkulation. Nach dem Schlüsselwort
model werden die Dimensionen (dimensions) und die Werte (measures)
deklariert. Die Dimensionen dienen
zum Ansprechen der Werte, die selbst
durch die nachfolgenden Rules verändert werden können. Im Folgenden ist
nun beschrieben, wie ein ApplicationExpress-Bericht so erweitert werden
kann, dass der Endbenutzer Formeln
eingeben kann, die der Bericht automatisch auswertet.
Schritt 1 – APEX-Bericht für SQL
Model-Clause vorbereiten
Man fügt der APEX-Anwendung mit
dem Bericht ein Formular-Eingabefeld
(Textbereich) zur Eingabe der Formeln
(PX_RULES) und eine Schaltfläche zum
Absenden derselben hinzu. Die APEXSeite soll nach Berechnung auf sich
selbst verzweigen.
Die eingegebenen Formeln müssen
in die SQL-Abfrage „integriert“ werden;
daher wird für den Bericht nun ein PL/
SQL-Block, der ein SQL-Kommando zurückgibt als „Quelle“ hinterlegt. Damit
das Ergebnis etwas mehr nach einem
Spreadsheet aussieht, werden die Spalten
mit A,B,C,D,E benannt. Zusätzlich soll
die Spalte F als leere numerische Spalte
(SQL NULL) angelegt werden. Der Quell-
declare
v_sql varchar2(32767);
begin
v_sql := ‚select zeile, a, b, c, d, e, f‘;
v_sql := v_sql || ‚ from emp ‚;
v_sql := v_sql || ‚ model ‚;
v_sql := v_sql || ‚ dimension by (rownum zeile) ‚;
v_sql := v_sql || ‚ measures ( ‚;
v_sql := v_sql || ‚
empno a, ename b, hiredate c, ‚;
v_sql := v_sql || ‚
sal d, comm e, cast(null as number) f ‚;
v_sql := v_sql || ‚ ) ‚;
v_sql := v_sql || ‚ rules upsert (‚;
v_sql := v_sql ||
:PX_RULES;
v_sql := v_sql || ‚)‘;
v_sql := v_sql || ‚order by zeile‘;
return v_sql;
end;
Listing 1: PL/SQL-Quellcode für den APEX-Bericht
code für die Berichtsregion sieht demnach wie folgt aus: (siehe Listing 1):
Der PL/SQL-Code setzt eine SQL-Abfrage zusammen und verwendet dabei die
SQL Model Clause – der Aufbau ist wie
folgt:
•
Die einzelnen Werte im virtuellen
Spreadsheet werden über die DIMENSION angesprochen. Die EMPTabelle hat nur eine Dimension, die
EMPNO; über diese werden alle Zeilen identifiziert. Hier wählen wir jedoch die ROWNUM, also die Zeilennummer des Berichts. Im Gegensatz
zu einem Spreadsheet in Excel können für die SQL Model Clause auch
mehr als zwei Dimensionen verwendet werden.
•
Die MEASURES sind die betroffenen
Werte; vergleichbar mit den Zelleninhalten eines Excel-Spreadsheets.
Bis auf die Zeilennummer sollen alle
Werte mit Formeln verändert werden können.
•
Danach folgen die RULES, die Formeln. Das Schlüsselwort UPSERT
bewirkt, dass die Formeln sowohl
bestehende Zeilen im Ergebnis ändern als auch neue Zeilen hinzufügen können. All dies betrifft nur die
Ergebnismenge des Berichts – nicht
die eigentliche Tabelle.
Die Application-Express-Anwendung
sieht nun in etwa wie in Abbildung 1
aus:
DOAG News Q4-2008 | 37
Entwicklung
f[1] = (d[1] / d[2] - 1) * 100
Wie hoch ist der Durchschnitt aller
Gehälter? d[15] = avg(d)[ANY]
Abbildung 1: Die Daten der Tabelle EMP als normaler Application-Express-Bericht
Eine Zelle wird immer mit ihrem Namen angesprochen – hier A bis F. Die
Navigation entlang der Dimensionen
(hier die Zeilennummer) erfolgt mithilfe der eckigen Klammern. In diesem Beispiel enthalten die eckigen
Klammern nur einen Wert, da in der
Model Clause nur eine Dimension bestimmt wurde. Das Schlüsselwort ANY
in der letzten Formel bedeutet dabei
„alle“; d[ANY] meint also: „alle Zellen
der Spalte D“. Das Ergebnis sollte wie
in Abbildung 2 aussehen.
Gerade, wenn es darum geht, Daten
für Endbenutzer aufzubereiten, kann
die SQL Model Clause ein wertvolles
Hilfsmittel sein. Wichtige Berechnungen, die im ganzen Unternehmen einheitlich erfolgen sollen, lassen sich
hier als RULES hinterlegen.
Eine Anwendung der SQL Model Clause:
Finanzmathematik
Ein Beispiel aus dem Finanzbereich demonstriert noch einige weitere wichtige Merkmale der SQL Model Clause.
Es soll ein Annuitäten-Darlehen berechnet werden. Bei dieser Form des
Darlehens wird monatlich eine feste
Rate gezahlt; insofern sinkt der Zinsanteil jeden Monat, wohingegen der
Tilgungsanteil entsprechend steigt.
Schritt 1 – APEX-Bericht für SQL
Model Clause vorbereiten
Abbildung 2: Application-Express-Bericht mit SQL Model Clause
Schritt 2 – SQL Model Clause
in Aktion: Erste Tests
•
Man gibt nun, durch Komma getrennt,
ein paar Formeln ein. Das Prinzip ist
immer dasselbe:
•
38 | www.doag.org
Man ändert in der zweiten Zeile den
Namen von BLAKE in SCHMIDT:
b[2] = ‚SCHMIDT‘
Man berechnet, um wie viel Prozent das Gehalt von KING über dem
von BLAKE (jetzt SCHMIDT) liegt
Man legt eine neue Seite in der Application-Express-Applikation an. Sie
enthält wiederum das Element PX_
RULES zur Eingabe der Regeln und
einen Bericht, der (im Augenblick)
lediglich eine leere Zeile erzeugt, in
dem er sechs mal die Null (0) von der
Tabelle DUAL selektiert. Der PL/SQLBlock, der als Regionsquelle für den
APEX-Bericht hinterlegt wird, ist in
Listing 2 enthalten.
Die Model Clause bekommt noch
zwei zusätzliche Parameter. Schließlich
muss der Tilgungsplan für das Darlehen solange berechnet werden, bis
der Darlehensbetrag auf Null getilgt
Entwicklung
declare
v_sql varchar2(32767);
begin
v_sql := ‚select zeile, a, b, c, d, e, f‘;
v_sql := v_sql || ‚ from dual ‚;
v_sql := v_sql || ‚ model ‚;
v_sql := v_sql || ‚ dimension by (rownum zeile) ‚;
v_sql := v_sql || ‚ measures (0 a, 0 b, 0 c, 0 d, 0 e, 0 f) ‚;
v_sql := v_sql || ‚ rules upsert iterate (‚;
v_sql := v_sql ||
nvl(:PX_ITCOUNT,1);
v_sql := v_sql ||
‚) until (‚||nvl(:PX_ITUNTIL, ‚0=1‘);
v_sql := v_sql || ‚ ) ‚;
v_sql := v_sql || ‚(‚;
v_sql := v_sql || :PX_RULES;
v_sql := v_sql || ‚)‘;
v_sql := v_sql || ‚order by zeile‘;
return v_sql;
end;
Schritt 2 – SQL Model Clause
in Aktion: Tilgungsplan berechnen
Als Ergebnis liefert der Bericht vorerst
nur eine Zeile mit Nullen zurück. Im
Tilgungsplan sei A die Darlehenssumme, B die Zinsen, C die Tilgung und D
die jeweilige Rate. Es wird monatlich
getilgt. Zur Berechnung gibt man folgende Formeln ein:
1.a[0]=200000,
2.b[ITERATION_
NUMBER]=a[cv(zeile)]*0.06/12,
3.d[ITERATION_NUMBER]=2000,
4.c[ITERATION_NUMBER]=
d[cv(zeile)]-b[cv(zeile)],
5.a[ITERATION_NUMBER+1]=
a[cv(zeile)-1] - c[cv(zeile)-1]
Listing 2: PL/SQL-Quellcode für den APEX-Bericht zum Berechnen eines Tilgungsplans
wurde. Dazu dient das Schlüsselwort
ITERATE. Es nimmt eine Zahl und
zusätzlich optional eine Bedingung
entgegen; hier durch die Elemente PX_ITCOUNT und PX_ITUNTIL
repräsentiert. Man erzeugt diese beiden
Elemente also auf der APEX-Seite zusätzlich als normale Eingabefelder mit
den Labels Iterate für PX_ITCOUNT
und Bedingung für PX_ITUNTIL.
Abbildung 3: SQL Model Clause in Aktion
Die Regeln werden nun maximal
PX_ITCOUNT mal durchgearbeitet.
Wenn vorher die Bedingung PX_ITUNTIL erfüllt wird, bricht die Bearbeitung
ab. Die NVL-Funktionen dienen der
Hinterlegung von Default-Werten für
den Fall, dass die Elemente leer sind.
In diesem Fall soll genau eine Iteration
stattfinden und die Bedingung soll 1=0
(also stets false) sein. Abbildung 3 zeigt
die fertig vorbereitete Seite.
Wichtig bei den Formeln sind das
Schlüsselwort ITERATION_NUMBER
und die Funktion cv(). ITERATION_
NUMBER gibt die laufende Nummer der
aktuellen Iteration zurück, beginnend
mit 0. Während der ersten Iteration
steht ITERATION_NUMBER also für 0
(Null), in der zweiten für 1 und so fort.
cv() kann nur auf der rechten Seite der
Gleichung verwendet werden. Diese
Funktion gibt einfach nur die aktuelle Zeilennummer der Zelle zurück,
die auf der linken Seite angesprochen
wurde. cv() verwendet man also stets
dann, wenn man dieselbe Berechnung
in vielen Zeilen immer wieder durchführen möchten. Damit erklären sich
die Formeln wie folgt:
6. Die anfängliche Darlehenssumme
(A) beträgt 200.000
7. Der Zinssatz ist sechs Prozent; die Zinsen werden für einen Monat berechnet. Die Zinsen (B) in der jeweiligen
Zeile seien gleich dem Darlehensbetrag (A) in der gleichen Zeile multipliziert mit dem 0.06 geteilt durch 12.
Die Rate (D) sei in jeder Zeile gleich
2.000
8. Die Tilgung (C) sei in jeder Zeile
gleich der Rate (D) weniger die Zinsen (B) (in der gleichen Zeile)
9. Der Darlehensbetrag (A) in der
nächsten Zeile (ITERATION_NUMBER+1) sei gleich dem Darlehensbetrag (A) weniger der Tilgung (C) in
der letzten Zeile (cv(zeile) -1).
DOAG News Q4-2008 | 39
Entwicklung
Man gibt die Formeln ein, trägt zunächst ins Feld Iterate eine 100 ein und
betätigt die Schaltfläche. Das Ergebnis
sollte wie in Abbildung 4 aussehen.
Man erhöht die Iterationen (Formularfeld Iterate) noch weiter und blättert
im Bericht solange vor, bis man den
Bereich findet, in dem der Restbetrag
des Darlehens negativ wird. Ziel wäre
es nun, den Bericht hier abbrechen zu
lassen.
Dies geschieht mit der Bedingung
a[ITERATION_NUMBER]<=0; es soll
also so lange gerechnet werden, bis der
Darlehensbetrag auf Null oder darunter sinkt. Dann würde der Tilgungsplan in den meisten Fällen jedoch immer noch mit einem negativen Betrag
enden. Da das Darlehen exakt auf Null
getilgt werden soll, muss die letzte Rate
kleiner als 2000 sein. Die Formel für
die Rate
Abbildung 4: Vorbereitete Seite für den Tilgungsplan
3. d[ITERATION_NUMBER]=2000
wird also geändert in:
3. d[ITERATION_
NUMBER]=least(2000,
a[cv(zeile)]+b[cv(zeile)]),
Die SQL-Funktion LEAST nimmt zwei
oder mehr Ausdrücke entgegen und
gibt den kleinsten zurück. Wenn der
Restbetrag des Darlehens plus Zinsen
also weniger als 2000 ausmacht, wird
die Rate entsprechend reduziert.
Mit dieser Technologie kann man
also beliebige Kalkulationen (nicht
nur) in APEX-Anwendungen aufnehmen. Dabei wird, wie im ersten Beispiel, auf der Basis von Tabellendaten
gerechnet oder, wie im zweiten Beispiel, eine freie Kalkulation durchgeführt. Natürlich wird man den Endanwender in den seltensten Fällen die
Formeln eintippen lassen – dies erfolgte hier vor allem zum Zweck der
Illustration. Von hier ausgehend ist
es jedoch ein Leichtes, Eingabefelder
für die Darlehenssumme, den Zinssatz
und die Rate zu hinterlegen sowie die
Eingaben entsprechend in den Bericht
einzubauen.
Weitere Informationen
Auf den Web-Seiten von Oracle und
im Internet ist umfangreiches Material
auch in deutscher Sprache vorhanden
– hier ein Überblick:
•
•
Oracle Application Express Community
http://www.oracle.com/global/de/community/index.html
Oracle Dokumentation: SQL Model
Clause
http://download.oracle.com/docs/cd/
B28359_01/server.111/b28313/sqlmodel.htm#g1042989
Kontakt:
Carsten Czarski
[email protected]
DOAG intern
Die DOAG ist Ausbildungsbetrieb
Bereits seit 1. September 2008 bildet die DOAG im Beruf „Kaufmann/Kauffrau für Marketingkommunikation“ aus. Die erste Auszubildende ist Julia Kasprzak, die sich in einem aufwendigen Auswahlprozess
aus mehreren Dutzend Bewerbern erfolgreich durchgesetzt hat. Von der Ausbildung bei der DOAG
erwartet sie kreative Anstöße, die ihr einen beruflichen Weg über die Ausbildung hinaus aufzeigen,
und viel praktische Erfahrung, die sie auf diesem Weg weiterbringt. In ihrer Freizeit liest sie viel, geht
gerne auf gesellschaftlich/kulturelle Veranstaltungen (Theater, Musical, Ballett, Kino etc.) und wie die
meisten Menschen sucht sie im Urlaub gern die Sonne. Wir wünschen Julia Kasprzak viel Erfolg in der
Ausbildung bei der DOAG!
40 | www.doag.org