View
6
Download
0
Category
Preview:
Citation preview
28
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
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ührungder 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 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
31
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 > CURSOR
2 [ FORWARD_ONLY | SCROLL ]3 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]4 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]5 FOR <SELECT Statement >
32
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 > CURSOR
2 [ FORWARD_ONLY | SCROLL ]3 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]4 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]5 FOR <SELECT Statement >
33
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 > CURSOR
2 [ FORWARD_ONLY | SCROLL ]3 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]4 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]5 FOR <SELECT Statement >
34
Ö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 >
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. 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
36
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 >
37
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 deklarieren2 DECLARE StudentenCursor CURSOR FAST_FORWARD3 FOR SELECT Vorname , Name FROM Studenten WHERE Semester > 10
4
5 DECLARE @StudentenVorname varchar (30) , @StudentenName varchar (30)
6
7 -- Zeilenweise Verarbeitung8 OPEN StudentenCursor
9 FETCH NEXT FROM StudentenCursor INTO @StudentenVorname , @StudentenName
10 WHILE ( @@FETCH_STATUS = 0)
11 BEGIN12 PRINT ’Vorname : ’ + @StudentenVorname
13 PRINT ’Name: ’ + @StudentenName
14 FETCH NEXT FROM StudentenCursor INTO @StudentenVorname , @StudentenName
15 END16
17 -- Cursor schlie ßen und entfernen18 CLOSE StudentenCursor
19 DEALLOCATE StudentenCursor
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
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 text8134 1031 16 false Fehler aufgrund Division durch Null
40
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
41
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 TRY
2 <Transact -SQL Kommandos >3 END TRY
4 BEGIN CATCH
5 <Transact -SQL Kommandos >6 END CATCH
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
gibt
aus
Datenbanken / Kapitel 10: Programmieren in SQL
1 BEGIN TRY2 SELECT *
3 FROM Studenten
4 WHERE ProfNr = 42
5 END TRY6 BEGIN CATCH7 PRINT @@ERROR
8 PRINT ERROR_MESSAGE ()
9 END CATCH
1 ErrorCode : 2072 Error: Ung u ltiger Spaltenname ’ProfNr ’.
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 Spracheimplementiert 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
§ 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 AS
5 <Folge von Transact -SQL Kommandos >
1 CREATE PROCEDURE HorenL o schen2 @Anzahl int = 0 OUTPUT
3 AS
4 DELETE FROM horen5 SET @Anzahl = @@ROWCOUNT
46
Ä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. sysobjects3 WHERE type = ’p’
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
Datenbanken / Kapitel 10: Programmieren in SQL
1 CREATE PROCEDURE SemesterErh ohen @MatrNr int , @Erh ohung int = 12 AS3 UPDATE Studenten4 SET Semester = Semester + @Erh ohung5 WHERE MatrNr = @MatrNr
1 EXECUTE SemesterErh ohen 328762 -- fuhrt Erh ohung um 1 durch2 EXECUTE SemesterErh ohen 328762 , 2 -- fuhrt Erh ohung um 2 durch
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; auchohne 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
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
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 Spracheimplementiert 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
§ 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 Ru ckgabeTyp4 AS
5 BEGIN
6 <Folge von Transact -SQL Kommandos >7 RETURN <Wert von Ru ckgabeTyp >8 END
52
Ä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 ’
53
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 int
2 RETURNS int
3 AS
4 BEGIN
5 RETURN CEILING ( @Semester / 2) -- Aufrunden6 END
1 SELECT *2 FROM Studenten3 WHERE Studienjahre ( Semester ) > 5
54
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
55
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
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#
§ 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 HochschulUDFs
6 {
7 [ SqlFunction ]
8 public static int StudienJahre (int semester )
9 {
10 return (int)Math. Ceiling ( semester / 2);
11 }
12 }
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 auch inanderen Sprachen (z.B. Java) implementieren
Datenbanken / Kapitel 10: Programmieren in SQL
59
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, 2013
http://www1.htwsaar.de/~braun/Uebungen/Datenbanken/
(Kapitel 8)
Datenbanken / Kapitel 10: Programmieren in SQL
Recommended