DECLARE CURSOR (Transact-SQL)

Definiert die Attribute eines Transact-SQL-Servercursors, wie z. B. dessen Scrollverhalten, sowie die Abfrage, die zum Erstellen des Resultsets verwendet wird, auf das der Cursor ausgeführt wird. DECLARE CURSOR unterstützt sowohl die Syntax basierend auf dem ISO-Standard als auch eine Syntax, für die eine Teilmenge der Transact-SQL-Erweiterungen verwendet wird.

Transact-SQL-Syntaxkonventionen

Syntax

ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

Argumente

• cursor_name
  Der Name des definierten Transact-SQL-Servercursors. Dabei muss cursor_name den Regeln für Bezeichner entsprechen. Weitere Informationen zu Regeln für Bezeichner finden Sie unter Verwenden von Bezeichnern als Objektnamen.

• INSENSITIVE
  Definiert einen Cursor, der eine temporäre Kopie der von ihm zu verwendenden Daten erzeugt. Sämtliche Anforderungen an den Cursor werden von dieser temporären Tabelle in tempdb beantwortet; Änderungen an den Basistabellen werden nicht in den Daten wiedergegeben, die durch Abrufvorgänge an diesen Cursor zurückgegeben wurden. Darüber hinaus lässt dieser Cursor keine Änderungen zu. Bei Verwendung der ISO-Syntax ohne die Option INSENSITIVE werden ausgeführte Löschvorgänge und Aktualisierungen an den zugrunde liegenden Tabellen (von einem beliebigen Benutzer) in späteren Abrufvorgängen wiedergegeben.

• SCROLL
  Gibt an, dass alle Abrufoptionen (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) zur Verfügung stehen. Wird SCROLL in einer ISO-DECLARE CURSOR-Anweisung nicht angegeben, wird nur die Abrufoption NEXT unterstützt. Es kann nur eine der beiden Optionen SCROLL oder FAST_FORWARD angegeben werden.

• select_statement
  Eine standardmäßige SELECT-Anweisung, die das Resultset des Cursors definiert. Bei der Deklaration eines Cursors sind die Schlüsselwörter COMPUTE, COMPUTE BY, FOR BROWSE und INTO innerhalb von select_statement nicht zulässig.

  SQL Server konvertiert den Cursor implizit in einen anderen Typ, wenn die Klauseln in select_statement in Konflikt mit der Funktionalität des angeforderten Cursortyps stehen. Weitere Informationen finden Sie unter Verwenden impliziter Cursorkonvertierungen.

• READ ONLY
  Verhindert, dass über diesen Cursor Aktualisierungen vorgenommen werden. Auf den Cursor kann nicht in einer WHERE CURRENT OF-Klausel in einer UPDATE- oder DELETE-Anweisung verwiesen werden. Diese Option setzt die Standardeinstellung außer Kraft, nach der ein Cursor aktualisiert werden kann.

• UPDATE [OF column_name [,...n]]
  Definiert aktualisierbare Spalten innerhalb des Cursors. Wenn OF column_name [,...n] angegeben wird, können Änderungen nur in den aufgelisteten Spalten vorgenommen werden. Wenn UPDATE ohne Spaltenliste angegeben wird, können alle Spalten aktualisiert werden.

• cursor_name
  Der Name des definierten Transact-SQL-Servercursors. Dabei muss cursor_name den Regeln für Bezeichner entsprechen. Weitere Informationen zu Regeln für Bezeichner finden Sie unter Verwenden von Bezeichnern als Objektnamen.

