[轉貼]SQL SERVER–cursor 簡單範例

http://pgtalk.blogspot.com/2007/11/sql-server-cursor.html


USE [testDB]
GO
/****** 物件: StoredProcedure [dbo].[pavo_CalculateRecordSum] 指令碼日期: 11/15/2007 11:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_cursor_Test]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
DECLARE Cur Cursor FOR
SELECT a,b,c
FROM Table_A
–開啟cursor
Open Cur

–定義cursor裡要取出的物件
DECLARE @a int,@b nvarchar(50), @c DATETIME

–進入cursor
Fetch NEXT FROM Cur INTO @a, @b,@c
print @@FETCH_STATUS;
While (@@FETCH_STATUS <> -1)
BEGIN
–在此加入LOOP中的內容

————————

–再到下個指標去
Fetch NEXT FROM Cur INTO @a, @b,@c
END

–關閉cursor
CLOSE Cur
–釋放cursor
DEALLOCATE Cur

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
–印出錯誤訊息
PRINT ERROR_NUMBER();
PRINT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
–關閉cursor
CLOSE Cur
–釋放cursor
DEALLOCATE Cur
END CATCH;
END