CURSOR DECLAREREN (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric Preview

Definieert de kenmerken van een Transact-SQL servercursor, zoals het schuifgedrag en de query die wordt gebruikt om de resultatenset te bouwen waarop de cursor werkt. DECLARE CURSOR accepteert zowel een syntaxis op basis van de ISO-standaard als een syntaxis met behulp van een set Transact-SQL extensies.

Transact-SQL syntaxis-conventies

Syntax

ISO syntax:

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

Transact-SQL uitgebreide syntaxis:

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 ] ] ]
[ ; ]

Arguments

cursor_name

De naam van de Transact-SQL servercursor gedefinieerd. cursor_name moet voldoen aan de regels voor id's.

INSENSITIVE

Hiermee definieert u een cursor waarmee een tijdelijke kopie van de gegevens wordt gebruikt door de cursor. Alle aanvragen voor de cursor worden beantwoord vanuit deze tijdelijke tabel in tempdb. Wijzigingen in basistabellen worden daarom niet doorgevoerd in de gegevens die worden geretourneerd door het ophalen van deze cursor en deze cursor staat geen wijzigingen toe. Wanneer ISO-syntaxis wordt gebruikt, INSENSITIVE worden doorgevoerde verwijderingen en updates die worden aangebracht in de onderliggende tabellen (door een gebruiker) doorgevoerd in volgende ophaalt.

SCROLL

Hiermee geeft u op dat alle ophaalopties (FIRST, LAST, PRIOR, NEXT, ) RELATIVEABSOLUTEbeschikbaar zijn. Als SCROLL niet is opgegeven in een ISO DECLARE CURSOR, NEXT wordt de enige optie voor ophalen ondersteund. SCROLL kan niet worden opgegeven als FAST_FORWARD deze ook is opgegeven. Als SCROLL dit niet is opgegeven, is alleen de optie NEXT ophalen beschikbaar en wordt de cursor.FORWARD_ONLY

select_statement

Een standaardinstructie SELECT waarmee de resultatenset van de cursor wordt gedefinieerd. De trefwoorden FOR BROWSEen INTO zijn niet toegestaan binnen select_statement van een cursordeclaratie.

SQL Server converteert de cursor impliciet naar een ander type als componenten in select_statement conflicteren met de functionaliteit van het aangevraagde cursortype.

READ_ONLY

Hiermee voorkomt u dat updates via deze cursor worden uitgevoerd. Er kan niet naar de cursor worden verwezen in een WHERE CURRENT OF component in een UPDATE of DELETE instructie. Met deze optie wordt de standaardmogelijkheid van een cursor overschreven die moet worden bijgewerkt.

UPDATE [ VAN column_name [ ,... n ] ]

Hiermee definieert u de kolommen die kunnen worden bijgewerkt binnen de cursor. Als OF <column_name> [, <... n> ] dit is opgegeven, staan alleen de vermelde kolommen wijzigingen toe. Als UPDATE deze is opgegeven zonder een kolomlijst, kunnen alle kolommen worden bijgewerkt.

cursor_name

De naam van de Transact-SQL servercursor gedefinieerd. cursor_name moet voldoen aan de regels voor id's.

LOCAL

Hiermee geeft u op dat het bereik van de cursor lokaal is voor de batch, opgeslagen procedure of trigger waarin de cursor is gemaakt. De cursornaam is alleen geldig binnen dit bereik. De cursor kan worden verwezen door lokale cursorvariabelen in de batch, opgeslagen procedure of trigger of een opgeslagen procedureparameter OUTPUT . Een OUTPUT parameter wordt gebruikt om de lokale cursor terug te geven aan de aanroepende batch, opgeslagen procedure of trigger, die de parameter kan toewijzen aan een cursorvariabele om te verwijzen naar de cursor nadat de opgeslagen procedure is beëindigd. De cursor wordt impliciet de toewijzing opgeheven wanneer de batch, opgeslagen procedure of trigger wordt beëindigd, tenzij de cursor is doorgegeven in een OUTPUT parameter. Als deze wordt doorgegeven aan een OUTPUT parameter, wordt de toewijzing van de cursor ongedaan gemaakt wanneer de laatste variabele waarnaar wordt verwezen, de toewijzing ervan ongedaan wordt gemaakt of buiten het bereik komt.

