Condividi tramite

DECLARE CURSOR (Transact-SQL)

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric

Definisce gli attributi di un cursore del server Transact-SQL, ad esempio lo scorrimento e la query usata per compilare il set di risultati su cui agisce il cursore. DECLARE CURSOR accetta sia la sintassi basata sullo standard ISO che una sintassi che usi un set di estensioni Transact-SQL.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi ISO:

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

Sintassi estesa transact-SQL:

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

Argomenti

cursor_name

Nome del cursore Transact-SQL Server definito. cursor_name deve essere conforme alle regole per gli identificatori.

INSENSITIVE

Definisce un cursore che crea una copia temporanea dei dati utilizzati dal cursore. Tutte le richieste al cursore vengono risposte da questa tabella temporanea in tempdb. Pertanto, le modifiche alla tabella di base non vengono riflesse nei dati restituiti dai recuperi apportati a questo cursore e questo cursore non consente modifiche. Nella sintassi ISO, se viene omessa la parola chiave INSENSITIVE, le operazioni di eliminazione e aggiornamento di cui è stato eseguito il commit nelle tabelle sottostanti da parte di qualsiasi utente si riflettono nelle operazioni di recupero successive.

SCROLL

Specifica che tutte le opzioni di recupero (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) sono disponibili. Se SCROLL non è specificato in un ISO DECLARE CURSOR, NEXT è l'unica opzione di recupero supportata. SCROLL non può essere specificato se FAST_FORWARD è specificato anche . Se SCROLL non viene specificato, è disponibile solo l'opzione NEXT di recupero e il cursore diventa FORWARD_ONLY.

select_statement

Istruzione standard SELECT che definisce il set di risultati del cursore. Le parole chiave e INTO non sono consentite FOR BROWSEall'interno di select_statement di una dichiarazione di cursore.

Se le clausole nell'argomento select_statement sono in conflitto con la funzionalità del tipo di cursore richiesto, SQL Server converte il cursore in modo implicito in un altro tipo.

READ_ONLY

Impedisce gli aggiornamenti eseguiti tramite il cursore. Non è possibile fare riferimento al cursore in una WHERE CURRENT OF clausola in un'istruzione UPDATE o DELETE . Questa opzione è prioritaria rispetto alla funzionalità di aggiornamento predefinita di un cursore.

UPDATE [ OF column_name [ ,...n ] ]

Definisce le colonne aggiornabili nel cursore. Se OF <column_name> [, <... n> ] viene specificato, solo le colonne elencate consentono modifiche. Se si specifica UPDATE senza un elenco di colonne, è possibile aggiornare tutte le colonne.

cursor_name

Nome del cursore Transact-SQL Server definito. cursor_name deve essere conforme alle regole per gli identificatori.

LOCAL

Specifica che l'ambito del cursore è locale rispetto al batch, alla stored procedure o al trigger in cui il cursore è stato creato. Il nome del cursore è valido solo in tale ambito. È possibile fare riferimento al cursore tramite variabili di cursore locali nel batch, nella stored procedure o nel trigger oppure tramite un parametro OUTPUT di stored procedure. Un parametro OUTPUT consente di passare il cursore locale al batch, alla stored procedure o al trigger chiamante, che può quindi assegnare il parametro a una variabile cursore per fare riferimento al cursore dopo l'esecuzione della stored procedure. Il cursore viene deallocato in modo implicito al termine del batch, della stored procedure o del trigger, a meno che non venga passato a un parametro OUTPUT. Se passa indietro in un OUTPUT parametro, il cursore viene deallocato quando l'ultima variabile che fa riferimento viene deallocata o esce dall'ambito.

GLOBAL

Specifica che l'ambito del cursore è globale rispetto alla connessione. È possibile fare riferimento al nome del cursore in qualsiasi stored procedure o batch eseguiti tramite la connessione. Il cursore viene deallocato solo in modo implicito in fase di disconnessione.

Nota

Se gli argomenti GLOBAL e LOCAL vengono entrambi omessi, il valore predefinito dipende dall'impostazione dell'opzione di database default to local cursor.

FORWARD_ONLY

Specifica che il cursore può solo spostarsi in avanti e scorrere dalla prima all'ultima riga. FETCH NEXT è l'unica opzione di recupero supportata. Tutte le istruzioni di inserimento, aggiornamento ed eliminazione effettuate dall'utente corrente (o di cui è stato eseguito il commit da altri utenti) che influiscono sulle righe nel set di risultati, sono visibili quando vengono recuperate le righe. Poiché non è possibile scorrere il cursore all'indietro, tuttavia, le modifiche apportate alle righe nel database dopo che la riga è stata recuperata non sono visibili tramite il cursore. I cursori forward-only sono dinamici per impostazione predefinita, vale a dire che tutte le modifiche vengono rilevate durante l'elaborazione della riga corrente. Questo consente maggiore rapidità di apertura del cursore e la visualizzazione nel set di risultati degli aggiornamenti apportati alle tabelle sottostanti. Mentre i cursori forward-only non supportano lo scorrimento indietro, le applicazioni possono tornare all'inizio del set di risultati chiudendo e riaprendo il cursore.

