--定義一個cursor 來自指定資料表內容
DECLARE MY_CURSOR Cursor FOR
SELECT cy_closeRate,Currencycode From [Currency_rate]
WHERE cy_closeTime > DATEADD(Day,-2,getdate())
--開啟cursor
Open MY_CURSOR
--定義cursor裡要取出的物件
DECLARE @closeRate FLOAT, @cyID VARCHAR(50)
--進入cursor
Fetch NEXT FROM MY_CURSOR INTO @closeRate, @cyID
--當陳述式失敗，或資料列超出結果集時停止迴圈
While (@@FETCH_STATUS <> -1)
BEGIN
--偵錯或加入要應用的程式碼
--PRINT CONVERT(VARCHAR(100),@closeRate) +'--'+ @cyID
--再到下個指標去
FETCH NEXT FROM MY_CURSOR INTO @closeRate, @cyID
END
--關閉cursor
CLOSE MY_CURSOR
--釋放cursor
DEALLOCATE MY_CURSOR
GO
2010年7月22日 星期四
MSSQL Cursor 範例
訂閱：
張貼留言 (Atom)
另一個Cursor範例
回覆刪除DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
cursor 的寫法 要看場合用
回覆刪除今天在藍色小舖看到神來之筆的回文
觸發了我的靈感, 謝謝!
/*將總合讀到temp db*/
select * into #priceTmp
from
(select area,emp,sum(price) as total_price
from tableA
group by area,emp) as a;
/*利用cursor insert到tableB*/
DECLARE @area varchar(50), @emp varchar(50),@price money
DECLARE priceCursor CURSOR FOR
SELECT* FROM #priceTmp
OPEN priceCursor
FETCH NEXT FROM priceCursor into @area,@emp,@price
WHILE @@FETCH_STATUS = 0
BEGIN
insert tableB values(@area,@emp,@price)
FETCH NEXT FROM priceCursor into @area,@emp,@price
END
CLOSE priceCursor
DEALLOCATE priceCursor;
drop table #priceTmp;