Ambito dei nomi dei cursori Transact-SQL
In Microsoft SQL Server sono supportate le parole chiave GLOBAL e LOCAL dell'istruzione DECLARE CURSOR che consentono di definire l'ambito del nome del cursore. La parola chiave GLOBAL indica che il nome del cursore è globale in relazione alla connessione, mentre LOCAL indica che il nome del cursore è locale in relazione alla stored procedure, al trigger o al batch che include l'istruzione DECLARE CURSOR.
Nelle versioni di Microsoft SQL Server precedenti alla 7.0 i nomi dei cursori Transact-SQL sono globali in relazione alla connessione, ovvero è possibile eseguire una stored procedure per creare un cursore e quindi richiamare un'altra stored procedure per recuperare le righe di tale cursore:
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE OpenCrsr AS
DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Person
WHERE LastName LIKE 'S%';
OPEN SampleCrsr;
GO
CREATE PROCEDURE ReadCrsr AS
FETCH NEXT FROM SampleCrsr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM SampleCrsr
END
GO
EXEC OpenCrsr; /* DECLARES and OPENS SampleCrsr. */
GO
EXEC ReadCrsr; /* Fetches the rows from SampleCrsr. */
GO
CLOSE SampleCrsr;
GO
DEALLOCATE SampleCrsr;
GO
I cursori locali offrono una protezione efficace per i cursori implementati in stored procedure e trigger. È possibile fare riferimento ai cursori globali all'esterno delle stored procedure o dei trigger in cui sono dichiarati. Di conseguenza possono venire modificati inavvertitamente da istruzioni esterne alla stored procedure o trigger. I cursori locali risultano più sicuri di quelli globali, in quanto non è possibile farvi riferimento all'esterno di una stored procedure, a meno che non vengano reinviati al chiamante in modo esplicito sotto forma di parametro di output del cursore.
Poiché è possibile fare riferimento ai cursori globali all'esterno di una stored procedure o di un trigger, tali cursori potrebbero provocare conseguenze impreviste che influiscono su altre istruzioni. Si supponga, ad esempio, che una stored procedure crei il cursore globale xyz che rimane aperto anche dopo l'esecuzione della stored procedure. Se dopo il completamento della stored procedure si dichiara un altro cursore globale specificando lo stesso nome xyz, viene generato un errore di duplicazione dei nomi.
Ai cursori globali e locali sono associati spazi dei nomi distinti. Di conseguenza è possibile utilizzare contemporaneamente cursori locali e globali con lo stesso nome. Le istruzioni Transact-SQL che accettano come parametro un nome di cursore supportano la parola chiave GLOBAL per l'identificazione dell'ambito del nome. Se la parola chiave GLOBAL viene omessa e sono disponibili un cursore globale e un cursore locale con il nome specificato nel parametro del nome di cursore, tale parametro farà riferimento al cursore locale.
Se entrambe le parole chiave LOCAL e GLOBAL vengono omesse, il valore predefinito per l'istruzione DECLARE CURSOR viene controllato dall'opzione di database CURSOR_DEFAULT, impostata con l'istruzione ALTER DATABASE. Il valore corrente per l'opzione di database è archiviato nella vista del catalogo sys.databases. Se il valore nella colonna local_cursor_default nella vista del catalogo sys.databases è true, per impostazione predefinita i cursori Transact-SQL sono locali. Se invece l'opzione è false, per impostazione predefinita i cursori Transact-SQL sono globali. In SQL Server il valore predefinito dell'opzione di database è FALSE (GLOBAL) per garantire la compatibilità con le versioni precedenti di SQL Server.
Tramite le stored procedure che dichiarano e aprono cursori locali è possibile passare i cursori che saranno utilizzati dalla stored procedure, dal trigger o dal batch di chiamata. Questa operazione viene eseguita specificando il nuovo tipo di dati CURSOR VARYING per il parametro OUTPUT. Le variabili di cursore sono utilizzabili solo come parametri di output, non come parametri di input. Per consentire il passaggio del cursore in un parametro OUTPUT, è necessario che al completamento della stored procedure il cursore sia aperto. Le variabili locali possono inoltre essere dichiarate con il nuovo tipo di dati CURSOR in modo che sia possibile includervi un riferimento a un cursore locale.
USE AdventureWorks2008R2;
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT TOP (20) LastName
FROM Person.Person
WHERE LastName LIKE 'S%';
OPEN @OutCrsr;
GO
/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR;
/* Execute the procedure created earlier to fill
the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT;
/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @CrsrVar
END;
CLOSE @CrsrVar;
DEALLOCATE @CrsrVar;
GO
Le API del database non supportano i parametri di output del cursore nelle stored procedure. Le stored procedure che includono un parametro di output di un cursore non possono essere eseguite direttamente da una funzione API di database, ma soltanto tramite una stored procedure, un trigger oppure un batch o script di Transact-SQL.
Un cursore GLOBAL risulta disponibile fino a quando non viene deallocato in modo esplicito oppure quando la connessione viene chiusa. I cursori LOCAL vengono deallocati in modo implicito al completamento della stored procedure, del trigger o del batch in cui sono stati creati, a meno che non vengano passati come parametro. In questo caso vengono deallocati in modo implicito quando il parametro o la variabile che fa riferimento al cursore nel codice che ha richiamato la procedura non è più inclusa nell'ambito valido.