Se si specifica l'opzione FORWARD_ONLY senza alcuna delle parole chiave STATIC, KEYSET o DYNAMIC, il cursore fungerà da cursore dinamico. Quando FORWARD_ONLY o SCROLL non vengono specificati, FORWARD_ONLY è l'impostazione predefinita, a meno che non vengano specificate le parole chiave STATIC, KEYSETo DYNAMIC . L'impostazione predefinita dei cursori STATIC, KEYSET e DYNAMIC è SCROLL. A differenza delle API di database, ad esempio ODBC e ADO, l'opzione FORWARD_ONLY è supportata con i cursori Transact-SQL STATIC, KEYSET e DYNAMIC.

STATIC

Specifica che il cursore deve sempre visualizzare il set di risultati com'era quando il cursore è stato aperto la prima volta e creare una copia temporanea dei dati che deve usare. Tutte le richieste al cursore vengono risposte da questa tabella temporanea in tempdb. Pertanto, gli inserimenti, gli aggiornamenti e le eliminazioni apportate alle tabelle di base non vengono riflessi nei dati restituiti dai recuperi eseguiti in questo cursore e questo cursore non rileva le modifiche apportate all'appartenenza, all'ordine o ai valori del set di risultati dopo l'apertura del cursore. I cursori statici potrebbero rilevare i propri aggiornamenti, eliminazioni e inserimenti, anche se non sono necessari per farlo.

Si supponga, ad esempio, che un cursore statico recuperi una riga e che un'altra applicazione aggiorni in seguito tale riga. Se l'applicazione recupera nuovamente la riga dal cursore statico, i valori che rileva sono invariati, nonostante le modifiche apportate dall'altra applicazione. Sono supportati tutti i tipi di scorrimento.

KEYSET

Specifica che all'apertura del cursore l'appartenenza e l'ordine delle righe nel cursore sono fissi. Il set di chiavi che identificano in modo univoco le righe viene integrato in una tabella in tempdb nota come keyset. Questo cursore offre funzionalità intermedie di cursore statico e cursore dinamico, per quanto riguarda la facoltà di quest'ultimo di rilevare le modifiche. Come un cursore statico, non rileva sempre le modifiche apportate all'appartenenza e all'ordine del set di risultati. Come un cursore dinamico, rileva le modifiche ai valori delle righe nel set di risultati.

I cursori gestiti da keyset vengono controllati da un set di identificatori univoci (chiavi), noti come keyset. Le chiavi sono costituite da un set di colonne che identificano in modo univoco le righe del set di risultati. Il keyset corrisponde al set di valori di chiave di tutte le righe restituite dall'istruzione della query. Con i cursori gestiti da keyset viene generata e salvata una chiave per ogni riga nel cursore e tale chiave viene archiviata nella workstation client o nel server. Quando si accede a ogni riga, la chiave archiviata viene usata per recuperare i valori correnti dei dati dall'origine dati. In un cursore gestito da keyset, quando il set di chiavi è completamente popolato, l'appartenenza del set di risultati è bloccata. Successivamente, le aggiunte o gli aggiornamenti che influiscono sull'appartenenza non fanno parte del set di risultati fino a quando non viene riaperto.

Le modifiche ai valori dei dati, apportate dal proprietario del keyset o da altri processi, sono visibili mentre l'utente scorre il set di risultati:

  • Se una riga viene eliminata, un tentativo di recuperare la riga restituisce un valore @@FETCH_STATUS di -2 perché la riga eliminata viene visualizzata come gap nel set di risultati. La chiave per la riga è presente nel keyset, ma la riga non esiste più nel set di risultati.

  • Gli inserimenti eseguiti all'esterno del cursore da altri processi sono visibili solo se il cursore viene chiuso e riaperto. Gli inserimenti eseguiti dall'interno del cursore sono visibili alla fine del set di risultati.

  • Le operazioni di aggiornamento di valori di chiave dall'esterno del cursore sono simili a un'operazione di eliminazione della riga precedente seguita da un'operazione di inserimento della nuova riga. La riga con i nuovi valori non è visibile e tenta di recuperare la riga con i valori precedenti restituisce un valore @@FETCH_STATUS di -2. I nuovi valori sono visibili se l'aggiornamento viene effettuato tramite il cursore con l'aggiunta della clausola WHERE CURRENT OF.

