每日一SQL-Cursor

2010-06-18

61260
0
SQL語法

每日一SQL-Cursor

今天又遇到個必須用SQL解決的問題。

有個已經上線的資料庫，某個資料表中已經塞了好幾百筆資料，但突然需要多加一個排序編號的欄位

而且必須依不同的類別從1開始排，那要怎麼辦呢?

一個一個手動修改嗎?不不不，這不是程式設計師該做的事。

記得上次寫過一個重新編號的SQL指令

如果我把它改寫成


as
(

select *
, row_number() over(order by [ID]) as rnk
from [My].[dbo].[Cursor]
where 類別='某個類別'

)
update 暫時table
set [排序] =  rnk

應該就能達到我要的需求，但是

如果一個一個類別分別下where，代表如果我有5個類別，我就要做五次動作

那如果我有100個類別，不就要做一百次!?

突然想到不久前同事跟我說Cursor的用處，應該可以派上用場。於是拿了一本SQL2005開始K..

模擬一下資料表及資料

Cursor可以看成是一個存資料集的物件，並且可以將資料一筆一筆取出來處理。

由於Cursor的標準語法宣告，實在有點複雜，所以這邊就不列出來啦。

我用我自己的想法去記，

1.利用Cursor將所要的條件資料存成一個資料集

2.建立Cursor跟Table的關聯

3.將資料塞進一個定義的變數中

4.做類似迴圈的動作，並在迴圈中做類似程式的next()方法

5.關閉Cursor與Table的關連

6.將Cursor移除

步驟很多，不過其實寫起來也不難，而且跟ADO.NET撈資料的方法有種似曾相識的感覺

直接看例子吧，先用Cursor做一個select，看結果是不是我想要的


FOR
SELECT distinct 類別 FROM dbo.[Cursor]   --條件

OPEN 按類別編號                 --2.開啟跟Table關連

DECLARE @類別變數 nvarchar(50)   --3.定義一個變數

FETCH NEXT FROM 按類別編號
into @類別變數					--將值塞進變數

WHILE(@@FETCH_STATUS=0)			--4.跑迴圈(判斷有沒有值)
BEGIN


SELECT *, row_number() over(order by ID) as rnk
FROM dbo.[Cursor]
WHERE 類別=@類別變數			--將變數拿來使用

FETCH NEXT from 按類別編號
into @類別變數					--並使用next指到下一個資料
								--如果有值就繼續跑迴圈
END

CLOSE 按類別編號				--5.關閉與Table關連
DEALLOCATE 按類別編號			--6.移除Cursor

出來結果就像這樣，有依照類別，從1開始編號

知道結果正確之後，就可以用上次重新編號的CTE的語法Update資料了。


FOR
SELECT distinct 類別 FROM dbo.[Cursor]   --條件

OPEN 按類別編號                 --2.開啟跟Table關連

DECLARE @類別變數 nvarchar(50)   --3.定義一個變數

FETCH NEXT FROM 按類別編號
into @類別變數					--將值塞進變數

WHILE(@@FETCH_STATUS=0)			--4.跑迴圈(判斷有沒有值)
BEGIN

WITH 暫時table
AS
(
select *, row_number() over(order by ID) as rnk
from dbo.[Cursor]
where 類別=@類別變數			--將變數拿來使用
)
update 暫時table
set   排序 = rnk			

FETCH NEXT from 按類別編號
into @類別變數					--並使用next指到下一個資料
								--如果有值就繼續跑迴圈
END

CLOSE 按類別編號				--5.關閉與Table關連
DEALLOCATE 按類別編號			--6.移除Cursor

執行成功!來看結果

成功!

雖然第一次使用Cursor花了點時間去研究

但如果因為不好懂就不學，只是用笨方法手動一筆一筆更新

也許資料筆數不多的時候，還是可以完成，

但哪天遇到幾萬筆資料的時候，可就沒辦法手動囉。

所以有機會的話，就學起來吧。

回首頁