10. Programmieren in SQL

10. Programmieren in SQL
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
2
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 durch
ein 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
3
Motivation
§ Erweiterung von SQL um prozedurale Sprachelemente
(z.B. Bedingungen und Schleifen) zur Implementierung
gespeicherter 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 zentral
im RDBMS abzubilden
Datenbanken / Kapitel 10: Programmieren in SQL
4
Motivation
§ Vorteile gespeicherter Prozeduren gegenüber einer
Implementierung in Anwendungsprogramm sind u.a.
§ Kapselung und zentrale Bereitstellung
von Funktionalität (business logic) im RDBMS
§ Ausführung direkt im RDBMS und damit häufig
auf leistungsstarker Server-Hardware
§ Reduzierung der Netzwerklast, da Daten
nicht zum Client transferiert werden müssen
§ Automatische Optimierung durch den
Anfrageoptimierer des RDBMS möglich
Datenbanken / Kapitel 10: Programmieren in SQL
5
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 nacheinander
auszuführender Kommandos
§ Implementierung in prozeduraler Erweiterung von SQL
(z.B. Transact-SQL bei MS SQL Server) oder
anderer Programmiersprache
§ nicht in anderen SQL-Kommandos verwendbar
Datenbanken / Kapitel 10: Programmieren in SQL
6
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 geeigneten
FOREIGN 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
7
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
8
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 Notendurchschnitts
unter 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
9
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
10
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
11
Kommentare
§ Transact-SQL kennt zwei Arten von Kommentaren
§ Zeilenkommentar (--)
1
-- Diese Zeile wird bei der Ausf ü hrung ignoriert
§ Blockkommentar (/* ... */)
1
2
3
4
5
/*
*
* Alle Zeilen werden bei der Ausf ü hrung ignoriert
*
*/
Datenbanken / Kapitel 10: Programmieren in SQL
12
Stapelverarbeitung
§ Stapelverarbeitung als einfache Anwendung, d.h. es
wird eine Folge von SQL-Kommandos ausgeführt
1
2
3
4
5
6
7
8
-- Tabelle Vorlesungen anlegen
CREATE TABLE Vorlesungen (
VorlNr int NOT NULL ,
Titel varchar (30) NOT NULL ,
SWS int NOT NULL ,
ProfNr int ,
PRIMARY KEY ( VorlNr )
)
9
10
11
12
13
14
-- Vorlesungen einf ü gen
INSERT INTO Vorlesungen ( VorlNr , Titel , SWS , ProfNr )
VALUES (1 , ’ Datenbanken ’ , 4 , NULL )
INSERT INTO Vorlesungen ( VorlNr , Titel , SWS , ProfNr )
VALUES (2 , ’ Programmierung ’ , 4 , NULL )
Datenbanken / Kapitel 10: Programmieren in SQL
13
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
14
Variablen
§ Transact-SQL unterscheidet lokale und globale Variablen
§ Globale Variablen werden vom System bereitgestellt und
sind 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 die
letzte Anweisung erzeugt hat
Datenbanken / Kapitel 10: Programmieren in SQL
15
Variablen
§ Lokale Variablen müssen deklariert werden und sind
am Präfix @ im Namen zu erkennen
§ Deklaration lokaler Variable mittels DECLARE
1
DECLARE @Variable1 Typ1 , @Variable2 Typ2 , ...
§ Lokale Variablen haben initial den Wert NULL
§ Wertzuweisung an lokale Variable mit SET oder SELECT
1
2
SET @Variable1 = Wert1
SELECT @Variable1 = Wert1
Datenbanken / Kapitel 10: Programmieren in SQL
16
Variablen
§ Beispiel: Deklaration von drei Variablen @a, @b und @c
vom Typ int, gefolgt von Wertzuweisung
1
2
3
4
DECLARE @a int , @b int , @c int
SET @a =1
SET @b =2
SET @c = @a + @b
Datenbanken / Kapitel 10: Programmieren in SQL
17
Variablen
§ Variable kann Ergebnis eines SELECT-Kommandos als
Wert zugewiesen werden, sofern dies aus einem
einzigen Wert besteht
§ Beispiel:
1
2
3
DECLARE @anz ahl_s tudent en int
SET @anz ahl_s tudent en =
( SELECT COUNT (*) FROM Studenten )
Datenbanken / Kapitel 10: Programmieren in SQL
18
Meldungen
§ Meldungen, z.B. zum Generieren von Berichten, können
mittels PRINT an den Benutzer ausgegeben werden
1
PRINT ’ Hello World ’
§ Zeichenketten können mittels + konkateniert werden;
Variablen anderen Typs müssen mittels CAST in eine
Zeichenkette umgewandelt werden
1
2
3
DECLARE @num int
SET @num = 13
PRINT ’ Hello User ’ + CAST ( @num AS varchar ) + ’! ’
Datenbanken / Kapitel 10: Programmieren in SQL
19
Anweisungsblöcke
§ Block von Anweisungen mit BEGIN ... END definierbar
1
2
3
4
BEGIN
UPDATE Studenten SET Semester = Semester + 1
DELETE FROM Studenten WHERE Semester > 30
END
§ Strichpunkt zum Trennen der Anweisungen verwendbar
§ Verwendung als Teil von Bedingungen und Schleifen
Datenbanken / Kapitel 10: Programmieren in SQL
20
Bedingungen
§ Transact-SQL stellt mit IF ... ELSE ein Kommando zur
bedingten Ausführung von Anweisungsblöcken bereit
1
2
3
4
IF < Bedingung >
< Anweisung oder Anweisungsblock >
ELSE
< Anweisung oder Anweisungsblock >
§ Beispiel:
1
2
3
4
5
6
7
IF EXISTS ( SELECT * FROM Professoren WHERE Name = ’ Simpson ’)
BEGIN
PRINT ’ Es gibt einen Professor namens Simpson ’
PRINT ’ Ob sein Vorname Homer ist ? ’
END
ELSE
PRINT ’ Es gibt keinen Professor namens Simpson ’
Datenbanken / Kapitel 10: Programmieren in SQL
21
Konditionale
§ Transact-SQL unterstützt mittels des CASE Kommandos
bedingte Ausdrücke, sogenannte Konditionale
1
2
3
4
CASE < Variable >
WHEN < Wert1 > THEN <R ü ckgabe1 >
WHEN < Wert2 > THEN <R ü ckgabe2 >
END
§ Konditional wertet zu einem Rückgabewert aus und
dient, im Gegensatz zu IF ... ELSE, nicht zur
Ablaufsteuerung
Datenbanken / Kapitel 10: Programmieren in SQL
22
Schleifen
§ Transact-SQL verfügt mit WHILE über ein Kommando zur
wiederholten Ausführung eines Anweisungsblocks
1
2
WHILE < Bedingung >
< Anweisung oder Anweisungsblock >
§ Zusätzlich kann die Schleife mit dem Kommando BREAK
beendet werden; mit dem Kommando CONTINUE wird ein
neuer Schleifendurchlauf gestartet
Datenbanken / Kapitel 10: Programmieren in SQL
23
Schleifen
§ Beispiel: Gebe Zahlen von 1 bis 10 aus
1
2
3
4
5
DECLARE @i int
SET @i = 1
WHILE @i <= 10
BEGIN
PRINT CAST ( @i AS VARCHAR )
6
7
8
9
10
PRINT CASE @i
WHEN 1 THEN ’ ist die erste Zahl ’
WHEN 10 THEN ’ ist die letzte Zahl ’
END
11
12
13
SET @i = @i + 1
END
Datenbanken / Kapitel 10: Programmieren in SQL
24
Dynamische Befehlsausführung
§ Transact-SQL erlaubt mittels des Kommandos EXECUTE
das Aufrufen einer gespeicherten Prozedur bzw. das
Ausführen eines als Zeichenkette hinterlegten Kommandos
1
2
DECLARE @order varchar (30)
DECLARE @command varchar (100)
3
4
5
SET @order = ’ DESC ’
SET @command ’ SELECT * FROM Kunden ORDER BY Name ’ + @order
6
7
EXECUTE ( @command )
Datenbanken / Kapitel 10: Programmieren in SQL
25
Zusammenfassung
§ Gespeicherte Prozeduren und benutzerdefinierte
Funktionen erlauben die Kapselung von Funktionalität,
welche direkt auf RDBMS-Server ausgeführt wird und
dadurch Ressourcen spart
§ Implementierung in prozeduralen Erweiterungen von
SQL (z.B. Transact-SQL bei SQL Server) oder anderen
Programmiersprachen
§ Transact-SQL erweitert SQL z.B. um Variablen,
Meldungen, Schleifen und Bedingungen
Datenbanken / Kapitel 10: Programmieren in SQL
26
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (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, 2013
http://www1.htwsaar.de/~braun/Uebungen/Datenbanken/
(Kapitel 8)
Datenbanken / Kapitel 10: Programmieren in SQL
27
Rückblick
§ Gespeicherte Prozeduren und benutzerdefinierte
Funktionen erlauben die Kapselung von Funktionalität,
welche direkt auf RDBMS-Server ausgeführt wird und
dadurch Ressourcen spart
§ Implementierung in prozeduralen Erweiterungen von
SQL (z.B. Transact-SQL bei SQL Server) oder anderen
Programmiersprachen
§ Transact-SQL erweitert SQL z.B. um Variablen,
Meldungen, Schleifen und Bedingungen
Datenbanken / Kapitel 10: Programmieren in SQL
28
Cursor
§ Cursor erlauben eines zeilenweise Verarbeitung einer
Tabelle oder eines Anfrageergebnisses
in 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
29
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ührung
der gewünschten Operationen
4. Schließen des Cursors
5. Löschen der Definition des Cursors
Datenbanken / Kapitel 10: Programmieren in SQL
30
Definition eines Cursors
§ Bei Definition eines Cursors ist festzulegen, ob er
§ nur lesend oder auch schreibend
auf das Ergebnis zugreift
§ sich nur vorwärts oder auch rückwärts
durch das Ergebnis bewegen kann
§ zwischenzeitliche Datenänderungen
im Ergebnis sehen soll
Datenbanken / Kapitel 10: Programmieren in SQL
31
Definition eines Cursors
§ Definition eines Cursors mittels DECLARE-Kommando
1
2
3
4
5
DECLARE < Name des Cursors > CURSOR
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
FOR < SELECT Statement >
§ 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
32
Definition eines Cursors
§ Definition eines Cursors mittels DECLARE-Kommando
1
2
3
4
5
DECLARE < Name des Cursors > CURSOR
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
FOR < SELECT Statement >
§ 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
33
Definition eines Cursors
§ Definition eines Cursors mittels DECLARE-Kommando
1
2
3
4
5
DECLARE < Name des Cursors > CURSOR
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
FOR < SELECT Statement >
§ 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
34
Öffnen und Bewegen eines Cursors
§ Öffnen eines Cursors mittels OPEN-Kommando
1
OPEN < Name des Cursors >
§ Bewegen des Cursors erfolgt mittels FETCH-Kommandos
1
2
3
4
FETCH
[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]
FROM < Name des Cursors >
INTO < lokale Variable >
§ 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
35
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. die
Bewegung 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
36
Schließen und Entfernen eines Cursors
§ Schließen eines Cursors mittels CLOSE-Kommando
1
CLOSE < Name des Cursors >
§ Entfernen eines Cursors mittels DEALLOCATE-Kommando
1
DEALLOCATE < Name des Cursors >
Datenbanken / Kapitel 10: Programmieren in SQL
37
Beispiel Cursor
§ Beispiel: Gib die Namen und Vornamen aller Studenten
in einem höheren als dem zehnten Semester aus
1
2
3
-- Cursor und lokale Variablen deklarieren
DECLARE StudentenCursor CURSOR FAST_FORWARD
FOR SELECT Vorname , Name FROM Studenten WHERE Semester > 10
4
5
DECLARE @Stu dente nVorna me varchar (30) , @StudentenName varchar (30)
6
7
8
9
10
11
12
13
14
15
-- Zeilenweise Verarbeitung
OPEN StudentenCursor
FETCH NEXT FROM StudentenCursor INTO @StudentenVorname , @StudentenName
WHILE ( @@FETCH_STATUS = 0)
BEGIN
PRINT ’ Vorname : ’ + @ Studen tenVor name
PRINT ’ Name : ’ + @StudentenName
FETCH NEXT FROM StudentenCursor INTO @StudentenVorname , @StudentenName
END
16
17
18
19
-- Cursor schlie ß en und entfernen
CLOSE StudentenCursor
DEALLOCATE StudentenCursor
Datenbanken / Kapitel 10: Programmieren in SQL
38
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
39
Beispiel Fehlerbehandlung
§ Beispiel: Division durch 0
1
PRINT 1/0 -- gibt Wert 8134 zur ü ck
1
2
3
4
SELECT
FROM
WHERE
AND
*
sys . messages
message_id = 8134
language_id = 1031
message id
language
severity
is event logged
text
8134
1031
16
false
Fehler aufgrund Division durch Null
Datenbanken / Kapitel 10: Programmieren in SQL
40
Fehlerschwere
§ Transact-SQL unterscheidet verschiedene Schweren
(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
41
Fehlerbehandlung
§ Transact-SQL verfügt, ähnlich Java, mit den Kommandos
TRY und CATCH über einen Möglichkeit, Fehlern
systematisch zu begegnen
1
2
3
4
5
6
BEGIN TRY
< Transact - SQL Kommandos >
END TRY
BEGIN CATCH
< Transact - SQL Kommandos >
END CATCH
§ 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
42
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
43
Beispiel Fehlerbehandlung
§ Beispiel: Anfrage mit ungültigem Attributnamen
1
2
3
4
5
6
7
8
9
BEGIN TRY
SELECT *
FROM Studenten
WHERE ProfNr = 42
END TRY
BEGIN CATCH
PRINT @@ERROR
PRINT ERROR_MESSAGE ()
END CATCH
gibt
1
2
ErrorCode : 207
Error : Ung ü ltiger Spaltenname ’ ProfNr ’.
aus
Datenbanken / Kapitel 10: Programmieren in SQL
44
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 Sprache
implementiert werden
Datenbanken / Kapitel 10: Programmieren in SQL
45
Anlegen einer gespeicherten Prozedur
§ Gespeicherte Prozedur lässt sich in MS SQL Server
anlegen mittels CREATE PROCEDURE
1
2
3
4
5
CREATE PROCEDURE < Name der Prozedur >
[ @Parameter1 Typ1 = Default1 ... ]
[ @ParameterN TypN = DefaultN OUTPUT ]
AS
< Folge von Transact - SQL Kommandos >
§ Beispiel: Löschen aller Datensätze in Tabelle hören
1
2
3
4
5
CREATE PROCEDURE H ö renL ö schen
@Anzahl int = 0 OUTPUT
AS
DELETE FROM h ö ren
SET @Anzahl = @@ROWCOUNT
Datenbanken / Kapitel 10: Programmieren in SQL
46
Ändern, Löschen und Anzeigen von Prozeduren
§ Ändern mittels ALTER PROCEDURE
1
ALTER PROCEDURE < Name der Prozedur >
§ Löschen mittels DROP PROCEDURE
1
DROP PROCEDURE < Name der Prozedur >
§ Anzeigen aller gespeicherten Prozeduren
1
2
3
SELECT *
FROM sys . sysobjects
WHERE type = ’p ’
Datenbanken / Kapitel 10: Programmieren in SQL
47
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
1
2
3
4
5
1
2
CREATE PROCEDURE SemesterErh ö hen @MatrNr int , @Erh ö hung int = 1
AS
UPDATE Studenten
SET Semester = Semester + @Erh ö hung
WHERE MatrNr = @MatrNr
EXECUTE SemesterErh ö hen 328762
-- f ü hrt Erh ö hung um 1 durch
EXECUTE SemesterErh ö hen 328762 , 2 -- f ü hrt Erh ö hung um 2 durch
Datenbanken / Kapitel 10: Programmieren in SQL
48
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; auch
ohne Wert kann so die Prozedur beendet werden
Datenbanken / Kapitel 10: Programmieren in SQL
49
Festlegen des Rückgabewerts
§ Beispiel: Ermittle Zahl der Studenten in einem Semester
Variante 1 mit OUTPUT Parameter
1
2
3
4
5
CREATE PROCEDURE StudentenAnzahl
@Semester int
@Anzahl int OUTPUT
AS
SET @Anzahl = ( SELECT COUNT (*) FROM Studenten WHERE Semester = @Semester )
Variante 2 mit RETURN
1
2
3
4
5
6
CREATE PROCEDURE StudentenAnzahl
@Semester int
AS
DECLARE @Anzahl int
SET @Anzahl = ( SELECT COUNT (*) FROM Studenten WHERE Semester = @Semester )
RETURN @Anzahl
Datenbanken / Kapitel 10: Programmieren in SQL
50
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 Sprache
implementiert werden
Datenbanken / Kapitel 10: Programmieren in SQL
51
Anlegen einer benutzerdefinierten Funktion
§ Benutzerdefinierte Funktion lässt sich in MS SQL Server
mittels CREATE FUNCTION anlegen
1
2
3
4
5
6
7
8
CREATE FUNCTION < Name der Funktion >
[ @Parameter1 Typ1 = Default1 ... ]
RETURNS R ü ckgabeTyp
AS
BEGIN
< Folge von Transact - SQL Kommandos >
RETURN < Wert von R ü ckgabeTyp >
END
§ 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
52
Ändern, Löschen und Anzeigen von Funktionen
§ Ändern mittels ALTER FUNCTION
1
ALTER FUNCTION < Name der Funktion >
§ Löschen mittels ALTER FUNCTION
1
DROP FUNCTION < Name der Funktion >
§ Anzeigen aller Funktionen mit skalarem Rückgabewert
1
SELECT * FROM sys . sysobjects WHERE type = ’ FN ’
§ Anzeigen aller Funktionen mit Tabelle als Rückgabewert
1
SELECT * FROM sys . sysobjects WHERE type = ’ IF ’
Datenbanken / Kapitel 10: Programmieren in SQL
53
Beispiel Funktion mit skalaren Rückgabewert
§ Beispiel: Studienjahre aus gegebenem Semester
1
2
3
4
5
6
DECLARE FUNCTION Studienjahre @Semester int
RETURNS int
AS
BEGIN
RETURN CEILING ( @Semester / 2) -- Aufrunden
END
§ Funktion Studienjahre() kann nun z.B. in einem
SELECT-Kommando verwendet werden
1
2
3
SELECT *
FROM Studenten
WHERE Studienjahre ( Semester ) > 5
Datenbanken / Kapitel 10: Programmieren in SQL
54
Beispiel Funktion mit Tabelle als Rückgabewert
§ Beispiel: Studenten in gegebenem Fach
1
2
3
4
5
6
DECLARE FUNCTION Studenten @Fach varchar (10)
RETURNS TABLE
AS
BEGIN
RETURN ( SELECT * FROM Studenten WHERE Fach = @Fach )
END
Datenbanken / Kapitel 10: Programmieren in SQL
55
10.5 Verwendung anderer Programmiersprachen
§ Gespeicherte Prozeduren und benutzerdefinierte
Funktionen lassen sich je nach RDBMS auch
in anderer Programmiersprache als der
jeweiligen prozeduralen Erweiterung
von 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 zu
erreichen und so Abstürze zu vermeiden
Datenbanken / Kapitel 10: Programmieren in SQL
56
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
57
Beispiel UDF in C#
§ Beispiel: Studienjahre aus gegebenem Semester in C#
1
2
3
using Math ;
using System ;
using Microsoft . SqlServer . Server ;
4
5
6
7
8
9
10
11
12
public static class HochschulUDFs
{
[ SqlFunction ]
public static int StudienJahre ( int semester )
{
return ( int ) Math . Ceiling ( semester / 2);
}
}
§ 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
58
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
59
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (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, 2013
http://www1.htwsaar.de/~braun/Uebungen/Datenbanken/
(Kapitel 8)
Datenbanken / Kapitel 10: Programmieren in SQL
60