Betrieb von Datenbanken Marco Skulschus & Marcus Wiederstein

Betrieb von DatenbankenMarco Skulschus & Marcus Wiederstein Grundlagen T-SQL Lehrbuch, Kapitel 5

 Seminar-Inhalt Grundlagen Einfache Abfragen Komplexe Abfragen Datenmanipulation Grundlagen T-SQL Programm-Module in der DB Administration

 Modul-Inhalt T-SQL Blöcke Kontrollanweisungen Fehlerbehandlung Cursor Transaktionen

T-SQL-Blöcke: Prinzip • Viele Datenbanken bieten umfassende Erweiterungen von SQL oder sogar eigene Programmiersprachen an bzw. erlauben, eine oder mehrere gängige Programmiersprachen innerhalb der Datenbank für die Anwendungsentwicklung zu nutzen. • MS SQL Server: T-SQL, .NET • Oracle: PL/SQL, Java, C++

T-SQL-Blöcke: Prinzip • Einsatzbereich von T-SQL • Anwendungsentwicklung mit der Zielsetzung, die Datenbank aus Administratorensicht zu automatisieren, • Sicherheits- und Integritätsmerkmale der Datenbank zu realisieren, die mit einfachem SQL bzw. einfachen Datenstrukturanweisungen nicht möglich wären, • strukturierten und vereinfachten Zugriff auf die Datenbank zu gewähren, wobei gleichzeitig auch Sicherheits- und Filteraspekte im Vordergrund stehen.

T-SQL-Blöcke: Variablen • Die allgemeine Syntax hat die Form: DECLARE {{ @variable [AS] datentyp } | { @cursor_variable CURSOR } | { @tabellen_variable_name < tabellen_definition > } } [ ,...n] • Initialisierung einer Variablen: SET { @lokale_variable [:: eigenschaft | feld ] = ausdruck | benutzerdefiniert_datentyp { . | :: } methode (argument [ ,...n ] ) }

 Fragen...

Kontrollanweisungen: Fallunterscheidungen • Fallunterscheidungen lassen sich mit if-else-Strukturen abbilden, wobei keine weiteren Fälle angegeben werden können. • Sofern der Testausdruck im IF-Zweig den Wahrheitswert TRUE liefert, werden die Anweisungen in seinem Anweisungsblock ausgeführt, ansonsten diejenigen des ELSE-Blocks. IF Testausdruck { SQL-Anweisung | Anweisungsblock } [ ELSE { SQL-Anweisung | Anweisungsblock } ]

Kontrollanweisungen: Schleifen • Um Anweisungen mehrfach aufgrund einer Bedingung auszuführen, gibt es WHILE. • Optionale Schlüsselwörter • BREAK für die vorzeitige und bedingten Unterbrechung der Schleife • CONTINUE für die bedingte Fortsetzung der Schleife vor der Ausführung der nachfolgenden Anweisungen WHILE Testausdruck { SQL-Anweisung | Anweisungsblock } [ BREAK ] { SQL-Anweisung | Anweisungsblock } [ CONTINUE ] { SQL-Anweisung | Anweisungsblock }

 Fragen...

Fehlerbehandlung: Ausnahmen • Ein Block wird von der neuen Syntax BEGIN TRY und END TRY umschlossen. • Jeder Fehler, der die Datenbankverbindung nicht schließt und der einen Schweregrad größer als 10 besitzt, führt nicht zu einer Fehlermeldung, sondern zu einem Sprung in den auf den TRY-Block folgenden CATCH-Block. BEGIN TRY { SQL-Anweisung | Anweisungsblock } END TRY BEGIN CATCH { SQL-Anweisung | Anweisungsblock } END CATCH

Fehlerbehandlung: Ausnahmen • Zur Untersuchung des aufgetreten Fehlers kann man folgende Funktionen im CATCH-Block verwenden. • ERROR_NUMBER() liefert die Fehlernummer. • ERROR_SEVERITY() liefert den Schweregrad. • ERROR_STATE() liefert die Fehlerstatusnummer. • ERROR_PROCEDURE() liefert den Namen der gespeicherten Prozedur oder des Triggers, welche den Fehler verursachten. • ERROR_LINE() liefert die Zeilennummer in dem Modul, in dem der Fehler aufgetreten ist. • ERROR_MESSAGE() liefert den Text der Fehlermeldung.

Fehlerbehandlung: Traditionelle Behandlung • Funktion @@errorliefert 0 oder eine Fehlernummer. • Da die Funktion immer nur Informationen über den letzten Fehler liefert, sollte man diese Informationen unmittelbar nach der SQL-Anweisung, die einen Fehler auslöst, auch abrufen und in einer lokalen Variable speichern. • Um herauszufinden, ob überhaupt ein Fehler aufgetreten ist, kann man @@errorauf den Wert 0 prüfen.

 Fragen...

