Ambito dei nomi dei cursori Transact-SQL

In MicrosoftSQL 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 MicrosoftSQL 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 richiamare un'altra stored procedure per recuperare le righe del cursore:

Utilizza AdventureWorks;
GO
CREATE PROCEDURE OpenCrsr AS

DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Contact
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; /* Dichiara e apre SampleCrsr. */
GO
EXEC ReadCrsr; /* Recupera le righe da 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 aventi 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 aventi 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 è controllato dall'opzione di database CURSOR_DEFAULT. 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, i cursori Transact-SQL per impostazione predefinita sono locali. Se invece l'opzione è impostata su false, per impostazione predefinita i cursori Transact-SQL sono globali. In SQL Server il valore predefinito dell'opzione è 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 tramite la chiamata della stored procedure, del trigger o del batch. 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 essere dichiarate inoltre con il nuovo tipo di dati CURSOR in modo che sia possibile includervi un riferimento a cursori locali.

USE AdventureWorks;
GO
/* Creare una procedura con un parametro di output del cursore. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS

SET @OutCrsr = CURSOR FOR
SELECT TOP (20) LastName
FROM Person.Contact
WHERE LastName LIKE 'S%';

OPEN @OutCrsr;
GO

/* Allocare una variabile del cursore. */
DECLARE @CrsrVar CURSOR;

/* Eseguire la prima procedura creata per riempire la variabile. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT;

/* Utilizzare la variabile per recuperare le righe dal cursore. */
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.