DECLARE CURSOR(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

스크롤 동작, 커서가 작동하는 결과 세트를 구축하는 데 사용되는 쿼리 등 Transact-SQL 서버 커서의 특성을 정의합니다. DECLARE CURSOR는 ISO 표준 기반의 구문과 Transact-SQL 확장 세트를 사용하는 구문을 모두 허용합니다.

Transact-SQL 구문 표기 규칙

구문

ISO 구문:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Transact-SQL 확장 구문:

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

인수

cursor_name

정의된 Transact-SQL 서버 커서의 이름입니다. cursor_name은 식별자에 대한 규칙을 따라야 합니다.

INSENSITIVE

커서에서 사용할 데이터를 임시로 복사해 주는 커서를 정의합니다. 커서에 대한 모든 요청은 다음의 임시 테이블에서 tempdb응답됩니다. 따라서 기본 테이블에 대한 수정 내용은 이 커서에 대한 페치에서 반환된 데이터에 반영되지 않으며 이 커서는 수정을 허용하지 않습니다. ISO 구문을 사용할 때 INSENSITIVE를 생략하면 사용자가 기본 테이블에 커밋한 삭제 및 업데이트 내용이 후속 인출에 반영됩니다.

SCROLL

모든 인출 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)을 사용할 수 있도록 지정합니다. ISO DECLARE CURSORNEXT 에 지정되지 않은 경우 SCROLL 유일하게 페치 옵션이 지원됩니다. SCROLL 를 지정할 FAST_FORWARD 수도 없습니다. 지정하지 않으면 SCROLL fetch 옵션 NEXT 만 사용할 수 있으며 커서가 됩니다 FORWARD_ONLY.

select_statement

커서의 결과 집합을 정의하는 표준 SELECT 문입니다. 키워드(keyword) FOR BROWSEINTO 커서 선언의 select_statement 내에서 허용되지 않습니다.

SQL Server는 select_statement의 절이 요청된 커서 유형의 기능과 충돌할 경우 커서를 다른 유형으로 암시적으로 변환합니다.

READ ONLY

이 커서를 통한 업데이트를 방지합니다. 또는 문의 절에서 UPDATEDELETE 커서를 WHERE CURRENT OF 참조할 수 없습니다. 이 옵션은 업데이트할 커서의 기본 기능을 무시합니다.

UPDATE [ OF column_name [ ,...n ] ]

커서 내에서 업데이트할 수 있는 열을 정의합니다. 지정된 경우 OF <column_name> [, <... n> ] 나열된 열만 수정할 수 있습니다. 열 목록 없이 UPDATE를 지정하면 모든 열을 업데이트할 수 있습니다.

cursor_name

정의된 Transact-SQL 서버 커서의 이름입니다. cursor_name은 식별자에 대한 규칙을 따라야 합니다.

LOCAL

커서 범위를 커서가 생성된 일괄 처리, 저장 프로시저, 트리거에 대해 로컬로 지정합니다. 커서 이름은 지정된 범위 내에서만 유효합니다. 일괄 처리, 저장 프로시저, 트리거의 로컬 커서 변수 또는 저장 프로시저의 OUTPUT 매개 변수에서 커서를 참조할 수 있습니다. OUTPUT 매개 변수는 호출한 일괄 처리, 저장 프로시저, 트리거로 로컬 커서를 다시 전달하는 데 사용되며 저장 프로시저가 종료된 후 커서 변수에 매개 변수를 할당하여 커서를 참조할 수 있습니다. 커서가 OUTPUT 매개 변수에서 다시 전달되지 않은 경우 일괄 처리나 저장 프로시저, 트리거가 종료되면 커서가 암시적으로 할당 취소됩니다. 매개 변수에 OUTPUT 다시 전달된 경우 참조하는 마지막 변수가 할당 취소되거나 범위를 벗어날 때 커서의 할당이 취소됩니다.

GLOBAL

커서 범위를 연결에 대해 전역으로 지정합니다. 연결되어 실행하는 모든 저장 프로시저 또는 일괄 처리에서 커서 이름을 참조할 수 있습니다. 커서는 연결 해제 시에만 암시적으로 할당이 취소됩니다.

FORWARD_ONLY