Cursor: Deklaration • Allgemeine Syntax: DECLARE name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR abfrage [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] • Wichtige Angaben: • SCROLL: Diese Angabe richtet den Cursor so ein, dass sämtliche Abrufoptionen (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) genutzt werden können. Ansonsten kann nur immer die nächste Zeile abgerufen werden. • UPDATE [OF column_name [,...n]]: Wenn ein Cursor aktualisierbar sein soll, dann kann man entscheiden, ob man alle Spalten aktualisierbar machen möchte oder nicht. Sofern man die UPDATE OF-Klausel nicht verwendet, können Änderungen an allen Spalten vorgenommen werden.

Cursor: Verwendung • Schritte zur Verwendung: • Öffnen • Abruf • Schließen • Löschen OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name } FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ] CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

Cursor: Funktionen • Ermittlung von Cursor-Zuständen: • @@FETCH_STATUS ermittelt den Status der letzten FETCH-Anweisung und liefert die Werte • 0 für einen erfolgreichen Abruf • -1 für einen fehlgeschlagenen Abruf • -2 für eine Zeile, die nicht mehr in der Ergebnismenge vorhanden ist • @@CURSOR_ROWS ruft die Anzahl der Zeilen ab, die im Cursor abrufbar sind. Die Funktion liefert folgende Werte: • -m liefert bei einem asynchron aufgefüllten Cursor die Werte, die aktuell verfügbar sind (keyset-Tabelle). • -1 zeigt an, dass es sich um einen dynamischen Cursor handelt und daher die Anzahl der abgerufenen Zeilen nicht definitiv ermittelt werden kann. • 0 zeigt an, dass kein Cursor offen ist oder der zuletzt geöffnete Cursor nun geschlossen ist und die Information nicht abgerufen werden kann. • n liefert die Anzahl der verfügbaren Zeilen.

Cursor: Funktionen • Ermittlung von Cursor-Zuständen: • Die Skalarfunktion CURSOR_STATUS liefert die Information zurück, ob eine Prozedur einen Cursor und eine relationale Ergebnismenge zurückgeliefert hat. • Die allgemeine Syntax lautet: CURSOR_STATUS ( { 'local' , 'cursor_name' } | { 'global' , 'cursor_name' } | { 'variable' , 'cursor_variable' } )

 Fragen...

Transaktionen: Grundprobleme • Durch einen allgemeinen Systemfehler wird eine Reihe von Operationen nicht komplett bis zum Ende ausgeführt, sondern bleibt quasi auf halber Strecke unvollendet. Dies führt dazu, dass Datenänderungen unvollständig umgesetzt wurden, obwohl noch weitere Änderungen vorgesehen waren. • Beim Mehrbenutzerbetrieb geschieht es regelmäßig, dass Benutzer mit gleichen Aktivitätsprofilen (Buchhaltung, Controller) auf bestimmte Tabellenbereiche lesend oder schreibend zugreifen. Dabei können im Moment des Lesens Abfrageergebnisse geändert werden, aufgrund derer andere Operationsentscheidungen getroffen wurden, die sonst nicht getroffen worden wären.

Transaktionen: ACID • Unteilbarkeit (Atomicity)Eine Transaktion umfasst einzelne oder mehrere Operationen in Form von SQL-Befehlen, die entweder komplett oder gar nicht ausgeführt werden. • Einheitlichkeit (Consistency) Die einzelnen Operationen einer Transaktionen müssen in ihrer Gesamtheit eine Datenbank von einem konsistenten Zustand in einen anderen konsistenten Zustand überführen. • Isolation (Isolation)Im Mehrbenutzerbetrieb werden verschiedene Transaktionen isoliert voneinander ausgeführt. Dadurch sind die Änderungen der einzelnen Transaktionen bzw. auch die Änderungen der einzelnen Operationen erst nach Abschluss der kompletten Transaktion für alle anderen Benutzer sichtbar. • Dauerhaftigkeit (Durability)Sobald eine Transaktion abgeschlossen ist, bleiben ihre Änderungen dauerhaft in der Datenbank erhalten und dürfen nicht erneut durch Fehler beeinträchtigt werden oder verloren gehen.

