Beispiel 1: Zugangsprüfung
Die nachfolgende Prozedur KUNDEN_LOGIN realisiert eine einfache Form der Zugangsprüfung für Kunden. Sie ist Bestandteil der Prozedurenbeispiele in der Beispieldatenbank von SESAM/SQL (siehe „ Basishandbuch“).
 | Die Prozedur |
***********************************************************************
* Prozedur KUNDEN_LOGIN definieren
***********************************************************************
SQL CREATE PROCEDURE KUNDEN_LOGIN 1,
( -
IN PAR_KUNDENNR INTEGER, 2.
IN PAR_KONTAKTNR INTEGER,
OUT PAR_STATUS CHAR(40),
OUT PAR_ANREDE CHAR(20),
OUT PAR_NACHNAME CHAR(25)
)
READS SQL DATA 3.
BEGIN 4.
/* Variablen Definition */ 5.
DECLARE VAR_EOD SMALLINT DEFAULT 0;
/* Handler Definition */ 6.
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET VAR_EOD = 1; 7.
/* Anweisungen */ 8.
SET PAR_ANREDE = ' ';
SET PAR_NACHNAME = ' ';
/* Pruefen, ob Kunde schon bekannt ist */
SELECT ANREDE, NACHNAME INTO PAR_ANREDE, PAR_NACHNAME
FROM KONTAKT
WHERE KONR = PAR_KONTAKTNR
AND KNR = PAR_KUNDENNR;
IF VAR_EOD = 1 THEN 9.
SET PAR_STATUS = 'Kunde unbekannt';
ELSE
SET PAR_STATUS = 'Login erfolgreich';
END IF;
END 10.
Prozedurkopf mit der Angabe des Prozedurnamens (Datenbank- und Schemaname sind voreingestellt).
Liste der Prozedurparameter.
Die Prozedur kann SQL-Anweisungen zum Lesen von Daten enthalten, jedoch keine SQL-Anweisungen zum Ändern von Daten. Die (einzige) Prozeduranweisung ist eine (nicht-atomare) COMPOUND-Anweisung. Sie führt weitere Prozeduranweisungen in einem gemeinsamen Kontext aus.
Definition lokaler Prozedurvariablen.
Definition der Fehlerbehandlung in Abhängigkeit vom SQLSTATE.
In diesem Fall wird die Prozedur fortgesetzt wenn ein SQLSTATE der Klasse 02xxx (keine Daten) auftritt.
Im Fehlerfall wird die lokale Variable
VAR_EODgesetzt.Es folgen die Prozeduranweisungen.
In Abhängigkeit vom Ergebnis der Abfrageanweisung werden die Ausgabefelder der Prozedur versorgt.
Ende von COMPOUND-Anweisung und Prozedur.
Beispiel 2: komplexe COMPOUND-Anweisung
Die nachfolgende Prozedur MyTables besteht aus einer komplexen Compound-Anweisung und zeigt verschiedene Möglichkeiten der Fehlerbehandlung. Sie speichert in der zentralen Basistabelle mySchema.myTabs die Namen derjenigen Tabellen ab, auf die der aktuelle Berechtigungsschlüssel zugreifen darf.
Der Eingabeparameter par_type gibt vor, ob Basistabellen oder Views zu berücksichtigen sind. Bei par_type='B' werden die Namen der Basistabellen, bei par_type='V' die Namen der Views gespeichert. Geliefert werden folgende Ausgabeparameter:
par_nbr_tables
Anzahl der für den aktuellen Benutzer insgesamt gespeicherten Tabellennamen des jeweiligen Tabellentyps (Basistabelle oder View)
par_nbr_new_tables
Anzahl der für den aktuellen Benutzer durch den Prozeduraufruf zusätzlich abgespeicherten Tabellennamen
par_message
Meldungstext (OK oder Fehlerhinweis)
-- Procedure header
CREATE PROCEDURE ProcSchema.MyTables
( IN par_type CHAR(1), OUT par_message CHAR(80),
OUT par_nbr_tables INTEGER, OUT par_nbr_new_tables INTEGER )
MODIFIES SQL DATA
-- Procedure body, COMPOUND statement, declaration section
myTab: BEGIN ATOMIC
DECLARE var_table_type CHAR(18);
DECLARE var_schema_name,var_table_name CHAR(31);
DECLARE var_eot SMALLINT DEFAULT 0;
DECLARE var_nbr_old_tables INTEGER DEFAULT 0;
DECLARE myCursor CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = var_table_type;
-- Error routines
DECLARE EXIT HANDLER FOR SQLSTATE '42SND'
SET par_message = 'catalog ' || CURRENT_REFERENCED_CATALOG
|| ' not accessible';
DECLARE CONTINUE HANDLER FOR SQLSTATE '23SA5'
-- Primary key not unique
SET var_nbr_old_tables = var_nbr_old_tables + 1;
DECLARE EXIT HANDLER FOR SQLSTATE '42SQK'
SET par_message = 'table MyTabs not accessible';
DECLARE UNDO HANDLER FOR SQLEXCEPTION
BEGIN -- COMPOUND statement
SET par_message = 'unexpected error';
SET par_nbr_tables = 0;
SET par_nbr_new_tables = 0;
END;
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET par_message = 'warning ignored';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET var_eot = 1;
-- Set initial values
SET par_message = 'OK';
SET par_nbr_tables = 0;
SET par_nbr_new_tables = 0;
IF par_type = 'V' THEN SET var_table_type = 'VIEW';
ELSEIF par_type = 'B' THEN SET var_table_type = 'BASE TABLE';
ELSE SET par_message = 'wrong input parameter par_type';
LEAVE myTab;
END IF;
-- Procedure statements
OPEN myCursor;
loop1: LOOP
FETCH myCursor INTO var_schema_name, var_table_name;
IF var_eot = 1 -- Set by error handler for error class 'not found'
THEN LEAVE loop1; -- End of tables reached
END IF;
INSERT INTO mySchema.myTabs VALUES
(var_schema_name, var_table_name, var_table_type,
current_user, current_date);
SET par_nbr_tables = par_nbr_tables + 1;
END LOOP loop1;
CLOSE myCursor;
SET par_nbr_new_tables = par_nbr_tables - var_nbr_old_tables;
-- var_nbr_old_tables set by error handler for SQLSTATE '23SA5'
END myTab
Beispiel 3: Unterschiedliche CALL-Aufrufe
Die Prozedur min_leistungssatz liefert anhand der übergebenen Auftragsnummer den niedrigsten Leistungssatz für diesen Auftrag zurück.
Wenn als Auftragsnummer der NULL-Wert übergeben wurde, dann wird als Leistungssatz der Wert -999 zurückgeliefert.
Wenn die Auftragsnummer existiert, aber in allen betroffenen Sätzen der Leistungssatz nicht signifikant ist, dann wird der NULL-Wert zurückgeliefert.
Wenn die Auftragsnummer nicht existiert, dann wird die CALL-Anweisung mit SQLSTATE („keine Daten“) beendet.
-- Procedure header
CREATE PROCEDURE min_leistungssatz
( IN in_anr CHAR(8), OUT out_lsatz NUMERIC(6) )
READS SQL DATA
-- Procedure body
IF in_anr IS NULL THEN out_lsatz = -999;
ELSE SELECT MIN(lsatz) INTO out_lsatz FROM leistung
WHERE anr = in_anr;
END IF
Anhand dieser Prozedur sollen die Reaktionen auf verschiedene CALL-Aufrufe der Prozedur dargestellt werden.
Zu beachten ist, dass die Parameter in_anr und out_lsatz keine Indikatoren haben (nicht erlaubt). Die Signifikanz von in_anr wird direkt über IS NULL geprüft. Dem Ausgabeparameterr out_lsatz kann in der INTO-Klausel direkt der NULL-Wert zugewiesen werden.
Betrachtet werden nun verschiedene statische CALL-Anweisungen. Das Argument für den Eingabewert kann die unterschiedlichsten Darstellungen haben. Als Argument für den Ausgabewert muss dagegen immer eine Benutzervariable angegeben werden. Sie muss einen numerischen Datentyp haben (kompatibel mit NUMERIC(6)). Sinnvollerweise sollte auch eine Indikatorvariable verwendet werden, die vor dem CALL-Aufruf mit -1 zu initialisieren ist. Andernfalls muss die Benutzervariable selbst mit einem (gemäß ihrem Datentyp) korrekten Wert initialisiert worden sein.
CALL min_leistungssatz(:anr, :lsatz INDICATOR :ind-lsatz)
Der Eingabewert wird als Benutzervariable übergeben. Da der NULL-Wert zurückgeliefert werden kann, ist die Angabe einer Indikatorvariablen für den Ausgabewert sinnvoll.
CALL min_leistungssatz(:anr :ind-anr, :lsatz :ind-lsatz)
Wie oben, aber durch das Setzen von :ind-anr auf -1 kann auch der NULL-Wert übergeben werden.
CALL min_leistungssatz('A#123456', :lsatz)
Der konkrete Eingabewert lautet A#123456. Wenn dafür der NULL-Wert zurückgeliefert werden sollte, dann fehlt die Angabe einer Indikatorvariablen, was zu einem SQLSTA-TE SEW2202 führt.
CALL min_leistungssatz(CAST(NULL AS CHAR (8)), :lsatz)
Da der Eingabewert NULL ist, wird der Wert -999 zurückgeliefert. Da die Benutzervariablee :lsatz keinen Indikator hat, muss sie vor dem Aufruf mit einem korrekten Wert (abhängig vom Datentyp) initialisiert worden sein.
CALL min_leistungssatz((SELECT MAX(anr) FROM leistung),:lsatz :ind-lsatz)
Der Eingabewert ist die höchste Auftragsnummer. Da der NULL-Wert zurückgeliefert werden kann, ist die Angabe einer Indikatorvariablen für den Ausgabewert sinnvoll. Wenn die Tabelle leistung leer ist, dann wird damit der NULL-Wert zurückgegeben.