커서가 앞으로만 이동하고 첫 번째 행에서 마지막 행까지 스크롤할 수 있도록 지정합니다. 유일하게 지원되는 인출 옵션은 FETCH NEXT입니다. 현재 사용자(또는 다른 사용자가 커밋한)가 결과 집합의 행에 영향을 주는 모든 삽입, 업데이트 및 삭제 문은 행을 가져올 때 표시됩니다. 그러나 커서를 뒤로 스크롤할 수 없으므로 행을 가져온 후 데이터베이스의 행에 대한 변경 내용은 커서를 통해 표시되지 않습니다. 정방향 전용 커서는 기본적으로 동적이며, 이는 현재 행이 처리될 때 모든 변경 내용이 감지됨을 의미합니다. 이렇게 하면 커서가 더 빨리 열리고 결과 집합이 기본 테이블에 대한 업데이트를 표시하도록 설정할 수 있습니다. 정방향 전용 커서는 뒤로 스크롤을 지원하지 않지만 애플리케이션은 커서를 닫고 다시 열어 결과 집합의 시작 부분으로 돌아갈 수 있습니다.

STATIC, KEYSET 또는 DYNAMIC 키워드를 사용하지 않고 FORWARD_ONLY를 지정하면 커서는 동적 커서로 작동합니다. FORWARD_ONLY 지정 DYNAMICSTATICKEYSET되거나 SCROLL 지정 FORWARD_ONLY 되지 않은 경우 키워드(keyword) 또는 지정되지 않는 한 기본값입니다. STATIC, KEYSET 및 DYNAMIC 커서는 기본적으로 SCROLL입니다. ODBC, ADO 등의 데이터베이스 API와는 달리, FORWARD_ONLY는 STATIC, KEYSET 및 DYNAMIC Transact-SQL 커서에서 지원됩니다.

STATIC

커서가 처음 열릴 때와 같은 결과 집합을 항상 표시하고 커서가 사용할 데이터의 임시 복사본을 만들도록 지정합니다. 커서에 대한 모든 요청은 다음의 임시 테이블에서 tempdb응답됩니다. 따라서 기본 테이블에 대한 삽입, 업데이트 및 삭제는 이 커서에 대한 페치에서 반환된 데이터에 반영되지 않으며, 이 커서는 커서를 연 후 결과 집합의 멤버 자격, 순서 또는 값에 대한 변경 내용을 검색하지 않습니다. 정적 커서는 자체 업데이트, 삭제 및 삽입을 검색할 수 있지만 그렇게 할 필요는 없습니다.

예를 들어 정적 커서가 행을 페치하고 다른 애플리케이션이 해당 행을 업데이트한다고 가정합니다. 애플리케이션이 정적 커서에서 행을 다시 페치하면 다른 애플리케이션에서 변경한 내용에도 불구하고 표시되는 값은 변경되지 않습니다. 모든 유형의 스크롤이 지원됩니다.

KEYSET

커서가 열릴 때 커서에 있는 행의 멤버 자격과 순서가 고정되도록 지정합니다. 행을 고유하게 식별하는 키 집합은 키 집합이라고 하는 테이블에 tempdb 기본 제공됩니다. 이 커서는 정적 커서와 동적 커서 간에 변경 내용을 검색하는 기능을 제공합니다. 정적 커서와 마찬가지로 결과 집합의 멤버 자격 및 순서에 대한 변경 내용을 항상 검색하지는 않습니다. 동적 커서처럼 결과 집합의 행 값에 대한 변경 내용을 검색합니다.

키 집합 커서는 키 집합이라는 고유 식별자(키) 집합으로 제어됩니다. 키는 결과 집합에서 행을 고유하게 식별하는 열 집합으로 작성됩니다. 키 집합은 쿼리 문에서 반환된 모든 행의 키 값 집합입니다. 키 집합 커서를 사용하여 커서의 각 행에 대해 키를 빌드 및 저장하고 클라이언트 워크스테이션 또는 서버에 저장합니다. 각 행에 액세스하면 저장된 키를 사용하여 데이터 원본에서 현재 데이터 값을 페치합니다. 키 집합 커서에서 키 집합이 완전히 채워지면 결과 집합 멤버 자격이 고정됩니다. 그 후 멤버 자격에 영향을 주는 추가 또는 업데이트는 다시 열릴 때까지 결과 집합의 일부가 아닙니다.

사용자가 결과 집합을 스크롤할 때 데이터 값(키 집합 소유자 또는 다른 프로세스에 의해 생성됨)에 대한 변경 내용이 나타납니다.

• 행이 삭제되면 삭제된 행이 결과 집합의 -2 간격으로 표시되므로 행 @@FETCH_STATUS 을 가져오려는 시도가 반환됩니다. 행의 키는 키 집합에 있지만 행은 결과 집합에 더 이상 존재하지 않습니다.

