Artikel
09/24/2010

Transact-SQL-Cursor

Transact-SQL-Cursor werden in erster Linie in gespeicherten Prozeduren, Triggern und Transact-SQL-Skripts verwendet. Darin stellen sie die Inhalte eines Resultsets für andere Transact-SQL-Anweisungen bereit.

Der normale Vorgang für das Verwenden eines Transact-SQL-Cursors in einer gespeicherten Prozedur oder einem Trigger setzt sich aus folgenden Schritten zusammen:

Deklarieren Sie die Transact-SQL-Variablen so, dass sie Daten enthalten, die vom Cursor zurückgegeben werden. Deklarieren Sie eine Variable pro Resultsetspalte. Deklarieren Sie die Variablen so, dass sie groß genug sind, um die Werte aufnehmen zu können, die von der Spalte zurückgegeben werden, und deklarieren Sie sie mit einem Datentyp, der implizit aus dem Datentyp der Spalte konvertiert werden kann.
Verwenden Sie eine DECLARE CURSOR-Anweisung, um einen Transact-SQL-Cursor mit einer SELECT-Anweisung zu verknüpfen. Die DECLARE CURSOR-Anweisung definiert die Merkmale des Cursors, wie beispielsweise den Cursornamen und die Möglichkeit zur Angabe eines schreibgeschützten Cursors oder eines Vorwärtscursors.
Verwenden Sie die OPEN-Anweisung, um die SELECT-Anweisung auszuführen, und füllen Sie den Cursor auf.
Verwenden Sie die FETCH INTO-Anweisung, um einzelne Zeilen abzurufen, und sorgen Sie dafür, dass die Daten für die einzelnen Spalten in eine angegebene Variable verschoben werden. Andere Transact-SQL-Anweisungen können dann auf diese Variablen verweisen, um auf abgerufene Datenwerte zuzugreifen. Transact-SQL-Cursor unterstützen nicht das Abrufen von Zeilenblöcken.
Wenn Sie den Cursor nicht mehr benötigen, verwenden Sie die CLOSE-Anweisung. Durch das Schließen eines Cursors werden Ressourcen freigegeben, wie z. B. das Resultset des Cursors und entsprechende Sperren für die aktuelle Zeile. Die Cursorstruktur steht jedoch noch immer für eine weitere Verarbeitung zur Verfügung, wenn Sie erneut eine OPEN-Anweisung eingeben. Da der Cursor noch immer vorhanden ist, können Sie den Cursornamen zu diesem Zeitpunkt nicht erneut verwenden. Die DEALLOCATE-Anweisung gibt alle Ressourcen, die dem Cursor zugeordnet sind, einschließlich des Cursornamens, vollständig frei. Nachdem die Zuordnung des Cursors aufgehoben wurde, müssen Sie eine DECLARE-Anweisung eingeben, um den Cursor neu zu erstellen.

Überwachen der Transact-SQL-Cursoraktivität

Sie können mithilfe der gespeicherten Systemprozedur sp_cursor_list eine Liste der Cursor abrufen, die für die aktuelle Verbindung sichtbar sind, und mithilfe von sp_describe_cursor, sp_describe_cursor_columns und sp_describe_cursor_tables die Merkmale eines Cursors ermitteln.

Nach dem Öffnen des Cursors zeigt die @@CURSOR_ROWS-Funktion oder die cursor_rows-Spalte, die von sp_cursor_list oder sp_describe_cursor zurückgeben wurde, die Anzahl der Zeilen im Cursor an.

@@FETCH_STATUS wird nach jeder FETCH-Anweisung aktualisiert, um dem Status des letzten Abrufvorgangs zu entsprechen. Sie können diese Statusinformationen auch über die von sp_describe_cursor zurückgegebene fetch_status-Spalte abrufen. @@FETCH_STATUS meldet den Status, wie das Abrufen über die letzte oder erste Zeile im Cursor hinaus. @@FETCH_STATUS ist für Ihre Verbindung global und wird durch jeden Abrufvorgang zurückgesetzt, die für irgendeinen für die Verbindung geöffneten Cursor ausgeführt wird. Wenn Sie später den Status benötigen, speichern Sie @@FETCH_STATUS in einer Benutzervariablen, bevor Sie eine weitere Anweisung über die Verbindung ausführen. Selbst wenn die nächste Anweisung eventuell keine FETCH-Anweisung ist, könnte es eine der Anweisungen INSERT, UPDATE oder DELETE sein, die einen Trigger mit FETCH-Anweisungen auslöst und damit @@FETCH_STATUS zurücksetzt. Die von sp_describe_cursor zurückgegebene fetch_status-Spalte ist für den angegebenen Cursor spezifisch und nicht von FETCH-Anweisungen betroffen, die auf andere Cursor verweisen. sp_describe_cursor ist jedoch von FETCH-Anweisungen betroffen, die auf denselben Cursor verweisen; somit sollten Sie bei der Verwendung trotzdem sorgfältig vorgehen.

