DECLARE CURSOR (Transact-SQL)

Aktualisiert: 05. Dezember 2005

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 SQL-92-Standard als auch eine Syntax, für die eine Teilmenge der Transact-SQL-Erweiterungen verwendet wird.

Transact-SQL-Syntaxkonventionen

Syntax

SQL 92 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
  Ist 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 SQL-92-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 SQL-92-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.

  Microsoft 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 aufgeführten Spalten vorgenommen werden. Wenn UPDATE ohne Spaltenliste angegeben wird, können alle Spalten aktualisiert werden.

• cursor_name
  Ist 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 scrollt. Von anderen Benutzern vorgenommene Einfügungen sind nicht sichtbar (Einfügevorgänge können nicht über einen Transact-SQL-Servercursor durchgeführt werden). Wird eine Zeile gelöscht und dann versucht, sie abzurufen, so wird für @@FETCH_STATUS der Wert -2 zurückgegeben. Aktualisierungen von Schlüsselwerten von außerhalb des Cursors sind vergleichbar mit dem Löschen der alten Zeile und anschließendem Einfügen der neuen Zeile. Die Zeile mit den neuen Werten ist nicht sichtbar; wird versucht, die Zeile mit den alten Werten abzurufen, so wird für @@FETCH_STATUS der Wert -2 zurückgegeben. Die neuen Werte sind sichtbar, wenn die Aktualisierung über den Cursor durch Angeben der WHERE CURRENT OF-Klausel durchgeführt wurde.

• DYNAMIC
  Definiert einen Cursor, der alle in den Zeilen vorgenommen Datenänderungen in seinem Resultset widerspiegelt, wenn Sie im Cursor scrollen. 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 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. Microsoft 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 seit ihrem Einlesen in den Cursor aktualisiert wurde. SQL Server sperrt keine Zeilen, die in den Cursor gelesen werden. Stattdessen wird durch Vergleiche von timestamp-Spaltenwerten oder durch einen Prüfsummenwert, falls 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 implizit vom angeforderten Typ in einen anderen Typ 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 aufgeführten 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 SQL-92-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 -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:

In der in select_statement angegebenen SELECT-Anweisung, die einen Cursor deklariert, können Variablen verwendet werden. Die Werte von Cursorvariablen ändern sich nach dem Deklarieren eines Cursors nicht. In SQL Server, Version 6.5 und niedriger, werden Variablenwerte bei jedem erneuten Öffnen des 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 werden. FETCH NEXT ist der einzige verfügbare Abrufvorgang, da die SCROLL-Option nicht angegeben wurde.

DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor

B. Verwenden von geschachtelten Cursorn zum Erstellen von Berichten

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

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 VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

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 pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @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)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf