DECLARE CURSOR (Transact-SQL)

• 14/03/2017
• 13 minuten om te lezen
• • c
  • r
  • i
  • M
  • m
  • +6

Is van toepassing op: SQL Server (alle ondersteunde versies ) Azure SQL Database

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 een set Transact-SQL-extensies.

Transact-SQL-syntaxisconventies

Syntaxis

Argumenten

cursor_name
Is de naam van de Transact-SQL server cursor gedefinieerd. cursor_naam moet voldoen aan de regels voor identifiers.

ONGEVOELIG
Definieert een cursor die een tijdelijke kopie maakt van de gegevens die door de cursor moeten worden gebruikt. Alle verzoeken aan de cursor worden beantwoord vanuit deze tijdelijke tabel in tempdb; daarom worden wijzigingen aan basistabellen niet weerspiegeld in de gegevens die worden geretourneerd door ophaalacties aan deze cursor, en deze cursor staat geen wijzigingen toe. Als de ISO-syntaxis wordt gebruikt en INSENSITIVE wordt weggelaten, worden vastgelegde verwijderingen en updates van de onderliggende tabellen (door elke gebruiker) weerspiegeld in volgende ophaalacties.

SCROLLEN
Specificeert dat alle ophaalopties (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) zijn beschikbaar. Als SCROLL niet is opgegeven in een ISO DECLARE CURSOR, is NEXT de enige ondersteunde ophaaloptie . SCROLL kan niet worden opgegeven als FAST_FORWARD ook is opgegeven. Als SCROLL niet is opgegeven, is alleen de ophaaloptie NEXT beschikbaar en wordt de cursor FORWARD_ONLY.

select_statement
Is een standaard SELECT -instructie die de resultatenset van de cursor definieert. De trefwoorden FOR BROWSE, en INTO zijn niet toegestaan binnen select_statement van een cursor-declaratie.

SQL Server converteert impliciet de cursor naar een ander type als clausules in select_statement in strijd zijn met de functionaliteit van het gevraagde cursortype.

ALLEEN LEZEN
Voorkomt updates die via deze cursor worden gemaakt. Er kan niet naar de cursor worden verwezen in een WHERE CURRENT OF -clausule in een UPDATE of DELETE -instructie. Deze optie overschrijft de standaard mogelijkheid van een cursor om te worden bijgewerkt.

cursor_naam
Is de naam van de Transact-SQL servercursor die is gedefinieerd. cursor_naam moet voldoen aan de regels voor IDs.

LOKAAL
Specificeert 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. Er kan naar de cursor worden verwezen door lokale cursorvariabelen in de batch, opgeslagen procedure of trigger, of een opgeslagen procedure OUTPUT parameter. Een parameter OUTPUT wordt gebruikt om de lokale cursor terug te sturen naar de aanroepende batch, opgeslagen procedure of trigger, die de parameter kan toewijzen aan een cursorvariabele om na de opgeslagen procedure naar de cursor te verwijzen eindigt. De cursor wordt impliciet ongedaan gemaakt wanneer de batch, de opgeslagen procedure of de trigger wordt beëindigd, tenzij de cursor is teruggegaan in een OUTPUT -parameter. Als het wordt teruggestuurd in een OUTPUT -parameter, wordt de cursor ongedaan gemaakt wanneer de laatste variabele die ernaar verwijst wordt ongedaan gemaakt of buiten het bereik valt.

GLOBAAL
Geeft aan dat het bereik van de cursor globaal is voor de verbinding. Er kan naar de cursornaam worden verwezen in elke opgeslagen procedure of batch die door de verbinding wordt uitgevoerd. De cursor wordt alleen impliciet ongedaan gemaakt bij het verbreken.

FORWARD_ONLY
Specificeert dat de cursor alleen vooruit kan en kan worden gescrold vanaf de eerste tot de laatste rij. FETCH NEXT is de enige ondersteunde ophaaloptie. Alle invoeg-, update- en verwijderinstructies van de huidige gebruiker (of vastgelegd door andere gebruikers) die betrekking hebben op rijen in de resultatenset, zijn zichtbaar wanneer de rijen worden opgehaald.Omdat de cursor echter niet naar achteren kan worden geschoven, zijn wijzigingen die in rijen in de database zijn aangebracht nadat de rij is opgehaald, niet zichtbaar door de cursor. Alleen-vooruit-cursors zijn standaard dynamisch, wat betekent dat alle wijzigingen worden gedetecteerd terwijl de huidige rij wordt verwerkt. Hierdoor wordt de cursor sneller geopend en kan de resultatenset updates weergeven die zijn aangebracht in de onderliggende tabellen. Hoewel cursors alleen voorwaarts scrollen niet ondersteunen, kunnen applicaties terugkeren naar het begin van de resultatenset door de cursor te sluiten en opnieuw te openen. Als FORWARD_ONLY is opgegeven zonder de STATIC, KEYSET, of DYNAMIC trefwoorden, de cursor werkt als een dynamische cursor. Wanneer noch FORWARD_ONLY noch SCROLL is gespecificeerd, is FORWARD_ONLY de standaard, tenzij de trefwoorden STATIC, KEYSET, of DYNAMIC zijn opgegeven. STATIC, KEYSET, en DYNAMIC cursors zijn standaard SCROLL. In tegenstelling tot database-APIs zoals ODBC en ADO, wordt FORWARD_ONLY ondersteund met STATIC, KEYSET, en DYNAMIC Transact-SQL-cursors.

STATIC
Specificeert dat de cursor altijd de resultatenset weergeeft zoals deze was toen de cursor voor het eerst werd geopend, en maakt een tijdelijke kopie van de gegevens die door de cursor moeten worden gebruikt. Alle verzoeken aan de cursor worden beantwoord vanuit deze tijdelijke tabel in tempdb. Daarom worden invoegingen, updates en verwijderingen in basistabellen niet weerspiegeld in de gegevens die worden geretourneerd door ophaalacties aan deze cursor, en deze cursor detecteert 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 werkt die rij dan bij. Als de toepassing de rij opnieuw ophaalt vanaf de statische cursor, blijven de waarden die het ziet ongewijzigd, ondanks de wijzigingen die door de andere toepassing zijn aangebracht. Alle soorten scrollen worden ondersteund.

KEYSET
Specificeert dat het lidmaatschap en de volgorde van rijen in de cursor vast zijn wanneer de cursor wordt geopend. De set sleutels die de rijen uniek identificeren, is ingebouwd in een tabel in tempdb die bekend staat als de keyset. Deze cursor biedt functionaliteit tussen een statische en een dynamische cursor in zijn vermogen om veranderingen te detecteren. Net als een statische cursor detecteert het niet altijd wijzigingen in het lidmaatschap en de volgorde van de resultatenset. Net als een dynamische cursor detecteert het wijzigingen in de waarden van rijen in de resultatenset. Keyset-gestuurde cursors worden bestuurd door een set unieke identifiers (toetsen) die bekend staan als de keyset. De sleutels zijn opgebouwd uit een reeks kolommen die de rijen in de resultaatset uniek identificeren. De sleutelset is de set sleutelwaarden van alle rijen die door de vraaginstructie worden geretourneerd. Met keyset-gestuurde 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 uit de gegevensbron op te halen. In een keyset-aangedreven cursor wordt het lidmaatschap van de resultaatset bevroren wanneer de keyset volledig is gevuld. 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 keyset-eigenaar of andere processen) zijn zichtbaar als de gebruiker door de resultatenset bladert:

• Als een rij wordt verwijderd, geeft een poging om de rij op te halen een @@FETCH_STATUS van -2 terug, omdat de verwijderde rij als een gat in de resultatenset verschijnt. De sleutel voor de rij bestaat in de keyset, maar de rij bestaat niet meer in de resultatenset.
• Invoegingen gemaakt buiten de cursor (door andere processen) zijn alleen zichtbaar als de cursor wordt gesloten en opnieuw geopend. Invoegingen die van binnenuit de cursor zijn gemaakt, zijn zichtbaar aan het einde van de resultaatset.
• Updates van sleutelwaarden van buiten de cursor lijken op het verwijderen van de oude rij, gevolgd door het invoegen van de nieuwe rij. De rij met de nieuwe waarden is niet zichtbaar, en pogingen om de rij met de oude waarden op te halen retourneren een @@FETCH_STATUS -2. De nieuwe waarden zijn zichtbaar als de update wordt uitgevoerd via de cursor door de WHERE CURRENT OF -clausule op te geven.

DYNAMIC
Definieert een cursor die alle gegevenswijzigingen weergeeft die zijn aangebracht in de rijen in de resultatenset terwijl u door de cursor scrollt en een nieuw record ophaalt, ongeacht of de wijzigingen plaatsvinden binnen 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 bij elke ophaalactie veranderen. De ophaaloptie ABSOLUTE wordt niet ondersteund met dynamische cursors. Updates die buiten de cursor zijn aangebracht, zijn pas zichtbaar als ze zijn vastgelegd (tenzij het isolatieniveau van de cursortransactie is ingesteld op UNCOMMITTED). Stel dat een dynamische cursor twee rijen ophaalt en vervolgens een andere toepassing werkt een van die rijen bij en verwijdert de andere. Als de dynamische cursor vervolgens die rijen ophaalt, zal hij de verwijderde rij niet vinden, maar de nieuwe waarden voor de bijgewerkte rij weergeven.

FAST_FORWARD
Specificeert een FORWARD_ONLY, READ_ONLY cursor met prestatie-optimalisaties 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.

ALLEEN GELEZEN
Voorkomt updates die via deze cursor worden aangebracht. Er kan niet naar de cursor worden verwezen in een WHERE CURRENT OF -clausule in een UPDATE of DELETE -instructie. Deze optie heeft voorrang op de standaard mogelijkheid van een cursor om te worden bijgewerkt.

SCROLL_LOCKS
Specificeert dat gepositioneerde updates of verwijderingen die door middel van de cursor zijn gemaakt, gegarandeerd slagen. SQL Server vergrendelt de rijen terwijl ze in de cursor worden ingelezen 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
Specificeert dat gepositioneerde updates of verwijderingen die via de cursor zijn gemaakt, niet slagen als de rij is bijgewerkt sinds deze in de cursor is ingelezen. SQL Server vergrendelt rijen niet wanneer ze in de cursor worden gelezen. Het gebruikt in plaats daarvan vergelijkingen van tijdstempelkolomwaarden, of een checksumwaarde als de tabel geen tijdstempelkolom heeft, om te bepalen of de rij is gewijzigd nadat deze in de cursor is ingelezen. Als de rij is gewijzigd, mislukt de poging om de positie bij te werken of te verwijderen. OPTIMISTIC kan niet worden opgegeven als FAST_FORWARD ook is opgegeven.

TYPE_WARNING
Geeft aan dat er een waarschuwingsbericht wordt verzonden naar de client wanneer de cursor impliciet wordt geconverteerd van het gevraagde type naar een ander.

select_statement
Is een standaard SELECT-instructie die de resultatenset van de cursor definieert. De zoekwoorden COMPUTE, COMPUTE BY, FOR BROWSE en INTO zijn niet toegestaan binnen select_statement van een cursor-declaratie.

SQL Server converteert impliciet de cursor naar een ander type als clausules in select_statement conflicteren met de functionaliteit van het gevraagde cursortype. Zie Impliciete cursorconversies voor meer informatie.

Opmerkingen