GLOBAL

Hiermee geeft u op dat het bereik van de cursor globaal is voor de verbinding. De cursornaam kan worden verwezen in elke opgeslagen procedure of batch die door de verbinding wordt uitgevoerd. De cursor wordt alleen impliciet opgeheven wanneer de verbinding is verbroken.

FORWARD_ONLY

Hiermee geeft u op dat de cursor alleen vooruit kan gaan en van de eerste naar de laatste rij kan worden geschoven. FETCH NEXT is de enige ondersteunde optie voor ophalen. Alle invoeg-, bijwerk- en verwijderinstructies van de huidige gebruiker (of doorgevoerd door andere gebruikers) die van invloed zijn op rijen in de resultatenset, zijn zichtbaar wanneer de rijen worden opgehaald. Omdat de cursor niet naar achteren kan worden geschoven, zijn wijzigingen in rijen in de database nadat de rij is opgehaald, echter niet zichtbaar via de cursor. Cursors met alleen-doorsturen zijn standaard dynamisch, wat betekent dat alle wijzigingen worden gedetecteerd als de huidige rij wordt verwerkt. Hierdoor wordt de cursor sneller geopend en wordt de resultatenset ingeschakeld om updates weer te geven die zijn aangebracht in de onderliggende tabellen. Terwijl cursors die alleen vooruitgaan geen ondersteuning bieden voor achteruit schuiven, kunnen toepassingen terugkeren naar het begin van de resultatenset door de cursor te sluiten en opnieuw te openen.

Als FORWARD_ONLY de cursor zonder de STATIC, KEYSETof DYNAMIC trefwoorden is opgegeven, werkt de cursor als een dynamische cursor. Wanneer FORWARD_ONLY of SCROLL niet is opgegeven, FORWARD_ONLY is dit de standaardwaarde, tenzij de trefwoorden STATIC, KEYSETof DYNAMIC worden opgegeven. STATIC, KEYSETen DYNAMIC cursors zijn standaard SCROLLingesteld op . In tegenstelling tot database-API's zoals ODBC en ADO, FORWARD_ONLY wordt dit ondersteund met STATICKEYSETen DYNAMIC Transact-SQL cursors.

STATIC

Hiermee geeft u op dat de cursor altijd de resultatenset weergeeft zoals toen de cursor voor het eerst werd geopend en een tijdelijke kopie maakt van de gegevens die door de cursor moeten worden gebruikt. Alle aanvragen voor de cursor worden beantwoord vanuit deze tijdelijke tabel in tempdb. Daarom worden invoegingen, updates en verwijderingen die zijn aangebracht in basistabellen, niet weergegeven in de gegevens die worden geretourneerd door het ophalen van deze cursor. Deze cursor detecteert dus geen wijzigingen die zijn aangebracht in het lidmaatschap, de volgorde of de waarden van de resultatenset nadat de cursor is geopend. Statische cursors kunnen hun eigen updates, verwijderingen en invoegingen detecteren, hoewel ze dit niet hoeven te doen.

Stel dat een statische cursor een rij ophaalt en een andere toepassing die rij vervolgens bijwerkt. Als de toepassing de rij van de statische cursor opnieuw maakt, zijn de waarden die worden weergegeven, ongewijzigd, ondanks de wijzigingen die door de andere toepassing zijn aangebracht. Alle typen scrollen worden ondersteund.

KEYSET