• LOCAL
  Gibt an, dass der Gültigkeitsbereich des Cursors lokal zu dem Batch, der gespeicherten Prozedur oder dem Trigger ist, in dem bzw. in der er erstellt wurde. Der Cursorname ist nur innerhalb dieses Bereichs gültig. Auf den Cursor kann durch lokale Cursorvariablen im Batch, in der gespeicherten Prozedur, im Trigger oder im OUTPUT-Parameter einer gespeicherten Prozedur verwiesen werden. Ein OUTPUT-Parameter kann den lokalen Cursor an den aufrufenden Batch, die aufrufende gespeicherte Prozedur oder den aufrufenden Trigger zurückgeben. Diese können den Parameter einer Cursorvariablen zuweisen, um nach dem Beenden der gespeicherten Prozedur auf den Cursor zu verweisen. Die Zuordnung des Cursors wird implizit aufgehoben, wenn der Batch, die gespeicherte Prozedur oder der Trigger beendet wird, es sei denn, der Cursor wurde in einem OUTPUT-Parameter zurückgegeben. Wenn die Rückgabe in einem OUTPUT-Parameter erfolgt, wird die Zuordnung des Cursors aufgehoben, wenn die Zuordnung der letzten auf ihn verweisenden Variablen aufgehoben wird oder wenn der Cursor den Gültigkeitsbereich verlässt.

• GLOBAL
  Gibt an, dass der Bereich des Cursors global zur Verbindung ist. Auf den Cursornamen kann in jeder gespeicherten Prozedur und in jedem Batch verwiesen werden, die bzw. der von der Verbindung ausgeführt wird. Die Zuordnung des Cursors wird nur implizit aufgehoben, wenn die Verbindung getrennt wird.

  Hinweis

  Wird weder GLOBAL noch LOCAL angegeben, wird der Standard durch die Einstellung der Datenbankoption default to local cursor gesteuert. In SQL Server, Version 7.0, ist diese Option standardmäßig FALSE, damit sich eine Übereinstimmung mit früheren Versionen von SQL Server ergibt, in denen alle Cursor global waren. Die Standardeinstellung dieser Option kann sich in zukünftigen Versionen von SQL Server ändern. Weitere Informationen finden Sie unter Festlegen von Datenbankoptionen.

• FORWARD_ONLY
  Gibt an, dass innerhalb des Cursors die Zeilen nur nacheinander von der ersten bis zur letzen Zeile gelesen werden können. FETCH NEXT ist die einzige unterstützte Abrufoption. Wenn FORWARD_ONLY ohne eines der Schlüsselwörter STATIC, KEYSET oder DYNAMIC angegeben wird, arbeitet der Cursor mit der Option DYNAMIC. Wenn weder FORWARD_ONLY noch SCROLL angegeben wird, wird standardmäßig FORWARD_ONLY verwendet, es sei denn, die Schlüsselwörter STATIC, KEYSET oder DYNAMIC werden angegeben. STATIC-, KEYSET- und DYNAMIC-Cursor werden standardmäßig auf SCROLL festgelegt. Anders als bei Datenbank-APIs, wie z. B. ODBC und ADO, wird FORWARD_ONLY für STATIC-, KEYSET- und DYNAMIC-Transact-SQL-Cursor unterstützt.

• STATIC
  Definiert einen Cursor, der eine temporäre Kopie der von ihm zu verwendenden Daten erzeugt. Sämtliche Anforderungen an den Cursor werden von dieser temporären Tabelle in tempdb beantwortet; Änderungen an den Basistabellen werden nicht in den Daten wiedergegeben, die durch Abrufvorgänge an diesen Cursor zurückgegeben wurden. Darüber hinaus lässt dieser Cursor keine Änderungen zu.

