SQL Server-處理結果集的循環機制——游標

1、游標的概念

結果集，結果集就是select查詢之后可能返回多條記錄，返回的所有行數據的集合。這時需要對數據進行處理以每次處理一行或一部分行，通過使用游標來逐條讀取查詢結果集中的記錄。

游標則是處理結果集的一種機制吧，它可以定位到結果集中的某一行，多數據進行讀寫，也可以移動游標定位到你所需要的行中進行操作數據。

一般復雜的存儲過程，都會有游標的出現，他的用處主要有：

允許定位在結果集的特定行。
從結果集的當前位置檢索一行或一部分行。
支持對結果集中當前位置的行進行數據修改，而不是所有行執行相同的操作。
為由其他用戶對顯示在結果集中的數據庫數據所做的更改提供不同級別的可見性支持。
提供腳本、存儲過程和觸發器中用于訪問結果集中的數據的 Transact-SQL 語句
是面向集合的數據庫管理系統和面向行的程序設計之間的橋梁

游標是一種處理數據的方法，主要用于存儲過程，觸發器和 T_SQL腳本中，它們使結果集的內容可用于其它T_SQL語句。在查看或處理結果集中向前或向后瀏覽數據的功能。類似與C語言中的指針，它可以指向結果集中的任意位置，當要對結果集進行逐條單獨處理時，必須聲明一個指向該結果集中的游標變量。

SQL Server 中的數據操作結果都是面向集合的，并沒有一種描述表中單一記錄的表達形式，除非使用WHERE子句限定查詢結果，使用游標可以提供這種功能，并且游標的使用和操作過程更加靈活、高效。

2、游標的優點

SELECT 語句返回的是一個結果集，但有時候應用程序并不總是能對整個結果集進行有效地處理，游標便提供了這樣一種機制，它能從包括多條記錄的結果集中每次提取一條記錄，游標總是與一跳SQL選擇語句相關聯，由結果集和指向特定記錄的游標位置組成。使用游標具有一下優點：

(1).允許程序對由SELECT查詢語句返回的行集中的每一次執行相同或不同的操作，而不是對整個集合執行同一個操作。

(2).提供對基于游標位置中的行進行刪除和更新的能力。

(3).游標作為數據庫管理系統和應用程序設計之間的橋梁，將兩種處理方式連接起來。

3.游標的語法

3.1--聲明游標的分類：

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

參數說明：

cursor_name：游標名稱。
Local：作用域為局部，只在定義它的批處理，存儲過程或觸發器中有效。
Global：作用域為全局，由連接執行的任何存儲過程或批處理中，都可以引用該游標。
[Local | Global]:默認為local。
Forward_Only:指定游標智能從第一行滾到最后一行。Fetch Next是唯一支持的提取選項。如果在指定Forward_Only是不指定Static、KeySet、Dynamic關鍵字，默認為Dynamic游標。如果Forward_Only和Scroll沒有指定，Static、KeySet、Dynamic游標默認為Scroll，Fast_Forward默認為Forward_Only
Static:靜態游標
KeySet：鍵集游標
Dynamic：動態游標，不支持Absolute提取選項
Fast_Forward：指定啟用了性能優化的Forward_Only、Read_Only游標。如果指定啦Scroll或For_Update，就不能指定他啦。
Read_Only:不能通過游標對數據進行刪改。
Scroll_Locks：將行讀入游標是，鎖定這些行，確保刪除或更新一定會成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic：指定如果行自讀入游標以來已得到更新，則通過游標進行的定位更新或定位刪除不成功。當將行讀入游標時，sqlserver不鎖定行，它改用timestamp列值的比較結果來確定行讀入游標后是否發生了修改，如果表不行timestamp列，它改用校驗和值進行確定。如果已修改改行，則嘗試進行的定位更新或刪除將失敗。如果指定啦Fast_Forward,則不能指定他。
Type_Warning:指定將游標從所請求的類型隱式轉換為另一種類型時向客戶端發送警告信息。
For Update[of column_name ,....] :定義游標中可更新的列。

靜態游標（static）：當游標被建立時，將會創建 FOR 后面的 SELECT 語句所包含數據集的副本存入 tempdb 數據庫中，任何對于底層表內數據的更改不會影響到游標的內容。