Transaktionen: Parallelität • Parallelitätsnebeneffekte • Dirty Read oder verlorene Aktualisierung: Transaktion A liest Daten, die von Transaktion B geändert und nicht bestätigt wurden. Dadurch kann es geschehen, dass die Daten nachträglich wieder geändert werden. • Nicht wiederholbarer Lesevorgang: Wiederholte Lesevorgänge liefern verschiedene Ergebnisse. • Phantom: Suchergebnisse ändern sich bei wiederholter Ausführung, weil Änderungen am Datenbestand vorgenommen werden. Transaktion A liest Datensätze, die von Transaktion B geändert werden. Beim nächsten Abruf von Transaktion A befinden sich bei gleichen Filtern andere Datensätze in der Ergebnismenge.

Transaktionen: Beginn • Um eine Transaktion zu beginnen, verwendet man BEGIN TRANSACTION. Dadurch legt man auch einen Zeitpunkt oder einen Zustand fest, an dem die Daten logisch und physisch konsistent sind. BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] • Folgende Parameter kann man verwenden: • transaction_name - Namen der Transaktion. • @tran_name_variable - Namen der Transaktion als Variable • WITH MARK [ 'description' ] legt fest, dass die Transaktion im Protokoll markiert wird.

Transaktionen: Ende • Um eine Transaktion zu beenden, verwendet man den COMMIT-Befehl. COMMIT [ WORK ] COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] • Um eine Transaktion und damit sämtliche Aktivitäten, die seit dem letzten BEGIN ausgeführt wurden, wieder zurückzusetzen verwendet man ROLLBACK. ROLLBACK [ WORK ] ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ]

Transaktionen: Funktionen • Zwei Funktionen, die im Rahmen der Transaktionsverwaltung nützlich sind: • XACT_STATE() liefert den aktuellen Transaktionsstatus einer Sitzung zurück. Folgende Werte sind möglich: • 1 gibt an, dass die Sitzung eine aktive Transaktion aufweist. • 0 gibt an, dass in der Sitzung keine Transaktion aktiv ist. • -1 gibt an, dass die Sitzung eine aktive, fehlerhafte Transaktion aufweist. • @@TRANCOUNT liefert einen Statuswert zurück, ob eine Transaktion geöffnet ist oder nicht.

Transaktionen: Sicherungspunkte • Um eine Möglichkeit zu besitzen, verschiedene einzelne Zwischenstationen zu speichern und auch zu ihnen zurückzukehren, gibt es die SAVE-Anweisung, mit der so genannte Sicherungspunkte erstellt werden können. SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } • Um eine Transaktion schließlich zu einem Sicherungspunkt zurückzusetzen, ist die erweiterte Form der ROLLBACK-Anweisung zu verwenden. • Sie erwartet zusätzlich den Namen eines Sicherungspunktes. ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ]

Transaktionen: Isolationsstufen • Es besteht die Möglichkeit, innerhalb einer Verbindung erweiterten Einfluss auf das Verhalten zu nehmen, Zeilensperren einzurichten oder verteilte Transaktionen einzurichten. • Dazu gibt es die Anweisung SET TRANSACTION ISOLATION LEVEL, welche eine von fünf verschiedenen Arten anbietet, dieses Verhalten zu steuern. • Die allgemeine Syntax der Anweisung lautet: SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }

Transaktionen: Isolationsstufen • Die Bedeutung und Funktionsweise der verschiedenen Angaben ist nachfolgende erläutert: • READ UNCOMMITTED - Zeilen, die gerade von anderen Transaktionen bearbeitet werden und noch nicht bestätigt sind, können ebenfalls gelesen werden (Gegenteil von READ COMMITTED). • READ COMMITTED - Zeilen, die gerade von anderen Transaktionen bearbeitet werden und noch nicht bestätigt sind, können nicht gelesen werden. • REPEATABLE READ - Zeilen, die gerade von anderen Transaktionen bearbeitet werden und noch nicht bestätigt sind, können nicht gelesen werden. Zusätzlich sind auch die Datensätze, die von der aktuellen Transaktion bearbeitet werden, für andere Transaktionen so lange unsichtbar, bis sie bestätigt werden. • SNAPSHOT legt fest, dass die Daten, die in einer Transaktion gelesen werden, einem konsistenten Zustand der Datenbank entsprechen, welcher zu Beginn der Transaktion gegeben war. • SERIALIZABLE legt fest, dass Anweisungen keine von anderen Transaktionen geänderte Daten lesen können, die noch nicht bestätigt sind, dass andere Transaktionen auch erst nach eigener Bestätigung geänderte Daten lesen können und dass andere Transaktionen erst nach der aktuellen Transaktion Daten mit neuen Schlüsselwerten einfügen können, die für die aktuelle Transaktion reserviert waren.

 Fragen...

Betrieb von Datenbanken Marco Skulschus & Marcus Wiederstein