Anbindung einer Oracle-DB an das WWW mittels PL/SQL: Ein kurzer Einstieg
Zur Anbindung einer DB an eine WWW-Applikation gibt es eine Reihe von Möglichkeiten. Im folgenden soll eine dieser Möglichkeiten anhand eines einfachen Beispiels kurz skizziert werden.
Die grundlegende Vorgangsweise (stark vereinfacht):
- Ein WWW-Browser nimmt Kontakt zu einem WWW-Server (in unserem Fall: Oracle Web Application Server) auf und gibt an, welche Seite geliefert werden soll.
- Der Server untersucht den angegebenen Pfad der Seite, wobei sich grundsätzlich drei Fälle unterscheiden lassen:
- Der Pfad entspricht einer gespeicherten Datei. In diesem Fall wird dieses Datei an den Browser retourniert.
- Der Pfad entspricht einem (speziellen) Programm. Der Server exekutiert das Programm und liefert die vom Programm erzeugte Ausgabe an den Browser.
- Der Pfad entspricht keiner lesbaren Datei, weil die Datei nicht existiert, Zugriffsrechte verletzt werden, etc. Der Server retourniert eine entsprechende Fehlermeldung an den Browser.
Wir konzentrieren uns im folgenden auf Variante 2. Das erwähnte spezielle Programm, das vom Server (nicht unbedingt direkt) exekutiert wird, ist in unserem Fall ein PL/SQL Programm, genauer eine stored procedure. Stored procedures werden wie normale Daten von einer Datenbankbenutzerin in der Datenbank gespeichert und bei Bedarf abgerufen. Das Erstellen kann zb mittels SQL*Plus erfolgen:
SQL> CREATE OR REPLACE PROCEDURE hugo IS
2 BEGIN
3 null;
4 END HUGO;
5 /
Der abschließende Schrägstrich / (entspricht in etwa RUN) führt das eingegebene Kommando durch (speichert also die Prozedur hugo in der Datenbank, bzw. ersetzt sie, falls schon vorhanden). Die Prozedur hugo (deren Rumpf aus einem einzigen Leerbefehl besteht) wird dabei in einen Zwischencode übersetzt und in der Datenbank (in beiden Formen) abgelegt. Den Abschluss dieser Tätigkeit meldet SQL*Plus mit
Procedure created.
sind bei der Übersetzung Fehler aufgetreten, so wird stattdessen
Warning: Procedure created with compilation errors.
gemeldet, die Fehler können mit
SQL> SHOW ERRORS
angezeigt werden. Der Aufruf der Prozedur erfolgt zb mittels SQL*Plus
SQL> EXEC hugo
PL/SQL procedure successfully completed.
Damit nun eine solche Prozedur vom WWW-Server benutzt werden kann, muss dieser mit der Datenbank Kontakt aufnehmen. Dies erfolgt über einen sogenannten Agent, der sich mit Hilfe eines DAD (Database Access Descriptor) bei der Datenbank anmeldet (und dabei unter anderem die Datenbank, den Benutzer und dessen Kennwort angibt). Ist der Kontakt hergestellt, wird die genannte Prozedur exekutiert und die erzeugte Ausgabe an den Browser retourniert.
Wir wollen uns nun von der Prozedur hugo lösen und ein einfaches Datenbankbeispiel betrachten. Es werden Lehrveranstaltungen, Studentinnen und Lehrerinnen verwaltet, das ganze ist eine starke vereinfachte Variante von Aufgabe 1. Zuerst die Tabellendefinitionen:
CREATE TABLE lv (
lv# NUMBER(10),
lvnr NUMBER(6),
studienjahr NUMBER(4) NOT NULL,
semester CHAR(1) NOT NULL,
typ CHAR(2) NOT NULL,
stunden NUMBER(2) NOT NULL,
titel VARCHAR2(50) NOT NULL,
leiterin# NUMBER(10)
);
CREATE TABLE person (
person# NUMBER(10),
name VARCHAR2(30) NOT NULL,
vorname VARCHAR2(30),
svnr NUMBER(4),
geburtsdatum DATE
);
CREATE TABLE lehrerin (
person# NUMBER(10),
personalnr NUMBER(4),
gehalt NUMBER(6)
);
CREATE TABLE studentin (
person# NUMBER(10),
matrikelnr NUMBER(7),
studium NUMBER(3)
);
CREATE TABLE lvteilnahme (
studentin# NUMBER(10),
lv# NUMBER(10),
note NUMBER(1)
);
Desweiteren sind folgende Einschränkungen definiert:
ALTER TABLE lv ADD CONSTRAINT lv_PK PRIMARY KEY (lv#);
ALTER TABLE lv ADD CONSTRAINT lv_lvnr UNIQUE (lvnr, studienjahr, semester);
ALTER TABLE person ADD CONSTRAINT person_PK PRIMARY KEY (person#);
ALTER TABLE person ADD CONSTRAINT person_svnr UNIQUE (svnr,geburtsdatum);
ALTER TABLE lehrerin ADD CONSTRAINT lehrerin_PK PRIMARY KEY (person#);
ALTER TABLE lehrerin ADD CONSTRAINT lehrerin_personalnr UNIQUE (personalnr);
ALTER TABLE studentin ADD CONSTRAINT studentin_PK PRIMARY KEY (person#);
ALTER TABLE studentin ADD CONSTRAINT studentin_matrikelnr UNIQUE (matrikelnr);
ALTER TABLE lvteilnahme ADD CONSTRAINT lvteilnahme_PK PRIMARY KEY (studentin#, lv#);
ALTER TABLE lv ADD CONSTRAINT lv_leiterin
FOREIGN KEY (leiterin#) REFERENCES lehrerin;
ALTER TABLE lehrerin ADD CONSTRAINT lehrerin_person
FOREIGN KEY (person#) REFERENCES person;
ALTER TABLE studentin ADD CONSTRAINT studentin_person
FOREIGN KEY (person#) REFERENCES person;
ALTER TABLE lvteilnahme ADD CONSTRAINT lvteilnahme_lv
FOREIGN KEY (lv#) REFERENCES lv;
ALTER TABLE lvteilnahme ADD CONSTRAINT lvteilnahme_studentin
FOREIGN KEY (studentin#) REFERENCES studentin;
Unser erstes Ziel ist eine Prozedur, die eine Liste aller gespeicherten Lehrveranstaltungen mit den Namen der entsprechenden Leiterinnen ausgibt. In SQL*Plus könnte eine solche Abfrage wie folgt aussehen:
SELECT lvnr, studienjahr, semester, titel, typ, stunden, name, vorname
FROM lv, person
WHERE lv.leiterin# = person.person#
ORDER BY studienjahr, semester DESC;
Die Ausgabe in SQL*Plus ist dann eine mehr oder weniger formatierte Liste der selektierten Tupel. Hier liegt auch schon das erste Problem: PL/SQL ist eine prozedurale Programmiersprache, die mit den Tupelmengen, die von SELECT-Ausdrücken erzeugt werden, nicht ohne weiteres zurechtkommt. Der Ausweg heißt cursor. Ein Cursor erlaubt es, auf eine Menge von Tupeln (der Reihe nach) zuzugreifen. Die Unterstützung dieses Konzeptes ist in PL/SQL recht komfortabel. Unsere Prozedur hat grundsätzlich folgende Struktur:
CREATE OR REPLACE PROCEDURE listlv1 IS
CURSOR Clv IS
SELECT lvnr, studienjahr, semester, titel, typ, stunden, name, vorname
FROM lv, person
WHERE lv.leiterin# = person.person#
ORDER BY studienjahr, semester DESC;
BEGIN
FOR Rlv IN Clv LOOP
-- Verarbeitung der einzelnen Tupel
END LOOP;
END listlv1;
Der Cursor Clv wird vor dem Prozedurrumpf definiert, das SQL-Statement ist dabei äquivalent zu jenem von oben. Im Rumpf wird der Cursor dann geöffnet, die einzelnen Tupel verarbeitet und geschlossen. Dies erfolgt in diesem Beispiel mit Hilfe einer Cursor-FOR-Schleife, das explizite Öffnen und Schließen des Cursors entfällt dabei. Im Kopf der FOR-Schleife wird eine Tupelvariable Rlv definiert. Mit dieser Variable kann innerhalb der Schleife auf die einzelnen Attribute der selektierten Tupel zugegriffen werden.
Außerdem wollen wir unsere Prozedur listlv1 in ein package einbetten. Ein Package ist eine Sammlung von Prozeduren und weiteren Objekten, die in etwa einem MODULE in Modula-2 entspricht (mit starken Einschränkungen auch einer Klasse in C++). Der Aufruf einer Prozedur eines Package erfolgt mittels packagename.prozedurname. Die Definition eines Package erfolgt in zwei Teilen: Im ersten Teil (Deklarationsteil) wird die Schnittstelle des Package angegeben (also welche Prozeduren etc. von außen benutzt werden können). Im zweiten Teil (Implementationsteil) werden diese und unter Umständen weitere Prozeduren implementiert. Diese Trennung entspricht in etwa der Unterscheidung zwischen Klassendefinition und Klassenimplementation in C++.
Die Ausgabe zum WWW-Server (und in der Folge zum Browser) erfolgt zB über die Prozedur print (Kurzform p) im Package htp. Die Prozedur htp.p erwartet (zum Beispiel) einen String als Parameter und gibt diesen aus. Im Package htp findet sich eine Unzahl von Prozeduren zur Ausgabe von HTML-formatiertem Text, deren Verwendung unter Umständen komfortabler ist als htp.p. Praktisch - vor allem zur Ausgabe von Daten aus der Datenbank - ist mitunter die Prozedur htp.prints (bzw. htp.ps), die jene Zeichen, die in HTML eine besondere Bedeutung haben, durch entsprechende HTML-Codes ersetzt.
Die Prozedur listlv1 (im Package beispiel01):
CREATE OR REPLACE PACKAGE beispiel01 AS
PROCEDURE listlv1;
END beispiel01;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY beispiel01 AS
PROCEDURE listlv1 IS
CURSOR Clv IS
SELECT lvnr, studienjahr, semester, titel, typ, stunden, name, vorname
FROM lv, person
WHERE lv.leiterin# = person.person#
ORDER BY studienjahr, semester DESC;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.listlv1</TITLE></HEAD><BODY>');
FOR Rlv IN Clv LOOP
htp.ps(Rlv.studienjahr || Rlv.semester);
htp.ps(Rlv.lvnr || ' ' || Rlv.titel);
htp.ps('(' || Rlv.stunden || Rlv.typ || ')');
htp.ps(Rlv.vorname || ' ' || Rlv.name);
htp.p('<BR>');
END LOOP;
htp.p('</BODY>');
END listlv1;
END beispiel01;
/
SHOW ERRORS
Das ganze wird sinnvollerweise in einer Datei abgespeichert und mittels @ in SQL*Plus eingelesen. Wie können wir unsere Prozedur jetzt über das WWW ansprechen? Dazu muss mit einem Browser die entsprechende URL angesteuert werden. Wie sieht diese URL aber aus? Unser WWW-Server läuft auf db-labor.cs.univie.ac.at. Zudem muss ein sogenannter virtueller Pfad zu unserer Prozedur angegeben werden; in unserem Fall plsqlapp. Danach ist nur noch der Namen der Prozedur anzugeben (und das Package, falls die Prozedur Teil eines Package ist).
Da im Rahmen der Übungen alle den selben Agent (er heißt de) benutzen, ist noch eine Kleinigkeit zu beachten: wie schon erwähnt, benutzt der Agent einen DAD, um mit der Datenbank Verbindung aufzunehmen. Dieser DAD legt unter anderem den User fest, der für den Datenbankzugriff benutzt wird, im Fall des Agent de ist dies der User - richtig! - de. Da sich das von uns erstellte Package beispiel01 mit der Prozedur listlv1 aber nicht im Schema des Users de befindet, sondern im Schema das Users, der das Package erstellt hat (das ist hier jan), ist auch das Schema beim Aufruf anzugeben. Außerdem muss dem User de erst die Berechtigung zur Benutzung dieses Package eingeräumt werden, da natürlich grundsätzlich jeder User nur seine eigenen Packages benutzen darf. Dies erfolgt in SQL*Plus mittels
GRANT EXECUTE ON beispiel01 TO de;
Mit diesem GRANT erhält der User de die Berechtigung zur Exekution aller im Package beispiel01 definierten Prozeduren (genauer: jener Prozeduren, die im Deklarationsteil angeführt sind und somit öffentlich zugänglich sind).
Anmerkung: Es ist nicht notwendig, dem User de auch die Berechtigung zum Lesen der Tabellen lv und person einzuräumen. Durch die Berechtigung zum Exekutieren der Prozedur listlv1 können diese Tabellen gelesen werden, allerdings eben NUR mit dieser Prozedur. Ein (direkter) Lesezugriff des Users de auf diese Tabellen (etwa mit Hilfe von SELECT in SQL*Plus) ist nach wie vor nicht erlaubt.
Der komplette Pfad zum Ausprobieren lautet somit: «http://db-labor.cs.univie.ac.at/plsqlapp/jan.beispiel01.listlv1».
Um den PL/SQL-Code kurz und übersichtlich zu halten, werden wir in dieser Einführung auf Verzierungen, Behübschungen und Benutzerführung generell verzichten. Bei der Projektarbeit sind derlei Dinge natürlich strengstens erlaubt…
Als nächstes soll die Prozedur listlv1 erweitert werden: Beim Aufruf soll angegeben werden können, von welchem Studienjahr und welchem Semester Lehrveranstaltungen ausgegeben werden sollen. Zu diesem Zweck erhält die Prozedur Parameter:
PROCEDURE listlv2 (sj IN INTEGER DEFAULT NULL, se IN VARCHAR2 DEFAULT NULL)
sj ist der Name des ersten Parameters, IN gibt an, dass es sich um einen Eingangsparameter handelt (weitere Varianten sind OUT und IN OUT). INTEGER ist der Typ, und die (optionale) Angabe DEFAULT NULL legt fest, dass für diesen Parameter beim Aufruf nicht unbedingt ein Wert angegeben werden muss. Wird kein Wert angegeben, wird der Parameter mit NULL initialisiert (natürlich ist auch jeder andere vom Typ her passende Defaultwert hier erlaubt). Dieser Mechanismus ist uns ja schon von C++ bekannt. Neu ist hier, dass beim Aufruf beliebige Parameter angegeben werden können, und nicht, wie in C++, die ersten n. Dies wird ermöglicht durch eine erweiterte Aufrufsyntax. Die von C++ bekannten Aufrufvarianten zu obiger Definition wären
exec beispiel01.listlv2(1997,'S');
exec beispiel01.listlv2(1997);
exec beispiel01.listlv2;
Weggelassen können bei dieser Aufrufmethode nur Parameter am Ende der Liste. In PL/SQL ist es aber auch möglich, beispielsweise nur für den zweiten Parameter einen Wert anzugeben, und zwar durch explizite Angabe des Parameternamens:
exec beispiel01.listlv2(se => 'S');
Dies funktioniert natürlich für alle anderen obigen Varianten auch. Apropos Ähnlichkeiten zu C++: auch das Überladen von Prozeduren ist möglich.
Zurück zu unserer neuen Prozedur listlv2. Sie muss natürlich auch im Definitionsteil angegeben werden:
CREATE OR REPLACE PACKAGE beispiel01 AS
PROCEDURE listlv1;
PROCEDURE listlv2 (sj IN INTEGER DEFAULT NULL, se IN VARCHAR2 DEFAULT NULL);
END beispiel01;
zudem muss das SELECT-Statement ein wenig erweitert werden:
PROCEDURE listlv2 (sj IN INTEGER DEFAULT NULL, se IN VARCHAR2 DEFAULT NULL) IS
CURSOR Clv IS
SELECT lvnr, studienjahr, semester, titel, typ, stunden, name, vorname
FROM lv, person
WHERE lv.leiterin# = person.person#
AND (studienjahr = sj OR sj IS NULL)
AND (semester = se OR se IS NULL)
ORDER BY studienjahr, semester DESC;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.listlv2</TITLE></HEAD><BODY>');
FOR Rlv IN Clv LOOP
htp.p(Rlv.studienjahr || Rlv.semester);
htp.p(Rlv.lvnr || ' ' || Rlv.titel);
htp.p('(' || Rlv.stunden || Rlv.typ || ')');
htp.p(Rlv.vorname || ' ' || Rlv.name);
htp.p('<BR>');
END LOOP;
htp.p('</BODY>');
END listlv2;
Beim Aufruf können somit Studienjahr und/oder Semester oder nichts von beiden angegeben werden. Wie kommen nun die Parameter vom Browser zu unserer Prozedur? Die Parameter werden, getrennt durch ein ?, an die URL angehängt und voneinander durch ein & getrennt. Diese Parameterliste wird vom WWW-Server an die PL/SQL-Cartridge weitergegeben und von dieser so aufbereitet, dass die Prozedur mit den richtigen Parametern aufgerufen wird. Einige konkrete Beispiele:
Der letzte Aufruf hat keine Parameter und somit auch keine Einschränkungen, das Ergebnis entspricht daher dem der Prozedur listlv1.
Als nächstes wollen wir eine Prozedur zur Ausgabe der Teilnehmerinnen einer Lehrveranstaltung erstellen. Die Grundstruktur dieser Prozedur ist jener von listlv sehr ähnlich: Wir definieren einen Cursor für die SQL-Abfrage und arbeiten die selektierten Tupel in einer Cursor-FOR-Schleife ab. Zusätzlich soll, als minimales Zugeständnis an optische Erfordernisse, eine HTML-Tabelle zum Einsatz kommen:
PROCEDURE listtn1 (lvn IN INTEGER) IS
CURSOR Ctn IS
SELECT matrikelnr, name, vorname, studium
FROM lvteilnahme, person, studentin
WHERE lvteilnahme.lv# = lvn
AND lvteilnahme.studentin# = studentin.person#
AND studentin.person# = person.person#;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.listtn1</TITLE></HEAD><BODY>');
htp.p('<TABLE BORDER=1>');
FOR Rtn IN Ctn LOOP
htp.p('<TR>');
htp.p('<TD>' || Rtn.matrikelnr);
htp.p('<TD>' || Rtn.name || ' ' || Rtn.vorname);
htp.p('<TD>' || Rtn.studium);
END LOOP;
htp.p('</TABLE>');
htp.p('</BODY>');
END listtn1;
Dass die Prozedur auch im Deklarationsteil des Package angegeben werden muss, wird ab jetzt nicht mehr erwähnt. Als Parameter erwartet die Prozedur die (interne) Nummer der Lehrveranstaltung. Der Aufruf
liefert die Teilnehmerinnen der Lehrveranstaltung mit der internen Nummer (lv#) 2. Wird beim Aufruf eine Lehrveranstaltung angegeben, die keine Teilnehmerinnen hat, wie in
so ist das Ergebnis eine leere Tabelle. Das gleiche Ergebnis lässt sich erzielen, wenn man eine nicht existierende Lehrveranstaltung angibt:
In beiden Fällen liefert die Abfrage, für die der Cursor Ctn definiert ist, keine Tupel, die entsprechende FOR-Schleife wird daher kein einziges Mal durchlaufen. Als Erweiterung der Prozedur soll nun eine Überschrift ausgegeben werden, die Titel, Nummer, etc. der Lehrveranstaltung angibt. Die entsprechenden Daten werden natürlich wieder mit Hilfe von SQL aus der Datenbank gewonnen. Da die lv# jede Lehrveranstaltung eindeutig identifiziert, wird die entsprechende Abfrage immer höchstens ein Tupel liefern. In solchen Fällen ist es oft sinnvoll, anstelle eines Cursors eine andere Variante des eingebetteten SELECT-Statements zu benutzen: SELECT INTO. Bei dieser Variante muss für jeden Ausdruck der SELECT-Liste eine passende Variable angegeben werden, in der der entsprechende Wert bei der Abfrage abgelegt wird:
PROCEDURE listtn2 (lvn IN INTEGER) IS
CURSOR Ctn IS
SELECT matrikelnr, name, vorname, studium
FROM lvteilnahme, person, studentin
WHERE lvteilnahme.lv# = lvn
AND lvteilnahme.studentin# = studentin.person#
AND studentin.person# = person.person#;
titel lv.titel%TYPE;
lvnr lv.lvnr%TYPE;
studienjahr lv.studienjahr%TYPE;
semester lv.semester%TYPE;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.listtn2</TITLE></HEAD><BODY>');
SELECT titel, lvnr, studienjahr, semester
INTO titel, lvnr, studienjahr, semester
FROM lv
WHERE lv# = lvn;
htp.p('<H2>' || lvnr || ' ' || titel);
htp.p('(' || studienjahr || semester || ')</H2>');
htp.p('<TABLE BORDER=1>');
FOR Rtn IN Ctn LOOP
htp.p('<TR>');
htp.p('<TD>' || Rtn.matrikelnr);
htp.p('<TD>' || Rtn.name || ' ' || Rtn.vorname);
htp.p('<TD>' || Rtn.studium);
END LOOP;
htp.p('</TABLE>');
htp.p('</BODY>');
END listtn2;
Zur Definition von "passenden" Variablen stellt PL/SQL eine recht komfortable Möglichkeit zur Verfügung: anstatt einen konkreten Typ wie INTEGER anzugeben, kann man mit Hilfe von %TYPE den Typ eines anderen Datenobjektes ermitteln und zur Definition benutzen. Aufgrund obiger Definition hat also die Variable titel den Typ des Attributes titel der Tabelle lv. Ob es elegant oder störend, hilfreich oder verwirrend ist, wenn die Variablen die gleichen Namen wie die Tabellenattribute tragen, darüber sind die Meinungen geteilt. Es ist jedenfalls erlaubt, aber nicht notwendig.
Die neue Prozedur liefert nun im Normalfall die Liste mit einer Überschrift:
im Falle von leeren Lehrveranstaltungen wird lediglich die Überschrift ausgegeben:
Ein Problem tritt jetzt allerdings auf, wenn eine nichtexistente Lehrveranstaltung angegeben wird:
Wie der Fehlermeldung zu entnehmen ist, tritt der Oracle-Fehler ORA-01403 auf (no data found). Dies deshalb, weil das SELECT INTO Statement kein passendes Tupel mit lv# = 0 gefunden hat und somit die angegebenen Variablen nicht mit Werten belegen konnte. Ein SELECT INTO muss also immer genau ein Tupel liefern, andernfalls tritt ein Fehler auf.
Es gilt allgemein als unhöflich (und wirkt auch ein wenig ungeschickt), die Benutzerin mit derartigen Fehlermeldungen zu konfrontieren, wir müssen also irgendeine Art von Fehlerbehandlung vorsehen. Abgesehen von der - an sich im Trend liegenden - Variante, die Fehlermeldung mit einer kleinen bunten animierten Grafik zu versehen, um der Benutzerin über die gröbste Enttäuschung hinwegzuhelfen, bieten sich grundsätzlich zwei Möglichkeiten an:
- Der Fehler wird von vornherein vermieden: in unserem Fall bedeutet dies, dass das SELECT INTO erst dann ausgeführt wird, wenn sichergestellt ist, dass es genau ein Tupel liefert.
- Der Fehler wird, wenn er auftritt, "abgefangen". Dies wird in PL/SQL durch sogenannte exceptions ermöglicht.
Mit Exceptions wollen wir uns später noch beschäftigen, vorerst wählen wir die Fehlervermeidung. Vor dem fehleranfälligen SELECT INTO ist also zu klären, ob die Lehrveranstaltung tatsächlich existiert:
PROCEDURE listtn3 (lvn IN INTEGER) IS
CURSOR Ctn IS
SELECT matrikelnr, name, vorname, studium
FROM lvteilnahme, person, studentin
WHERE lvteilnahme.lv# = lvn
AND lvteilnahme.studentin# = studentin.person#
AND studentin.person# = person.person#;
titel lv.titel%TYPE;
lvnr lv.lvnr%TYPE;
studienjahr lv.studienjahr%TYPE;
semester lv.semester%TYPE;
n INTEGER;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.listtn3</TITLE></HEAD><BODY>');
SELECT count(*) INTO n
FROM lv
WHERE lv# = lvn;
IF n = 0 THEN
htp.p('<H2>Fehler: Lehrveranstaltung existiert nicht!</H>');
ELSE
SELECT titel, lvnr, studienjahr, semester
INTO titel, lvnr, studienjahr, semester
FROM lv
WHERE lv# = lvn;
htp.p('<H2>' || lvnr || ' ' || titel);
htp.p('(' || studienjahr || semester || ')</H2>');
htp.p('<TABLE BORDER=1>');
FOR Rtn IN Ctn LOOP
htp.p('<TR>');
htp.p('<TD>' || Rtn.matrikelnr);
htp.p('<TD>' || Rtn.name || ' ' || Rtn.vorname);
htp.p('<TD>' || Rtn.studium);
END LOOP;
htp.p('</TABLE>');
END IF;
htp.p('</BODY>');
END listtn3;
Jetzt klappen alle drei Fälle:
Anmerkung: der Fehler kann allerdings unter bestimmtem Umständen nach wie vor auftreten. Welche Umstände sind das?
Als nächstes wollen wir die Lehrveranstaltungsliste mit der Teilnehmerinnenliste verknüpfen. Zu diesem Zweck bekommt in der Lehrveranstaltungsliste jede Zeile einen weiteren Eintrag, der als HTML-Link zur jeweiligen Teilnehmerinnenliste fungiert - allerdings nur dann, wenn die Lehrveranstaltung auch Teilnehmerinnen hat. Zu diesem Zweck benötigen wir für jede Lehrveranstaltung die jeweilige Anzahl von Teilnehmerinnen, die mit Hilfe eines weiteren SELECT-Statements in der FOR-Schleife ermittelt wird. Die so ermittelte Teilnehmerinnenanzahl wird in der Liste gleich mit ausgegeben. Außerdem nutzen wir die Gelegenheit, und verpacken auch diese Liste in eine HTML-Tabelle samt Überschrift:
PROCEDURE listlv3 (sj IN INTEGER DEFAULT NULL, se IN VARCHAR2 DEFAULT NULL) IS
CURSOR Clv IS
SELECT lv#, lvnr, studienjahr, semester, titel, typ, stunden, name, vorname
FROM lv, person
WHERE lv.leiterin# = person.person#
AND (studienjahr = sj OR sj IS NULL)
AND (semester = se OR se IS NULL)
ORDER BY studienjahr, semester DESC;
n INTEGER;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.listlv3</TITLE></HEAD><BODY>');
htp.p('<H2>Lehrveranstaltungen</H2>');
htp.p('<TABLE BORDER=1>');
FOR Rlv IN Clv LOOP
htp.p('<TR>');
htp.p('<TD>' || Rlv.studienjahr || Rlv.semester);
htp.p('<TD>' || Rlv.lvnr || '<TD>' || Rlv.titel);
htp.p('<TD>' || Rlv.stunden || Rlv.typ);
htp.p('<TD>' || Rlv.vorname || ' ' || Rlv.name);
SELECT count(*) INTO n FROM lvteilnahme
WHERE lv# = Rlv.lv#;
htp.p('<TD>' || n);
IF n > 0 THEN
htp.p('<A HREF=jan.beispiel01.listtn3?lvn=' || Rlv.lv# || '>Teilnehmerinnen</A>');
ELSE
htp.p('Teilnehmerinnen');
END IF;
END LOOP;
htp.p('</TABLE>');
htp.p('</BODY>');
END listlv3;
Über diese Lehrveranstaltungsliste
lässt sich nun - sofern Teilnehmerinnen vorhanden sind - durch Anklicken von 'Teilnehmerinnen' die jeweilige Teilnehmerinnenliste abrufen.
Anmerkung: Obwohl wir in listlv3 sicherstellen, dass listtn3 nur mit gültigen Lehrveranstaltungen aufgerufen wird, ist die Fehlerbehandlung in listtn3 keineswegs überflüssig. Schließlich lässt es sich kaum verhindern, dass eine Benutzerin dennoch irgendwelche ungültigen Werte direkt über eine URL in ihrem Browser eingibt. Dieser Umstand ist bei derartigen Prozeduren generell zu beachten!
Bisher haben wir uns ausschließlich mit der Ausgabe von in der Datenbank gespeicherten Daten beschäftigt. Wesentlich für eine Datenbankapplikation ist allerdings auch die Erfassung neuer Daten und die Speicherung derselben in der Datenbank. Unser nächstes Ziel ist es daher, eine Möglichkeit zur Erfassung von Lehrveranstaltungen zu schaffen.
In HTML steht zur Eingabe von Daten in Formularen das FORM-Element zur Verfügung. Die Verarbeitung eines Formulars besteht im allgemeinen aus zwei Schritten.
- Gibt eine Benutzerin in ihrem Browser eine URL an, die ein Formular enthält (dies kann wie immer eine statische Seite oder eine von einem Programm dynamisch erzeugte Seite sein), so stellt der Browser das Formular mit den entsprechenden Eingabefeldern dar. Die Benutzerin gibt daraufhin die gewünschten Daten ein und schließt die Eingabe durch Betätigung eines bestimmten Button (SUBMIT) ab.
- Der Browser sammelt daraufhin alle eingegebenen Daten und übermittelt sie in einem speziellen Protokoll an jene URL, die bei der Definition des Formulars mit dem ACTION-Attribut angegeben wurde.
Entsprechend benötigen wir für unseren Fall zwei Dinge:
- Eine HTML-Seite, die ein Formular zur Eingabe einer Lehrveranstaltung enthält bzw. eine Prozedur, die eine solche HTML-Seite erzeugt. Wir wählen die zweite Variante, da wir neben den verschiedenen Eingabefeldern auch eine Auswahl der zur Verfügung stehenden Lehrveranstaltungsleiterinnen anbieten wollen (diese Auswahl wird dynamisch aus der Datenbank erzeugt).
- Eine Prozedur, die die von der Benutzerin eingegebenen und vom Browser übermittelten Daten entgegennimmt und sie in der Datenbank ablegt. Die URL dieser Prozedur wird im FORM-Definition angegeben. Die Übermittlung der Daten ist in PL/SQL recht komfortabel gelöst: In einem HTML-Formular wird jedem Element mit dem NAME-Attribut ein Name zugeordnet. Unsere PL/SQL-Prozedur muss nun für jedes Formularelement einen Parameter mit gleichem Namen und passendem Typ haben. Die PL/SQL-Cartridge übernimmt die Daten vom WWW-Server und ruft die Prozedur dann mit den passenden Parameterwerten auf.
Betrachten wir zunächst die Prozedur zur Erzeugung des Form. Sie definiert einen Cursor zur Selektion aller vorhandenen Lehrerinnen, für jede Lehrerin wird in einer Cursor-FOR-Schleife ein OPTION-Element erzeugt. Außerdem erhält jedes Formularelement durch das NAME-Attribute einen eindeutigen Namen:
PROCEDURE lvform1 IS
CURSOR Cl IS
SELECT lehrerin.person#, name, vorname
FROM person, lehrerin
WHERE person.person# = lehrerin.person#
ORDER BY name;
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.lvform1</TITLE></HEAD><BODY>');
htp.p('<H2>Neue Lehrveranstaltung anlegen</H2>');
htp.p('<FORM ACTION=jan.beispiel01.addlv1 METHOD=post>');
htp.p('<TABLE BORDER=1>');
htp.p('<TR><TD>');
htp.p('LV-Nr. <INPUT TYPE=text SIZE=6 MAXLENGTH=6 NAME=nlvnr>');
htp.p('Typ <INPUT TYPE=text SIZE=2 MAXLENGTH=2 NAME=ntyp>');
htp.p('Stunden <INPUT TYPE=text SIZE=2 MAXLENGTH=2 NAME=nstunden>');
htp.p('<TR><TD>');
htp.p('Studienjahr <INPUT TYPE=text SIZE=4 MAXLENGTH=4 NAME=nstudienjahr>');
htp.p('<INPUT TYPE=radio NAME=nsemester VALUE=W CHECKED>Wintersemester');
htp.p('<INPUT TYPE=radio NAME=nsemester VALUE=S>Sommersemester');
htp.p('<TR><TD>');
htp.p('Titel <INPUT TYPE=text SIZE=40 MAXLENGTH=50 NAME=ntitel>');
htp.p('<TR><TD>');
htp.p('Leiterin <SELECT NAME=nleiterin>');
FOR Rl IN Cl LOOP
htp.p('<OPTION VALUE=' || Rl.person# || '>' || Rl.name || ' ' || Rl.vorname);
END LOOP;
htp.p('</SELECT>');
htp.p('<TR><TD ALIGN=center>');
htp.p('<INPUT TYPE=submit VALUE=Fertig>');
htp.p('</TABLE>');
htp.p('</FORM>');
htp.p('</BODY>');
END lvform1;
Als ACTION wird die Prozedur addlv1 angegeben. Wie bereits erwähnt muss diese für jedes benannte Formularelement einen Parameter haben:
PROCEDURE addlv1 (nlvnr IN INTEGER, ntyp IN VARCHAR2, nstunden IN INTEGER, nstudienjahr IN INTEGER, nsemester IN VARCHAR2, ntitel IN VARCHAR2, nleiterin IN INTEGER) IS
BEGIN
-- INSERT hier
END addlv1;
Der Rumpf der Prozedur enthält dann ein INSERT-Statement zum Anlegen der neuen Lehrveranstaltung. Jede Lehrveranstaltung hat eine (interne) eindeutige Nummer im Attribut lv#. Zur Erzeugung solcher eindeutiger Nummern stellt Oracle die Sequence zur Verfügung:
CREATE SEQUENCE seq;
erzeugt (zB in SQL*Plus) eine Sequence namens seq. Mittels der Pseudo-Attribute seq.NEXTVAL und seq.CURRVAL kann auf diese Sequence zugegriffen werden: jede Verwendung von seq.NEXTVAL liefert einen neuen eindeutigen Wert, seq.CURRVAL liefert den (in der gleichen Session) zuletzt von seq.NEXTVAL gelieferten Wert.
Das INSERT in addlv1 könnte also wie folgt aussehen:
INSERT INTO lv (lv#, lvnr, typ, stunden, studienjahr, semester, titel, leiterin#)
VALUES (seq.NEXTVAL, nlvnr, ntyp, nstunden, nstudienjahr, nsemester, ntitel, nleiterin);
Damit ist sichergestellt, dass jede mit addlv1 erzeugte Lehrveranstaltung eine eindeutige Nummer erhält. Diese Vorkehrung nützt jedoch nichts, wenn eine Benutzerin auf anderen Wegen Lehrveranstaltungen einfügt, zB mittels INSERT in SQL*Plus. Abhilfe schafft hier ein Trigger: Trigger sind spezielle Prozeduren, die vom DBMS automatisch aufgerufen werden, wenn vordefinierte Situationen eintreten. So lässt sich etwa ein Trigger definieren, der immer dann exekutiert wird, wenn ein Tupel in die Tabelle lv eingefügt wird. Dieser Trigger belegt dann das Attribut lv# mit dem von der Sequence erzeugten eindeutigen Wert, unabhängig davon, von wem und wie das Tupel in die Tabelle eingefügt wurde:
CREATE OR REPLACE TRIGGER lv_lv#
BEFORE INSERT ON lv FOR EACH ROW
DECLARE
lvseq lv.lv#%TYPE;
BEGIN
SELECT seq.NEXTVAL INTO lvseq FROM dual;
:new.lv# := lvseq;
END;
/
Das ganze wird wiederum in einer Datei gespeichert und in SQL*Plus exekutiert.
Das INSERT in addlv1 kann nun vereinfacht werden, da der Wert von lv# ohnehin vom Trigger lv_lv# beim Einfügen erzeugt wird:
PROCEDURE addlv1 (nlvnr IN INTEGER, ntyp IN VARCHAR2, nstunden IN INTEGER, nstudienjahr IN INTEGER, nsemester IN VARCHAR2, ntitel IN VARCHAR2, nleiterin IN INTEGER) IS
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.addlv1</TITLE></HEAD><BODY>');
INSERT INTO lv (lvnr, typ, stunden, studienjahr, semester, titel, leiterin#)
VALUES (nlvnr, ntyp, nstunden, nstudienjahr, nsemester, ntitel, nleiterin);
htp.p('<H2>Starke Leistung!</H2>');
htp.p('Die Lehrveranstaltung wurde erfolgreich angelegt.');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
END addlv1;
Anmerkung: wenn PL/SQL-Code das Zeichen & enthält, so kann dies zu Problemen führen, wenn der Code mit SQL*Plus bearbeitet wird. SQL*Plus verwendet in der Standard-Einstellung das Zeichen & zur Variablensubstitution. Die Variablensubstitution lässt sich mit
SET DEFINE OFF
abschalten.
Zurück zu unserer Prozedur: leider können noch eine Reihe von Problemen im Zusammenhang mit dem Einfügen einer Lehrveranstaltung auftreten. Die lvnr muss pro Studienjahr/Semester eindeutig sein (UNIQUE Einschränkung), eine Reihe von Attributen sind mit NOT NULL Einschränkung definiert, die angegebene Leiterin muss auch existieren (FOREIGN KEY Einschränkung). Wenn beim Einfügen eine dieser Einschränkungen verletzt wird oder ein anderer Fehler auftritt, wird die Benutzerin wiederum mit der schon bekannten Oracle-Fehlermeldung konfrontiert.
Zur Behandlung derartiger Fälle bietet PL/SQL das Konzept der Exceptions. Wird etwa eine UNIQUE-Einschränkung verletzt (Oracle-Fehler ORA-00001), so wird die vordefinierte Exception dup_val_on_index ausgelöst. Diese kann abgefangen werden, indem ein Exception-Handler für diese Exception installiert wird. Die Exception-Handler für einen Block werden mit dem Schlüsselwort EXCEPTION definiert:
PROCEDURE addlv1 (nlvnr IN INTEGER, ntyp IN VARCHAR2, nstunden IN INTEGER, nstudienjahr IN INTEGER, nsemester IN VARCHAR2, ntitel IN VARCHAR2, nleiterin IN INTEGER) IS
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.addlv1</TITLE></HEAD><BODY>');
INSERT INTO lv (lvnr, typ, stunden, studienjahr, semester, titel, leiterin#)
VALUES (nlvnr, ntyp, nstunden, nstudienjahr, nsemester, ntitel, nleiterin);
htp.p('<H2>Starke Leistung!</H2>');
htp.p('Die Lehrveranstaltung wurde erfolgreich angelegt.');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
EXCEPTION
WHEN dup_val_on_index THEN
htp.p('<H2>FEHLER:</H2> Eine UNIQUE-constraint wurde verletzt');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
END addlv1;
Wird innerhalb des Blockes (also zB beim INSERT) die Exception dup_val_on_index ausgelöst, so wird die Abarbeitung des Blocks abgebrochen und mit dem Exception-Handler für diese Exception fortgesetzt. Nach der Abarbeitung des Exception-Handlers wird der Block verlassen.
Allerdings gibt es nicht für alle Oracle-Fehler auch vordefinierte Exceptions. Wollen wir etwa auf diese Weise den Fehler abfangen, der auftritt, wenn in eine mit NOT NULL Einschränkung definierte Spalte (explizit oder implizit) ein NULL-Wert eingetragen wird (ORA-01400), so müssen wir die entsprechende Exception erst definieren. Dies erfolgt in zwei Schritten: zuerst muss die Exception selbst definiert werden, danach wird sie an den gewünschten Oracle-Fehler "gebunden":
not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT (not_null_verletzt, -1400);
Ist die Exception derart definiert, kann im EXCEPTION-Teil eines Blockes ein Handler installiert werden:
WHEN not_null_verletzt THEN
htp.p('<H2>FEHLER:</H2> Für ein NOT NULL-Attribut wurde kein Wert angegeben');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
Mit Hilfe der Pseudo-Exception OTHERS ist es möglich, auch für "unvorhergesehene" Exceptions einen Handler zu installieren. Der OTHERS-Handler wird (falls vorhanden) für all jene Exceptions aufgerufen, für die kein eigener Handler installiert ist. Mittels SQLCODE und SQLERRM ist es möglich, auf den Oracle-Fehlercode und eine entsprechende Fehlermeldung zuzugreifen.
Somit ergibt sich folgendes Bild:
PROCEDURE addlv1 (nlvnr IN INTEGER, ntyp IN VARCHAR2, nstunden IN INTEGER, nstudienjahr IN INTEGER, nsemester IN VARCHAR2, ntitel IN VARCHAR2, nleiterin IN INTEGER) IS
not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT (not_null_verletzt, -1400);
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.addlv1</TITLE></HEAD><BODY>');
INSERT INTO lv (lvnr, typ, stunden, studienjahr, semester, titel, leiterin#)
VALUES (nlvnr, ntyp, nstunden, nstudienjahr, nsemester, ntitel, nleiterin);
htp.p('<H2>Starke Leistung!</H2>');
htp.p('Die Lehrveranstaltung wurde erfolgreich angelegt.');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
EXCEPTION
WHEN dup_val_on_index THEN
htp.p('<H2>FEHLER:</H2> Eine UNIQUE-constraint wurde verletzt');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
WHEN not_null_verletzt THEN
htp.p('<H2>FEHLER:</H2> Für ein NOT NULL-Attribut wurde kein Wert angegeben');
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
WHEN OTHERS THEN
htp.p('<H2>FEHLER:</H2>');
htp.p('SQLCODE: ' || SQLCODE);
htp.p('<BR>SQLERRM: ' || SQLERRM);
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
END addlv1;
Im allgemeinen wird man eine Mischform zwischen Fehlervermeidung und Fehlerabfangen wählen, außerdem wären die Eingaben genauer zu analysieren, um der Benutzerin sinnvolle Hinweise auf mögliche Fehlerquellen in der Eingabe geben zu können. Die OTHERS Variante eignet sich in dieser Form bestenfalls für die Testphase bzw. für echte Systemfehler.
Die Tatsache, dass das Auftreten einer Exception die Abarbeitung des Blocks abbricht und diese nicht mehr fortgesetzt wird, hat allerdings auch Nachteile. Soll die Verarbeitung auch im Fehlerfall weitergehen, bzw. in jedem Fall gewisse "Aufräumarbieten" durchgeführt werden (wie in unserem Beispiel die beiden Aufrufe der Prozedur htp.p zur Ausgabe eines Hinweises und zum Beenden des BODY-Elementes), so müssen die entsprechenden Anweisungen in jedem Exception-Handler wiederholt werden.
Mitunter kann es daher sinnvoll sein, jene Anweisungen, die Exceptions auslösen können, in einem eigenen Block (mit eigener EXCEPTIONS-Abteilung) zu kapseln:
PROCEDURE addlv1 (nlvnr IN INTEGER, ntyp IN VARCHAR2, nstunden IN INTEGER, nstudienjahr IN INTEGER, nsemester IN VARCHAR2, ntitel IN VARCHAR2, nleiterin IN INTEGER) IS
not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT (not_null_verletzt, -1400);
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.addlv1</TITLE></HEAD><BODY>');
BEGIN
INSERT INTO lv (lvnr, typ, stunden, studienjahr, semester, titel, leiterin#)
VALUES (nlvnr, ntyp, nstunden, nstudienjahr, nsemester, ntitel, nleiterin);
htp.p('<H2>Starke Leistung!</H2>');
htp.p('Die Lehrveranstaltung wurde erfolgreich angelegt.');
EXCEPTION
WHEN dup_val_on_index THEN
htp.p('<H2>FEHLER:</H2> Eine UNIQUE-constraint wurde verletzt');
WHEN not_null_verletzt THEN
htp.p('<H2>FEHLER:</H2> Für ein NOT NULL-Attribut wurde kein Wert angegeben');
WHEN OTHERS THEN
htp.p('<H2>FEHLER:</H2>');
htp.p('SQLCODE: ' || SQLCODE);
htp.p('<BR>SQLERRM: ' || SQLERRM);
END;
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
END addlv1;
Wichtig: Im EXCEPTION-Teil eines Blocks können nur Exceptions behandelt werden, die innerhalb dieses Blocks auftreten. Eine häufige Klasse von Exceptions kann auf diese Weise nicht abgefangen werden: Exceptions, die bei der Parameterübergabe aufgrund von Datentyp-Unverträglichkeiten auftreten, treten per definitionem außerhalb der Prozedur auf, und können daher nur in jenem Block behandelt werden, in dem die Prozedur aufgerufen wurde. Bei Aufrufen via http ist dies schwierig, da der aufrufende Code von uns nicht verändert werden kann. Abhilfe: Parameter von Prozeduren, die via http aufgerufen werden, immer als VARCHAR2 übergeben und Datentyp-Umwandlungen innerhalb der Prozedur durchführen (am einfachsten per Zuweisung). Tritt bei der Umwandlung/Zuweisung eine Exception auf, kann diese wie gehabt im EXCEPTION-Teil abgefangen werden. Um den Code kurz zu halten, wird diese Grundregel bei den gezeigten Beispielen nicht beachtet.
Unsere Prozedur ist fürs erste einmal fertig:
Nach dem Drücken des SUBMIT-Elementes ("Fertig") übermittelt der Browser die eingegebenen Daten an den WWW-Server, die PL/SQL-Cartridge ruft in weiterer Folge die Prozedur addlv1 mit entsprechenden Parameterwerten auf. Nach gelungenem Einfügen sollte die neue Lehrveranstaltung in der «Lehrveranstaltungsliste» aufscheinen.
Gestärkt von diesem Erfolg wollen wir auch eine Möglichkeit zum Ändern von bereits gespeicherten Lehrveranstaltungen schaffen. Da das entsprechende Formular wohl ziemlich ähnlich aussehen wird, liegt es nahe, unsere beiden Prozeduren lvform1 und addlv1 so zu adaptieren, dass sie beide Fälle (Einfügen und Ändern) abdecken. Folgende Vorkehrungen sind dafür zu treffen:
- In den von lvform2 erzeugten Formularelementen sind die aktuellen Daten der Lehrveranstaltung als Vorgabewerte auszugeben. Dies erfolgt im allgemeinen mit Hilfe des VALUE-Attributes der einzelnen Formularelemente. Ausnahmen: Radio-Buttons (Attribut CHECKED bei dem entsprechenden INPUT-Element) und Selection (Attribut SELECTED bei dem entsprechenden OPTION-Element).
- Die Prozedur addlv2 muss erkennen, ob eine Lehrveranstaltung neu angelegt oder aber geändert werden soll. Entsprechend wird dann ein INSERT oder eben ein UPDATE durchgeführt.
Die Frage, die zu klären ist: woran erkennt die Prozedur addlv2, ob eine Lehrveranstaltung "neu" ist? Das einzige Attribut, das von Änderungen immer unberührt bleibt, ist unsere interne Nummer lv#. Diese muss also offenbar an addlv2 mitübergeben werden, addlv2 kann dann feststellen, ob eine Lehrveranstaltung mit dieser lv# bereits existiert (UPDATE) oder nicht (INSERT). Das Problem dabei: addlv2 wird i.A. nicht "direkt" über eine URL aufgerufen, sondern vom Browser, wenn die Benutzerin das SUBMIT-Element betätigt. Und dann werden nur jene Parameter übergeben, für die es ein entsprechendes FORM-Element gibt. Für das Attribut lv# gibt es kein FORM-Element, und das ist durchaus in unserem Sinn, da dieses Element für die Benutzerin nicht sichtbar und schon gar nicht änderbar sein soll. Genau für solche Fälle gibt es eine eigene Variante des INPUT-Elementes, nämlich jenes vom Typ hidden. Wir erzeugen also in unserer Form ein zusätzliches, verstecktes INPUT-Element, und weisen ihm mittels VALUE-Attribut den Wert der lv# zu. Das Element wird vom Browser nicht dargestellt, der Wert wird aber mit den Werten aller anderen FORM-Elemente als Parameter an die Prozedur addlv2 übergeben.
Die Prozedur lvform2 erhält also einen (optionalen) Parameter: wird dieser nicht angegeben, hat er den Wert NULL oder verweist er auf eine nichtexistente Lehrveranstaltung, so verhält sich lvform2 wie lvform1 und stellt eine leere Form zur Verfügung. Andernfalls werden die Daten der angegebenen Lehrveranstaltung aus der Datenbank gelesen und in den Feldern des Formulars zur Verfügung gestellt. Außerdem spendieren wir unserer Maske eine RESET-Taste zum Wiederherstellen der ursprünglichen Werte:
PROCEDURE lvform2 (lvn IN INTEGER DEFAULT NULL) IS
CURSOR Cl IS
SELECT lehrerin.person#, name, vorname
FROM person, lehrerin
WHERE person.person# = lehrerin.person#
ORDER BY name;
n INTEGER;
titel lv.titel%TYPE DEFAULT NULL;
lvnr lv.lvnr%TYPE DEFAULT NULL;
studienjahr lv.studienjahr%TYPE DEFAULT NULL;
semester lv.semester%TYPE DEFAULT NULL;
typ lv.typ%TYPE DEFAULT NULL;
stunden lv.stunden%TYPE DEFAULT NULL;
leiterin lv.leiterin#%TYPE DEFAULT NULL;
lvnn lv.lv#%TYPE;
BEGIN
SELECT count(*) INTO n FROM lv
WHERE lv# = lvn;
IF n = 0 THEN
lvnn := NULL;
ELSE
lvnn := lvn;
SELECT lvnr, studienjahr, semester, typ, stunden, titel, leiterin#
INTO lvnr, studienjahr, semester, typ, stunden, titel, leiterin
FROM lv
WHERE lv# = lvnn;
END IF;
htp.p('<HTML><HEAD><TITLE>beispiel01.lvform2</TITLE></HEAD><BODY>');
htp.p('<H2>Lehrveranstaltungdaten</H2>');
htp.p('<FORM ACTION=jan.beispiel01.addlv2 METHOD=post>');
htp.p('<INPUT TYPE=hidden NAME=lvn VALUE=' || lvnn || '>');
htp.p('<TABLE BORDER=1>');
htp.p('<TR>');
htp.p('<TD>');
htp.p('LV-Nr. <INPUT TYPE=text SIZE=6 MAXLENGTH=6 NAME=nlvnr VALUE=' || lvnr || '>');
htp.p('Typ <INPUT TYPE=text SIZE=2 MAXLENGTH=2 NAME=ntyp VALUE=' || typ || '>');
htp.p(' Stunden <INPUT TYPE=text SIZE=2 MAXLENGTH=2 NAME=nstunden VALUE=' || stunden || '>');
htp.p('<TR>');
htp.p('<TD>');
htp.p(' Studienjahr <INPUT TYPE=text SIZE=4 MAXLENGTH=4 NAME=nstudienjahr VALUE=' || studienjahr || '>');
IF semester = 'W' THEN
htp.p('<INPUT TYPE=radio NAME=nsemester VALUE=W CHECKED>Wintersemester');
htp.p('<INPUT TYPE=radio NAME=nsemester VALUE=S>Sommersemester');
ELSE
htp.p('<INPUT TYPE=radio NAME=nsemester VALUE=W>Wintersemester');
htp.p('<INPUT TYPE=radio NAME=nsemester VALUE=S CHECKED>Sommersemester');
END IF;
htp.p('<TR>');
htp.p('<TD>');
htp.p('Titel <INPUT TYPE=text SIZE=40 MAXLENGTH=50 NAME=ntitel VALUE="' || titel || '">');
htp.p('<TR>');
htp.p('<TD>');
htp.p('Leiterin <SELECT NAME=nleiterin>');
FOR Rl IN Cl LOOP
IF Rl.person# = leiterin THEN
htp.p('<OPTION VALUE=' || Rl.person# || ' SELECTED>' || Rl.name || ' ' || Rl.vorname);
ELSE
htp.p('<OPTION VALUE=' || Rl.person# || '>' || Rl.name || ' ' || Rl.vorname);
END IF;
END LOOP;
htp.p('</SELECT>');
htp.p('<TR>');
htp.p('<TD ALIGN=center>');
htp.p('<INPUT TYPE=reset VALUE=Reset>');
htp.p('<INPUT TYPE=submit VALUE=Fertig>');
htp.p('</TABLE>');
htp.p('</FORM>');
htp.p('</BODY>');
END lvform2;
Die Prozedur addlv2 erhält einen zusätzlichen Parameter (lvn) zur Übergabe der lv#. Hat dieser Parameter den Wert NULL (bzw. wurde nicht angegeben) oder referenziert er eine nicht-existente Lehrveranstaltung (dies wird mit dem ersten SELECT INTO geklärt), so verhält sich addlv2 wie addlv1: eine neue Lehrveranstaltung wird eingefügt. Andernfalls wird die Lehrveranstaltung mit der angegebenen lv# geändert.
PROCEDURE addlv2 (lvn IN INTEGER, nlvnr IN INTEGER, ntyp IN VARCHAR2, nstunden IN INTEGER, nstudienjahr IN INTEGER, nsemester IN VARCHAR2, ntitel IN VARCHAR2, nleiterin IN INTEGER) IS
n INTEGER;
not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT (not_null_verletzt, -1400);
BEGIN
htp.p('<HTML><HEAD><TITLE>beispiel01.addlv2</TITLE></HEAD><BODY>');
SELECT count(*) INTO n FROM lv
WHERE lv# = lvn;
BEGIN
IF n = 0 THEN
INSERT INTO lv (lvnr, typ, stunden, studienjahr, semester, titel, leiterin#)
VALUES (nlvnr, ntyp, nstunden, nstudienjahr, nsemester, ntitel, nleiterin);
htp.p('<H2>Starke Leistung!</H2>');
htp.p('Die Lehrveranstaltung wurde erfolgreich angelegt.');
ELSE
UPDATE lv SET lvnr = nlvnr, typ = ntyp, stunden = nstunden,
studienjahr = nstudienjahr, semester = nsemester, titel = ntitel,
leiterin# = nleiterin
WHERE lv# = lvn;
htp.p('<H2>Starke Leistung!</H2>');
htp.p('Die Lehrveranstaltung wurde erfolgreich geändert.');
END IF;
EXCEPTION
WHEN dup_val_on_index THEN
htp.p('<H2>FEHLER:</H2> Eine UNIQUE-constraint wurde verletzt');
WHEN not_null_verletzt THEN
htp.p('<H2>FEHLER:</H2> Für ein NOT NULL-Attribut wurde kein Wert angegeben');
WHEN OTHERS THEN
htp.p('<H2>FEHLER:</H2>');
htp.p('SQLCODE: ' || SQLCODE);
htp.p('<BR>SQLERRM: ' || SQLERRM);
END;
htp.p('<P>Betätigen Sie die Back-Taste Ihres Browsers, um zur Eingabemaske zurückzugelangen');
htp.p('</BODY>');
END addlv2;
Um das Formular zum Ändern einer Lehrveranstaltung einzubinden, können wir etwa einen weiteren Link in der «Liste der Lehrveranstaltungen» unterbringen.
Letzte Änderung: 19.08.2008, 16:57 | 6640 Worte