Hiermee geeft u op dat het lidmaatschap en de volgorde van rijen in de cursor worden hersteld wanneer de cursor wordt geopend. De set sleutels waarmee de rijen uniek worden geïdentificeerd, is ingebouwd in een tabel in tempdb de naam van de sleutelset. Deze cursor biedt functionaliteit tussen een statische en een dynamische cursor in de mogelijkheid om wijzigingen te detecteren. Net als bij een statische cursor worden wijzigingen in het lidmaatschap en de volgorde van de resultatenset niet altijd gedetecteerd. Net als bij een dynamische cursor worden wijzigingen in de waarden van rijen in de resultatenset gedetecteerd.

Sleutelsetgestuurde cursors worden beheerd door een set unieke id's (sleutels) die de sleutelset worden genoemd. De sleutels zijn opgebouwd uit een set kolommen waarmee de rijen in de resultatenset uniek worden geïdentificeerd. De sleutelset is de set sleutelwaarden uit alle rijen die worden geretourneerd door de query-instructie. Met sleutelsetgestuurde cursors wordt een sleutel gebouwd en opgeslagen voor elke rij in de cursor en opgeslagen op het clientwerkstation of op de server. Wanneer u elke rij opent, wordt de opgeslagen sleutel gebruikt om de huidige gegevenswaarden op te halen uit de gegevensbron. In een sleutelsetgestuurde cursor wordt het lidmaatschap van de resultatenset geblokkeerd wanneer de sleutelset volledig is ingevuld. Daarna maken toevoegingen of updates die van invloed zijn op lidmaatschap geen deel uit van de resultatenset totdat deze opnieuw wordt geopend.

Wijzigingen in gegevenswaarden (gemaakt door de eigenaar van de sleutelset of andere processen) zijn zichtbaar wanneer de gebruiker door de resultatenset bladert:

• Als een rij wordt verwijderd, retourneert een poging om de rij op te halen een @@FETCH_STATUS van -2 omdat de verwijderde rij wordt weergegeven als een tussenruimte in de resultatenset. De sleutel voor de rij bestaat in de sleutelset, maar de rij bestaat niet meer in de resultatenset.

• Invoegen buiten de cursor (door andere processen) zijn alleen zichtbaar als de cursor is gesloten en opnieuw wordt geopend. Invoegen die vanuit de cursor zijn gemaakt, zijn zichtbaar aan het einde van de resultatenset.

• Updates van sleutelwaarden buiten de cursor lijken op een verwijdering van de oude rij, gevolgd door een invoeging van de nieuwe rij. De rij met de nieuwe waarden is niet zichtbaar en probeert de rij op te halen met de oude waarden als resultaat.@@FETCH_STATUS-2 De nieuwe waarden zijn zichtbaar als de update via de cursor wordt uitgevoerd door de WHERE CURRENT OF component op te geven.

DYNAMIC

Hiermee definieert u een cursor die alle gegevenswijzigingen in de rijen in de resultatenset weergeeft terwijl u rond de cursor bladert en een nieuwe record ophaalt, ongeacht of de wijzigingen zich voordoen vanuit de cursor of door andere gebruikers buiten de cursor. Daarom zijn alle invoeg-, update- en verwijderinstructies van alle gebruikers zichtbaar via de cursor. De gegevenswaarden, volgorde en lidmaatschap van de rijen kunnen worden gewijzigd bij elke ophaalopdracht. De ABSOLUTE optie ophalen wordt niet ondersteund met dynamische cursors. Updates die buiten de cursor zijn aangebracht, zijn pas zichtbaar nadat ze zijn doorgevoerd (tenzij het niveau van de transactieisolatie van de cursor is ingesteld op UNCOMMITTED).

Stel dat een dynamische cursor twee rijen ophaalt en een andere toepassing vervolgens een van deze rijen bijwerkt en de andere verwijdert. Als de dynamische cursor deze rijen vervolgens ophaalt, wordt de verwijderde rij niet gevonden, maar worden de nieuwe waarden voor de bijgewerkte rij weergegeven.

FAST_FORWARD