• KEYSET
  Gibt an, dass im Cursor die Mitgliedschaft und Reihenfolge der Zeilen fest ist, wenn der Cursor geöffnet wird. Die Menge der Schlüssel, die die Zeilen eindeutig identifizieren, wird in einer Tabelle in tempdb erstellt, die als keyset bezeichnet wird.

  Hinweis
  Wenn die Abfrage auf mindestens eine Tabelle ohne einen eindeutigen Index verweist, wird der Keysetcursor in einen statischen Cursor konvertiert.

  Änderungen an Nichtschlüsselwerten in den Basistabellen, die vom Cursorbesitzer oder durch Ausführen eines Commits von anderen Benutzern vorgenommen wurden, werden sichtbar, wenn der Besitzer im Cursor einen Bildlauf durch den Cursor durchführt. Von anderen Benutzern vorgenommene Einfügungen sind nicht sichtbar (Einfügevorgänge können nicht über einen Transact-SQL-Servercursor durchgeführt werden). Wenn eine Zeile gelöscht wird, wird bei einem Versuch, die Zeile abzurufen, @@FETCH_STATUS mit dem Wert -2 zurückgegeben. Aktualisierungen von Schlüsselwerten von außerhalb des Cursors sind vergleichbar mit einem Löschen der alten Zeile, gefolgt von einem Einfügen der neuen Zeile. Die Zeile mit den neuen Werten ist nicht sichtbar, und bei Versuchen, die Zeile mit den alten Werten abzurufen, wird @@FETCH_STATUS mit dem Wert -2 zurückgegeben. Die neuen Werte sind sichtbar, wenn die Aktualisierung über den Cursor durch Angeben der WHERE CURRENT OF-Klausel durchgeführt wird.

• DYNAMIC
  Definiert einen Cursor, der alle in den Zeilen vorgenommen Datenänderungen in seinem Resultset widerspiegelt, wenn Sie im Cursor einen Bildlauf durchführen. Datenwerte, Reihenfolge und Mitgliedschaft der Zeilen können sich bei jedem Abrufvorgang ändern. Die Abrufoption ABSOLUTE wird für dynamische Cursor nicht unterstützt.

• FAST_FORWARD
  Gibt einen FORWARD_ONLY-, READ_ONLY-Cursor mit aktivierter Leistungsoptimierung an. FAST_FORWARD kann nur angegeben werden, wenn weder SCROLL noch FOR UPDATE angegeben ist.

  Hinweis
  In SQL Server 2000 schließen sich die Cursoroptionen FAST_FORWARD und FORWARD_ONLY gegenseitig aus. Wenn beide angegeben werden, wird ein Fehler ausgelöst. In SQL Server 2005 und höheren Versionen können beide Schlüsselwörter in derselben DECLARE CURSOR-Anweisung verwendet werden.

• READ_ONLY
  Verhindert, dass über diesen Cursor Aktualisierungen vorgenommen werden. Auf den Cursor kann nicht in einer WHERE CURRENT OF-Klausel in einer UPDATE- oder DELETE-Anweisung verwiesen werden. Diese Option setzt die Standardeinstellung außer Kraft, nach der ein Cursor aktualisiert werden kann.

• SCROLL_LOCKS
  Gibt an, dass positionierte Aktualisierungen oder Löschvorgänge über den Cursor sicherlich erfolgreich verlaufen. SQL Server sperrt die Zeilen, die in den Cursor gelesen werden, um ihre Verfügbarkeit für spätere Änderungen sicherzustellen. SCROLL_LOCKS kann nicht angegeben werden, wenn FAST_FORWARD oder STATIC ebenfalls angegeben wird.

• OPTIMISTIC
  Gibt an, dass positionierte Aktualisierungen oder Löschvorgänge über den Cursor nicht erfolgreich verlaufen, wenn die Zeile aktualisiert wurde, nachdem sie in den Cursor gelesen wurde. SQL Server sperrt keine Zeilen, die in den Cursor gelesen werden. Stattdessen wird durch das Vergleichen der timestamp-Spaltenwerte oder durch einen Prüfsummenwert, wenn die Tabelle keine timestamp-Spalte aufweist, bestimmt, ob die Zeile nach dem Einlesen in den Cursor geändert wurde. Wurde die Zeile geändert, so schlägt die versuchte positionierte Aktualisierung oder Löschung fehl. Es kann nur eine der beiden Optionen OPTIMISTIC oder FAST_FORWARD angegeben werden.

