目錄：1.準備數據2.游標知識點3.While知識點4.游標和While對比5.While循環(huán)性能提升1.準備數據測試代碼如下：--創(chuàng)建數據表createtabletestfor(FIDintidentity(1,1)primarykey,--主鍵FYearint,--年份FMonthint,--月份FAmountint,--金額FQtyint--數量)--插入數據declare@fyearintset@fyear=1001declare@iintset@i=1--總共循環(huán)=3000*12=36000遍循環(huán)--總共插入數據量=36000*12=432000行--第一層循環(huán)，3000遍循環(huán)while(@fyear<=4000)begin--第二層循環(huán)，12遍循環(huán)while(@i<=12) begin insertintotestfor(fyear,fmonth,famount,fqty)values(@fyear,1,100+@i*10,10+@i),(@fyear,2,200+@i*10,20+@i),(@fyear,3,300+@i*10,30+@i),(@fyear,4,400+@i*10,40+@i),(@fyear,5,500+@i*10,50+@i),(@fyear,6,600+@i*10,60+@i),(@fyear,7,700+@i*10,70+@i),(@fyear,8,800+@i*10,80+@i),(@fyear,9,900+@i*10,90+@i),(@fyear,10,1000+@i*10,100+@i),(@fyear,11,1100+@i*10,110+@i),(@fyear,12,1200+@i*10,120+@i) set@i=@i+1 end--細節(jié)注意：第二層循環(huán)完成要重置循環(huán)值set@i=1set@fyear=@fyear+1endselects*fromtestfor執(zhí)行結果如下：2.游標知識點2.1.游標的定義游標（cursor）是系統為用戶開設的一個數據緩沖區(qū)，存放SQL語句的執(zhí)行結果。每個游標區(qū)都有一個名字,用戶可以用SQL語句逐一從游標中獲取記錄，并賦給主變量，交由主語言進一步處理。游標是處理結果集的一種機制吧，它可以定位到結果集中的某一行，多數據進行讀寫，也可以移動游標定位到你所需要的行中進行操作數據。一般復雜的存儲過程，都會有游標的出現，他的用處主要有：定位到結果集中的某一行。對當前位置的數據進行讀寫?？梢詫Y果集中的數據單獨操作，而不是整行執(zhí)行相同的操作。是面向集合的數據庫管理系統和面向行的程序設計之間的橋梁。2.2.游標的種類MSSQLSERVER支持三種類型的游標：Transact_SQL游標，API服務器游標和客戶游標。（1）Transact_SQL游標Transact_SQL游標是由DECLARECURSOR語法定義、主要用在Transact_SQL腳本、存儲過程和觸發(fā)器中。Transact_SQL游標主要用在服務器上，由從客戶端發(fā)送給服務器的Transact_SQL語句或是批處理、存儲過程、觸發(fā)器中的Transact_SQL進行管理。Transact_SQL游標不支持提取數據塊或多行數據。（2）API游標API游標支持在OLEDB，ODBC以及DB_library中使用游標函數，主要用在服務器上。每一次客戶端應用程序調用API游標函數，MSSQLSEVER的OLEDB提供者、ODBC驅動器或DB_library的動態(tài)鏈接庫（DLL）都會將這些客戶請求傳送給服務器以對API游標進行處理。（3）客戶游標客戶游標主要是當在客戶機上緩存結果集時才使用。在客戶游標中，有一個缺省的結果集被用來在客戶機上緩存整個結果集。客戶游標僅支持靜態(tài)游標而非動態(tài)游標。由于服務器游標并不支持所有的Transact-SQL語句或批處理，所以客戶游標常常僅被用作服務器游標的輔助。因為在一般情況下，服務器游標能支持絕大多數的游標操作。由于API游標和Transact-SQL游標使用在服務器端，所以被稱為服務器游標，也被稱為后臺游標，而客戶端游標被稱為前臺游標。在本章中我們主要講述服務器（后臺）游標。2.3.游標的分類根據游標檢測結果集變化的能力和消耗資源的情況不同，SQLServer支持的API服務器游標分為一下4種：靜態(tài)游標:靜態(tài)游標的結果集，在游標打開的時候建立在TempDB中，不論你在操作游標的時候，如何操作數據庫，游標中的數據集都不會變。例如你在游標打開的時候，對游標查詢的數據表數據進行增刪改，操作之后，靜態(tài)游標中select的數據依舊顯示的為沒有操作之前的數據。如果想與操作之后的數據一致，則重新關閉打開游標即可。動態(tài)游標:這個則與靜態(tài)游標相對，滾動游標時，動態(tài)游標反應結果集中的所有更改。結果集中的行數據值、順序和成員在每次提取時都會變化。所有用戶做的增刪改語句通過游標均可見。如果使用API函數或T-SQLWhereCurrentof子句通過游標進行更新，他們將立即可見。在游標外部所做的更新直到提交時才可見。只進游標：只進游標不支持滾動，只支持從頭到尾順序提取數據，數據庫執(zhí)行增刪改，在提取時是可見的，但由于該游標只能進不能向后滾動，所以在行提取后對行做增刪改是不可見的。鍵集驅動游標：打開鍵集驅動游標時，該有表中的各個成員身份和順序是固定的。打開游標時，結果集這些行數據被一組唯一標識符標識，被標識的列做刪改時，用戶滾動游標是可見的，如果沒被標識的列增該，則不可見，比如insert一條數據，是不可見的，若可見，須關閉重新打開游標。靜態(tài)游標在滾動時檢測不到表數據變化，但消耗的資源相對很少。動態(tài)游標在滾動時能檢測到所有表數據變化，但消耗的資源卻較多。鍵集驅動游標則處于他們中間，所以根據需求建立適合自己的游標，避免資源浪費。2.4.游標的語法游標的生命周期包含有五個階段：聲明游標、打開游標、讀取游標數據、關閉游標、釋放游標。--聲明2個變量declare@O_IDnvarchar(20)declare@A_Salaryfloat/*DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]]參數說明：cursor_name：游標名稱。1.LOCAL/GLOBAL二選一游標有全局和局部游標，如果不指定游標作用域，默認作用域為GLOBAL。LOCAL：作用域為局部，只在定義它的批處理，存儲過程或觸發(fā)器中有效。GLOBAL：作用域為全局，由連接執(zhí)行的任何存儲過程或批處理中，都可以引用該游標。舉例：declaremycursorcursorlocalfordeclaremycursorcursorglobalfor2.FORWARD_ONLY和SCROLL二選一FORWARD_ONLY：指定游標只能從第一行滾到最后一行。FetchNext是唯一支持的提取選項。SCROLL：游標可在定義的數據集中向任何方向，或任何位置移動。1.如果在指定FORWARD_ONLY時不指定STATIC/KEYSET和DYNAMIC關鍵字，默認為Dynamic游標。2.如果FORWARD_ONLY和SCROLL均為不指定，Static、KeySet、Dynamic游標默認為Scroll，Fast_Forward游標默認為Forward_Only。3.STATIC/KEYSET/DYNAMIC和FAST_FORWARD四選一這四個關鍵字是游標所在數據集所反應的表內數據和游標讀取出的數據的關系STATIC：靜態(tài)游標，當游標被建立時，將會創(chuàng)建FOR后面的SELECT語句所包含數據集的副本存入tempdb數據庫中，任何對于底層表內數據的更改不會影響到游標的內容。KEYSET：鍵集游標，可以理解為介于STATIC和DYNAMIC的折中方案。將游標所在結果集的唯一能確定每一行的主鍵存入tempdb,當結果集中任何行改變或者刪除時，@@FETCH_STATUS會為-2,KEYSET無法探測新加入的數據。DYNAMIC：動態(tài)游標，是和STATIC完全相反的選項,當底層數據庫更改時，游標的內容也隨之得到反映，在下一次fetch中，數據內容會隨之改變，動態(tài)游標不支持ABSOLUTE提取選項。FAST_FORWARD：指定啟動了性能優(yōu)化的FORWARD_ONLY、READ_ONLY游標。如果指定了SCROLL或FOR_UPDATE,則不能指定FAST_FORWARD。4.READ_ONLY/SCROLL_LOCKS/OPTIMISTIC三選一READ_ONLY：意味著聲明的游標只能讀取數據,游標不能做任何更新操作。SCROLL_LOCKS：是另一種極端，將讀入游標的所有數據進行鎖定，防止其他程序進行更改，以確保更新的絕對成功，如果還指定了FAST_FORWARD或STATIC，則不能指定SCROLL_LOCKS。OPTIMISTIC：是相對比較好的一個選擇，OPTIMISTIC不鎖定任何數據，當需要在游標中更新數據時，如果底層表數據更新，則游標內數據更新不成功，如果，底層表數據未更新，則游標內表數據可以更新，如果還指定了FAST_FORWARD，則不能指定OPTIMISTIC。TYPE_WARNING:指定游標從所請求的類型隱式轉換為另一種類型時，向客戶端發(fā)送警告消息。ForUpdate[ofcolumn_name,....]：定義游標中可更新的列。*/--聲明一個游標mycursordeclaremycursorcursorforselectO_ID,A_SalaryfromAddSalary--打開游標openmycursor/*FETCH[[NEXT|PRIOR|FIRST|LAST????????|ABSOLUTE{n}????????|RELATIVE{n}????]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]NEXT：當前位置的下一行，如果NEXT為對游標的第一次提取操作，則返回結果集中的第一行。第二次FETCHNEXT即是提取下一行數據。依次遞增取行記錄。NEXT為默認的游標提取選項。PRIOR：當前位置的上一行，如果FETCHPRIOR為對游標的第一次提取操作，則沒有行返回并且游標置于第一行。FIRST：結果集的第一行。LAST：最后一行。ABSOLUTE{n}：如果n為正，則返回從游標頭開始向后n行的第n行，并將返回行變成新的當前行。如果n為負，則返回從游標末尾開始向前的n行的第n行，并將返回行變成新的當前行。如果n為0，則不返回行。n數據類型必須為int、tinyint或smallint。RELATIVE{n}：如果n為正，則返回從當前行開始向后的第n行。如果n為負，則返回從當前行開始向前的第n行。如果n為0，則返回當前行,對游標第一次提取時， 如果在將n設置為負數或0的情況下指定FETCHRELATIVE,則不返回行，n的數據類型必須是int、tinyint或smallint。Into@variable_name[,...]：將提取到的數據存放到變量variable_name中。*/--從游標里取出數據賦值到我們剛才聲明的2個變量中fetchnextfrommycursorinto@O_ID,@A_Salary--判斷游標的狀態(tài)--0fetch語句成功---1fetch語句失敗或此行不在結果集中---2被提取的行不存在while(@@fetch_status=0)begin--顯示出我們每次用游標取出的值print'游標成功取出一條數據'print@O_IDprint@A_Salary--用游標去取下一條記錄fetchnextfrommycursorinto@O_ID,@A_Salaryend--關閉游標closemycursor--釋放游標DEALLOCATEmycursorGO2.4.一個游標的例子計算數據表testfor，2018年的數量、金額累計數，按月份、按FID排序。declare@fidintdeclare@fyearintdeclare@fmonthintdeclare@famountintdeclare@fqtyintdeclare@sumqtyint--累計數declare@sumamtint--累計數set@sumqty=0set@sumamt=0selectfid,fyear,fmonth,famount,fqty,0asfsumqty,0asfsumamtinto#tmp1fromtestforwherefyear=2018declaremycursorcursorFORWARD_ONLYforselectfid,fyear,fmonth,famount,fqtyfromtestforwherefyear=2018orderbyfyear,fmonth,fidopenmycursorfetchnextfrommycursorinto@fid,@fyear,@fmonth,@famount,@fqtywhile(@@fetch_status=0)beginset@sumqty=@sumqty+@fqtyset@sumamt=@sumamt+@famountupdatet1sett1.fsumqty=@sumqty,t1.fsumamt=@sumamtfrom#tmp1t1wheret1.fid=@fidfetchnextfrommycursorinto@fid,@fyear,@fmonth,@famount,@fqtyendclosemycursorDEALLOCATEmycursorselect*from#tmp1orderbyfyear,fmonth,fidtruncatetable#tmp1droptable#tmp1GO執(zhí)行結果：3.while知識點while循環(huán)主要是while邏輯的運用，沒有那么多知識點，這里，還是舉例來演示。計算數據表testfor，2018年的數量、金額累計數，按月份、按FID排序。代碼如下：declare@famountintdeclare@fqtyintdeclare@sumqtyint--累計數declare@sumamtint--累計數set@sumqty=0set@sumamt=0selectidentity(int,1,1)assno,convert(varchar(10),fid)asfid,fyear,fmonth,famount,fqty,0asfsumqty,0asfsumamtinto#tmp1fromtestforwherefyear=2018orderbyfyear,fmonth,fiddeclare@countintdeclare@iint--while循環(huán)的要點是：--我們在臨時表設置了一個從“1”開始不間斷連續(xù)自增的序號字段[sno]--同時設置一個循環(huán)中間變量@i，也從1開始增長。這樣，每次循環(huán)@i的值和[sno]數值剛好相等。--同時設置循環(huán)最大值變量@count，數值等于[sno]的最大值。set@i=1select@count=max(sno)from#tmp1while@i<=@countbeginselect@fqty=fqty,@famount=famountfrom#tmp1wheresno=@iset@sumqty=@sumqty+@fqtyset@sumamt=@sumamt+@famountupdatet1sett1.fsumqty=@sumqty,t1.fsumamt=@sumamtfrom#tmp1t1wheret1.sno=@i--每一輪循環(huán)的最后，@i要增加“1”set@i=@i+1endselect*from#tmp1orderbyfyear,fmonth,fidtruncatetable#tmp1droptable#tmp1GO執(zhí)行結果：4.游標和while對比1.While循環(huán)，還是游標循環(huán)，效率都不會高。2.游標是對結果集進行逐行遍歷操作，While循環(huán)則是重復某一組操作。游標占用的資源比較多，如果要用循環(huán)，盡量使用While循環(huán)。對于游標一些優(yōu)化建議：1.如果能不用游標，盡量不要使用游標。用完之后，一定要關閉和釋放。2.盡量不要在大量數據上定義游標。3.盡量不要使用游標上更新數據。4.盡量不要使用insensitive,static和keyset這些參數定義游標。如果可以，盡量使用FAST_FORWARD關鍵字定義游標。5.如果只對數據進行讀取，當讀取時只用到FETCHNEXT選項，最好使用FORWARD_ONLY參數。5、While性能提升第一種寫法代碼如下：declare@famountintdeclare@fqtyintdeclare@sumqtyint--累計數declare@sumamtint--累計數set@sumqty=0set@sumamt=0selectidentity(int,1,1)assno,convert(varchar(10),fid)asfid,fyear,fmonth,famount,fqty,0asfsumqty,0asfsumamtinto#tmp1fromtestforwherefyearbetween2001and2100orderbyfyear,fmonth,fiddeclare@countintdeclare@iintset@i=1select@count=max(sno)from#tmp1while@i<=@countbeginselect@fqty=fqty,@famount=famountfrom#tmp1wheresno=@iset@sumqty=@sumqty+@fqtyset@sumamt=@sumamt+@famountupdatet1sett1.fsumqty=@sumqty,t1.fsumamt=@sumamtfrom#tmp1t1wheret1.sno=@iset@i=@i+1endselect*from#tmp1orderbyfyear,fmonth,fidtruncatetable#tmp1droptable#tmp1GO執(zhí)行結果如下：重啟SQLSERVER服務后，第一次執(zhí)行時間31秒：重啟SQLSERVER服務后，第二次執(zhí)行時間32秒：重啟SQLSERVER服務后，第三次執(zhí)行時間32秒：第二種寫法代碼如下：declare@famountintdeclare@fqtyintdeclare@sumqtyint--累計數declare@sumamtint--累計數set@sumqty=0set@sumamt=0selectidentity(int,1,1)assno,convert(varchar(10),fid)asfid,fyear,fmonth,famount,fqty,0asfsumqty,0asfsumam