Hiermee geeft u een FORWARD_ONLY, READ_ONLY cursor waarop prestatieoptimalisaties zijn ingeschakeld. FAST_FORWARD kan niet worden opgegeven als SCROLL of FOR_UPDATE ook is opgegeven. Dit type cursor staat geen gegevenswijzigingen toe vanuit de cursor.

READ_ONLY

Hiermee voorkomt u dat updates via deze cursor worden uitgevoerd. Er kan niet naar de cursor worden verwezen in een WHERE CURRENT OF component in een UPDATE of DELETE instructie. Met deze optie wordt de standaardmogelijkheid van een cursor overschreven die moet worden bijgewerkt.

SCROLL_LOCKS

Hiermee geeft u op dat geplaatste updates of verwijderingen die via de cursor zijn aangebracht, gegarandeerd slagen. SQL Server vergrendelt de rijen terwijl ze worden gelezen in de cursor om ervoor te zorgen dat ze beschikbaar zijn voor latere wijzigingen. SCROLL_LOCKS kan niet worden opgegeven als FAST_FORWARD of STATIC ook is opgegeven.

OPTIMISTIC

Hiermee geeft u op dat geplaatste updates of verwijderingen die via de cursor zijn aangebracht, niet slagen als de rij is bijgewerkt omdat deze in de cursor is gelezen. SQL Server vergrendelt geen rijen terwijl ze in de cursor worden gelezen. In plaats daarvan worden vergelijkingen gebruikt van tijdstempelkolomwaarden of een controlesomwaarde als de tabel geen tijdstempelkolom heeft, om te bepalen of de rij is gewijzigd nadat deze in de cursor is gelezen.

Als de rij is gewijzigd, mislukt de geprobeerde update of verwijdering. OPTIMISTIC kan niet worden opgegeven als FAST_FORWARD deze ook is opgegeven.

Als STATIC samen met het OPTIMISTIC cursorargument wordt opgegeven, wordt de combinatie van de twee impliciet geconverteerd naar het equivalent van de combinatie van het gebruik STATIC en READ_ONLY de argumenten, of de STATIC argumenten FORWARD_ONLY .

TYPE_WARNING

Hiermee geeft u op dat een waarschuwingsbericht naar de client wordt verzonden wanneer de cursor impliciet wordt geconverteerd van het aangevraagde type naar een ander type.

Er wordt geen waarschuwing naar de client verzonden wanneer de combinatie van OPTIMISTIC argumenten en STATIC cursorargumenten worden gebruikt en de cursor impliciet wordt geconverteerd naar het equivalent van een STATIC READ_ONLY of STATIC FORWARD_ONLY cursor. De conversie die READ_ONLY moet worden omgezet in een FAST_FORWARD en READ_ONLY cursor vanuit het perspectief van een klant.

select_statement

Een standaardinstructie SELECT waarmee de resultatenset van de cursor wordt gedefinieerd. De trefwoordenCOMPUTE, COMPUTE BYFOR BROWSEen INTO zijn niet toegestaan binnen select_statement van een cursordeclaratie.

SQL Server converteert de cursor impliciet naar een ander type als componenten in select_statement conflicteren met de functionaliteit van het aangevraagde cursortype.

VOOR UPDATE [ VAN column_name [ ,... n ] ]

Hiermee definieert u de kolommen die kunnen worden bijgewerkt binnen de cursor. Als OF <column_name> [, <... n>] deze optie is opgegeven, staan alleen de vermelde kolommen wijzigingen toe. Als UPDATE deze is opgegeven zonder een kolomlijst, kunnen alle kolommen worden bijgewerkt, tenzij de READ_ONLY gelijktijdigheidsoptie is opgegeven.

Remarks

DECLARE CURSOR definieert de kenmerken van een Transact-SQL servercursor, zoals het schuifgedrag en de query die wordt gebruikt om de resultatenset te maken waarop de cursor werkt. De OPEN instructie vult de resultatenset en FETCH retourneert een rij uit de resultatenset. Met CLOSE de instructie wordt de huidige resultatenset vrijgegeven die aan de cursor is gekoppeld. Met DEALLOCATE de instructie worden de resources die door de cursor worden gebruikt, vrijgegeven.

