Freigeben über

DECLARE CURSOR (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

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

Transact-SQL-Syntaxkonventionen

Syntax

ISO-Syntax:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ_ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Erweiterte Transact-SQL-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 Transact-SQL-Servercursors, der definiert ist. cursor_name muss den Regeln für Bezeichner entsprechen.

INSENSITIVE

Definiert einen Cursor, der eine temporäre Kopie der von ihm zu verwendenden Daten erzeugt. Alle Anforderungen an den Cursor werden von dieser temporären Tabelle in tempdbbeantwortet. Daher werden Änderungen der Basistabelle nicht in den daten widergespiegelt, die von Abrufen an diesem Cursor zurückgegeben werden, und dieser Cursor lässt keine Änderungen zu. Bei Verwendung der ISO-Syntax ohne INSENSITIVE werden ausgeführte Löschvorgänge und Updates 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. Wenn SCROLL in einer ISO DECLARE CURSORnicht angegeben ist, NEXT wird die einzige Abrufoption unterstützt. SCROLL kann nicht angegeben werden, wenn FAST_FORWARD auch angegeben wird. Wenn SCROLL nicht angegeben, ist nur die Abrufoption NEXT verfügbar, und der Cursor wird FORWARD_ONLY.

select_statement

Eine Standard-Anweisung SELECT , die den Resultset des Cursors definiert. Die Schlüsselwörter FOR BROWSEund INTO sind innerhalb select_statement einer Cursordeklaration 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.

READ_ONLY

Verhindert, dass über diesen Cursor Updates vorgenommen werden. Auf den Cursor kann in einer Klausel in einer WHERE CURRENT OF UPDATE Oder-Anweisung DELETE nicht verwiesen werden. Diese Option überschreibt die Standardeinstellung, nach der ein Cursor aktualisiert werden kann.

UPDATE [ VON column_name [ ,...n ] ]

Definiert aktualisierbare Spalten innerhalb des Cursors. Wenn OF <column_name> [, <... n> ] angegeben, lassen nur die aufgelisteten Spalten Änderungen zu. Wenn UPDATE ohne Spaltenliste angegeben wird, können alle Spalten aktualisiert werden.

cursor_name

Der Name des Transact-SQL-Servercursors, der definiert ist. cursor_name muss den Regeln für Bezeichner entsprechen.

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 der Cursor in einem OUTPUT Parameter übergeben wird, wird der Cursor abgeglichen, wenn die letzte Variable, auf die sie verweist, deallocated oder außerhalb des Gültigkeitsbereichs liegt.

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.

FORWARD_ONLY

Gibt an, dass der Cursor von der ersten bis zur letzten Zeile nur vorwärts bewegt werden kann. FETCH NEXT ist die einzige unterstützte Abrufoption. Alle Einfüge-, Aktualisierungs- und Löschanweisungen des aktuellen Benutzers (oder von anderen Benutzern zugesichert), die sich auf Zeilen im Resultset auswirken, sind sichtbar, wenn die Zeilen abgerufen werden. Da der Cursor nicht rückwärts gescrollt werden kann, werden änderungen, die an Zeilen in der Datenbank vorgenommen wurden, nachdem die Zeile abgerufen wurde, nicht durch den Cursor sichtbar. Vorwärtscursor sind standardmäßig dynamisch, d. h. dass alle Änderungen ermittelt werden, während die aktuelle Zeile verarbeitet wird. Damit kann der Cursor schneller gestartet werden, und Updates an den zugrunde liegenden Tabellen können im Resultset angezeigt werden. Während Vorwärtscursor den Rückwärtslauf nicht unterstützen, können Anwendungen zum Anfang des Resultsets zurückkehren, indem sie den Cursor schließen und erneut öffnen.

Wenn FORWARD_ONLY ohne eines der Schlüsselwörter STATIC, KEYSET oder DYNAMIC angegeben wird, ist der Cursor ein dynamischer Cursor. Wenn FORWARD_ONLY oder SCROLL nicht angegeben wird, ist die Standardeinstellung, FORWARD_ONLY es sei denn, die Schlüsselwörter STATIC, KEYSET, oder DYNAMIC werden angegeben. Die Cursor STATIC, KEYSET und DYNAMIC sind standardmäßig auf SCROLL festgelegt. Anders als bei Datenbank-APIs wie ODBC und ADO wird FORWARD_ONLY für die Transact-SQL-Cursor STATIC, KEYSET und DYNAMIC unterstützt.

STATIC

Legt fest, dass der Cursor das Resultset immer so anzeigt, wie es war, als der Cursor gestartet wurde, und erstellt eine temporäre Kopie der Daten, die vom Cursor verwendet werden. Alle Anforderungen an den Cursor werden von dieser temporären Tabelle in tempdbbeantwortet. Daher werden an Basistabellen vorgenommene Einfügungen, Aktualisierungen und Löschungen nicht in den Daten widergespiegelt, die von Abrufen an diesem Cursor zurückgegeben werden, und dieser Cursor erkennt keine Änderungen, die an der Mitgliedschaft, Reihenfolge oder den Werten des Resultsets vorgenommen wurden, nachdem der Cursor geöffnet wurde. Statische Cursor erkennen möglicherweise ihre eigenen Updates, Lösch- und Einfügungen, obwohl sie dazu nicht erforderlich sind.

Angenommen ein statischer Cursor ruft eine Zeile ab, und eine andere Anwendung aktualisiert diese Zeile dann. Wenn die Anwendung die Zeile erneut vom statischen Cursor abruft, sind die erkannten Werte unverändert, obwohl die andere Anwendung Änderungen vorgenommen hat. Alle Arten des Scrollens werden unterstützt.

KEYSET

Gibt an, dass im Cursor die Mitgliedschaft und Reihenfolge der Zeilen fest ist, wenn der Cursor geöffnet wird. Der Satz von Schlüsseln, die die Zeilen eindeutig identifizieren, ist in eine Tabelle integriert, die tempdb als Keyset bezeichnet wird. Mit seiner Fähigkeit, Änderungen zu erkennen, bietet dieser Cursor Funktionalität zwischen statischen und dynamischen Cursor. Wie ein statischer Cursor erkennt er nicht immer Änderungen an der Mitgliedschaft und Reihenfolge des Resultsets. Wie ein dynamischer Cursor ermittelt er Änderungen an den Werten der Zeilen im Resultset.

Keysetgesteuerte Cursor werden von einer Reihe von eindeutigen Bezeichnern (Schlüssel) gesteuert, die als das Keyset bezeichnet werden. Die Schlüssel werden anhand einer Reihe von Spalten erstellt, die die Zeilen im Resultset eindeutig identifizieren. Das Keyset besteht aus Schlüsselwerten aus allen Zeilen, die von der Abfrageanweisung zurückgegeben werden. Mit einem keysetgesteuerten Cursor wird für jede Zeile im Cursor ein Schlüssel erstellt und gespeichert, der wiederum entweder auf der Clientarbeitsstation oder dem Server gespeichert wird. Wenn Sie auf eine beliebige Zeile zugreifen, wird der gespeicherte Schlüssel zum Abrufen der aktuellen Datenwerte aus der Datenquelle verwendet. In einem keysetgesteuerter Cursor wird die Mitgliedschaft des Resultsets fixiert, wenn das Keyset vollständig gefüllt wurde. Danach sind Ergänzungen oder Updates, die sich auf die Mitgliedschaft auswirken, erst dann Teil des Resultsets, wenn sie erneut geöffnet wird.

Änderungen an Datenwerten (entweder durch den Besitzer des Keysets oder andere Prozesse), die sichtbar sind, während der Benutzer durch das Resultset scrollt:

  • Wenn eine Zeile gelöscht wird, wird versucht, die Zeile abzurufen, weil @@FETCH_STATUS -2 die gelöschte Zeile als Lücke im Resultset angezeigt wird. Der Schlüssel für die Zeile ist im Keyset enthalten, aber die Zeile ist nicht mehr im Resultset vorhanden.

  • INSERTs außerhalb des Cursors (durch andere Prozesse) sind nur sichtbar, wenn der Cursor beendet und neu gestartet wird. INSERTs innerhalb des Cursors werden am Ende des Resultsets angezeigt.

  • Updates 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 versucht, die Zeile mit den alten Werten abzurufen, die einen @@FETCH_STATUS von -2. Die neuen Werte sind sichtbar, wenn das Update über den Cursor durch Angeben der WHERE CURRENT OF-Klausel durchgeführt wird.

Hinweis

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

DYNAMIC

Definiert einen Cursor, der alle Datenänderungen an den Zeilen im Resultset widerspiegelt, während Sie im Cursor scrollen und einen neuen Datensatz abrufen, unabhängig davon, ob die Änderungen innerhalb oder außerhalb (durch andere Benutzer) des Cursors vorgenommen werden. Daher sind alle INSERT-, UPDATE- und -DELETE-Anweisungen von allen Benutzern über den Cursor sichtbar. Datenwerte, Reihenfolge und Mitgliedschaft der Zeilen können sich bei jedem Abrufvorgang ändern. Die ABSOLUTE Abrufoption wird mit dynamischen Cursorn nicht unterstützt. Aktualisierungen außerhalb des Cursors werden erst angezeigt, wenn sie zugesichert werden (es sei denn, die Cursortransaktionsisolationsstufe ist auf festgelegt UNCOMMITTED).

Angenommen, ein dynamischer Cursor ruft zwei Zeilen ab, und eine andere Anwendung aktualisiert dann eine dieser Zeilen und löscht die andere. Wenn der dynamische Cursor diese Zeilen dann abruft, wird die gelöschte Zeile nicht gefunden, aber es werden die neuen Werte für die aktualisierte Zeile angezeigt.

FAST_FORWARD

Gibt einen FORWARD_ONLY-, READ_ONLY-Cursor mit aktivierten Leistungsoptimierungen an. FAST_FORWARD kann nicht angegeben werden, wenn SCROLL oder FOR_UPDATE auch angegeben wird. Dieser Cursortyp lässt keine Datenänderungen innerhalb des Cursors zu.

Hinweis

FAST_FORWARD und FORWARD_ONLY können nicht in der gleichen DECLARE CURSOR-Anweisung verwendet werden.

READ_ONLY

Verhindert, dass über diesen Cursor Updates vorgenommen werden. Auf den Cursor kann in einer Klausel in einer WHERE CURRENT OF UPDATE Oder-Anweisung DELETE nicht verwiesen werden. Diese Option überschreibt die Standardeinstellung, nach der ein Cursor aktualisiert werden kann.

SCROLL_LOCKS

Gibt an, dass positionierte Updates oder Löschungen durch den Cursor garantiert erfolgreich sind. SQL Server sperrt die Zeilen, während sie in den Cursor eingelesen werden, um ihre Verfügbarkeit für spätere Änderungen sicherzustellen. SCROLL_LOCKS kann nicht angegeben werden, wenn FAST_FORWARD oder STATIC auch angegeben wird.

OPTIMISTIC

Gibt an, dass positionierte Aktualisierungen oder Löschvorgänge, die über den Cursor vorgenommen wurden, nicht erfolgreich sind, wenn die Zeile seit dem Lesen in den Cursor aktualisiert wurde. SQL Server sperrt keine Zeilen, während sie in den Cursor eingelesen werden. Stattdessen wird durch Vergleiche von timestamp-Spaltenwerten oder 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 der versuchte positionierte Update- oder Löschvorgang fehl. OPTIMISTIC kann nicht angegeben werden, wenn FAST_FORWARD auch angegeben wird.

Wenn STATIC zusammen mit dem OPTIMISTIC Cursorargument angegeben wird, wird die Kombination der beiden implizit in das Äquivalent der Kombination aus Verwendung STATIC und READ_ONLY Argumenten oder den STATIC Argumenten FORWARD_ONLY konvertiert.

TYPE_WARNING

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

Beim Verwenden der Kombination von OPTIMISTIC Argumenten und STATIC Cursorn wird keine Warnung an den Client gesendet, und der Cursor wird implizit in das Äquivalent eines Cursors STATIC READ_ONLY oder STATIC FORWARD_ONLY Cursors konvertiert. Die Konvertierung, die READ_ONLY sich aus der Perspektive eines Clients in einen FAST_FORWARD Cursor verwandelt READ_ONLY .

select_statement

Eine Standard-Anweisung SELECT , die den Resultset des Cursors definiert. Die Schlüsselwörter COMPUTE, COMPUTE BY, , FOR BROWSEund INTO sind innerhalb select_statement einer Cursordeklaration nicht zulässig.

Hinweis

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

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.

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.

Bemerkungen

DECLARE CURSOR definiert die Attribute eines Transact-SQL-Servercursors, wie z. B. dessen Scrollverhalten sowie die Abfrage, die zum Erstellen des Resultsets verwendet wird, in dem der Cursor ausgeführt wird. Die OPEN-Anweisung füllt das Resultset auf, 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 von DECLARE CURSOR verwendet Transact-SQL-Erweiterungen, mit denen Sie Cursor mit den gleichen Cursortypen definieren können, die in den Datenbank-API-Cursorfunktionen von ODBC oder ADO verwendet werden.

Sie können die beiden Formen nicht kombinieren. Wenn Sie die SCROLL INSENSITIVE Schlüsselwörter vor dem CURSOR Schlüsselwort angeben, können Sie keine Schlüsselwörter zwischen den CURSOR Schlüsselwörtern und FOR <select_statement> Schlüsselwörtern verwenden. Wenn Sie Schlüsselwörter zwischen den CURSOR Schlüsselwörtern und FOR <select_statement> Schlüsselwörtern angeben, können Sie das Schlüsselwort weder angeben noch vor dem CURSOR Schlüsselwort angeben SCROLL INSENSITIVE.

Wenn eine DECLARE CURSOR transact-SQL-Syntax nicht folgendes angibt READ_ONLY, OPTIMISTICoder SCROLL_LOCKSlautet der Standardwert wie folgt:

  • Wenn die SELECT Anweisung keine Updates unterstützt (unzureichende Berechtigungen, Zugriff auf Remotetabellen, die keine Updates unterstützen usw.), lautet READ_ONLYder Cursor .

  • Die Cursor STATIC und FAST_FORWARD sind standardmäßig auf READ_ONLY festgelegt.

  • Die Cursor DYNAMIC und KEYSET sind standardmäßig auf OPTIMISTIC festgelegt.

Cursornamen können nur von anderen Transact-SQL-Anweisungen referenziert werden. Sie können nicht von Datenbank-API-Funktionen referenziert werden. Beispielsweise kann nach dem Deklarieren eines Cursors nicht über OLE DB-, ODBC- oder ADO-Funktionen oder -Methoden auf den Cursornamen verwiesen werden. Die Cursorzeilen können nicht mithilfe der Abruffunktionen oder Methoden der APIs abgerufen werden. die Zeilen können nur von Transact-SQL-Anweisungen FETCH abgerufen werden.

Nachdem ein Cursor deklariert wurde, können diese vom System gespeicherten Prozeduren verwendet werden, um die Merkmale des Cursors zu bestimmen.

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ärts- oder Bildlaufcursor 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 der select_statement verwendet werden, die einen Cursor deklariert. Cursorvariablenwerte ändern sich nicht, nachdem ein Cursor deklariert wurde.

Berechtigungen

In der Standardeinstellung haben alle Benutzer die DECLARE CURSOR-Berechtigung, wenn sie über die SELECT-Berechtigungen für die im Cursor verwendeten Sichten, Tabellen und Spalten verfügen.

Begrenzungen

In einer Tabelle mit einem gruppierten Columnstore-Index können keine Cursor oder Trigger verwendet werden. Diese Einschränkung gilt nicht für nicht gruppierte Columnstore-Indizes. In Tabellen mit einem nicht gruppierten Columnstore-Index können Cursor und Trigger verwendet werden.

Beispiele

A. Verwenden von einfachen Cursorn und Syntax

Das beim Öffnen dieses Cursors generierte Resultset enthält alle Zeilen und Spalten in der Tabelle. Dieser Cursor kann aktualisiert werden. Alle Updates und Löschungen werden in Abrufen dieses Cursors dargestellt. FETCH NEXT ist der einzige Abruf verfügbar, da die SCROLL Option nicht angegeben ist.

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

B. Verwenden von geschachtelten Cursorn zum Erstellen der Berichtsausgabe

Im folgenden 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;

Zugehöriger Inhalt