DECLARE CURSOR (Transact-SQL)

Definisce gli attributi di un cursore del server Transact-SQL, ad esempio lo scorrimento e la query utilizzata per compilare il set di risultati su cui agisce il cursore. L'istruzione DECLARE CURSOR supporta la sintassi basata sullo standard ISO e la sintassi che utilizza un set di estensioni Transact-SQL.

Convenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

• cursor_name
  Nome del cursore server Transact-SQL definito. L'argomento cursor_name deve essere conforme alle regole per gli identificatori. Per ulteriori informazioni sulle regole per gli identificatori, vedere Utilizzo degli identificatori come nomi di oggetti.

• INSENSITIVE
  Definisce un cursore per la creazione di una copia temporanea dei dati che devono essere utilizzati nel cursore. La risposta alle richieste indirizzate al cursore viene formulata tramite questa tabella temporanea in tempdb. Le modifiche alle tabelle di base non vengono pertanto riportate nei dati restituiti dalle operazioni di recupero eseguite in questo cursore, in cui non è consentito apportare modifiche. Nella sintassi ISO, se viene omessa la parola chiave INSENSITIVE, le operazioni di eliminazione e aggiornamento eseguite nelle tabelle sottostanti da parte degli utenti e di cui è stato eseguito il commit vengono riportate nelle successive operazioni di recupero.

• SCROLL
  Specifica che tutte le opzioni di recupero (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) sono disponibili. Se non viene specificata l'opzione SCROLL in un'istruzione DECLARE CURSOR di ISO, l'unica opzione di recupero supportata è NEXT. Non è possibile specificare SCROLL se è stata specificata l'opzione FAST_FORWARD.

• select_statement
  Istruzione SELECT standard che definisce il set di risultati del cursore. Le parole chiave COMPUTE, COMPUTE BY, FOR BROWSE e INTO non possono essere utilizzate nell'argomento 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. Per ulteriori informazioni, vedere Utilizzo della conversione implicita del cursore.

• READ ONLY
  Impedisce gli aggiornamenti eseguiti tramite il cursore. Non è possibile fare riferimento al cursore in una clausola WHERE CURRENT OF di 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 viene specificato 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.

• cursor_name
  Nome del cursore server Transact-SQL definito. L'argomento cursor_name deve essere conforme alle regole per gli identificatori. Per ulteriori informazioni sulle regole per gli identificatori, vedere Utilizzo degli identificatori come nomi di oggetti.

• 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 da variabili cursore locali nel batch, nella stored procedure o nel trigger oppure da 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. In tal caso, il cursore viene deallocato quando l'ultima variabile che vi fa riferimento viene deallocata o risulta esterna all'ambito di validità.

• 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 le opzioni GLOBAL e LOCAL vengono omesse entrambe, il valore predefinito dipende dall'impostazione dell'opzione di database default to local cursor. Nella versione 7.0 di SQL Server, questa opzione viene impostata automaticamente su FALSE per la compatibilità con le versioni precedenti di SQL Server in cui tutti i cursori sono globali. Nelle future versioni di SQL Server il valore predefinito di questa opzione potrebbe essere diverso. Per ulteriori informazioni, vedere Impostazione delle opzioni di database.

• FORWARD_ONLY
  Specifica che è possibile scorrere il cursore solo dalla prima all'ultima riga. FETCH NEXT è l'unica opzione dell'istruzione FETCH supportata. Se si specifica l'opzione FORWARD_ONLY senza la parola chiave STATIC, KEYSET o DYNAMIC, il cursore sarà un cursore DYNAMIC. Se vengono omesse sia l'opzione FORWARD_ONLY che l'opzione SCROLL, FORWARD_ONLY corrisponde al valore predefinito quando non vengono specificate le parole chiave STATIC, KEYSET o DYNAMIC. I cursori STATIC, KEYSET e DYNAMIC vengono impostati automaticamente su SCROLL. A differenza delle API del database, ad esempio ODBC e ADO, l'opzione FORWARD_ONLY è supportata con cursori STATIC, KEYSET e DYNAMIC di Transact-SQL.

• STATIC
  Definisce un cursore per la creazione di una copia temporanea dei dati che devono essere utilizzati nel cursore. La risposta alle richieste indirizzate al cursore viene formulata tramite questa tabella temporanea in tempdb. Le modifiche alle tabelle di base non vengono pertanto riportate nei dati restituiti dalle operazioni di recupero eseguite in questo cursore, in cui non è consentito apportare modifiche.

• KEYSET
  Specifica che all'apertura del cursore l'appartenenza e l'ordine delle righe nel cursore sono fissi. Il set di chiavi che identifica le righe in modo univoco è incluso in una tabella di tempdb denominata keyset.

  Nota

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

  Le modifiche a valori non chiave apportate nelle tabelle di base dal proprietario del cursore o confermate tramite commit da altri utenti sono visibili quando il proprietario scorre il cursore. Gli inserimenti eseguiti da altri utenti non sono visibili (tali inserimenti non possono essere eseguiti tramite un cursore Transact-SQL del server). Se viene eliminata una riga, i tentativi di recupero di tale riga provocano la restituzione del valore -2 per la funzione @@FETCH_STATUS. 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 contenente i nuovi valori non è visibile e i tentativi di recupero della riga contenente i valori precedenti provocano la restituzione del valore -2 per la funzione @@FETCH_STATUS. I nuovi valori sono visibili se l'aggiornamento viene effettuato tramite il cursore con l'aggiunta della clausola WHERE CURRENT OF.