De eerste vorm van de DECLARE CURSOR instructie maakt gebruik van de ISO-syntaxis voor het declareren van cursorgedrag. In de tweede vorm worden DECLARE CURSOR Transact-SQL extensies gebruikt waarmee u cursors kunt definiëren met dezelfde cursortypen die worden gebruikt in de database-API-cursorfuncties van ODBC of ADO.

Je kunt de twee vormen niet combineren. Als u de SCROLL of INSENSITIVE trefwoorden opgeeft vóór het CURSOR trefwoord, kunt u geen trefwoorden tussen de CURSOR trefwoorden en FOR <select_statement> trefwoorden gebruiken. Als u trefwoorden opgeeft tussen de CURSOR trefwoorden en FOR <select_statement> trefwoorden, kunt u het trefwoord niet opgeven SCROLL of INSENSITIVE vóór het CURSOR trefwoord.

Als een DECLARE CURSOR using Transact-SQL syntaxis niet opgeeft READ_ONLY, OPTIMISTICof SCROLL_LOCKS, is de standaardwaarde als volgt:

• Als de SELECT instructie geen ondersteuning biedt voor updates (onvoldoende machtigingen, toegang tot externe tabellen die geen updates ondersteunen, enzovoort), is READ_ONLYde cursor.

• STATIC en FAST_FORWARD cursors zijn standaard ingesteld op READ_ONLY.

• DYNAMIC en KEYSET cursors zijn standaard ingesteld op OPTIMISTIC.

Er kan alleen naar cursornamen worden verwezen door andere Transact-SQL instructies. Er kan niet naar worden verwezen door database-API-functies. Na het declareren van een cursor kan bijvoorbeeld niet naar de naam van de cursor worden verwezen vanuit OLE DB-, ODBC- of ADO-functies of -methoden. De cursorrijen kunnen niet worden opgehaald met behulp van de functies of methoden voor ophalen van de API's; de rijen kunnen alleen worden opgehaald door Transact-SQL FETCH instructies.

Nadat een cursor is gedeclareerd, kunnen deze door het systeem opgeslagen procedures worden gebruikt om de kenmerken van de cursor te bepalen.

Variabelen kunnen worden gebruikt als onderdeel van de select_statement die een cursor declareert. De waarden van de cursorvariabele worden niet gewijzigd nadat een cursor is gedeclareerd.

Permissions

Standaardmachtigingen DECLARE CURSOR voor elke gebruiker met machtigingen voor de weergaven, tabellen en kolommen die SELECT in de cursor worden gebruikt.

Limitations

U kunt geen cursors of triggers gebruiken in een tabel met een geclusterde columnstore-index. Deze beperking geldt niet voor niet-geclusterde columnstore-indexen. U kunt cursors en triggers in een tabel gebruiken met een niet-geclusterde columnstore-index.

Examples

A. Basiscursor en syntaxis gebruiken

De resultatenset die is gegenereerd bij het openen van deze cursor, bevat alle rijen en alle kolommen in de tabel. Deze cursor kan worden bijgewerkt en alle updates en verwijderingen worden weergegeven in opgehaalde items op basis van deze cursor. FETCH NEXT is de enige ophaaloptie die beschikbaar is omdat de SCROLL optie niet is opgegeven.

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

B. Geneste cursors gebruiken om rapportuitvoer te produceren

In het volgende voorbeeld ziet u hoe cursors kunnen worden genest om complexe rapporten te produceren. De binnenste cursor wordt voor elke leverancier gedeclareerd.

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;

Related content

• @@FETCH_STATUS (Transact-SQL)
• CLOSE (Transact-SQL)
• Cursors (Transact-SQL)
• DEALLOCATE (Transact-SQL)
• FETCH (Transact-SQL)
• SELECT (Transact-SQL)
• sp_configure (Transact-SQL)