2
Inhalt§ 10.1 Motivation
§ 10.2 Prozedurale Erweiterungen von SQL
§ 10.3 Gespeicherte Prozeduren
§ 10.4 Benutzerdefinierte Funktionen
§ 10.5 Verwendung anderer Programmiersprachen
Datenbanken / Kapitel 10: Programmieren in SQL
3
10.1 Motivation§ SQL als interaktive Anfragesprache für menschliche
Benutzer oder Anwendungsprogramme entworfen
§ Komplexe Operationen lassen sich nicht (leicht) in einem einzelnen SQL-Kommando ausdrücken, so dass sie in mehrere Kommandos zerlegt und deren Ablauf durchein Anwendungsprogramm gesteuert werden muss
§ Kapitel 11 behandelt die Implementierung solcher Anwendungsprogramme (z.B. in Java oder C),die von außen auf die Datenbank zugreifen
Datenbanken / Kapitel 10: Programmieren in SQL
4
Motivation§ Erweiterung von SQL um prozedurale Sprachelemente
(z.B. Bedingungen und Schleifen) zur Implementierunggespeicherter Prozeduren (stored procedures) und benutzerdefinierter Funktionen (user-defined functions)zur Implementierung komplexer Operationen im RDBMS
§ Gespeicherte Prozeduren erlauben, neben den Daten, auch Funktionalität der modellierten Anwendung (business logic) redundanzfrei und zentralim RDBMS abzubilden
Datenbanken / Kapitel 10: Programmieren in SQL
5
Motivation§ Vorteile gespeicherter Prozeduren gegenüber einer
Implementierung in Anwendungsprogramm sind u.a.
§ Kapselung und zentrale Bereitstellungvon Funktionalität (business logic) im RDBMS
§ Ausführung direkt im RDBMS und damit häufigauf leistungsstarker Server-Hardware
§ Reduzierung der Netzwerklast, da Datennicht zum Client transferiert werden müssen
§ Automatische Optimierung durch denAnfrageoptimierer des RDBMS möglich
Datenbanken / Kapitel 10: Programmieren in SQL
6
Gespeicherte Prozeduren§ Gespeicherte Prozeduren (stored procedures)
§ können einen Rückgabewert haben§ ändern meist die gespeicherten Daten§ typische Anwendung ist die Stapelverarbeitung,
d.h. die Prozedur kapselt eine Folge nacheinanderauszuführender Kommandos
§ Implementierung in prozeduraler Erweiterung von SQL(z.B. Transact-SQL bei MS SQL Server) oderanderer Programmiersprache
§ nicht in anderen SQL-Kommandos verwendbar
Datenbanken / Kapitel 10: Programmieren in SQL
7
Gespeicherte Prozeduren§ Beispiel: Bereinigung Hochschuldaten zu Semesterbeginn
§ Erhöhe das Semester aller Studenten um 1
§ Lösche Studenten, deren Semester danach größer als 30 ist;ihre Einträge in hören sollen mittels eines geeignetenFOREIGN KEY Constraints entfernt werden
§ Lösche Vorlesungen, die von keinem Studenten gehört werden und die kein Professor liest
Datenbanken / Kapitel 10: Programmieren in SQL
8
Benutzerdefinierte Funktionen§ Benutzerdefinierte Funktionen (user-defined functions)
§ berechnen einen Rückgabewert§ ändern die gespeicherten Daten nicht§ typische Anwendung ist das Bereitstellen von Funktionalität,
die das RDBMS nicht mitbringt (z.B. statistische Funktionen, Umwandlung von Zeichenketten),welche wiederverwendet werden kann
§ Implementierung in prozeduraler Erweiterung von SQL(z.B. Transact-SQL) oder anderer Programmiersprache
§ in anderen SQL-Kommandos verwendbar
Datenbanken / Kapitel 10: Programmieren in SQL
9
Benutzerdefinierte Funktionen§ Beispiele in Hochschulanwendung:
§ Abkürzung von Vorlesungstiteln mittels Ersetzungsregeln§ „Einführung“ wird zu „Einf.“
§ „in die“ wird zu „i.d.“
§ “Grundlagen“ wird zu „Grdl.“
§ Eingabe ist Vorlesungstitel; Ausgabe ist seine Abkürzung
§ Berechnung eines gewichteten Notendurchschnittsunter Berücksichtigung der Semesterzahl§ Prüfungsergebnisse sollen mit SWS gewichtet werden
§ -0.3 Abzug je Semester unter Regelstudienzeit
§ Eingabe ist Matrikelnummer; Ausgabe ist Notendurchschnitt
Datenbanken / Kapitel 10: Programmieren in SQL
10
10.2 Prozedurale Erweiterungen von SQL§ Erweiterung von SQL um prozedurale Sprachelemente
(z.B. Bedingungen und Schleifen)
§ SQL/PSM als Bestandteil des SQL-Standards (seit 1996)
§ PL/SQL bei Oracle
§ SQL PL bei IBM
§ PL/pgSQL bei PostgreSQL
§ Transact-SQL bei Microsoft
§ Systemspezifische Sprachen vor Standard entstanden;zunehmenden Unterstützung des SQL/PSM-Standards
Datenbanken / Kapitel 10: Programmieren in SQL
11
Transact-SQL§ Wir betrachten Transact-SQL als prozedurale Erweiterung
von SQL in Microsoft SQL Server etwas genauer
§ Transact-SQL erweitert SQL um folgende Konzepte
§ Meldungen/Ausgaben an den Benutzer
§ lokale und globale Variablen
§ Bedingungen
§ Kommentare
§ Datentypen
§ Schleifen
§ Cursor
Datenbanken / Kapitel 10: Programmieren in SQL
12
Kommentare§ Transact-SQL kennt zwei Arten von Kommentaren
§ Zeilenkommentar (--)
§ Blockkommentar (/* ... */)
Datenbanken / Kapitel 10: Programmieren in SQL
1 -- Diese Zeile wird bei der Ausf
¨
uhrung ignoriert
1 /*
2 *
3 * Alle Zeilen werden bei der Ausf
¨
uhrung ignoriert
4 *
5 */
13
Stapelverarbeitung§ Stapelverarbeitung als einfache Anwendung, d.h. es
wird eine Folge von SQL-Kommandos ausgeführt
Datenbanken / Kapitel 10: Programmieren in SQL
1 -- Tabelle Vorlesungen anlegen
2 CREATE TABLE Vorlesungen (
3 VorlNr int NOT NULL ,
4 Titel varchar (30) NOT NULL ,
5 SWS int NOT NULL ,
6 ProfNr int ,
7 PRIMARY KEY( VorlNr )
8 )
9
10 -- Vorlesungen einf
¨
ugen
11 INSERT INTO Vorlesungen (VorlNr , Titel , SWS , ProfNr )
12 VALUES (1, ’Datenbanken ’, 4, NULL)
13 INSERT INTO Vorlesungen (VorlNr , Titel , SWS , ProfNr )
14 VALUES (2, ’Programmierung ’, 4, NULL)
14
Datentypen§ Transact-SQL unterstützt von SQL bekannte Datentypen
§ bigint (8 Bytes) und int (4 Bytes) für ganze Zahlen
§ float und real für Gleitkommazahlen
§ money (4 Nachkommastellen)
§ char(n) als Zeichenkette fester Länge
§ varchar(n) als Zeichenkette variabler Länge
Datenbanken / Kapitel 10: Programmieren in SQL
15
Variablen§ Transact-SQL unterscheidet lokale und globale Variablen
§ Globale Variablen werden vom System bereitgestellt undsind am Präfix @@ im Namen zu erkennen, z.B.
§ @@SERVERNAME liefert Name des Servers§ @@VERSION liefert Version des Servers§ @@ERROR liefert Ausführungscode der letzten Anweisung
§ @@ROWCOUNT als Anzahl von Zeilen, welche dieletzte Anweisung erzeugt hat
Datenbanken / Kapitel 10: Programmieren in SQL
16
Variablen§ Lokale Variablen müssen deklariert werden und sind
am Präfix @ im Namen zu erkennen
§ Deklaration lokaler Variable mittels DECLARE
§ Lokale Variablen haben initial den Wert NULL
§ Wertzuweisung an lokale Variable mit SET oder SELECT
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE @Variable1 Typ1 , @Variable2 Typ2 , ...
1 SET @Variable1 = Wert12 SELECT @Variable1 = Wert1
17
Variablen§ Beispiel: Deklaration von drei Variablen @a, @b und @c
vom Typ int, gefolgt von Wertzuweisungen
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE @a int , @b int , @c int2 SET @a =13 SET @b =24 SET @c=@a+@b
18
Variablen§ Variable kann Ergebnis eines SELECT-Kommandos als
Wert zugewiesen werden, sofern dies aus einemeinzigen Wert besteht
§ Beispiel:
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE @anzahl_studenten int2 SET @anzahl_studenten =3 ( SELECT COUNT (*) FROM Studenten )
19
Meldungen§ Meldungen, z.B. zum Generieren von Berichten, können
mittels PRINT an den Benutzer ausgegeben werden
§ Zeichenketten können mittels + konkateniert werden; Variablen anderen Typs müssen mittels CAST in eine Zeichenkette umgewandelt werden
Datenbanken / Kapitel 10: Programmieren in SQL
1 PRINT ’Hello World ’
1 DECLARE @num int2 SET @num = 133 PRINT ’Hello User ’ + CAST(@num AS varchar ) + ’!’
20
Anweisungsblöcke§ Block von Anweisungen mit BEGIN ... END definierbar
§ Strichpunkt zum Trennen der Anweisungen verwendbar
§ Verwendung als Teil von Bedingungen und Schleifen
Datenbanken / Kapitel 10: Programmieren in SQL
1 BEGIN2 UPDATE Studenten SET Semester = Semester + 13 DELETE FROM Studenten WHERE Semester > 304 END
21
Bedingungen§ Transact-SQL stellt mit IF ... ELSE ein Kommando zur
bedingten Ausführung von Anweisungsblöcken bereit
§ Beispiel:
Datenbanken / Kapitel 10: Programmieren in SQL
1 IF <Bedingung >
2 <Anweisung oder Anweisungsblock >
3 ELSE4 <Anweisung oder Anweisungsblock >
1 IF EXISTS ( SELECT * FROM Professoren WHERE Name = ’Simpson ’)2 BEGIN3 PRINT ’Es gibt einen Professor namens Simpson ’4 PRINT ’Ob sein Vorname Homer ist?’5 END6 ELSE7 PRINT ’Es gibt keinen Professor namens Simpson ’
22
Konditionale§ Transact-SQL unterstützt mittels des CASE Kommandos
bedingte Ausdrücke, sogenannte Konditionale
§ Konditional wertet zu einem Rückgabewert aus unddient, im Gegensatz zu IF ... ELSE, nicht zurAblaufsteuerung
Datenbanken / Kapitel 10: Programmieren in SQL
1 CASE <Variable >2 WHEN <Wert1 > THEN <Ruckgabe1 >3 WHEN <Wert2 > THEN <Ruckgabe2 >4 END
23
Schleifen§ Transact-SQL verfügt mit WHILE über ein Kommando zur
wiederholten Ausführung eines Anweisungsblocks
§ Zusätzlich kann die Schleife mit dem Kommando BREAKbeendet werden; mit dem Kommando CONTINUE wird ein neuer Schleifendurchlauf gestartet
Datenbanken / Kapitel 10: Programmieren in SQL
1 WHILE <Bedingung >
2 <Anweisung oder Anweisungsblock >
24
Schleifen§ Beispiel: Gebe Zahlen von 1 bis 10 aus
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE @i int2 SET @i = 13 WHILE @i <= 104 BEGIN5 PRINT CAST(@i AS VARCHAR )6
7 PRINT CASE @i8 WHEN 1 THEN ’ist die erste Zahl ’9 WHEN 10 THEN ’ist die letzte Zahl ’
10 END11
12 SET @i = @i + 113 END
25
Dynamische Befehlsausführung§ Transact-SQL erlaubt mittels des Kommandos EXECUTE
das Aufrufen einer gespeicherten Prozedur bzw. das Ausführen eines als Zeichenkette hinterlegten Kommandos
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE @order varchar (30)2 DECLARE @command varchar (100)3
4 SET @order = ’DESC ’5 SET @command ’SELECT * FROM Kunden ORDER BY Name ’ + @order6
7 EXECUTE ( @command )
26
Cursor§ Cursor erlauben eines zeilenweise Verarbeitung einer
Tabelle oder eines Anfrageergebnissesin Transact-SQL
§ Cursor sind grob vergleichbar mit Iteratoren in modernen Programmiersprachen wie Java; ein wichtiger Unterschied ist, dass prinzipiell auch eine Veränderung der zugrundeliegenden Daten möglich ist
Datenbanken / Kapitel 10: Programmieren in SQL
27
Cursor§ Verwendung eines Cursors in Transact-SQL wie folgt
1. Definition des Cursors
2. Öffnen des definierten Cursors
3. Abrufen von Informationen aus dem Cursor(z.B. einer Datensatzes) und Durchführungder gewünschten Operationen
4. Schließen des Cursors
5. Löschen der Definition des Cursors
Datenbanken / Kapitel 10: Programmieren in SQL
28
Definition eines Cursors§ Bei Definition eines Cursors ist festzulegen, ob er
§ nur lesend oder auch schreibendauf das Ergebnis zugreift
§ sich nur vorwärts oder auch rückwärtsdurch das Ergebnis bewegen kann
§ zwischenzeitliche Datenänderungenim Ergebnis sehen soll
Datenbanken / Kapitel 10: Programmieren in SQL
29
Definition eines Cursors§ Definition eines Cursors mittels DECLARE-Kommando
§ Ob der Cursor nur lesend oder auch schreibend zugreift
§ READ_ONLY : nur lesend
§ SCROLL_LOCKS : schreibend mit Sperren
§ OPTIMISTIC : schreibend ohne Sperren
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE <Name des Cursors > CURSOR2 [ FORWARD_ONLY | SCROLL ]
3 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
4 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
5 FOR <SELECT Statement >
30
Definition eines Cursors§ Definition eines Cursors mittels DECLARE-Kommando
§ Ob sich Cursor nur vorwärts oder auch rückwärts bewegt
§ FORWARD_ONLY : nur vorwärts§ SCROLL : vorwärts und rückwärts
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE <Name des Cursors > CURSOR2 [ FORWARD_ONLY | SCROLL ]
3 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
4 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
5 FOR <SELECT Statement >
31
Definition eines Cursors§ Definition eines Cursors mittels DECLARE-Kommando
§ Ob der Cursor zwischenzeitliche Datenänderung sieht
§ STATIC : es wird initial Kopie des Ergebnis erzeugt
§ KEYSET : Kopie der Schlüssel, andere Attribute nachgelesen
§ DYNAMIC : Änderungen ab aktueller Zeile sichtbar
§ FAST_FORWARD : schreibgeschützt, nur in eine Richtung
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE <Name des Cursors > CURSOR2 [ FORWARD_ONLY | SCROLL ]
3 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
4 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
5 FOR <SELECT Statement >
32
Öffnen und Bewegen eines Cursors§ Öffnen eines Cursors mittels OPEN-Kommando
§ Bewegen des Cursors erfolgt mittels FETCH-Kommandos
§ NEXT liest nächste Zeile; PRIOR liest vorherige Zeile
§ FIRST liest erste Zeile; LAST liest letzte Zeile
§ ABSOLUTE n liest n-te Zeile (nur bei STATIC)
§ RELATIVE n liest n-te Zeile vor/nach aktueller Zeile
Datenbanken / Kapitel 10: Programmieren in SQL
1 OPEN <Name des Cursors >
1 FETCH2 [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]
3 FROM <Name des Cursors >
4 INTO <lokale Variable >
33
Cursor-Status§ Beim Bewegen des Cursors wird in der globalen Variable@@FETCH_STATUS ein Status zurückgegeben; dieser kann ausgelesen werden, um Fehler zu erkennen bzw. dieBewegung des Cursors zu steuern
§ Werte der Variable @@FETCH_STATUS
§ 0 : alles in Ordnung
§ -1 : Operation außerhalb des Cursors
§ -2 : Zeile nicht vorhanden
Datenbanken / Kapitel 10: Programmieren in SQL
34
Schließen und Entfernen eines Cursors§ Schließen eines Cursors mittels CLOSE-Kommando
§ Entfernen eines Cursors mittels DEALLOCATE-Kommando
Datenbanken / Kapitel 10: Programmieren in SQL
1 CLOSE <Name des Cursors >
1 DEALLOCATE <Name des Cursors >
35
Beispiel Cursor§ Beispiel: Gib die Namen und Vornamen aller Studenten
in einem höheren als dem zehnten Semester aus
Datenbanken / Kapitel 10: Programmieren in SQL
1 -- Cursor und lokale Variablen deklarieren
2 DECLARE StudentenCursor CURSOR FAST_FORWARD3 FOR SELECT Vorname , Name FROM Studenten WHERE Semester > 104
5 DECLARE @StudentenVorname varchar (30) , @StudentenName varchar (30)6
7 -- Zeilenweise Verarbeitung
8 OPEN StudentenCursor9 FETCH NEXT FROM StudentenCursor INTO @StudentenVorname , @StudentenName
10 WHILE ( @@FETCH_STATUS = 0)11 BEGIN12 PRINT ’Vorname : ’ + @StudentenVorname13 PRINT ’Name: ’ + @StudentenName14 FETCH NEXT FROM StudentenCursor INTO @StudentenVorname , @StudentenName15 END16
17 -- Cursor schlie ßen und entfernen
18 CLOSE StudentenCursor19 DEALLOCATE StudentenCursor
36
Fehlerbehandlung§ Die globale Variable @@ERROR zeigt an, ob das letzte
Transact-SQL-Kommando erfolgreich ausgeführt wurde;der Wert wird nach jedem Kommando neu zugewiesen
§ Werte der Variable @@ERROR
§ 0 : Kommando wurde erfolgreich ausgeführt
§ > 0 : Kommando wurde nicht erfolgreich ausgeführt
§ Im Fehlerfall verweist der Rückgabewert auf einen Eintrag in der Systemtabelle sys.messages, die genauere Informationen zum Fehler enthält
Datenbanken / Kapitel 10: Programmieren in SQL
37
Beispiel Fehlerbehandlung§ Beispiel: Division durch 0
Datenbanken / Kapitel 10: Programmieren in SQL
1 PRINT 1/0 -- gibt Wert 8134 zur uck
1 SELECT *2 FROM sys. messages3 WHERE message_id = 81344 AND language_id = 1031
message id language severity is event logged text
8134 1031 16 false Fehler aufgrund Division durch Null
38
Fehlerschwere§ Transact-SQL unterscheidet verschiedene Schweregrade
(severity) von Fehler; eine Schwere höher als 10 führt zum Abbruch des Programms bzw. Sprung in CATCH-Block
§ Schwere von Fehler (severity in sys.messages)
§ 0-10 : Informationsmeldungen
§ 11-16 : Fehlerhaftes Programm (z.B. falsche Syntax)
§ 17 : Mangelnde Ressourcen (z.B. Speicher)
§ 18 : Problem im MS SQL Server
§ 19 : Schwerer interner Fehler
§ 20-25 : Fataler Fehler
Datenbanken / Kapitel 10: Programmieren in SQL
39
Fehlerbehandlung§ Transact-SQL verfügt, ähnlich Java, mit den Kommandos TRY und CATCH über einen Möglichkeit, Fehlernsystematisch zu begegnen
§ Läuft der TRY-Block
§ ohne Fehler (0-10) ab, wird CATCH-Block nicht ausgeführt
§ mit Fehler (11-16) ab, wird CATCH-Block ausgeführt
§ mit fatalem Fehler (> 17) ab, wird Programm abgebrochen
Datenbanken / Kapitel 10: Programmieren in SQL
1 BEGIN TRY2 <Transact -SQL Kommandos >
3 END TRY4 BEGIN CATCH5 <Transact -SQL Kommandos >
6 END CATCH
40
Fehlerbehandlung§ Innerhalb eines CATCH-Blocks stehen folgende Funktionen
zur Verfügung, um mehr über den Fehler zu erfahren
§ ERROR_LINE() gibt Zeile an, in der Fehler auftrat
§ ERROR_MESSAGE() gibt Fehlermeldung aus
§ ERROR_NUMER() gibt Fehlercode aus
§ ERROR_SEVERITY() gibt Schwere des Fehlers aus
§ ERROR_STATE() gibt Status zurück
Datenbanken / Kapitel 10: Programmieren in SQL
41
Beispiel Fehlerbehandlung§ Beispiel: Anfrage mit ungültigem Attributnamen
gibt
aus
Datenbanken / Kapitel 10: Programmieren in SQL
1 BEGIN TRY2 SELECT *3 FROM Studenten4 WHERE ProfNr = 425 END TRY6 BEGIN CATCH7 PRINT @@ERROR8 PRINT ERROR_MESSAGE ()9 END CATCH
1 ErrorCode : 207
2 Error: Ung
¨
u ltiger Spaltenname ’ProfNr ’.
42
10.3 Gespeicherte Prozeduren§ Gespeicherte Prozeduren (stored procedures) kapseln
Funktionalität (business logic) zentral im RDBMS
§ können einen Rückgabewert haben
§ dürfen die gespeicherte Daten ändern§ können nicht in anderen SQL-Kommandos
(z.B. SELECT) verwendet werden
§ können in prozeduraler Erweiterung von SQL(z.B. Transact-SQL) oder anderer Spracheimplementiert werden
Datenbanken / Kapitel 10: Programmieren in SQL
43
Anlegen einer gespeicherten Prozedur§ Gespeicherte Prozedur lässt sich in MS SQL Server
anlegen mittels CREATE PROCEDURE
§ Beispiel: Löschen aller Datensätze in Tabelle hören
Datenbanken / Kapitel 10: Programmieren in SQL
1 CREATE PROCEDURE <Name der Prozedur >
2 [ @Parameter1 Typ1 = Default1 ... ]
3 [ @ParameterN TypN = DefaultN OUTPUT ]
4 AS5 <Folge von Transact -SQL Kommandos >
1 CREATE PROCEDURE HorenL o schen2 @Anzahl int = 0 OUTPUT3 AS4 DELETE FROM horen5 SET @Anzahl = @@ROWCOUNT
44
Ändern, Löschen und Anzeigen von Prozeduren§ Ändern mittels ALTER PROCEDURE
§ Löschen mittels DROP PROCEDURE
§ Anzeigen aller gespeicherten Prozeduren
Datenbanken / Kapitel 10: Programmieren in SQL
1 ALTER PROCEDURE <Name der Prozedur >
1 DROP PROCEDURE <Name der Prozedur >
1 SELECT *
2 FROM sys. sysobjects
3 WHERE type = ’p’
45
Parametrisierung§ Gespeicherte Prozeduren können mehrere Parameter als
Eingabe und einen Parameter als Ausgabe haben
§ Parameter können mit Vorgabewert belegt werden; dieser wird verwendet, wenn kein Wert angegeben wird
§ Beispiel: Erhöhung des Semesters eines Studenten
Datenbanken / Kapitel 10: Programmieren in SQL
1 CREATE PROCEDURE SemesterErh
¨
ohen @MatrNr int , @Erh
¨
ohung int = 1
2 AS3 UPDATE Studenten
4 SET Semester = Semester + @Erh
¨
ohung
5 WHERE MatrNr = @MatrNr
1 EXECUTE SemesterErh
¨
ohen 328762 -- f
¨
uhrt Erh
¨
ohung um 1 durch
2 EXECUTE SemesterErh
¨
ohen 328762 , 2 -- f
¨
uhrt Erh
¨
ohung um 2 durch
46
Festlegen des Rückgabewerts§ Rückgabewert einer gespeicherten Prozedur wird mit OUTPUT unter den Parametern festgelegt
§ Endet die Ausführung der Prozedur, wird der aktuelle Wert des mit OUTPUT markierten Parameters zurückgegeben
§ Mittels RETURN kann die Ausführung der Prozedur beendet und ein Wert zurückgegeben werden; auchohne Wert kann so die Prozedur beendet werden
Datenbanken / Kapitel 10: Programmieren in SQL
47
Festlegen des Rückgabewerts§ Beispiel: Ermittle Zahl der Studenten in einem Semester
Variante 1 mit OUTPUT Parameter
Variante 2 mit RETURN
Datenbanken / Kapitel 10: Programmieren in SQL
1 CREATE PROCEDURE StudentenAnzahl2 @Semester int3 @Anzahl int OUTPUT4 AS5 SET @Anzahl = ( SELECT COUNT (*) FROM Studenten WHERE Semester = @Semester )
1 CREATE PROCEDURE StudentenAnzahl2 @Semester int3 AS4 DECLARE @Anzahl int5 SET @Anzahl = ( SELECT COUNT (*) FROM Studenten WHERE Semester = @Semester )6 RETURN @Anzahl
48
10.4 Benutzerdefinierte Funktionen§ Benutzerdefinierte Funktionen (user-defined functions)
§ berechnen einen Rückgabewert§ dürfen die gespeicherten Daten nicht ändern§ können in anderen SQL-Kommandos
(z.B. SELECT) verwendet werden
§ können in prozeduraler Erweiterung von SQL(z.B. Transact-SQL) oder anderer Spracheimplementiert werden
Datenbanken / Kapitel 10: Programmieren in SQL
49
Anlegen einer benutzerdefinierten Funktion§ Benutzerdefinierte Funktion lässt sich in MS SQL Server
mittels CREATE FUNCTION anlegen
§ Funktionen können skalaren Wert (z.B. vom Typ int)oder eine Tabelle (dann Rückgabewert TABLE)zurückgeben
Datenbanken / Kapitel 10: Programmieren in SQL
1 CREATE FUNCTION <Name der Funktion >
2 [ @Parameter1 Typ1 = Default1 ... ]
3 RETURNS R
¨
u ckgabeTyp
4 AS5 BEGIN6 <Folge von Transact -SQL Kommandos >
7 RETURN <Wert von R
¨
uckgabeTyp >
8 END
50
Ändern, Löschen und Anzeigen von Funktionen§ Ändern mittels ALTER FUNCTION
§ Löschen mittels DROP FUNCTION
§ Anzeigen aller Funktionen mit skalarem Rückgabewert
§ Anzeigen aller Funktionen mit Tabelle als Rückgabewert
Datenbanken / Kapitel 10: Programmieren in SQL
1 ALTER FUNCTION <Name der Funktion >
1 DROP FUNCTION <Name der Funktion >
1 SELECT * FROM sys. sysobjects WHERE type = ’FN ’
1 SELECT * FROM sys. sysobjects WHERE type = ’IF ’
51
Beispiel Funktion mit skalaren Rückgabewert§ Beispiel: Studienjahre aus gegebenem Semester
§ Funktion Studienjahre() kann nun z.B. in einem SELECT-Kommando verwendet werden
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE FUNCTION Studienjahre @Semester int2 RETURNS int3 AS4 BEGIN5 RETURN CEILING ( @Semester / 2) -- Aufrunden6 END
1 SELECT *2 FROM Studenten3 WHERE Studienjahre ( Semester ) > 5
52
Beispiel Funktion mit Tabelle als Rückgabewert§ Beispiel: Studenten in gegebenem Fach
Datenbanken / Kapitel 10: Programmieren in SQL
1 DECLARE FUNCTION Studenten @Fach varchar (10)2 RETURNS TABLE3 AS4 BEGIN5 RETURN ( SELECT * FROM Studenten WHERE Fach = @Fach)6 END
53
10.5 Verwendung anderer Programmiersprachen§ Gespeicherte Prozeduren und benutzerdefinierte
Funktionen lassen sich je nach RDBMS auchin anderer Programmiersprache als derjeweiligen prozeduralen Erweiterungvon SQL implementieren, z.B.
§ .NET Sprachen (C#, Visual Basic, F#) bei MS SQL Server§ Java bei Oracle
§ Sowohl bei MS SQL Server wird Prozedur bzw. Funktion in eigener Umgebung (CLR bzw. VM) ausgeführt,um eine Abschirmung vom RDBMS selbst zuerreichen und so Abstürze zu vermeiden
Datenbanken / Kapitel 10: Programmieren in SQL
54
Verwendung anderer Programmiersprachen§ Schritte zur Implementierung einer Prozedur bzw. Funktion
in einer anderen unterstützten Programmiersprachen
§ Implementierung auf lokalem Rechner
§ Installation auf RDBMS-Server, d.h. Übertragen der Binaries
§ Registrierung im RDBMS-Server
§ Aufruf der Prozedur bzw. Funktion
Datenbanken / Kapitel 10: Programmieren in SQL
55
Beispiel UDF in C#§ Beispiel: Studienjahre aus gegebenem Semester in C#
§ Nach Installation und Registrierung bei MS SQL Server(über das SQL Server Management Studio), wird die Funktion mittels [dbo].StudienJahre() aufgerufen
Datenbanken / Kapitel 10: Programmieren in SQL
1 using Math;2 using System ;3 using Microsoft . SqlServer . Server ;4
5 public static class HochschulUDFs6 {7 [ SqlFunction ]8 public static int StudienJahre (int semester )9 {
10 return (int)Math. Ceiling ( semester / 2);11 }12 }
56
Zusammenfassung§ Cursor erlauben die zeilenweise Verarbeitung des
Ergebnis eines SELECT-Kommandos in Transact-SQL
§ Fehlerbehandlung mittels TRY- und CATCH-Blöcken
§ Parametrisierung von gespeicherten Prozeduren mit Standardwerten und Rückgabeparameter
§ Gespeicherte Prozeduren und benutzerdefinierte Funktionen lassen sich je nach System in anderen Sprachen (z.B. .NET oder Java) implementieren
Datenbanken / Kapitel 10: Programmieren in SQL
57
Literatur[1] A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2015 (Kapitel 12)
[2] G. Saake, K.-U. Sattler und A. Heuer:Datenbanken - Konzepte und Sprachen,mitp Professional, 2013 (Kapitel 13)
[3] K. Huckert: Relationale Datenbanken, htw saar, 2013http://www1.htwsaar.de/~braun/Uebungen/Datenbanken/ (Kapitel 8)
Datenbanken / Kapitel 10: Programmieren in SQL
Top Related