DECLARE CURSOR definieert de kenmerken van een Transact-SQL-servercursor, zoals de scrollgedrag en de query die wordt gebruikt om de resultaatset te bouwen waarop de cursor werkt. De instructie OPEN vult de resultatenset en FETCH retourneert een rij uit de resultatenset. De instructie CLOSE geeft de huidige resultatenset vrij die aan de cursor is gekoppeld. De DEALLOCATE instructie geeft de bronnen vrij die door de cursor worden gebruikt.

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

U kunt de twee vormen niet mengen. Als u de SCROLL of INSENSITIVE zoekwoorden opgeeft vóór het CURSOR zoekwoord, kunt u geen trefwoorden tussen de CURSOR en FOR <select_statement> trefwoorden. Als u zoekwoorden opgeeft tussen de CURSOR en FOR <select_statement> zoekwoorden, kunt u SCROLL of INSENSITIVE vóór het CURSOR zoekwoord.

Als een DECLARE CURSOR die de Transact-SQL-syntaxis gebruikt, READ_ONLY, OPTIMISTIC, of SCROLL_LOCKS, de standaardinstelling is als volgt:

• Als de SELECT -instructie ondersteunt geen updates (onvoldoende rechten, toegang tot tabellen op afstand die geen updates ondersteunen, enzovoort), de cursor is READ_ONLY.

• STATIC en FAST_FORWARD cursors zijn standaard READ_ONLY.

• DYNAMIC en KEYSET cursors zijn standaard OPTIMISTIC .

Er kan alleen naar cursornamen worden verwezen door andere Transact-SQL-instructies. Er kan niet naar worden verwezen door database-API-functies. Nadat bijvoorbeeld een cursor is gedeclareerd, kan er niet naar de cursornaam worden verwezen vanuit OLE DB-, ODBC- of ADO-functies of -methoden. De cursorrijen kunnen niet worden opgehaald met de ophaalfuncties of -methoden van de APIs; 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.

Door het systeem opgeslagen procedures	Beschrijving
sp_cursor_list	Geeft een lijst terug met cursors die momenteel zichtbaar zijn op de verbinding en hun attributen.
sp_describe_cursor	Beschrijft de attributen van een cursor, bijvoorbeeld of het een alleen-vooruit of scrollende cursor is.
sp_describe_cursor_columns	Beschrijft de attributen van de kolommen in de cursorresultaatset.
sp_describe_cursor_tables	Beschrijft de gebruikte basistabellen door de cursor.

Variabelen kunnen worden gebruikt als onderdeel van de select_statement die declareert een cursor. Waarden van cursorvariabelen veranderen niet nadat een cursor is gedeclareerd.

Toestemmingen

Toestemmingen van DECLARE CURSOR zijn standaard voor elke gebruiker die SELECT machtigingen voor de weergaven, tabellen en kolommen die in de cursor worden gebruikt.

Beperkingen en beperkingen

U kunt geen cursors of triggers gebruiken op een tabel met een geclusterde columnstore-index. Deze beperking is niet van toepassing op niet-geclusterde columnstore-indexen; je kunt cursors en triggers gebruiken op een tabel met een niet-geclusterde columnstore-index.

Voorbeelden

A. Gebruik van eenvoudige cursor en syntaxis

De resultatenset die bij het openen van deze cursor wordt gegenereerd, bevat alle rijen en alle kolommen in de tabel. Deze cursor kan worden bijgewerkt en alle updates en verwijderingen worden weergegeven in ophaalacties tegen deze cursor. FETCH NEXT is de enige beschikbare ophaalactie omdat de optie SCROLL niet is opgegeven.

B. Geneste cursors gebruiken om rapportuitvoer te produceren

Het volgende voorbeeld laat zien hoe cursors kunnen worden genest om complexe rapporten te produceren. De binnenste cursor wordt voor elke leverancier gedeclareerd.

Zie ook