Skinnysite.net

... un modo di esprimersi.

Skip to content

Homa Page di Daniele Franceschini

Gestione dei cursori con Transact-SQL

Valutazione attuale:

/ 3

Dettagli: Categoria principale: Code Snippets; Pubblicato Mercoledì, 06 Agosto 2008 21:35; Visite: 2192

Nel libro "Transact SQL Programmazione avanzata" di Itzik Ben-Gan e Tom Moreau si legge che si giustifica l'uso dei cursori solo per le seguenti ragioni:

Cattiva comprensione di SQL
Cattiva progettazione del database
Business rules intricate
Necessità di eseguire una stored procedure per ogni riga
Necessità di suddividere le modifiche dei dati in porzioni più gestibili

Spero che il caso vostro sia solo l'ultimo. L'implementazione dei cursori richiede i seguenti comandi:

DECLARE
OPEN
FETCH
UPDATE-DELETE (Facoltativo)
CLOSE
DEALLOCATE

ISTRUZIONE DECLARE

Sintassi come riportata dal manuale in linea

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 ] ] ]

Significati delle varie opzioni:

LOCAL

Significa che l'accesso al cursore sarà consentito solo all'interno della stored.

GLOBAL

Significa che tutti i livelli nidificati di stored procedure potranno accedervi, se non esiste una vera esigenza se ne sconsiglia l'uso.

FORWARD_ONLY

Specifica che l'unico tipo di istruzione FETCH che sarà possibile fare è la FETCH NEXT, ossia sarà possibile scorrere il cursore solo in avanti.

SCROLL

L'istruzione SCROLL permette lo scorrimento sia in avanti che indietro del cursore.

STATIC, KEYSET, DYNAMIC, FAST_FORWARD (Sensibilità del cursore)

STATIC: Salva le righe in una tabella di lavoro in tempdb e i locks sono liberati dalle tabelle di origine, ogni cambiamento apportato alle tabelle non verrà riflesso in nessuna istruzione FETCH. Con questo cursore non è possibile aggiornare.
KEYSET: Crea una tabella di lavoro in tempdb basata sulle chiavi della select, ogni modifica ad attributi non chiave viene riportata nel cursore
DINAMIC: Questa opzione è più sensibile delle prcedenti, riflette tutti i cambiamenti nelle tabelle sottostanti, con questa opzione non è possibile utilizzare un FETCH ABSOLUTE.
FAST_FORWARD: È una cobinazione di FORWARD_ONLY e READ_ONLY ottimizzata per le prestazioni, non può essere utilizzata con le opzioni FORWARD_OLNY, SCROLL o FOR UPDATE.

READ_ONLY

Ovviamente indica che il cursore e di sola lettura.

SCROLL_LOCKS

Posiziona i locks sulle righe mentre vengono elaborate in modo che sia garantito l'aggiornamento delle righe attraverso il cursore. Non può essere utilizzata con l'opzione FAST_FOREWARD.

OPTIMISTIC

Si riferisce al locking ottimistico, se si aggiorna una tabella presente nel cursore e la riga è stata cambiata da quando è stata elaborata, si ottiene l'errore 16934 "Optimistic concurrency check failed. The row was outside the cursor"
Se le opzioni READ_ONLY, OPTIMISTIC o SCROLL_LOCK non sono state specificate nella definizione del cursore, il comportamento predefinito è il seguente:

Se SELECT non permette aggiornamenti, se usa aggregazioni diventa READ_ONLY
STATIC e FAST_FOREWARD sono di default READ_ONLY
DYNAMIC E KEYSET sono di default OPTIMISTIC

ISTRUZIONE OPEN

Dopo aver specificato il cursore si invia il comando OPEN per cominciare a ricevere le righe, questo è il punto in cui viene eseguita la select stabilita nella dichiarazione del cursore.

ISTRUZIONE FETCH

Per recuperare una riga si utilizza il comando FETCH, non esiste modo per recuperare gruppi di righe. La sintassi del comando è la seguente:

FETCH 
    [ [ NEXT | PRIOR | FIRST | LAST 
      | ABSOLUTE { n | @nvar } 
      | RELATIVE { n | @nvar } 
      ] 
      FROM 
    ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ]

Le opzioni FETCH sono regolate dalla dichiarazione DECLARE CURSOR, FORWARD_ONLY e FAST_FORWARD permettono solo l'opzione NEXT. Con KEYSET, STATIC, o SCROLL si possono utilizzare tutte le opzioni. Con DYNAMIC si può utilizzare tutto tranne ABSOLUTE. Per indicare la posizione alle opzioni ABSOLUTE e RELATIVE è possibile utilizzare variabili di tipo smallint, int, o tinyint. Se non si specifica la clausola INTO l'intera riga avrà un output come se si facesse select, in caso contrario bisognerà specificare una variabile per ogni colonna presente nella select.

ISTRUZIONE UPDATE/DELETE

Utili qualora si voglia aggiornare o cancellare delle righe presenti nel cursore. Per specificare la riga corrente da aggiornare si usa l'istruzione CURRENT nella clausula WHERE.

ISTRUZIONE CLOSE

L'istruzione CLOSE rilascia alcune risorse occupate dal cursore come il set dei risultati e il lock che ha sull'ultima riga elaborata. L'istruzione CLOSE non rilasci il cursore.