• TYPE_WARNING
  Gibt an, dass dem Client eine Warnmeldung gesendet wird, wenn der Cursor vom angeforderten Typ in einen anderen Typ implizit konvertiert wird.

• select_statement
  Eine standardmäßige SELECT-Anweisung, die das Resultset des Cursors definiert. Bei der Deklaration eines Cursors sind die Schlüsselwörter COMPUTE, COMPUTE BY, FOR BROWSE und INTO innerhalb von select_statement nicht zulässig.

  Hinweis
  Sie können einen Abfragehinweis in einer Cursordeklaration verwenden. Wenn Sie jedoch auch die FOR UPDATE OF-Klausel verwenden, geben Sie OPTION (query_hint) nach FOR UPDATE OF an.

  SQL Server konvertiert den Cursor implizit in einen anderen Typ, wenn die Klauseln in select_statement in Konflikt mit der Funktionalität des angeforderten Cursortyps stehen. Weitere Informationen finden Sie unter Implizite Cursorkonvertierungen.

• FOR UPDATE [OF column_name [,...n]]
  Definiert aktualisierbare Spalten innerhalb des Cursors. Wenn OF column_name [,...n] angegeben wird, können Änderungen nur in den aufgelisteten Spalten vorgenommen werden. Wenn UPDATE ohne Spaltenliste angegeben wird, können alle Spalten aktualisiert werden, sofern nicht die Parallelitätsoption READ_ONLY angegeben wurde.

Hinweise

DECLARE CURSOR definiert die Attribute eines Transact-SQL-Servercursors, wie z. B. dessen Scrollverhalten, sowie die Abfrage, die zum Erstellen des Resultsets verwendet wird, auf das der Cursor ausgeführt wird. Die OPEN-Anweisung füllt das Resultset auf, und FETCH gibt eine Zeile aus dem Resultset zurück. Die CLOSE-Anweisung gibt das aktuelle Resultset frei, das dem Cursor zugeordnet ist. Die DEALLOCATE-Anweisung gibt die vom Cursor verwendeten Ressourcen frei.

Die erste Form der DECLARE CURSOR-Anweisung verwendet zum Deklarieren des Cursorverhaltens die ISO-Syntax. Die zweite Form der DECLARE CURSOR-Anweisung verwendet Transact-SQL-Erweiterungen, die die Definition von Cursorn mithilfe der gleichen Cursortypen zulassen, die in den Datenbank-API-Cursor-Funktionen von ODBC oder ADO verwendet werden.

Die beiden Formen können nicht gleichzeitig verwendet werden. Wenn Sie die Schlüsselwörter SCROLL oder INSENSITIVE vor dem Schlüsselwort CURSOR angeben, können keine Schlüsselwörter zwischen CURSOR und FOR select_statement verwendet werden. Wenn Sie eines der Schlüsselwörter zwischen CURSOR und FOR select_statement angeben, kann weder SCROLL noch INSENSITIVE vor dem Schlüsselwort CURSOR angegeben werden.

Wird in einer DECLARE CURSOR-Anweisung mit der Transact-SQL-Syntax weder READ_ONLY, OPTIMISTIC noch SCROLL_LOCKS angegeben, ist der Standard folgendermaßen:

• Unterstützt die SELECT-Anweisung keine Aktualisierungen (wegen fehlender Berechtigungen, Zugriff auf Remotetabellen, die keine Aktualisierungen unterstützen usw.), ist der Cursor READ_ONLY.

• STATIC- und FAST_FORWARD-Cursor sind standardmäßig READ_ONLY.

• DYNAMIC- und KEYSET-Cursor sind standardmäßig OPTIMISTIC.

