2015年7月14日 星期二

[程式] 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

4 則留言:

  1. 原本用 stored procedure產生一個Table,想跑迴圈,但因為Cursor的關係想放棄了,最後搜尋看到樓主的很棒解法,跟您說聲謝了。

    By Josh

    回覆刪除
  2. 如果有迴圈有Insert...需求的話,另外也可以參閱這篇文章,大意是說用Top 1 的模式來處理
    https://msdn.microsoft.com/zh-tw/library/ms188774.aspx

    回覆刪除