T-SQL Ninja #20

CURSOR

Was sind CURSOR?

In den letzten beiden Wochen hattet ihr im Dojo das WHILE-Statement kennengelernt und angewendet um Batches von Zeilen zu löschen. Wir hatten euch dabei den Warnhinweis gegeben, dass der SQL Server (so wie fast jede relationale Datenbankengine) primär darauf ausgelegt ist, mit Datenmengen, also mit mehreren Zeilen zu operieren. Doch was ist, wenn ihr wirklich aus irgendwelchen Gründen gezwungen seid, einzelne Zeilen einer Ergebnismenge zu verarbeiten? Einige unserer WHILE-Konstrukte haben bereits genau das getan und einige von euch werden zusammengezuckt sein und die Seite instinktiv geschlossen haben. Wenn ihr das seid, könnt ihr das für diese Woche auch tun, denn heute behandeln wir CURSOR. Einen CURSOR könnt ihr euch wie einen Zeiger vorstellen, der auf die erste Zeile der Ergebnismenge zeigt und den ihr immer eine Zeile weiter springen lassen könnt.

Wie könnt ihr mit CURSORn arbeiten?

Um einen Cursor zu definieren, müsst ihr zunächst eine Variable definieren, die die Zeile hält. Das tut ihr, indem ihr eine Variable deklariert, die als Typ CURSOR FOR euer SELECT Statement ist. Schreiben wir also zunächst ein Statement, das uns eine Ergebnismenge liefert, über die wir sinnvollerweise iterieren können. Nehmen wir beispielsweise einmal die Menge der Datenbanken auf unserem Server:

USE master; 

SELECT [name] 
FROM dbo.sysdatabases

Nun wollen wir über diese Ergebnismenge iterieren und für jede Datenbank eine sinnvolle Operation durchführen. Wir hatten etwas ähnliches vor zwei Wochen schon getan, als wir über alle Indexe einer Datenbank iterieren wollten. Damals hatten wir immer mit TOP(1) die erste Zeile ausgewählt und aus der Ergebnismenge gelöscht. Heute gehen wir anders vor und definieren uns einen CURSOR um über die Ergebnismenge zu iterieren. Konkret könnte das wie folgt aussehen:

DECLARE my_cursor 
CURSOR FOR 
SELECT [name] 
FROM dbo.sysdatabases

Nun müssen wir die Abfrage ausführen und den Cursor auf die erste Zeile der Ergebnismenge setzen, dafür definieren wir zunächst eine Variable, die das Ergebnis hält:

DECLARE @name varchar(255)

Nun können wir den Cursor öffnen und die nächste Zeile in die Variable schreiben (wenn euer Select mehreren Spalten enthält, könnt ihr jede auf eine eigene Variable mappen).

OPEN my_cursor  
FETCH NEXT FROM my_cursor INTO @name

Um nun eine Zeile nach der anderen in die Variable zu lesen, verwenden wir wieder eine WHILE-Schleife. Die Schleifenbedingung ist dabei, dass noch eine Zeile vom Cursor gelesen werden konnte. Das sieht dann wie folgt aus:

WHILE @@FETCH_STATUS = 0  
BEGIN  
      FETCH NEXT FROM my_cursor INTO @name 
END 

Wenn ihr damit fertig seid, müsst ihr den CURSOR zunächst schließen, also dafür sorgen, dass er nicht mehr auf eine Ergebnismenge zeigt und ihn dann löschen, was über den Befehl DEALLOCATE geht:

CLOSE my_cursor  
DEALLOCATE my_cursor 

In der Schleife könnt ihr nun mit dem Datenbanknamen, der in @name gespeichert ist, tun was ihr möchtet. Ihr könnt beispielsweise Backups aller Datenbanken erstellen. In Gänze sähe der Code dafür so aus:

DECLARE @name varchar(255)
DECLARE @backuppath varchar(255)
DECLARE @backupfullfile varchar(510)

SET @backuppath = '~/'

OPEN my_cursor  
FETCH NEXT FROM my_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @backupfullfile = @backuppath+@name+'.bak'
    BACKUP DATABASE @name TO DISK =  @backupfullfile
    FETCH NEXT FROM my_cursor INTO @name 
END 

CLOSE my_cursor  
DEALLOCATE my_cursor 

Der Sicherheit halber sei hier nochmals gewarnt: anders als ein Katana ist ein CURSOR ein zweischneidiges Schwert. Einerseits ermöglicht er es euch, viele Dinge, gerade im Bereich der Wartung von Datenbanken mehrfach auszuführen, andererseits ist genau dieses zeilenweise Vorgehen eben keine Effiziente Art in relationalen Datenbanksystemen zu Arbeiten. Doch wenn euch das bewusst ist, könnt ihr über CURSORs viele eurer täglichen Aufgaben automatisieren…

Referenzen

Ninja-Notebooks @ GitHub

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.