即打開游標之后，對游標查詢的數據表的數據進行增刪改操做之后，靜態游標中 select 的數據依舊顯示的為沒有操作之前的數據。

如果想與操作之后的數據一致，則關閉之后重新打開游標即可。

動態游標（dynamic）：動態游標與靜態游標相反，當底層數據表的數據更改時，游標的內容也隨之得到反映，在下一次 fetch 中，行的數據值、順序和成員身份在每次提取時都會更改。

只進游標（fast_forward）：只進游標不支持滾動，只支持從頭到尾按順序讀取數據，對數據執行增刪改操作，在提取時是可見的，但由于該游標只能進不能向后滾動，所以在行提取后對行做增刪改是不可見的。

鍵集游標（keyset）：打開鍵集驅動游標時，結果集的每行數據被一組唯一標識符進行標識，被標識的列做刪改時，用戶滾動游標是可見的，其他用戶執行的插入是不可見的（不能通過 Transact-SQL 服務器游標執行插入）。如果刪除了某行，嘗試讀取的行返回 @@FETCH_STATUS為-2。從游標外部更新鍵值類似于刪除舊行后再插入新行。具有新值的行不可見，并且嘗試提取具有舊值的行返回 @@FETCH_STATUS為-2。如果通過指定 WHERE CURRENT OF 子句來通過游標執行更新，則新值可見

3.2

3.3--提取游標并賦值

Fetch [ [Next|prior|Frist|Last|Absoute n|Relative n ] from ][Global] cursor_name [into @variable_name[,....]]

參數說明：

Frist：結果集的第一行
Prior：當前位置的上一行
Next：當前位置的下一行
Last：最后一行
Absoute n：從游標的第一行開始數，第n行。
Relative n：從當前位置數，第n行。（n 為正數時是對于目前行向前，為負數時是對于目前行向后）
Into @variable_name[,...] : 將提取到的數據存放到變量variable_name中

創建測試數據——

CREATE TABLE [dbo].[bank3](
[userName] [varchar](10) NOT NULL,
[cardID] [varchar](10) NOT NULL,
[currentMoney] [float] NOT NULL,
[createID] [varchar](20)
) ON [PRIMARY]
GO

insert into bank3 values('XX1',11002,'1.5','202111');
insert into bank3 values('XX2',11002,'2.7','202111');
insert into bank3 values('XX3',11002,'3.7','202109');
insert into bank3 values('XX3',11004,'4.2','202110');

CREATE TABLE [dbo].[bank2](
[userName] [varchar](10) NOT NULL,
[cardID] [varchar](10) NOT NULL,
[currentMoney] [float] NOT NULL,
[createID] [varchar](20)
) ON [PRIMARY]
GO

insert into bank2 values('XX1',11002,'444.78','202103');
insert into bank2 values('XX3',11004,'555.99','202109');
insert into bank2 values('XX4',11001,'333.33','202111');

-----------------------------------------------------------------

select * from bank2
SELECT *FROM BANK3
select * from bank202109
SELECT *FROM BANK202111
SELECT *FROM BANK202110

--------------------------------------------------------------

使用游標對數據的插入操作——

方式1：查詢表bank2的數據是否在表bank3中存在，如果不存在則插入到表bank3中

declare @varCursor varchar(22), --定義輸出變量
@cardid varchar(22),
@currentmoney float,
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float

declare @xSQL varchar(200)

declare cursor_bank2 cursor for --創建游標
select username,currentmoney,cardid from bank2 ORDER BY CURRENTMONEY

open cursor_bank2 --打開游標

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --從游標變量中讀取值,并賦值

while(@@FETCH_STATUS=0) --游標讀取下一條數據是否成功，等于0的時候，游標查詢的結果有，游標就會將查詢到的數據直接存入游標捕捉數據的變量,@@fetch_status值的改變是通過fetch next from實現的
begin

