[SQL Server] CURSOR

CURSOR

• 결과 집합을 한 번에 한 행씩 순차적으로 처리하는 구조

예시

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

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;

---

DECLARE CURSOR

• 스크롤 동작, 커서가 작동하는 결과 집합을 구축하는 데 사용되는 쿼리 등 Transact-SQL 서버 커서의 특성을 정의
• 아래 2가지 형식을 함께 사용할 수 없음
• CURSOR 키워드 앞에 'SCROLL' 또는 'INSENSITIVE'를 지정하면 CURSOR 및 FOR <select_statement> 사이에 어떤 키워드도 사용할 수 없으며, 반대의 경우도 동일
• Transact-SQL 구문 사용 시, '[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]'를 지정하지 않았을 때,
  1. 어떠한 이유로, SELECT문이 업데이트를 지원하지 않으면, 'READ_ONLY'가 기본값
  2. STATIC 및 FAST_FORWARD는 'READ_ONLY'가 기본값
  3. DYNAMIC 및 KEYSET은 'OPTIMISTIC'이 기본값
• CURSOR를 선언한 후, 아래의 시스템 저장 프로시저를 사용하여 CURSOR의 특징을 확인할 수 있음

System Stored Procedure	Description
sp_cursor_list	현재 연결에서 볼 수 있는 커서 목록과 그 특성을 반환
sp_describe_cursor	정방향 전용커서, 스크롤 커서 등의 커서 특성을 설명
sp_describe_cursor_columns	커서 결과 집합에서 열의 특성을 설명
sp_describe_cursor_tables	커서에 의해 액세스되는 기본 테이블을 설명

-- ISO Syntax  
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR   
     FOR select_statement   
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]  
[;]  
-- Transact-SQL Extended Syntax  
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 ] ] ]  
[;]

 

---

OPEN

• 결과 집합을 채움
• 최대 행 크기를 초과하는 행이 있으면 OPEN이 실패
• INSENSITIVE 또는 STATIC을 사용하여 커서를 선언한 경우 임시 테이블을 만들어 결과 집합을 보관
• KEYSET을 사용하여 커서를 선언한 경우 임시 테이블을 만들어 키 집합을 보관
• 임시 테이블은 TEMPDB에 저장

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

---

FETCH

• 결과 집합에서 행을 반환
• 'DECLARE CURSOR'에서 SCROLL 옵션이 지정되지 않은 경우, NEXT만 지원하나, SCROLL 옵션이 지정된 경우, 모든 옵션을 지원
• DECLARE CURSOR에서 Transact-SQL 구문 사용 시,
  1. 'FORWAR_ONLY' 또는 'FAST_FORWARD'를 지정하면 NEXT만 지원
  2. 'DYNAMIC', 'FORWAR_ONLY' 또는 'FAST_FORWARD'를 지정하지 않고 'KEYSET', 'STATIC' 또는 'SCROLL' 중 하나를 지정하는 경우 모든 옵션을 지원
  3. DYNAMIC SCROLL 커서는 'ABSOLUTE'를 제외한 모든 옵션을 지원

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

---

CLOSE

• 커서와 연결된 현재 결과 집합을 해제
• OPEN된 커서에서만 실행되어야 하며, 선언만 되었거나 이미 닫혀 있는 커서에는 사용할 수 없음
• 커서를 다시 OPEN할 때까지 인출과 위치 지정 업데이트는 허용되지 않음

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

---

DEALLOCATE

• 커서에서 사용된 리소스를 해제
• 인출의 격리를 보호하는데 사용되는 스크롤 잠금을 해제

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

---

참조 URL : https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/cursors-transact-sql?view=sql-server-ver15