18.2 SQL-Anweisungen in Bezug auf
DB-Prozeduren
18.3 Fehlerbehandlung und Meldungen
In
diesem Kapitel werden
INGRES-Datenbank-Prozeduren
beschrieben. Nach der
Einführung, in der
Bedeutung und
Nutzen von DB-Prozeduren für
die Datenbankanwendungen
erläutert werden, werden alle
in Bezug auf DB-Prozeduren
existierenden INGRES-SQL-Anweisungen definiert und
erklärt.
Am Ende des Kapitels werden einige praktische Beispiele mit
DB-Prozeduren gezeigt.
DB-Prozeduren sind eine
Art von Funktionen.
Sie werden
vom
DBA (oder vom Benutzer) erstellt und genauso wie alle
anderen Datenbankobjekte (Tabellen,
Views usw.) in einer
Datenbank abgelegt. Jede DB-Prozedur beinhaltet sowohl SQL-
als
auch prozedurale
Anweisungen. Durch die
kombinierte
Anwendung dieser beiden
Anweisungsarten hat der Benutzer die
Möglichkeit, eine erweiterte
Funktionalität (im Vergleich zu
der Verwendung reiner SQL-Anweisungen)
zu erreichen.
Jeder DB-Prozedur können Daten als
Werteparameter zugewiesen
werden. Die Übergabe wird beim Prozeduraufruf durchgeführt.
Innerhalb einer DB-Prozedur können
SQL-Anweisungen verwendet
werden, um Datenwerte einer
Datenbank abzufragen bzw. zu
modifizieren.
Jede übersetzte DB-Prozedur wird
immer in einer ausführbaren
Form gespeichert. Diese Vorgehensweise bringt einen
entscheidenden Vorteil für oft verwendete Anweisungsfolgen.
Jede SQL-Anweisung muß generell mehrere Phasen
("parsing"-,
Optimierungsphase usw.)
vor der Ausführung
durchlaufen.
Durch die Erstellung von
DB-Prozeduren, die häufig
verwendete SQL-Anweisungsfolgen enthalten, ist es möglich,
die wiederholte Durchführung solcher Phasen zu eliminieren.
Dieses Verfahren bringt auch
einen anderen Vorteil: Die
Anzahl der Zugriffe zwischen
den Anwendungsprogrammen und
dem Datenbank-Server kann wesentlich
reduziert werden, falls
eine DB-Prozedur mit einer
Anweisungsfolge benutzt wird,
anstatt SQL-Anweisungen einzeln zu
benutzen.
Ein
weiterer Vorteil von
DB-Prozeduren ist, daß
alle
Datenbankanwendungen, die das
entsprechende Zugriffsrecht
haben, die DB-Prozeduren gemeinsam
verwenden können. Dadurch
wird die Programmierung und die
Verwendung der existierenden
Ressourcen effizienter.
Die
DB-Prozeduren können u.a. für folgende Zwecke verwendet
werden:
- zur Trennung
von Datendefinitions- und
Datenmanipulationsanweisungen;
- zur Einschränkung von
Zugriffsrechten auf
Datenwerte einer
Datenbank;
- zur Erstellung einer Protokolldatei, die die
Schreib- bzw. Leseoperationen auf Daten einer
Tabelle enthält;
Die Verwendung von
DB-Prozeduren für die
Programmierung
von
Datendefinitionsanweisungen ist
sinnvoll, weil dadurch
eine klare Trennlinie zwischen
der Erstellung von
Datenbankobjekten in den DB-Prozeduren einerseits und der
Datenmanipulation in den
Anwendungsprogrammen andererseits
gezogen wird.
Die
Einschränkung von Zugriffsrechten mit Hilfe von
DB-Prozeduren kann
als Ergänzung bzw.
Alternative zum
Autorisierungsmechanismus
gesehen werden. Falls z.B.
eine
Anwendung nur gewisse Spalten einer Tabelle ändern
darf
(und über die Existenz der
anderen nicht informiert sein
sollte), kann
eine DB-Prozedur geschrieben werden,
die das
ermöglicht.
Die Erstellung einer DB-Prozedur, die lesende bzw.
schreibende Zugriffe auf gewählte Tabelle(n) protokolliert,
kann als eine zusätzliche Sicherheitsmaßnahme betrachtet
werden. Dadurch kann z.B. verfolgt
werden, welcher Benutzer,
bzw. welches Anwendungsprogramm Datenwerte einer Tabelle
verfälscht.
Eine DB-Prozedur wird, ähnlich
wie alle anderen
Datenbankobjekte (Tabellen, Views usw.), mit der Anweisung
CREATE PROCEDURE erstellt.
Diese Anweisung
hat folgende
Form:
[CREATE] PROCEDURE
proz_name
[(param_1=typ_1
[,param_2=typ_2,...])]
[declare-Abschnitt]
BEGIN
anweisung_1; [anweisung_2;...]
END
proz_name ist der
Name der DB-Prozedur,
während param_1,
param_2,... Parameternamen sind. typ_1, typ_2,...
spezifizieren den
Datentyp einzelner Parameter. Im
declare-Abschnitt werden
alle lokalen Variablen, die
in
der DB-Prozedur verwendet
werden, definiert. Dieser
Abschnitt fängt mit dem
Schlüsselwort DECLARE an, gefolgt
von der
Variablendeklaration. anweisung_1,
anweisung_2,... stellen die
Anweisungen dar, die beim
Prozeduraufruf ausgeführt werden.
Jede mit CREATE PROCEDURE
erstellte DB-Prozedur kann
entweder mit der EXECUTE
PROCEDURE-Anweisung explizit oder
implizit mit Hilfe einer
Regel aufgerufen werden. (Für die
Beschreibung der Regel siehe Kapitel 20.) Genauso kann
jede
DB-Prozedur sowohl interaktiv als auch eingebettet erstellt
und benutzt werden.
Hinweis.
Die
Syntax der SQL-Anweisungen, die im Zusammenhang mit den
DB-Prozeduren stehen, unterscheidet sich, abhängig
davon,
ob die DB-Prozedur interaktiv
oder eingebettet verwendet
wird. In diesem Buch
werden die Syntax und die Verwendung
der
Anweisungen in der
interaktiven Umgebung beschrieben.
Für die Unterschiede zu der Verwendung in der eingebetteten
Umgebung, die im allgemeinen
geringfügig sind, möchten wir
auf INGRES-Manuale verweisen.
Jeder Benutzer darf eine
DB-Prozedur erstellen, falls er
das Zugriffsrecht für die
in der DB-Prozedur verwendeten
Tabellen bzw. Views hat. Folgende SQL-Anweisungen dürfen
im
Anweisungsteil einer DB-Prozedur
erscheinen:
COMMIT MESSAGE UPDATE
DELETE RETURN WHILE
IF ROLLBACK RAISE
ERROR
INSERT SELECT
Beispiel 18.1
CREATE PROCEDURE proz1 (param_ch=CHAR(8) NOT NULL) AS
DECLARE
lokal_varch=CHAR(8)
NOT NULL;
lokal_varfl=FLOAT;
lokal_vardat=DATE;
BEGIN
lokal_varch =
'ABCDEFGH';
lokal_varfl = NULL;
lokal_vardat
= '25-jun-1991';
param_ch = :lokal_varch;
END
In
Beispiel 18.1 wird die
DB-Prozedur proz1 definiert,
die einen Parameter param_ch enthält, dessen Datentyp
CHAR(8) ist. Im DECLARE-Abschnitt sind drei Variablen
lokal_varch, lokal_varfl und lokal_vardat definiert, denen
im Anweisungsteil Werte zugewiesen
werden. Im Anweisungsteil
wird auch der Wert der lokalen Variablen lokal_varch dem
Parameter param_ch
zugewiesen.
Die EXECUTE PROCEDURE-Anweisung, mit der eine existierende
DB-Prozedur aufgerufen werden kann,
hat folgende Form
EXECUTE PROCEDURE proz_name
[param_1=:wert_1
[,param_2=:wert_2,...]]
[INTO :status];
proz_name ist
der Name der
aufgerufenen DB-Prozedur,
während param_1,param_2,... die
Parameternamen sind, die
den gleichnamigen Parametern der CREATE PROCEDURE-Anweisung
entsprechen.
wert_1, wert_2,... sind
numerische, alphanumerische oder
NULL-Werte, die
den Parametern zugewiesen
werden. Der
Datentyp eines Parameters muß
mit dem zugewiesenen Wert
kompatibel sein. Ausdrücke dürfen
nicht als Parameterwerte
verwendet werden.
status kennzeichnet eine ganzzahlige Variable, in der der
Rückgabewert der DB-Prozedur
gespeichert wird.
Jede erstellte
DB-Prozedur kann -
wie jedes andere
Datenbankobjekt (Tabelle, Index
usw.) - gelöscht werden. Die
Anweisung
DROP PROCEDURE proz_name
löscht die DB-Prozedur namens proz_name. Nur der Eigentümer
einer DB-Prozedur hat das Recht, die
DB-Prozedur zu löschen.
Prozedurale Anweisungen in DB-Prozeduren sind u.a.
- IF,
- WHILE
und
- RETURN.
Die IF-Anweisung enthält eine Bedingung
und mehrere
Anweisungsgruppen, von
welchen eine ausgeführt
wird.
Die Anweisung WHILE wiederholt
die Ausführung einer
Anweisungsgruppe, solange die angegebene Bedingung erfüllt
ist. Sowohl die
IF- als auch
die WHILE-Anweisung sind
allgemein bekannte
Anweisungen, deren Syntax
identisch
der
Syntax der gleichnamigen
Anweisungen in den
meisten
Programmiersprachen ist.
Die Anweisung
RETURN [status];
beendet unmittelbar
die Ausführung einer DB-Prozedur
und
übergibt die Steuerung an die
Anwendung zurück, deren Teil
die DB-Prozedur ist.
Die optionale ganzzahlige
Variable
status kennzeichnet den von
der DB-Prozedur an die Anwendung
übergebenen Rückgabewert. (Um den Rückgabewert auswerten zu
können, muß die DB-Prozedur
mit der Angabe INTO status in
der EXECUTE PROCEDURE-Anweisung
aufgerufen werden.)
Falls ein Fehler innerhalb
einer DB-Prozedur auftritt,
werden vom INGRES-System
standardmäßig Schritte unternommen,
die
abhängig davon sind, ob die DB-Prozedur explizit mit
der EXECUTE PROCEDURE-Anweisung oder durch das
Aktivieren
einer Regel aufgerufen wird.
Beim Aufruf durch
das
Aktivieren einer Regel wird die DB-Prozedur
beendet und
sowohl alle ausgeführten
Anweisungen in der
DB-Prozedur
als
auch die Anweisung,
die die Regel
aktiviert hat,
werden zurückgesetzt. In
den DB-Prozeduren, die
mittels
EXECUTE PROCEDURE-Anweisung
aufgerufen werden, werden
alle ausgeführten
Anweisungen innerhalb der
DB-Prozedur
zurückgesetzt, die DB-Prozedur wird aber nicht
beendet,
sondern mit der
auf die fehlerhafte
folgende Anweisung
weiter ausgeführt.
In
beiden Fällen wird
sowohl das Feld
sqlcode der
sqlca-Struktur als auch errerno
der INQUIRE INGRES-Anweisung
mit
der Fehlernummer versorgt.
Zusätzlich zu sqlcode
und
errerno existieren weitere
Systemvariablen iierror,
number und
iirowcount sowie die
SQL-Anweisung RAISE
ERROR, die dem Anwendungsprogrammierer die Fehlerbehandlung
ermöglichen. Die
Variable iierrornumber enthält bei
den
Datenmanipulationsanweisungen
den Wert 0,
falls
eine Anweisung in der
DB-Prozedur erfolgreich beendet
wurde bzw. die INGRES-Systemfehlernummer, falls
sie
fehlerhaft war. Die Variable iirowcount enthält
bei den
Datenmanipulationsanweisungen im ersten Fall die Anzahl der
verarbeiteten Zeilen und im zweiten
Fall den Wert 0.
Die Anweisung
RAISE ERROR fehler_nr [fehler_text];
meldet dem Anwendungsprogramm das
Auftreten eines Fehlers.
Diese Anweisung kann im
Zusammenhang mit dem
Aktivieren
einer Regel sinnvoll verwendet
werden. (siehe Beispiel
20.2).
fehler_nr ist
eine ganzzahlige Konstante,
eine lokale
Variable oder ein Parameter. Diese
Angabe enthält eine
lokale DBMS-Fehlernummer,
die im Feld
sqlerrd(1) der
sqlca-Struktur abgelegt
wird. fehler_text definiert
den
entsprechenden Fehlertext.
INGRES unterstützt eine weitere
SQL-Anweisung
MESSAGE
text | nummer | text nummer,
mit der Meldungen aus
einer DB-Prozedur an das
Anwendungsprogramm, das sie
aufgerufen hat, geschickt werden
können. text kennzeichnet den Meldungstext während nummer
die Meldungsnummer darstellt.
Die Behandlung der Meldungen durch das
Anwendungsprogramm
hängt davon ab, ob die DB-Prozedur explizit mit der EXECUTE
PROCEDURE-Anweisung oder durch
das Aktivieren einer Regel
aufgerufen wurde. In beiden
Fällen wird standardmäßig
der Meldungstext und die
Meldungsnummer am Bildschirm
ausgegeben.
Falls die DB-Prozedur durch das
Aktivieren einer Regel
aufgerufen wurde, wird, wie
oben beschrieben, der Text und
die
Meldungsnummer ausgegeben. Beim
Aufruf der DB-Prozedur
mittels EXECUTE
PROCEDURE-Anweisung wird der
Text und die
Nummer an die DB-Prozedur übergeben,
und zusätzlich wird dem
Feld sqlcode der sqlca-Struktur
der Wert +700 zugewiesen.
Beispiel 18.2
CREATE PROCEDURE gruppenleiter (m_name CHAR(20))
AS
DECLARE
meldung CHAR(80);
zaehler INTEGER;
BEGIN
SELECT COUNT(*) INTO :zaehler
FROM arbeiten
WHERE
m_name = :m_name
AND
aufgabe= 'Gruppenleiter';
IF zaehler = 0 THEN
meldung='Fehler 12345: Mitarbeiter "' +
:m_name + '"
ist kein Gruppenleiter.';
RAISE ERROR
12345 : meldung;
ENDIF;
END;
In der DB-Prozedur gruppenleiter wird
überprüft, ob ein
Mitarbeiter der Firma in
einem der Projekte Gruppenleiter
ist. Falls dies nicht der Fall ist, wird ein Meldungstext
und die Meldungsnummer 12345 am
Bildschirm ausgegeben.