DECLARE CURSOR (Transact-SQL)

Data aggiornamento: 5 dicembre 2005

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

Convenzioni della sintassi Transact-SQL

Sintassi

SQL 92 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 del server Transact-SQL che si desidera definire. 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 SQL-92, 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 SQL-92, 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 sono consentite all'interno dell'argomento select_statement di una dichiarazione del cursore.

  Microsoft 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 specificata la clausola OF column_name [,...n], sono consentite 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 del server Transact-SQL che si desidera definire. 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. In SQL Server versione 7.0, questa opzione viene impostata automaticamente su FALSE per 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 l'argomento relativo all'impostazione delle opzioni del 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, il tentativo di recuperare tale riga restituisce un valore della funzione @@FETCH_STATUS uguale a -2. L'operazione di aggiornamento dei valori di chiave dall'esterno del cursore è simile a un'operazione di eliminazione della vecchia riga seguita dall'inserimento della nuova riga. La riga contenente i nuovi valori non è visibile e i tentativi di recupero della riga contenente i vecchi valori restituiscono un valore della funzione @@FETCH_STATUS uguale a -2. I nuovi valori sono visibili se l'aggiornamento viene eseguito tramite il cursore specificando la 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 attivate 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 è possibile utilizzare entrambe le parole chiave nella 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 Microsoft 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 posizionati o le eliminazioni eseguite tramite il cursore non verranno eseguiti correttamente se la riga è stata aggiornata dopo la lettura nel cursore. Durante la lettura nel cursore le righe non vengono bloccate. Vengono invece eseguiti confronti tra i valori della colonna di tipo timestamp, o di un valore checksum se la tabella non include una colonna di tipo timestamp, per determinare se la riga è stata modificata dopo la lettura nel cursore. 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 se il cursore viene convertito in modo implicito dal tipo richiesto a 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 sono consentite all'interno dell'argomento select_statement di una dichiarazione del 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 specificata la clausola OF column_name [,...n], sono consentite 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 costruire 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 SQL-92 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 istruzioni o i metodi di recupero API, ma solo utilizzando istruzioni FETCH di 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 delle variabili vengono aggiornati a ogni riapertura di 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. Il cursore è aggiornabile e tutte le eliminazioni e gli aggiornamenti vengono riportati nelle operazioni di recupero eseguite nel cursore. FETCH NEXT è l'unica istruzione FETCH disponibile perché l'opzione SCROLL non è stata specificata.

DECLARE vend_cursor CURSOR
FOR SELECT * 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 illustrata la possibilità di nidificazione dei 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

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)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione	Cronologia
5 dicembre 2005	Nuovo contenuto Aggiunta di una nota sull'utilizzo di un hint per la query in una dichiarazione di cursore.