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
MicrosoftSQL Server supporta anche le variabili con un tipo di dati 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 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.