MS SQL.Procedure -Cursor, Fetch

[ MS SQL.Procedure -Cursor, Fetch ]

1. 예제1

DECLARE CUR_COL CURSOR FOR SELECT DISTINCT DeliveryArea FROM DeliveryRqmt ; OPEN CUR_COL; FETCH NEXT FROM CUR_COL INTO @DA WHILE @@FETCH_STATUS = 0 BEGIN ..... SET @CNT = @CNT+1 ---------------------------------------------------------- FETCH NEXT FROM CUR_COL INTO @DA END CLOSE CUR_COL; DEALLOCATE CUR_COL;

2. 예제2

DECLARE @id INT DECLARE @class INT DECLARE @score INT DECLARE @sum INT SET @sum = 0 -- 커서 정의 DECLARE myCursor CURSOR FOR SELECT Id,Class,Score FROM [Scores] ORDER BY LastUpdate -- 커서 Open OPEN myCursor -- 첫 레코드 읽기 FETCH NEXT FROM myCursor INTO @id,@class,@score -- 데이타가 있으면 -- 계속 루핑 WHILE @@FETCH_STATUS = 0 BEGIN -- 데이타 처리 SET @sum = @sum + @score -- 다음 레코드 가져옴 FETCH NEXT FROM myCursor INTO @id,@class,@score END -- 커서 닫기 CLOSE myCursor DEALLOCATE myCursor PRINT @sum