[程式] stored procedure中不使用cursor逐步讀取資料列的方法

從我第一份工作開始

公司的前輩就跟我說可以的話盡量不要用cursor

當時，我正在用strored procedure寫一份A3大小非常複雜的報表

似乎是遠傳的案子，小菜鳥第一支sp就複雜萬分

從此我對sp的印象就是：用程式很難做到的複雜事就交給stored procedure吧！！

後來證明這句話只對一半，用程式很難做到的不一定是複雜的事.....

資料庫效能也是一個大問題

沒錯！！就是效能，回到第一句話，前輩說用cursor會影響效能

可以的話盡量不要用cursor

還問前輩 "不用cursor的話為什麼要用stored procedure寫？"

可能是因為這位前輩只有大我一歲，所以才會這麼沒大沒小吧

最後在第一家公司還是沒有得到答案

後來進第二家公司之後，DBA還是說盡量不用使用cursor

雖然我忘記他講的原因是什麼了

但是他教了我一招，可以做到cursor做到的事並且不影響資料庫效能

用Temp Table取代Cursor

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

    --To 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