• 커서 외부에서 수행한 삽입은(다른 프로세스에 의해)은 커서를 닫았다가 다시 열 때만 볼 수 있습니다. 커서 내에서 수행한 삽입은 결과 집합의 끝에 표시됩니다.

• 커서 외부에서 키 값을 업데이트하는 것은 이전 행을 삭제하고 새 행을 삽입하는 것과 비슷합니다. 새 값이 있는 행이 표시되지 않으며 이전 값이 있는 행을 가져오려고 시도하면 다음 값이 @@FETCH_STATUS-2반환됩니다. WHERE CURRENT OF 절을 지정하여 커서를 통해 업데이트를 수행한 경우에는 새 값을 볼 수 있습니다.

DYNAMIC

커서 내부 또는 커서 외부의 다른 사용자가 변경했는지에 관계없이 커서를 스크롤하고 새 코드를 가져올 때 결과 집합의 행에 대해 수행된 모든 데이터 변경 내용을 반영하는 커서를 정의합니다. 따라서 모든 사용자가 실행한 모든 삽입, 업데이트 및 삭제 문은 커서를 통해 볼 수 있습니다. 따라서 인출할 때마다 행의 데이터 값, 순서 및 멤버 자격이 변경될 수 있습니다. ABSOLUTE 인출 옵션은 동적 커서에서 지원되지 않습니다. 커서 외부에서 만들어진 업데이트 커밋될 때까지 표시되지 않습니다(커서 트랜잭션 격리 수준이 설정UNCOMMITTED되지 않은 경우).

예를 들어 동적 커서가 두 개의 행을 가져오고 다른 애플리케이션이 해당 행 중 하나를 업데이트하고 다른 행을 삭제한다고 가정합니다. 동적 커서가 해당 행을 가져오면 삭제된 행을 찾을 수 없지만 업데이트된 행에 대한 새 값이 표시됩니다.

FAST_FORWARD

성능 최적화가 설정된 FORWARD_ONLY, READ_ONLY 커서를 지정합니다. FAST_FORWARD 를 지정할 SCROLLFOR_UPDATE 수 없습니다. 이 유형의 커서는 커서 내부에서 데이터를 수정할 수 없습니다.

READ_ONLY

이 커서를 통한 업데이트를 방지합니다. 또는 문의 절에서 UPDATEDELETE 커서를 WHERE CURRENT OF 참조할 수 없습니다. 이 옵션은 업데이트할 커서의 기본 기능을 무시합니다.

SCROLL_LOCKS

커서를 통해 현재 위치 업데이트 또는 삭제가 반드시 실행되도록 지정합니다. SQL Server는 커서에서 읽어 들이는 행을 잠가 나중에 수정할 때 사용할 수 있도록 합니다. SCROLL_LOCKS 를 지정할 FAST_FORWARDSTATIC 수 없습니다.

OPTIMISTIC

커서를 읽은 후 행이 업데이트된 경우 커서를 통해 수행된 위치가 지정된 업데이트 또는 삭제가 성공하지 않도록 지정합니다. SQL Server는 커서를 읽을 때 행을 잠그지 않습니다. 대신 timestamp 열 값을 비교하거나 테이블에 timestamp 열이 없을 경우 체크섬 값을 비교하여 커서로 읽은 행이 수정되었는지 여부를 확인합니다. 행이 수정된 경우 지정된 위치에서 업데이트나 삭제가 실행되지 않습니다. OPTIMISTIC 를 지정할 FAST_FORWARD 수도 없습니다.

TYPE_WARNING

요청한 커서 형식이 다른 형식으로 암시적으로 변환된 경우 클라이언트에게 경고 메시지를 보내도록 지정합니다.

select_statement

커서의 결과 집합을 정의하는 표준 SELECT 문입니다. 커서 선언의 select_statement COMPUTE내에서 키워드(keyword) , COMPUTE BYFOR BROWSE및 INTO 허용되지 않습니다.

SQL Server는 select_statement의 절이 요청된 커서 유형의 기능과 충돌할 경우 커서를 다른 유형으로 암시적으로 변환합니다.

FOR UPDATE [ of column_name [ ,...n ] ]

커서 내에서 업데이트할 수 있는 열을 정의합니다. OF <column_name> [, <... n>]이 제공된 경우 나열된 열만 수정이 가능합니다. READ_ONLY 동시성 옵션이 지정되지 않은 경우 열 목록 없이 UPDATE를 지정하면 모든 열을 업데이트할 수 있습니다.

