cursor Lab

cursor(資料指標)
cursor本質上算是table一種。
能夠逐筆取出資料單獨處理，這是select辦不到的事情。


主要用於SQL批次、預存程序、自訂函數、觸發程序中
常常與迴圈(while)配合



use Northwind
GO

1. 定義 Cursor (請留意 Keyset 選項)
DECLARE CursorLab CURSOR
Keyset//若沒打這行則預設為Dynamic，則cursor內容會隨時動態更新
FOR
select * from Customers
  order by CustomerID


Keyset:只將select出的資料中，具有唯一性的鍵值欄存入"暫時資料表"中(這個暫時資料表名稱為Keyset)，其他欄位則動態由原始資料讀取。當原始資料被更改時，若未更改到健值欄，則可透過cursor讀取到最新資料；但是若鍵值欄被更改或紀錄被刪除了，那麼經由cursor去讀取該筆記錄時，即會發生@@FETCH_STATUS為-2的錯誤(但若該項更改或刪除是經由cursor去進行的，則仍可正常讀取不會有誤)。

2.開啟 Cursor
OPEN CursorLab

3.請檢視第一筆的內容
FETCH NEXT FROM CursorLab

只秀出一筆資料
ContactName欄位值為Maria Anders

4.在另一個新增查詢視窗(第二視窗) 修改資料
update Customers set ContactName = 'Maria Anders2' where CustomerID = 'ALFKI'

原始資料被更改，未更改到健值欄


5.在第一視窗返回前一筆(原先的第一筆), 資料內容是什麼?
FETCH PRIOR FROM CursorLab

可透過cursor讀取到最新資料
欄位值改成了Maria Ander2

6.關閉  刪除cursor
CLOSE CursorLab
deallocate CursorLab


---------------cursor 在while loop的應用-----------------

use Northwind
go

declare LabCursor cursor keyset for
  select ProductID, ProductName, UnitsInStock
    from Products
    order by ProductID
   
open LabCursor

print @@FETCH_STATUS//此時值為-1

fetch next from LabCursor//所以進行while前要fetch next一次

while @@FETCH_STATUS = 0
begin
  fetch next from LabCursor
end
print @@fetch_status//while完整跑完時，fetch status為-1
close LabCursor
deallocate LabCursor

-------------cursor抓取的資料丟給變數--------
declare LabCursor cursor keyset for
  select ProductID, ProductName, UnitsInStock
    from Products
    order by ProductID
declare @ProductID int
declare @ProductName nvarchar(40)
declare @UnitsInStock int
open LabCursor
fetch next from LabCursor into @ProductID, @ProductName, @UnitsInStock

while @@FETCH_STATUS = 0
begin
  fetch next from LabCursor into @ProductID, @ProductName, @UnitsInStock
將fetch抓出的紀錄，丟給變數
print @Productid
print @productname
print @unitsinstock
end
close LabCursor
deallocate LabCursor


---利用cursor將資料丟到暫存table，-----------------------

use Northwind
go
create table #Report
(
  ProductID int,
  ProductName nvarchar(40),
  UnitsInStock int,
  cUnitsInStock int
)
go
declare LabCursor cursor keyset for
  select ProductID, ProductName, UnitsInStock
    from Products
    order by ProductID
declare @ProductID int
declare @ProductName nvarchar(40)
declare @UnitsInStock int
declare @cUnitsInStock int = 0
open LabCursor
fetch next from LabCursor into @ProductID, @ProductName, @UnitsInStock
while @@FETCH_STATUS = 0
begin
  set @cUnitsInStock = @cUnitsInStock + @UnitsInStock//算商品總庫存量
  insert into #Report values
    (@ProductID, @ProductName, @UnitsInStock, @cUnitsInStock)
  fetch next from LabCursor into @ProductID, @ProductName, @UnitsInStock
end
select * from #Report
close LabCursor
deallocate LabCursor
drop table #Report