• 아티클

Transact-SQL 커서 이름 범위

Microsoft SQL Server에서는 커서 이름 범위를 정의할 수 있도록 DECLARE CURSOR 문에서 GLOBAL과 LOCAL 키워드를 지원합니다. GLOBAL은 커서 이름이 연결에 대해 전역적이라는 것을 지정하고 LOCAL은 커서 이름이 DECLARE CURSOR 문을 포함하는 저장 프로시저, 트리거 또는 일괄 처리에 대해 지역적이라는 것을 지정합니다.

Microsoft SQL Server 7.0 이전 버전에서는 Transact-SQL 커서 이름이 연결에 대해 전역적이었습니다. 커서를 만드는 한 저장 프로시저를 실행한 다음 해당 커서에서 행을 인출하는 다른 저장 프로시저를 호출할 수 있었습니다.

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

로컬 커서는 저장 프로시저와 트리거에서 구현된 커서에 대해 중요한 보호 기능을 제공합니다. 전역 커서는 선언된 저장 프로시저나 트리거 밖에서 참조할 수 있습니다. 따라서 저장 프로시저나 트리거 밖의 문에 의해 실수로 변경될 수 있습니다. 로컬 커서는 커서 출력 매개 변수와 같은 호출자로 일부러 다시 전달하지 않는 한 저장 프로시저 밖에서 참조할 수 없으므로 전역 커서에 비해 훨씬 안전합니다.

전역 커서는 저장 프로시저나 트리거 밖에서 참조할 수 있으므로 다른 문에 의도하지 않은 부정적 영향을 줄 수 있습니다. 예를 들어 xyz라는 전역 커서를 만들고 완료 시 커서가 열려 있는 저장 프로시저가 있다고 가정합니다. 이 저장 프로시저가 완료된 다음 xyz라는 이름의 다른 전역 커서를 선언하려고 하면 이름이 중복되었다는 오류가 발생하며 작업이 수행되지 않습니다.

전역 커서와 로컬 커서는 별도의 네임스페이스를 갖고 있으므로 전역 커서와 로컬 커서의 이름이 동일할 수 있습니다. 또한 커서 이름 매개 변수를 사용하는 Transact-SQL 문은 GLOBAL 키워드를 사용하여 이름 범위를 지정할 수 있습니다. GLOBAL을 지정하지 않았는데 커서 이름 매개 변수에 지정한 이름의 로컬 커서와 전역 커서가 있으면 로컬 커서가 참조됩니다.

ALTER DATABASE 문에서 CURSOR_DEFAULT 데이터베이스 옵션을 설정하면 LOCAL과 GLOBAL 모두 지정되지 않은 경우 DECLARE CURSOR 문에 사용되는 기본값을 제어할 수 있습니다. 이 데이터베이스 옵션의 현재 값은 sys.databases 카탈로그 뷰에 저장됩니다. sys.databases 카탈로그 뷰의 local_cursor_default 열 값이 true이면 Transact-SQL 커서가 기본적으로 로컬 커서가 됩니다. 이 옵션이 false이면 Transact-SQL 커서가 기본적으로 전역 커서가 됩니다. SQL Server에서는 이전 버전의 SQL Server 동작과 일치하도록 데이터베이스 옵션 자체가 기본적으로 FALSE(GLOBAL)로 설정됩니다.

로컬 커서를 선언하고 여는 저장 프로시저는 호출하는 저장 프로시저, 트리거 또는 일괄 처리에서 사용할 수 있도록 커서를 밖으로 전달합니다. 이 작업은 새 CURSOR VARYING 데이터 형식으로 정의된 OUTPUT 매개 변수를 사용하여 수행합니다. 커서 변수는 OUTPUT 매개 변수로만 사용할 수 있으며 INPUT 매개 변수로는 사용할 수 없습니다. 저장 프로시저가 완료될 때는 OUTPUT 매개 변수에서 다시 전달할 커서가 열려 있어야 합니다. 지역 변수를 새 CURSOR 데이터 형식으로 선언하여 로컬 커서에 대한 참조를 포함할 수도 있습니다.

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

데이터베이스 API는 저장 프로시저에서 커서 출력 매개 변수를 지원하지 않습니다. 커서 출력 매개 변수를 포함하는 저장 프로시저는 데이터베이스 API 함수에서 직접 실행할 수 없습니다. 이러한 저장 프로시저는 다른 저장 프로시저, 트리거 또는 Transact-SQL 일괄 처리나 스크립트를 통해서만 실행할 수 있습니다.

GLOBAL 커서는 명시적으로 할당이 해제되거나 연결이 닫힐 때까지 사용할 수 있습니다. LOCAL 커서를 만든 저장 프로시저, 트리거 또는 일괄 처리가 종료되면 매개 변수로 다시 전달되지 않는 한 커서가 암시적으로 할당이 해제됩니다. 그런 다음 프로시저를 호출한 코드에서 커서를 참조하는 매개 변수 또는 변수가 범위를 벗어날 때 LOCAL 커서가 암시적으로 할당이 해제됩니다.