Nota

Se la query fa riferimento ad almeno una tabella priva di indice univoco, il cursore keyset viene convertito in cursore statico.

DYNAMIC

Definisce un cursore che riflette tutte le modifiche apportate ai dati delle righe nel relativo set di risultati quando l'utente scorre il cursore e recupera un nuovo record, indipendentemente dal fatto che le modifiche vengano eseguite dall'interno del cursore o da altri utenti all'esterno del cursore stesso. Di conseguenza tutte le istruzioni di inserimento, aggiornamento ed eliminazione eseguite da tutti gli utenti sono visibili tramite il cursore. I valori dei dati, l'ordine e l'appartenenza delle righe possono cambiare a ogni operazione di recupero. L'opzione ABSOLUTE di recupero non è supportata con i cursori dinamici. Gli aggiornamenti eseguiti all'esterno del cursore non sono visibili finché non viene eseguito il commit (a meno che il livello di isolamento della transazione del cursore non sia impostato su UNCOMMITTED).

Si supponga, ad esempio, che un cursore dinamico recuperi due righe e un'altra applicazione aggiorni una di queste righe ed elimini l'altra. Se il cursore dinamico recupera tali righe, non trova la riga eliminata, ma visualizza i nuovi valori per la riga aggiornata.

FAST_FORWARD

Specifica un cursore FORWARD_ONLY, READ_ONLY con le ottimizzazioni delle prestazioni abilitate. FAST_FORWARD non può essere specificato se SCROLL o FOR_UPDATE è specificato anche . Questo tipo di cursore non consente modifiche ai dati dall'interno del cursore.

Nota

Le opzioni FAST_FORWARD e FORWARD_ONLY possono essere usate nella stessa istruzione DECLARE CURSOR.

READ_ONLY

Impedisce gli aggiornamenti eseguiti tramite il cursore. Non è possibile fare riferimento al cursore in una WHERE CURRENT OF clausola in un'istruzione UPDATE o DELETE . Questa opzione è prioritaria rispetto alla funzionalità di aggiornamento predefinita di un cursore.

SCROLL_LOCKS

Specifica che gli aggiornamenti o le eliminazioni posizionate eseguite tramite il cursore avranno esito positivo. Durante la lettura nel cursore, SQL Server blocca le righe in modo che siano disponibili per modifiche successive. SCROLL_LOCKS non può essere specificato se FAST_FORWARD o STATIC è specificato anche .

OPTIMISTIC

Specifica che gli aggiornamenti o le eliminazioni posizionati eseguiti tramite il cursore non hanno esito positivo, se la riga è stata aggiornata perché è stata letta nel cursore. SQL Server non blocca le righe mentre vengono lette nel cursore. Per determinare se la riga è stata modificata dopo la lettura nel cursore, vengono usati confronti tra i valori della colonna timestamp oppure un valore di checksum se la tabella non include una colonna timestamp.

Se la riga è stata modificata, i tentativi di eseguire un aggiornamento o un'eliminazione posizionata hanno esito negativo. OPTIMISTIC non può essere specificato se FAST_FORWARD è specificato anche .

Se STATIC viene specificato insieme all'argomento OPTIMISTIC cursore, la combinazione dei due viene convertita in modo implicito nell'equivalente della combinazione di argomenti e using STATIC o degli STATIC argomenti e READ_ONLY FORWARD_ONLY .

TYPE_WARNING

Specifica che deve essere inviato un messaggio di avviso al client quando il cursore viene convertito in modo implicito dal tipo richiesto in un altro tipo.

Non viene inviato alcun avviso al client quando viene usata la combinazione di OPTIMISTIC argomenti di cursore e STATIC il cursore viene convertito in modo implicito nell'equivalente di un STATIC READ_ONLY cursore o STATIC FORWARD_ONLY . Conversione da READ_ONLY trasformare in un FAST_FORWARD cursore e READ_ONLY dal punto di vista di un client.

select_statement

Istruzione standard SELECT che definisce il set di risultati del cursore. Le parole chiave COMPUTE, COMPUTE BY, FOR BROWSEe INTO non sono consentite all'interno di select_statement di una dichiarazione di cursore.

Nota

È possibile usare un hint per la query all'interno di una dichiarazione di cursore. Tuttavia, se si usa anche la FOR UPDATE OF clausola , specificare OPTION (<query_hint>) dopo FOR UPDATE OF.

Se le clausole nell'argomento select_statement sono in conflitto con la funzionalità del tipo di cursore richiesto, SQL Server converte il cursore in modo implicito in un altro tipo.

