[情報] SQL DECLARE cursor_name CURSOR

[water2924 (Borussia M'gladbach)]

http://technet.microsoft.com/zh-tw/library/ms180169.aspx

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

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

--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 111.251.162.176