if NOT exists(select *from bank3 where CARDID=@cardid ) --查詢表BANK3是否包含有游標值的此條數據，如果沒有就執行插入游標查詢表BANK2的這一條數據
BEGIN
set @xSQL ='insert into bank3 values ('''+@varCursor+''','''+@cardid+''','''+cast(@currentmoney as varchar(20))+''') '
exec(@xSQL);
END
else
BEGIN
print ('已存在數據')
END

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --取下一行數據，否則@@fetch_status永遠等于0，則While進入死循環

end

close cursor_bank2 --關閉游標

deallocate cursor_bank2 --釋放游標

------------------------------------------------------------------------------------------------------------------------------------------

方式2：將表bank3數據按 createID字段分類創建對應表，并把該分類數據寫入對應分類表內

begin

declare @biao varchar(100),@sql varchar(1000),@tempentinfo1 varchar(100)
set @tempEntInfo1 ='bank'
set @sql=+@tempEntInfo1+'%'
declare c cursor for select name from sysobjects where type='u' and name like @sql and len(name)='10'
set @sql='drop table '
open c
fetch c into @biao
while @@fetch_status=0
begin
set @sql=@sql+@biao
exec(@sql)
set @sql='drop table '
fetch c into @biao
end
close c
deallocate c

------------------------------------------------------
declare @varCursor varchar(22), --定義輸出變量
@cardid varchar(22),
@currentmoney float,
@createID varchar(22),
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float,
@createID1 varchar(22)

declare @xSQL varchar(200),
@createSQL VARCHAR(200),
@deleteSQL VARCHAR(200)

declare @count int,@tempEntInfo VARCHAR(200)

declare cursor_bank2 cursor for --創建游標
select username,currentmoney,cardid,createID from bank3 ORDER BY CURRENTMONEY

open cursor_bank2 --打開游標

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --從游標變量中讀取值,并賦值

while(@@FETCH_STATUS=0) --游標讀取下一條數據是否成功，等于0的時候，游標查詢的結果有，游標就會將查詢到的數據直接存入游標捕捉數據的變量,@@fetch_status值的改變是通過fetch next from實現的
begin
set @tempEntInfo ='bank'+@createID+''
select @count=COUNT(*) from sysobjects where name=@tempEntInfo -- select COUNT(*) from sysobjects where name='bank202111'
if (@count=0)
begin
SET @createSQL='CREATE TABLE bank'+@createID+' ([userName] [varchar](10) NOT NULL,[cardID] [varchar](10) NOT NULL, [currentMoney] [float] NOT NULL, [createID] [varchar](20)) ON [PRIMARY]'
exec (@createSQL)
end
else
begin
print ('表已經存在')
end;
begin
set @xSQL ='insert into bank'+@createID+' values ('''+@varCursor+''','''+@cardid+''','''+cast(@currentmoney as varchar(20))+''','''+@createID+''') '
exec(@xSQL);
end

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --取下一行數據，否則@@fetch_status永遠等于0，則While進入死循環

end

close cursor_bank2 --關閉游標

deallocate cursor_bank2 --釋放游標

end

----------------------------------------------------------------------------------------------------------------------------------------------

使用游標對數據的刪除操作——

方式1：查詢表bank2內的數據不包含在表bank3里面時，就刪除對應該條數據

declare @varCursor varchar(22), --定義輸出變量
@cardid varchar(22),
@currentmoney float,
@createID varchar(22),
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float,
@createID1 varchar(22)

declare @xSQL varchar(200),
@createSQL VARCHAR(200),
@deleteSQL VARCHAR(200)

declare @count int,@tempEntInfo VARCHAR(200)

declare cursor_bank2 cursor for --創建游標
select username,currentmoney,cardid,createID from bank2 ORDER BY CURRENTMONEY

open cursor_bank2 --打開游標

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --從游標變量中讀取值,并賦值

while(@@FETCH_STATUS=0) --游標讀取下一條數據是否成功，等于0的時候，游標查詢的結果有，游標就會將查詢到的數據直接存入游標捕捉數據的變量,@@fetch_status值的改變是通過fetch next from實現的
begin
if NOT exists(select *from bank3 where cardid=@cardid ) --如果查詢的游標值不包含在表BANK3里面此條數據就執行刪除
begin
delete from bank2 where cardid=@cardid --執行更新操作
end
else
begin
print '已包含'
end

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --取下一行數據，否則@@fetch_status永遠等于0，則While進入死循環

end

close cursor_bank2 --關閉游標

deallocate cursor_bank2 --釋放游標

----------------------------------------------------------------------------------------------------------------------------------------------

使用游標對數據的更新操作——

方式1：判斷數據是否存在

declare @varCursor varchar(22), --定義輸出變量
@cardid varchar(22),
@currentmoney float

declare @temp_cu table(currentmoney float)

declare cursor_bank cursor for --創建游標
select username,currentmoney,cardid from bank3 ORDER BY CURRENTMONEY

open cursor_bank --打開游標

fetch next from cursor_bank INTO @VARCURSOR,@currentmoney,@cardid --從游標變量中讀取值,并賦值

while(@@FETCH_STATUS=0) --游標讀取下一條數據是否成功，等于0的時候，游標查詢的結果有，游標就會將查詢到的數據直接存入游標捕捉數據的變量,@@fetch_status值的改變是通過fetch next from實現的
begin

if NOT exists(select *from bank2 where currentmoney=@currentmoney ) --如果查詢的游標值不包含在表BANK2里面更新的此條數據就輸出XX，包含就執行更新語句
begin
update bank2 set currentMoney =@currentmoney where username=@varCursor and cardid=@cardid --執行更新操作
end
else
begin
print '已包含'
end

fetch next from cursor_bank INTO @VARCURSOR,@currentmoney,@cardid --取下一行數據，否則@@fetch_status永遠等于0，則While進入死循環

end

close cursor_bank --關閉游標

deallocate cursor_bank --釋放游標

--------------------------------------------------------------------------------------------------------------------------------------------

方式2：雙游標嵌套查詢

declare @varCursor varchar(22), --定義輸出變量
@cardid varchar(22),
@currentmoney float,
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float

declare @xSQL varchar(200)

--declare @temp_cu table(currentmoney float)

declare cursor_bank2 cursor for --創建游標
select username,currentmoney,cardid from bank3 ORDER BY CURRENTMONEY

open cursor_bank2 --打開游標

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --從游標變量中讀取值,并賦值

while(@@FETCH_STATUS=0) --游標讀取下一條數據是否成功，等于0的時候，游標查詢的結果有，游標就會將查詢到的數據直接存入游標捕捉數據的變量,@@fetch_status值的改變是通過fetch next from實現的
begin
--再定義另一游標循環多表操作判斷
declare cursor_bank3 cursor for

select currentmoney,username,cardid from bank2 ORDER BY CURRENTMONEY

open cursor_bank3

fetch next from cursor_bank3 into @currentmoney1,@varCursor1,@cardid1

while(@@fetch_status=0)
begin
if (@varCursor=@varCursor1)
BEGIN
--update bank3 set currentMoney =@currentmoney where username=@varCursor and cardid=@cardid
set @xSQL ='update bank2 set currentMoney='''+cast(@currentmoney as varchar(20))+''' where username= '''+cast(@varCursor as varchar(20))
+''' and cardid= '''+cast(@cardid as varchar(20))+''' '
--print (@xSQL)
exec(@xSQL);
END
else
BEGIN
print ('不存在')
END
fetch next from cursor_bank3 into @currentmoney1,@varCursor1,@cardid1
end
close cursor_bank3
deallocate cursor_bank3

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --取下一行數據，否則@@fetch_status永遠等于0，則While進入死循環

end

close cursor_bank2 --關閉游標

deallocate cursor_bank2 --釋放游標

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

對游標的一些優化建議：

不得不使用游標時，如果不需要用游標寫數據，就設置成只讀游標，如游標指針不需要上移，就用只進游標。
用完之后一定要關閉、釋放游標。
盡量不要在大量數據上定義游標。
盡量不要使用游標上執行更新、刪除操作
盡量使用Fast_Forward關鍵字定義游標

總結：

（1）while循環和游標都可以實現循環的目的，while用法只是通過判斷比較條件來實現，比較簡潔明了，游標使用光標循環記錄；

（2）游標是在while的基礎上實現循環的功能，但是語法較復雜，在數據量較少時效率比while高，但如果數據量很多的情況下一般推薦使用游標，因為這會導致效率很慢且需要耗大量內存；

如果可能盡量使用while、子查詢、臨時表、函數、表變量等來替換游標。

（3）while循環體內支持再嵌套一個循環，但是一定要注意不能出現死循環，否則sql服務就會卡死、崩潰；同樣使用游標時也要注意執行Fetch......去指向下一個游標位置，否則同樣會死循環。

posted @ 2021-10-25 17:45 小柒仔閱讀(2457) 評論(0) 收藏舉報

刷新頁面返回頂部

小柒仔

SQL Server-處理結果集的循環機制——游標

公告