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.INSENSITIVE
Definisce un cursore che crea una copia temporanea dei dati utilizzati dal 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 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.
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.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.
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 2005 e versioni successive, è possibile utilizzare FAST_FORWARD e FORWARD_ONLY 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 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. 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 eseguite tramite il cursore non avranno esito positivo se la riga ha subito un aggiornamento dopo la lettura nel cursore. SQL Server non blocca le righe man mano che 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.
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. FETCH NEXT è l'unica operazione di recupero 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 annidati 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.
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;