存儲過程和游標(biāo)7教學(xué)幻燈片

7.1存儲過程概述

存儲過程簡介 SQLServer提供了一種方法：可以將一些預(yù)先編譯的SQL語句集中起來由SQLServer數(shù)據(jù)庫服務(wù)器來完成以實(shí)現(xiàn)某個任務(wù)，這就是存儲過程。存儲過程常駐在SQLServer數(shù)據(jù)庫服務(wù)器的內(nèi)存中。存儲過程優(yōu)點(diǎn)存儲過程分類使用Createprocedure語句例1創(chuàng)建存儲過程，查詢安徽表中所有內(nèi)容，并按名字進(jìn)行排序。CREATEPROCEDUREexp1asSelect*From安徽Orderbyname使用Createprocedure語句例2創(chuàng)建存儲過程，加入一條記錄到安徽表中。CREATEPROCEDUREexp2@IDint,@AREAfloat,@PERIMETERfloat,@BNDRY_IDchar(6),@NAMEchar(8),@DIQUchar(8)asInsertinto安徽Valus(@ID,@AREA,@

PERIMETER,

@BNDRY_ID,@NAME,@DIQU)存儲過程參數(shù)的使用例3創(chuàng)建帶參數(shù)的存儲過程，完成輸入某一個字，輸出包含該字的記錄。CREATEPROCEDUREexp2@namevarchar(8)AsSelect*From安徽WhereNAMElike‘%’+@name+’%’存儲過程參數(shù)的使用例4創(chuàng)建帶參數(shù)的存儲過程，完成輸入某行政區(qū)劃代碼，如果存在輸出該縣（市）的信息，如果不存在，則顯示“您輸入錯誤！”CREATEPROCEDUREexp4@BNDRY_IDvarchar(8)AsBeginDeclare@bit_existbitIfexists(Select*From安徽WhereBNDRY_ID=@BNDRY_ID)Set@bit_exist=1elseSet@bit_exist=0if@bit_exist=1BeginSelect*From安徽WhereBNDRY_ID=@BNDRY_IDEndElsePrint‘您輸入錯誤！’end存儲過程參數(shù)的使用例5創(chuàng)建帶參數(shù)的存儲過程，完成輸入某行政區(qū)劃代碼前4位，統(tǒng)計(jì)該地區(qū)下轄縣（市）有幾個。CREATEPROCEDUREexp5@BNDRY_IDvarchar(8)AsSelectcount(*)From安徽WhereBNDRY_IDlike@BNDRY_ID+‘%’加密存儲過程如果用戶不想讓其他人查看存儲過程的定義文本，可以在定義存儲過程的同時，對其進(jìn)行加密，來保護(hù)代碼的安全性，通過加密的存儲過程可以正常使用，但是無法查看該存儲過程的內(nèi)容，在SQLServerManagementStudio中也不能修改，只能通過ALTER語句來修改。CREATEPROCEDUREexp6WithencryptionasSelect*From安徽OrderbynameExecsp_helptextexp6查看存儲過程源代碼存儲過程的維護(hù)存儲過程修改ALTERPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement存儲過程修改ALTERPROCEDUREexp6asSelectdistinctDIQUFrom安徽存儲過程刪除使用命令刪除存儲過程DROPPROC[EDURE]procedure_name[,…n]DROP

PROCEDUREexp67.2分行處理程序—游標(biāo)

在數(shù)據(jù)庫開發(fā)過程中，使用SELECT語句查詢，得到一個結(jié)果集，對這個結(jié)果集中的不同數(shù)據(jù)行，可能要做不同的處理。也就是要逐一處理每一個數(shù)據(jù)行。游標(biāo)提供了一種比較好的解決方案。7.2分行處理程序—游標(biāo)例在圖書表中查詢出與數(shù)據(jù)庫相關(guān)的圖書，并對其中的部分書的價格進(jìn)行調(diào)整。7.2.2使用游標(biāo)操作步驟:1.聲明游標(biāo)（變量）。2.打開游標(biāo)。3.從一個游標(biāo)中提取信息。4.關(guān)閉（釋放）游標(biāo)。1.聲明游標(biāo)語法格式：DECLARE<游標(biāo)名>[SCROLL]CURSORFOR<SELECT語句>[FOR{READONLY|UPDATE[OF<列名>[,...n]]}]例7-15聲明只讀游標(biāo)。declarecur_ccursorforselect書名,作者,定價from圖書forreadonly例7-16聲明更新游標(biāo)。declarecur_ccursorforselect書名,作者,定價from圖書forupdate

1.聲明游標(biāo)2.聲明游標(biāo)變量語法格式：DECLARE@<變量名>CURSOR建立游標(biāo)變量與游標(biāo)之間的關(guān)聯(lián)。方法1：先聲明游標(biāo)和游標(biāo)變量，然后用SET語句將游標(biāo)賦給游標(biāo)變量。例：DECLAREc1CURSORFORSELECT*FROMsDECLARE@cur_varCURSORSET@cur_var=c1

3.打開游標(biāo)游標(biāo)聲明后，如果要從游標(biāo)中讀取數(shù)據(jù)，必須打開游標(biāo)。

語法格式：OPEN[GLOBAL]<游標(biāo)名>|<游標(biāo)變量名>其中，GLOBAL表示要打開的是一個全局游標(biāo)。4.讀取游標(biāo)中的數(shù)據(jù)

