[SQL Server]游標示例
IF(OBJECT_ID('tempdb..#Books') IS NOT NULL)
BEGIN
DROP TABLE #Books;
END
CREATE TABLE #Books
(
BookName NVARCHAR(20),
BookCode NVARCHAR(20)
)
INSERT INTO #Books( BookName, BookCode ) VALUES(N'C# 本質論','JC556874');
INSERT INTO #Books( BookName, BookCode ) VALUES(N'JS 本質論','JS556874');
--申明一個游標
DECLARE MyCursor CURSOR FOR
SELECT TOP 5 BookName,BookCode FROM #Books;
--打開一個游標
OPEN MyCursor;
DECLARE @BookName nvarchar(20);
DECLARE @BookCode nvarchar(20);
--循環一個游標
FETCH NEXT FROM MyCursor INTO @BookName,@BookCode;
WHILE(@@FETCH_STATUS = 0)
BEGIN
PRINT('Book Name: '+@BookName);
FETCH NEXT FROM MyCursor INTO @BookName,@BookCode;
END
--關閉游標
CLOSE MyCursor;
--釋放資源
DEALLOCATE MyCursor;
-------------------------------------------------------------------------------------------------------------------------------------
@@FETCH_STATUS 是一個全局變量,其值有以下三種 :
1) 0 FETCH 語句成功
2) -1 FETCH 語句失敗或此行不在結果集中
3) -2 被提取的行不存在

浙公網安備 33010602011771號