FOR UPDATE [ OF column_name [ ,...n ] ]

Definisce le colonne aggiornabili nel cursore. Se si specifica OF <column_name> [, <... n>], è possibile apportare modifiche solo nelle colonne elencate. Se l'istruzione UPDATE viene specificata senza un elenco di colonne, è possibile aggiornare tutte le colonne, a meno che non sia stata specificata l'opzione di concorrenza READ_ONLY.

Osservazioni:

DECLARE CURSOR definisce gli attributi di un cursore del server Transact-SQL, ad esempio lo scorrimento e la query usata per compilare il set di risultati su cui agisce il cursore. L'istruzione OPEN popola il set di risultati e l'istruzione FETCH restituisce una riga di questo set. L'istruzione CLOSE rilascia il set di risultati corrente associato al cursore. L'istruzione DEALLOCATE rilascia le risorse usate dal cursore.

La prima forma dell'istruzione DECLARE CURSOR dichiara il funzionamento del cursore tramite la sintassi ISO. La seconda forma dell'istruzione DECLARE CURSOR usa estensioni Transact-SQL che consentono di definire cursori in base allo stesso tipo usato nelle funzioni di cursore delle API di database ODBC o ADO.

Non è possibile combinare le due forme. Se si specificano le SCROLL parole chiave o INSENSITIVE prima della CURSOR parola chiave , non è possibile usare parole chiave tra le CURSOR parole chiave e FOR <select_statement> . Se si specificano parole chiave tra le CURSOR parole chiave e FOR <select_statement> , non è possibile specificare SCROLL o INSENSITIVE prima della CURSOR parola chiave .

Se un DECLARE CURSOR oggetto che usa la sintassi Transact-SQL non specifica READ_ONLY, OPTIMISTICo SCROLL_LOCKS, l'impostazione predefinita è la seguente:

  • Se l'istruzione SELECT non supporta gli aggiornamenti (autorizzazioni insufficienti, accesso a tabelle remote che non supportano gli aggiornamenti e così via), il cursore è READ_ONLY.

  • L'impostazione predefinita dei cursori STATIC e FAST_FORWARD è READ_ONLY.

  • L'impostazione predefinita dei cursori DYNAMIC e KEYSET è OPTIMISTIC.

I nomi dei cursori possono essere referenziati solo da altre istruzioni Transact-SQL. Non è possibile fare riferimento alle funzioni DELL'API del database. Ad esempio, dopo aver dichiarato un cursore, non è possibile fare riferimento al nome del cursore da funzioni o metodi OLE DB, ODBC o ADO. Le righe del cursore non possono essere recuperate usando le funzioni di recupero o i metodi delle API; Le righe possono essere recuperate solo dalle istruzioni Transact-SQL FETCH .

Dopo la dichiarazione di un cursore, queste stored procedure di sistema possono essere usate per determinare le caratteristiche del cursore.

Stored procedure di sistema Descrizione
sp_cursor_list Restituisce l'elenco dei cursori visibili nella connessione e gli attributi corrispondenti.
sp_describe_cursor Descrive gli attributi di un cursore, ad esempio se si tratta di un cursore forward-only o di scorrimento.
sp_describe_cursor_columns Descrive gli attributi delle colonne nel set di risultati del cursore.
sp_describe_cursor_tables Descrive le tabelle di base a cui ha avuto accesso il cursore.

Le variabili possono essere usate come parte del select_statement che dichiara un cursore. I valori delle variabili di cursore non cambiano dopo la dichiarazione di un cursore.

Autorizzazioni

Le autorizzazioni per l'istruzione DECLARE CURSOR vengono assegnate per impostazione predefinita a qualsiasi utente che disponga delle autorizzazioni per l'istruzione SELECT nelle viste, nelle tabelle e nelle colonne usate nel cursore.

Limiti

Non è possibile usare cursori o trigger in una tabella con un indice columnstore cluster. Questa restrizione non si applica agli indici columnstore non cluster. È possibile usare cursori e trigger in una tabella con un indice columnstore cluster.

Esempi

R. Usare il cursore e la sintassi di base

Il set di risultati generato all'apertura del cursore include tutte le righe e le colonne della tabella. Questo cursore può essere aggiornato e tutti gli aggiornamenti e le eliminazioni sono rappresentati nei recuperi eseguiti su questo cursore. FETCH NEXT è l'unico recupero disponibile perché l'opzione SCROLL non è specificata.

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

B. Usare cursori annidati per produrre l'output del report

Nell'esempio seguente viene illustrato in che modo è possibile nidificare i cursori per generare report complessi. Il cursore interno viene dichiarato per ogni fornitore.

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;

Contenuto correlato