當(dāng)游標(biāo)被打開后，就可以從游標(biāo)中逐行地讀取數(shù)據(jù)。語法格式：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]<游標(biāo)名>}|<@游標(biāo)變量>}[INTO@<變量名>[,...n]]8.2.4.3讀取游標(biāo)的數(shù)據(jù)（P207）Fatch[next|prior|first|last][absoluten|relativen]from游標(biāo)名[into變量名]Next----返回當(dāng)前行的下一行記錄Prior----返回緊鄰當(dāng)前行前面的結(jié)果行Last----返回最后一行記錄Absoluten----返回第n行記錄，當(dāng)n為負(fù)值則從最后一行算起。Relative----游標(biāo)當(dāng)前位置起向后第n行，若n為負(fù)值則向前第n行。游標(biāo)的操作Declarecur1scrollcursorforSelect*from圖書Where書名like‘%數(shù)據(jù)庫%’Opencur1Fetchfirstfromcur1Fetchlastfromcur1Fetchpriorfromcur1Fetchabsolute2fromcur1Fetchrelative2fromcur1Closecur1Deallocatecur1(1)使用CLOSE命令關(guān)閉游標(biāo)

處理完游標(biāo)中數(shù)據(jù)后，必須關(guān)閉游標(biāo)來釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖。語法格式:CLOSE[GLOBAL]<游標(biāo)名>|@<游標(biāo)變量>CLOSE語句可以關(guān)閉游標(biāo)，但不釋放游標(biāo)的數(shù)據(jù)結(jié)構(gòu)。如果要再次使用游標(biāo)，可用OPEN命令重新打開。6.釋放游標(biāo)用CLOSE命令關(guān)閉游標(biāo)并沒有釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu)。使用DEALLOCATE命令將釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu)，游標(biāo)使用的任何資源也隨之釋放。語法格式：DEALLOCATE[GLOBAL]<游標(biāo)名>|@<游標(biāo)變量>

游標(biāo)的關(guān)閉指釋放游標(biāo)的結(jié)果集所占用的資源，游標(biāo)的釋放指釋放游標(biāo)占用的所有資源，當(dāng)然也包括結(jié)果集占用的資源。7.2分行處理程序—游標(biāo)例在圖書表中查詢出與數(shù)據(jù)庫相關(guān)的圖書，并對其中的部分書的價格進(jìn)行調(diào)整。Declarecur1scrollcursorforselect書名,作者,定價from圖書where書名like‘%數(shù)據(jù)庫%’forupdateof定價7.2分行處理程序—游標(biāo)例在圖書表中查詢出與數(shù)據(jù)庫相關(guān)的圖書，并對其中的部分書的價格進(jìn)行調(diào)整。Declarecur1scrollcursorforselect書名,作者,定價from圖書where書名like‘%數(shù)據(jù)庫%’forupdateof定價Opencur17.2分行處理程序—游標(biāo)例在圖書表中查詢出與數(shù)據(jù)庫相關(guān)的圖書，并對其中的部分書的價格進(jìn)行調(diào)整。Declarecur1scrollcursorforselect書名,作者,定價from圖書where書名like‘%數(shù)據(jù)庫%’forupdateof定價Opencur1Fetchabsolute9fromcur17.2分行處理程序—游標(biāo)例在圖書表中查詢出與數(shù)據(jù)庫相關(guān)的圖書，并對其中的部分書的價格進(jìn)行調(diào)整。Declarecur1scrollcursorforselect書名,作者,定價from圖書where書名like‘%數(shù)據(jù)庫%’Opencur1Fetchabsolute9fromcur1Update圖書set定價=30wherecurrentofcur17.2.3游標(biāo)應(yīng)用實(shí)例

1.用于游標(biāo)的UPDATE語句的語法格式：UPDATE<表名>SET<列名>={<表達(dá)式>|NULL|<select子句>}[,…n]WHERECURRENTOF<游標(biāo)名>2.用于游標(biāo)的DELETE語句的語法格式：DELETEFROM<表名>WHERECURRENTOF<游標(biāo)名>利用變量輸出游標(biāo)中的字段值DeclareS1scrollcursorforSelect姓名,班級,綜合測評from學(xué)生Where班級like‘%地信%’Declare@snintDeclare@scvarchar(10)Declare@ssvarchar(10)OpenS1Fetchabsolute4fromSinto@sn,@sc,@ssSelect@sn,@sc,@ssCloseS1DeallocateS1利用游標(biāo)修改數(shù)據(jù)庫中的數(shù)據(jù)Select姓名,班級,綜合測評from學(xué)生DeclareS2scrollcursorforSelect姓名,班級,綜合測評from學(xué)生Where班級like‘%地信%’OpenS2FetchfirstfromSUpdate學(xué)生set姓名='modifybycursor'wherecurrentofS2CloseS2DeallocateS2實(shí)例11:利用游標(biāo)刪除數(shù)據(jù)庫中的數(shù)據(jù)Select姓名,班級,綜合測評from學(xué)生DeclareS3scrollcursorforSelect姓名,班級,綜合測評from學(xué)生Where班級like‘%地信%’OpenS3Fetchabsolute3fromS3Deletefrom職工wherecurrentofS3CloseS3DeallocateS3使用@@fetch_status:該全局變量/配置函數(shù)返回被最后FETCH語句執(zhí)行的游標(biāo)的狀態(tài)，返回類型為int：0：FETCH語句成功－1:FETCH語句失敗或此行不在結(jié)果集中－2：被提取的行不存在實(shí)例12:利用游標(biāo)判斷記錄是否存在DeclareS4scrollcursorforSelect姓名,班級,綜合測評from學(xué)生Where班級like‘%地信%’OpenS4If@@fe