Cursori Transact-SQL
I cursori Transact-SQL vengono utilizzati principalmente in stored procedure, trigger e script Transact-SQL per rendere disponibile ad altre istruzioni Transact-SQL il contenuto di un set di risultati.
La procedura normalmente adottata per utilizzare un cursore Transact-SQL in una stored procedure o in un trigger è la seguente:
- Dichiarare le variabili Transact-SQL in cui includere i dati restituiti dal cursore, una variabile per ogni colonna del set di risultati. Dichiarare variabili di dimensioni sufficienti a contenere i valori restituiti dalla colonna e associate a un tipo di dati verso cui è possibile convertire in modo implicito il tipo di dati della colonna.
- Associare un cursore Transact-SQL a un'istruzione SELECT mediante l'istruzione DECLARE CURSOR. Questa istruzione definisce inoltre le caratteristiche del cursore, ad esempio il nome e se si tratta di un cursore di sola lettura o forward-only.
- Utilizzare l'istruzione OPEN per eseguire l'istruzione SELECT e popolare il cursore.
- Utilizzare l'istruzione FETCH INTO per recuperare singole righe e trasferire i dati di ogni colonna nella variabile specificata. È quindi possibile fare riferimento a queste variabili in altre istruzioni Transact-SQL per accedere ai valori dei dati recuperati. I cursori Transact-SQL non supportano il recupero di blocchi di righe.
- Dopo avere definito il cursore, eseguire l'istruzione CLOSE. La chiusura del cursore consente di liberare alcune risorse, ad esempio il set di risultati del cursore e i blocchi corrispondenti nella riga corrente. La struttura del cursore tuttavia diventa di nuovo disponibile per l'elaborazione se si riesegue l'istruzione OPEN. Poiché il cursore è ancora presente, in questa fase non è possibile riutilizzarne il nome. L'istruzione DEALLOCATE libera completamente tutte le risorse allocate al cursore, tra cui il nome del cursore. Dopo avere deallocato un cursore, è necessario eseguire un'istruzione DECLARE per ricostruirlo.
Monitoraggio dell'attività di cursori Transact-SQL
La stored procedure di sistema sp_cursor_list consente di visualizzare l'elenco dei cursori visibili nella connessione corrente, mentre sp_describe_cursor, sp_describe_cursor_columns e sp_describe_cursor_tables consentono di determinare le caratteristiche di un cursore.
Dopo l'apertura del cursore, la funzione @@CURSOR_ROWS o la colonna cursor_rows restituita da sp_cursor_list o sp_describe_cursor visualizza il numero di righe del cursore.
Dopo ogni esecuzione dell'istruzione FETCH, la variabile @@FETCH_STATUS viene aggiornata in modo da riflettere lo stato dell'ultima operazione di recupero. Le informazioni sullo stato sono inoltre riportate nella colonna fetch_status restituita da sp_describe_cursor. @@FETCH_STATUS rileva condizioni quali operazioni di recupero in posizioni che precedono la prima riga del cursore o successive all'ultima. Questa variabile è globale rispetto alla connessione e viene reimpostata per ogni operazione di recupero eseguita sui cursori aperti per la connessione. Se è necessario conoscere lo stato in un momento successivo, salvare @@FETCH_STATUS in una variabile utente prima di eseguire un'altra istruzione sulla connessione. L'istruzione successiva, anche se diversa da FETCH, potrebbe essere un'istruzione INSERT, UPDATE o DELETE che attiva un trigger contenente istruzioni FETCH che reimpostano @@FETCH_STATUS. La colonna fetch_status restituita da sp_describe_cursor è specifica del cursore e non viene modificata dalle istruzioni FETCH che fanno riferimento ad altri cursori. Le istruzioni FETCH che fanno riferimento allo stesso cursore, tuttavia, hanno effetto sulla stored procedure sp_describe_cursor che è pertanto necessario utilizzare con cautela.
Dopo il completamento di un'istruzione FETCH, il cursore viene posizionato nella riga recuperata, che è denominata riga corrente. Se il cursore non è stato dichiarato come cursore di sola lettura, è possibile eseguire un'istruzione UPDATE o DELETE con una clausola WHERE CURRENT OF cursor_name per impostare come corrente una riga diversa.
Il nome assegnato a un cursore Transact-SQL dall'istruzione DECLARE CURSOR può essere globale o locale. Qualsiasi batch, stored procedure o trigger in esecuzione nella stessa connessione può fare riferimento a nomi di cursore globali, mentre non è possibile fare riferimento ai nomi di cursore locali all'esterno del batch, della stored procedure o del trigger in cui il cursore è stato dichiarato. I cursori locali inclusi in trigger e stored procedure sono pertanto protetti contro possibili riferimenti esterni involontari.
Utilizzo della variabile di cursore
Microsoft SQL Server 2005 supporta inoltre variabili di tipo cursor. Per associare un cursore a una variabile cursor è possibile utilizzare uno dei metodi seguenti:
/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR
DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks.Person.Contact
SET @MyVariable = MyCursor
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR
SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks.Person.Contact;
DEALLOCATE MyCursor;
Dopo che un cursore è stato associato a una variabile di tipo cursor, nelle istruzioni Transact-SQL è possibile specificare la variabile cursor anziché il nome del cursore. È inoltre possibile assegnare il tipo di dati cursor ai parametri di output di una stored procedure e associare quindi i parametri a un cursore. Ciò consente di controllare l'esposizione di cursori locali tramite stored procedure.
Riferimenti a cursori Transact-SQL
È possibile fare riferimento alle variabili e ai nomi di cursore Transact-SQL solo in istruzioni Transact-SQL e non nelle funzioni API di OLE DB, ODBC e ADO. Se, ad esempio, si utilizzano le istruzioni DECLARE CURSOR e OPEN con un cursore Transact-SQL, non è possibile utilizzare le funzioni SQLFetch o SQLFetchScroll per recuperare una riga del cursore Transact-SQL. Le applicazioni che richiedono l'elaborazione del cursore e che utilizzano queste API devono utilizzare il supporto per cursori predefinito dell'API di database anziché i cursori Transact-SQL.
Per utilizzare i cursori Transact-SQL nelle applicazioni, è possibile eseguire l'istruzione FETCH e associare ogni colonna restituita dall'istruzione a una variabile di applicazione. Dato che l'istruzione Transact-SQL FETCH non supporta i batch, tuttavia, questo è il modo meno efficiente per restituire dati a un'applicazione. Per il recupero di ogni riga è necessario eseguire un roundtrip al server. Risulta pertanto più efficiente ricorrere alle funzionalità di cursore delle API di database che supportano il recupero di batch di righe.
I cursori Transact-SQL risultano estremamente efficienti quando sono inclusi in stored procedure e trigger, in quanto viene eseguita la compilazione in un unico piano di esecuzione nel server e non esiste alcun traffico di rete associato alle operazioni di recupero delle righe.
Cursori Transact-SQL e opzioni SET
In SQL Server 2005 viene restituito un errore se viene eseguita un'istruzione FETCH in cui compaiono valori diversi rispetto al momento in cui il cursore è stato aperto. L'errore viene generato per tutte le opzioni relative ai piani elencate di seguito, o per le opzioni necessarie a viste e colonne calcolate. Per evitare la generazione dell'errore, non modificare le opzioni SET mentre un cursore è aperto.
Opzioni relative ai piani |
ARITHABORT NUMERIC_ROUNDABORT FORCEPLAN QUOTED_IDENTIFIER ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF ANSI_WARNINGS ANSI_PADDING ANSI_NULLS CONCAT_NULL_YIELDS_NULL DATEFIRST DATEFORMAT LANGUAGE TEXTSIZE |
Viste indicizzate e colonne calcolate |
ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT (con livello di compatibilità uguale o inferiore a 80) CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER NUMERIC_ROUNDABORT |
In SQL Server 2000 le modifiche a ANSI_NULLS e QUOTED_IDENTIFIER non generavano errori, le modifiche ad altre opzioni generavano errori.
Vedere anche
Concetti
Ambito dei nomi dei cursori Transact-SQL
Altre risorse
@@FETCH_STATUS (Transact-SQL)
FETCH (Transact-SQL)
CLOSE (Transact-SQL)
Funzioni per i cursori (Transact-SQL)
OPEN (Transact-SQL)
DEALLOCATE (Transact-SQL)
WHERE (Transact-SQL)
DECLARE CURSOR (Transact-SQL)