설명

DECLARE CURSOR는 스크롤 동작, 커서가 작동하는 결과 세트를 구축하는 데 사용되는 쿼리 등 Transact-SQL 서버 커서의 특성을 정의합니다. OPEN 문은 결과 세트를 채우고 FETCH는 결과 세트에서 행을 반환합니다. CLOSE 문은 커서와 연결된 현재 결과 세트를 해제합니다. DEALLOCATE 문은 커서에서 사용된 리소스를 해제합니다.

DECLARE CURSOR 문의 첫 번째 형식은 커서 동작을 선언하기 위해 ISO 구문을 사용합니다. DECLARE CURSOR 문의 두 번째 형식은 ODBC 또는 ADO의 데이터베이스 API 커서 함수에서 사용된 것과 동일한 커서 형식을 사용하여 커서를 정의할 수 있는 Transact-SQL 확장을 사용합니다.

두 가지 양식을 혼합할 수 없습니다. 키워드(keyword) 앞에 CURSOR 키워드(keyword) 지정 SCROLLINSENSITIVE 하는 경우 키워드(keyword) 간에 CURSORFOR <select_statement> 키워드(keyword) 사용할 수 없습니다. 키워드(keyword) 간에 CURSORFOR <select_statement> 키워드(keyword) 지정하는 경우 키워드(keyword) 이전이나 INSENSITIVECURSOR 지정할 SCROLL 수 없습니다.

DECLARE CURSOR using Transact-SQL 구문에서 지정READ_ONLYOPTIMISTICSCROLL_LOCKS하지 않거나 기본값은 다음과 같습니다.

• SELECT 문이 업데이트를 지원하지 않는 경우(권한 부족, 업데이트를 지원하지 않는 원격 테이블 액세스 등) 커서는 다음과 같습니다READ_ONLY.

• STATIC 및 FAST_FORWARD 커서는 기본적으로 READ_ONLY입니다.

• DYNAMIC 및 KEYSET 커서는 기본적으로 OPTIMISTIC입니다.

커서 이름은 다른 Transact-SQL 문에서만 참조할 수 있습니다. 데이터베이스 API 함수에서는 참조할 수 없습니다. 예를 들어 커서를 선언한 후에는 OLE DB, ODBC 또는 ADO 함수 또는 메서드에서 커서 이름을 참조할 수 없습니다. 커서 행은 API의 페치 함수 또는 메서드를 사용하여 가져올 수 없습니다. 행은 Transact-SQL FETCH 문에서만 가져올 수 있습니다.

커서가 선언되면 이러한 시스템 저장 프로시저를 사용하여 커서의 특성을 확인할 수 있습니다.

변수는 커서를 선언하는 select_statement 일부로 사용될 수 있습니다. 커서 변수 값은 커서가 선언된 후에 변경되지 않습니다.

사용 권한

DECLARE CURSOR 권한은 커서에 사용된 보기, 테이블, 열에 대한 SELECT 권한이 있는 모든 사용자에게 기본적으로 부여됩니다.

제한 사항

클러스터형 columnstore 인덱스가 있는 테이블에서는 커서 또는 트리거를 사용할 수 없습니다. 이 제한은 비클러스터형 columnstore 인덱스에는 적용되지 않습니다. 비클러스터형 columnstore 인덱스가 있는 테이블에서는 커서 또는 트리거를 사용할 수 있습니다.

예제

A. 기본 커서 및 구문 사용

다음 커서를 열 때 생성된 결과 집합에는 테이블에 있는 모든 행과 모든 열이 포함됩니다. 이 커서는 업데이트가 가능하며 이 커서에 대해 수행한 인출에는 모든 업데이트와 삭제 내용이 나타납니다. FETCH NEXT 는 옵션이 지정되지 않았기 때문에 사용할 수 있는 SCROLL 유일한 인출입니다.

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. 중첩된 커서를 사용하여 보고서 출력 생성

다음 예에서는 커서를 중첩시켜 복잡한 보고서를 생성하는 방법을 보여 줍니다. 각 공급업체에 대해 내부 커서가 선언됩니다.

SET NOCOUNT ON;

DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),
    @message VARCHAR(80), @product NVARCHAR(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' +
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

관련 콘텐츠

• @@FETCH_STATUS (Transact-SQL)
• CLOSE(Transact-SQL)
• 커서(Transact-SQL)
• DEALLOCATE(Transact-SQL)
• FETCH(Transact-SQL)
• SELECT(Transact-SQL)
• sp_configure(Transact-SQL)