• DYNAMIC
  Definisce un cursore in cui, durante lo scorrimento, vengono riportate tutte le modifiche ai dati apportate alle righe nel set di risultati. I valori dei dati, l'ordine e l'appartenenza delle righe possono cambiare in ogni operazione di recupero. L'opzione ABSOLUTE dell'istruzione FETCH non è supportata con cursori dinamici.

• FAST_FORWARD
  Specifica un cursore FORWARD_ONLY, READ_ONLY per il quale sono abilitate le ottimizzazioni delle prestazioni. Non è possibile specificare l'opzione FAST_FORWARD se è stata specificata l'opzione SCROLL o FOR_UPDATE.

  Nota

  In SQL Server 2000 le opzioni di cursore FAST_FORWARD e FORWARD_ONLY si escludono a vicenda. Se vengono specificate entrambe, viene generato un errore. In SQL Server 2005 e versioni successive, è possibile utilizzare entrambe le parole chiave in una stessa istruzione DECLARE CURSOR.

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

• SCROLL_LOCKS
  Specifica che gli aggiornamenti posizionati o le eliminazioni eseguite tramite il cursore verranno eseguiti correttamente. Durante la lettura nel cursore SQL Server blocca le righe in modo che siano disponibili per modifiche successive. Non è possibile specificare l'opzione SCROLL_LOCKS se è stata specificata l'opzione FAST_FORWARD o STATIC.

• OPTIMISTIC
  Specifica che gli aggiornamenti o le eliminazioni posizionate eseguiti tramite il cursore non avranno esito positivo se la riga è stata aggiornata dopo la lettura 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 utilizzati confronti tra i valori della colonna di tipo timestamp oppure un valore di checksum se la tabella non include una colonna di tipo timestamp. Se la riga è stata modificata, i tentativi di aggiornamento posizionato o di eliminazione hanno esito negativo. Non è possibile specificare l'opzione OPTIMISTIC se è specificata l'opzione FAST_FORWARD.

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

• select_statement
  Istruzione SELECT standard che definisce il set di risultati del cursore. Le parole chiave COMPUTE, COMPUTE BY, FOR BROWSE e INTO non possono essere utilizzate nell'argomento select_statement di una dichiarazione di cursore.

  Nota

  È possibile utilizzare un hint per la query in una dichiarazione di cursore. Se tuttavia si utilizza anche la clausola FOR UPDATE OF, 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. Per ulteriori informazioni, vedere l'argomento relativo alla conversione implicita dei cursori.

• FOR UPDATE [OF column_name [,...n]]
  Definisce le colonne aggiornabili nel cursore. Se viene specificato 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 opposta READ_ONLY.

Osservazioni

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

Nella prima forma dell'istruzione DECLARE CURSOR viene utilizzata la sintassi ISO per dichiarare il funzionamento del cursore. Nella seconda forma dell'istruzione vengono utilizzate le estensioni di Transact-SQL che consentono di definire i cursori in base allo stesso tipo di cursore utilizzato nelle funzioni di cursore delle API del database ODBC o ADO.

Non è possibile combinare le due forme dell'istruzione. Se viene specificata la parola chiave SCROLL o INSENSITIVE prima della parola chiave CURSOR, non è possibile specificare parole chiave tra le parole chiave CURSOR e FOR select_statement. Se vengono specificate parole chiave tra le due parole chiave CURSOR e FOR select_statement, non è possibile specificare SCROLL o INSENSITIVE prima della parola chiave CURSOR.

Se in un'istruzione DECLARE CURSOR basata sulla sintassi Transact-SQL viene omessa l'opzione READ_ONLY, OPTIMISTIC o SCROLL_LOCKS, per impostazione predefinita i cursori vengono impostati come descritto di seguito:

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

• I cursori STATIC e FAST_FORWARD vengono impostati automaticamente come cursori READ_ONLY.

• I cursori DYNAMIC e KEYSET vengono impostati automaticamente come cursori OPTIMISTIC.

È possibile fare riferimento a nomi di cursore solo tramite altre istruzioni Transact-SQL. Non è possibile fare riferimento ai nomi di cursore con funzioni API del database. Dopo la dichiarazione del cursore, ad esempio, non è possibile fare riferimento al nome del cursore tramite le funzioni o i metodi di OLE DB, ODBC o ADO. Le righe del cursore non possono essere recuperate tramite le funzioni o i metodi di recupero API, ma solo utilizzando istruzioni FETCH Transact-SQL.

Dopo la dichiarazione di un cursore è possibile eseguire le stored procedure di sistema seguenti 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 scorrevole.
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.

È possibile utilizzare variabili come parte dell'istruzione select_statement che dichiara un cursore. Dopo la dichiarazione di un cursore i valori delle variabili di cursore non cambiano. In SQL Server versione 6.5 e precedenti, i valori di variabile vengono aggiornati ogni volta che viene riaperto un cursore.

Autorizzazioni

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

Esempi

A. Utilizzo di una semplice sintassi per la definizione di un cursore

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. FETCHNEXT è l'unica operazione di recupero disponibile perché l'opzione SCROLL non è stata specificata.

USE AdventureWorks2008R2;
GO
DECLARE vend_cursor CURSOR
    FOR SELECT BusinessEntityID, Name, CreditRating FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. Utilizzo di cursori nidificati per la generazione di 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.

USE AdventureWorks2008R2;
GO
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 BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;

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 AS pv
    INNER JOIN Production.Product AS v 
        ON pv.ProductID = v.ProductID AND
           pv.BusinessEntityID = @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;

Vedere anche

Riferimento

@@FETCH_STATUS (Transact-SQL)

CLOSE (Transact-SQL)

Cursori (Transact-SQL)

DEALLOCATE (Transact-SQL)

FETCH (Transact-SQL)

SELECT (Transact-SQL)

sp_configure (Transact-SQL)