Ein Verweis auf Cursornamen ist nur von anderen Transact-SQL-Anweisungen aus möglich. Auf sie kann nicht von Datenbank-API-Funktionen verwiesen werden. Nach der Deklaration eines Cursors besteht beispielsweise keine Möglichkeit, in einer OLE DB-, ODBC- oder ADO-Funktion oder -Methode auf den Cursornamen zu verweisen. Die Zeilen des Cursors können nicht mithilfe von API-Funktionen oder API-Methoden abgerufen werden. Ein Abrufen der Zeilen ist lediglich mit den FETCH-Anweisungen von Transact-SQL möglich.

Nach der Deklaration eines Cursors können die Eigenschaften des Cursors mithilfe der folgenden gespeicherten Systemprozeduren bestimmt werden:

Gespeicherte Systemprozeduren	Beschreibung
sp_cursor_list	Gibt eine Liste der in der Verbindung aktuell sichtbaren Cursor und ihrer Attribute zurück.
sp_describe_cursor	Beschreibt die Attribute eines Cursors, z. B. ob es sich um einen Vorwärtscursor oder einen Scrollcursor handelt.
sp_describe_cursor_columns	Beschreibt die Spaltenattribute im Resultset des Cursors.
sp_describe_cursor_tables	Beschreibt die Basistabellen, auf die der Cursor zugreift.

Variablen können als Teil von select_statement angegeben werden, die einen Cursor deklariert. Die Werte von Cursorvariablen ändern sich nach dem Deklarieren eines Cursors nicht. In SQL Server, Version 6.5 und früher, werden Variablenwerte bei jedem erneuten Öffnen eines Cursors aktualisiert.

Berechtigungen

Standardmäßig haben alle Benutzer die DECLARE CURSOR-Berechtigung, wenn sie die SELECT-Berechtigungen für die im Cursor verwendeten Sichten, Tabellen und Spalten haben.

Beispiele

A. Verwenden eines einfachen Cursors und einer einfachen Syntax

Das beim Öffnen dieses Cursors generierte Resultset enthält alle Zeilen und Spalten in der Tabelle. Der Cursor kann aktualisiert werden, und alle Aktualisierungen und Löschungen werden in Abrufvorgängen dargestellt, die für diesen Cursor ausgeführt wurden. FETCHNEXT ist der einzige verfügbare Abrufvorgang, da die Option SCROLL nicht angegeben wurde.

USE AdventureWorks2008R2;
GO
DECLARE vend_cursor CURSOR
    FOR SELECT BusinessEntityID, Name, CreditRating FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. Verwenden von geschachtelten Cursorn zum Erstellen von Berichten

Im folgenden Beispiel wird gezeigt, wie Cursor zum Erzeugen komplexer Berichte geschachtelt werden können. Der innere Cursor wird für jeden Anbieter deklariert.

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;

DECLARE @vendor_id int, @vendor_name nvarchar(50),
    @message varchar(80), @product nvarchar(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR 
SELECT BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' ';
    SELECT @message = '----- Products From Vendor: ' + 
        @vendor_name;

    PRINT @message;

    -- Declare an inner cursor based   
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR 
    SELECT v.Name
    FROM Purchasing.ProductVendor AS pv
    INNER JOIN Production.Product AS v 
        ON pv.ProductID = v.ProductID AND
           pv.BusinessEntityID = @vendor_id;  -- Variable value from the outer cursor

    OPEN product_cursor;
    FETCH NEXT FROM product_cursor INTO @product;

    IF @@FETCH_STATUS <> 0 
        PRINT '         <<None>>' ;    

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product;
        END;

    CLOSE product_cursor;
    DEALLOCATE product_cursor;
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor 
    INTO @vendor_id, @vendor_name;
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Siehe auch

Verweis

@@FETCH_STATUS (Transact-SQL)

CLOSE (Transact-SQL)

Cursor (Transact-SQL)

DEALLOCATE (Transact-SQL)

FETCH (Transact-SQL)

SELECT (Transact-SQL)

sp_configure (Transact-SQL)