cursor通常用來逐筆處理資料使用,下面是一個簡單的範例(MS SQL Server 2008)
- declare @myId int
- declare @myName nvarchar(20)
- declare @myCursor CURSOR
-
- set @myCursor = CURSOR FAST_FORWARD
- FOR
- SELECT ID, NAME FROM Employee
- open @myCursor
- INTO @myId, @myName
- WHILE @@FETCH_STATUS = 0
- BEGIN
-
- --DO SOMETHING
-
- FETCH NEXT FROM @myCursor
- INTO @myId, @myName
-
- END
-
- CLOSE @myCursor
- DEALLOCATE @myCursor
用temp table來模擬cursor的操作模式,簡單的說就是把SELECT ID, NAME FROM Employee的結果存到temp table中,在逐步讀取,所以必須先建立一個temp table如下
- create table #tempEmployee
- (
- ID int,
- NAME nvarchar(20)
- )
然後把資料insert into select 到 #tempEmployee
- insert into #tempEmployee (ID,NAME)
- select ID,NAME
- from Employee
接下來,就是逐步讀取這個temp table #tempEmployee
在MS SQL Server中使用
SET ROWCOUNT 1來控制select時一次只撈出一筆資料,而且每做完一筆就刪掉,直到#tempEmployee沒有資料為止
- declare @countTemp int --用來計算#tempEmployee還剩幾筆資料
-
- --計算#tempEmployee資料數
- select @countTemp = count(*) from #tempEmployee
-
- while(@countTemp > 0)
- begin
- set rowcount 1
- select @myId = ID, @myName = NAME from #tempEmployee
- --To Something
-
- --因為set rowcount 1的關係,所以一次只會刪一筆
- delete from #tempEmployee
-
- --計算還剩幾筆,@countTemp > 0繼續
- select @countTemp = count(*) from #tempEmployee
- end
-
- --#刪除 tempEmployee
- drop table #tempEmployee
-
- --把select的預設比數恢復正常
- set rowcount 0
如此一般,完整的程式碼如下
- declare @myId int
- declare @myName nvarchar(20)
-
- create table #tempEmployee
- (
- ID int,
- NAME nvarchar(20)
- )
-
- declare @countTemp int --用來計算#tempEmployee還剩幾筆資料
-
- --計算#tempEmployee資料數
- select @countTemp = count(*) from #tempEmployee
-
- while(@countTemp > 0)
- begin
- set rowcount 1
- select @myId = ID, @myName = NAME from #tempEmployee
- --To Something
-
- --因為set rowcount 1的關係,所以一次只會刪一筆
- delete from #tempEmployee
-
- --計算還剩幾筆,@countTemp > 0繼續
- select @countTemp = count(*) from #tempEmployee
- end
-
- --#刪除 tempEmployee
- drop table #tempEmployee
參考資料/來源:
遜砲賴的爆肝筆記-stored procedure中不使用cursor逐步讀取資料列的方法