Nach dem Beenden von FETCH wird der Cursor in der abgerufenen Zeile positioniert. Die abgerufene Zeile wird als aktuelle Zeile bezeichnet. Wenn der Cursor nicht als schreibgeschützter Cursor deklariert wurde, können Sie die Anweisungen UPDATE oder DELETE mit einer WHERE CURRENT OF cursor_name-Klausel ausführen, um die aktuelle Zeile zu ändern.

Es kann sich bei dem Namen, der einem Transact-SQL-Cursor von der DECLARE CURSOR-Anweisung gegeben wird, um einen globalen oder einen lokalen Namen handeln. Auf globale Cursornamen wird von jedem Batch, jeder gespeicherten Prozedur oder jedem Trigger, die bzw. der für dieselbe Verbindung ausgeführt wird, verwiesen. Auf lokale Cursornamen kann von außerhalb des Batches, der gespeicherten Prozedur oder dem Trigger, in der bzw. dem der Cursor deklariert wurde, nicht verwiesen werden. Deshalb sind lokale Cursor in Triggern oder gespeicherten Prozeduren vor unbeabsichtigten Verweisen außerhalb der gespeicherten Prozedur oder des Triggers geschützt.

Verwenden der cursor-Variablen

MicrosoftSQL Server unterstützt auch Variablen mit einem cursor-Datentyp. Ein Cursor kann einer cursor-Variablen durch eine der beiden folgenden Methoden zugeordnet werden:

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks.Person.Contact

SET @MyVariable = MyCursor
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks.Person.Contact;
DEALLOCATE MyCursor;

Nachdem ein Cursor einer cursor-Variablen zugeordnet wurde, kann die cursor-Variable anstelle des Cursornamens in Transact-SQL-Cursoranweisungen verwendet werden. Der cursor-Datentyp kann auch Ausgabeparametern von gespeicherten Prozeduren zugewiesen werden, die dann einem Cursor zugeordnet werden. Dies ermöglicht gespeicherten Prozeduren, lokale Cursor kontrolliert verfügbar zu machen.

Verweisen auf Transact-SQL-Cursor

Auf Transact-SQL-Cursornamen und -Variablen kann nur durch Transact-SQL-Anweisungen verwiesen werden; es ist nicht möglich, über die API-Funktionen von OLE DB, ODBC bzw. ADO auf sie zu verweisen. Wenn Sie beispielsweise DECLARE CURSOR verwenden und den Transact-SQL-Cursor mit OPEN öffnen, besteht keine Möglichkeit, die ODBC-Funktionen SQLFetch oder SQLFetchScroll zum Abrufen einer Zeile aus dem Transact-SQL-Cursor zu verwenden. Anwendungen, die Cursor verarbeiten müssen und diese APIs verwenden, sollten die Cursorunterstützung, die in die Datenbank-API integriert ist, anstelle der Transact-SQL-Cursor verwenden.

Sie können Transact-SQL-Cursor in Anwendungen mithilfe von FETCH und durch Binden der von FETCH zurückgegebenen Spalten an eine Programmvariable verwenden. FETCH von Transact-SQL unterstützt jedoch keine Batches; somit ist dies die ineffektivste Methode für die Rückgabe von Daten an eine Anwendung. Für jedes Abrufen einer Zeile ist ein Roundtrip zum Server notwendig. Es ist effizienter, die in die Datenbank-APIs integrierte Cursorfunktionalität zu verwenden, die das Abrufen von Zeilenbatches unterstützt.

Transact-SQL-Cursor sind dann außerordentlich effizient, wenn sie in gespeicherten Prozeduren und Triggern enthalten sind. Der Grund dafür ist, dass alle Vorgänge in einen Ausführungsplan auf dem Server kompiliert werden und kein Netzwerkverkehr im Zusammenhang mit dem Abrufen der Zeilen entsteht.

Transact-SQL-Cursor und SET-Optionen

In SQL Server wird ein Fehler ausgelöst, wenn eine FETCH-Anweisung ausgegeben wird, in der eine Änderung der Werte vorliegt, die seit dem Zeitpunkt des Öffnens des Cursors eingetreten ist. Dieser Fehler tritt bei allen im Folgenden genannten Optionen auf, die sich auf den Ausführungsplan auswirken, sowie bei den Optionen, die für indizierte Sichten und berechnete Spalten erforderlich sind. Damit dieser Fehler vermieden wird, sollten Sie keine SET-Optionen ändern, während ein Cursor geöffnet ist.

Optionen, die sich auf den Ausführungsplan auswirken

ARITHABORT

NUMERIC_ROUNDABORT

FORCEPLAN

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

ANSI_WARNINGS

ANSI_PADDING

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

DATEFIRST