存儲過程游標(biāo)詳解與實例

.PAGE1.存儲過程、游標(biāo)詳解與實例一、遇到的問題我們在之前學(xué)習(xí)的課程寫了不少的批處理語句，這些批處理語句存在兩個問題：1.沒法像函數(shù)那樣傳參數(shù)運行（自定義化）2.沒法像函數(shù)那樣可以反復(fù)地調(diào)用（功能化模塊化）說到這里，我們可以猜到，數(shù)據(jù)庫中應(yīng)該可以建立函數(shù)形式的數(shù)據(jù)庫對象來解決這樣的問題。但是介紹這種數(shù)據(jù)庫對象之前，我們再來看幾個需要解決的問題：我們已經(jīng)學(xué)會把一條select語句封裝在視圖中，但是它只能用來查詢，如果我們希望進(jìn)行其它操作，比如增刪改記錄、建刪庫表等，是不能用視圖來完成的。另一方面，如果把一個帶有更改操作的批處理整個存儲成數(shù)據(jù)庫對象，還可以把處理數(shù)據(jù)的程序移動到離數(shù)據(jù)盡可能近的地方，而不是總把這些操作寫在客戶端程序（如Java、C#）中。通過將處理數(shù)據(jù)的程序從客戶應(yīng)用程序移動到服務(wù)器，可以降低網(wǎng)絡(luò)流量，并提高性能和數(shù)據(jù)的完整性。二、存儲過程的概念解決上面的問題，我們可以使用一種叫做“存儲過程”的數(shù)據(jù)庫對象。存儲過程(StoredProcedure)把我們經(jīng)常用到的一串復(fù)雜sql語句保存成一個數(shù)據(jù)庫對象，并給它起一個名字。每次使用存儲過程只需要使用如下的形式即可：execproc存儲過程名存儲過程還可以帶參數(shù)運行：execproc存儲過程名參數(shù)值[,參數(shù)值...]存儲過程并不神秘，它就是批處理。之前提到視圖是保存在服務(wù)器上的命名select語句，與之類似，存儲過程是保存在服務(wù)器上的命名批處理，系統(tǒng)將預(yù)先對它進(jìn)行編譯。存儲過程可以包含幾乎所有的T-SQL語句，如數(shù)據(jù)存取語句、流程控制語句、錯誤處理語句等，使用起來彈性很大。數(shù)據(jù)庫中也存在著系統(tǒng)函數(shù)和用戶定義函數(shù)這兩種對象，用戶定義函數(shù)的功能和存儲過程很像，但是有一定的區(qū)別，我們將在后面的課程中介紹?！敬鎯^程的分類】系統(tǒng)存儲過程systemstoredprocedure前綴sp_例如sp_helpsp、helpdb擴(kuò)展存儲過程extendedstoredprocedure前綴xp_例如xp_cmdshell用戶自定義存儲過程user-definedstoredprocedure也就是我們自己創(chuàng)建的三、用戶自定義存儲過程的創(chuàng)建、修改、刪除【創(chuàng)建存儲過程】createproc存儲過程名--無參數(shù)的存儲過程as批處理語句go【修改存儲過程】alterproc存儲過程名--無參數(shù)的存儲過程as批處理語句go可以看到，修改存儲過程的語法和創(chuàng)建的語法只差一個單詞，把create換成alt即可?！緞h除存儲過程】dropproc存儲過程名我們還可使用managementstudio來管理存儲過程，展開菜單樹中的“可編程性”，在“存儲過程”的子節(jié)點中可以進(jìn)行各種操作。這里要說一下創(chuàng)建：當(dāng)點擊“新建存儲過程”之后，會出現(xiàn)一個基于模板的創(chuàng)建語句。這時點擊菜單中的“查詢→指定模板參數(shù)的值”，即可彈出對話框來對模板進(jìn)行設(shè)置，從而建立我們想要的存儲過程。另外，點擊菜單中的“視圖→模板資源管理器”，可以看到SQLSERVER2005為我們提供的各種SQL語句模板。模板的功能比較高級，有興趣的同學(xué)務(wù)必自學(xué)一下?！疽粋€簡單的例子】--插入一個以時間為用戶名的用戶createprocinsUserasbegintrandeclare@usernamevarchar(20)set@username=convert(varchar(8),getdate(),112)+replace(convert(varchar(10),getdate(),8),':','')ifnotexists(select*fromyonghuwhereyonghuming=@username)

insertintoyonghuvalues(@username,'111111','@163.com','新用戶')committran--也可以寫commit，但是建議不要去掉trango然后使用exec執(zhí)行這個存儲過程：execinsUser選中exec這一行，然后按F5快速地反復(fù)執(zhí)行，你會發(fā)現(xiàn)在同一秒內(nèi)只能插入一個用戶。這個存儲過程一旦建立就不能再次執(zhí)行這段代碼了，可以把create改成alter來修改。注意存儲過程的代碼中不能go語句，因為go是用來提交批的，一旦遇到go系統(tǒng)會認(rèn)為這個存儲過程的代碼已經(jīng)書寫完畢，會提交create或者alter的批處理。如果希望在存儲過程中執(zhí)行另一個批處理，請把該批處理寫成另一個存儲過程并調(diào)用。四、用戶自定義存儲過程的參數(shù)傳遞和返回值【傳遞參數(shù)】create|alterproc存儲過程名

@參數(shù)名參數(shù)類型[,@參數(shù)名參數(shù)類型...]as批處理語句go還記得我們前面做過的一個案例嗎：declare@tablenamenvarchar(10),@idvarchar(10),@idvalueintdeclare@sqlvarchar(100)set@tablename='yiren'set@id='yirenid'set@idvalue=10set@sql='select*from'+@tablename+'where'+@id+'='+cast(@idvalueasvarchar)print@sqlexec(@sql)現(xiàn)在我們把它寫成存儲過程。這樣我們每次都可以從一個指定表中提取我們想要的記錄了createprocqueryItem

@tablenamenvarchar(10),

@idvarchar(20),

@idvalueint--參數(shù)外面還可以套上圓括號，看起來更加清晰as

declare@sqlvarchar(100)

set@sql='select*from'+@tablename+'where'+@id+'='+cast(@idvalueasvarchar)

exec(@sql)go調(diào)用的方法：execqueryItem'yiren','yirenid',@idvalue=10queryItem'jingjiren','jingjirenid',1存儲過程不使用exec也可以調(diào)用，但是不推薦這么做。存儲過程參數(shù)的名字可以在調(diào)用時寫出來，但是這是完全沒必要的，所以@idvalue=10直接寫成10就可以了。exec加不加括號效果不一樣。加括號是執(zhí)行sql語句，不加括號是執(zhí)行存儲過程。【返回值】1.以retrun返回，始終是整數(shù)值return只能返回整數(shù)，即使不顯式寫出“return整數(shù)值”這樣的語句，存儲過程也會自動返回一個數(shù)值0表示成功。我們可以在發(fā)生錯誤時返回非0值，表示有錯誤發(fā)生。不要試圖使用return返回一個在存儲過程中處理的結(jié)果，比如姓名、生日之類的內(nèi)容，因為它是整數(shù)，功能極為有限。我們只用它返回存儲過程執(zhí)行的狀態(tài)就足夠了。請看例子：createprocreturnProcasbegintrandeclare@errorintinsertintoyiren(xingming)values('王美麗')set@error=@@errorinsertintoyiren(yirenid)values(1)set@error=@error+@@errorif@error>0

rollbacktranelse

committranreturn@errorgo調(diào)用的方法：declare@errorint--set@error=execreturnProc--這樣寫是錯誤的……exec@error=returnProcselect'返回值'=@error如果returnProc有個參數(shù)@xingming希望傳入'王美麗'，可以這樣調(diào)用：exec@error=returnProc'王美麗'--或者：exec@error=returnProc@xingming='王美麗'2.以output參數(shù)返回數(shù)據(jù)output可以用來返回任何類型的數(shù)據(jù)，嚴(yán)格來說，它并不是一個“返回值”，而是一個能夠被存儲過程調(diào)用代碼處看到的“外部變量”。這樣說的原因看下面的例子就明白了：--通過id查詢藝人的姓名和年齡createprocqueryProfile

@idint,

@xingmingvarchar(50)output,--必須有output

@nianlingintoutputas

select@xingming=xingming,@nianling=nianling

fromyirenwhereyirenid=@idgo調(diào)用的方法：declare@xingmingvarchar(50),@nianlingintexecqueryProfile1,@xingmingoutput,@nianlingoutput--必須有outputprint'1號藝人的姓名是'+@xingming

+'，年齡是'+cast(@nianlingasvarchar)+'歲'在調(diào)用處，我們先定義了兩個變量，然后我們以output的方式把兩個變量傳遞給了存儲過程。于是存儲過程就可以看到這兩個來自外部的變量了。那么存儲過程中對這兩個變量的一切修改都可以立刻體現(xiàn)到調(diào)用處的代碼中，因為它修改的實際上就是調(diào)用處的兩個變量。3.select語句的結(jié)果集如果在存儲過程中執(zhí)行了select語句并顯示結(jié)果集（并不是使用select語句給變量賦值），那么這個結(jié)果集也可以看做是一種返回值（不能被批處理語句用，但是作為結(jié)果集可以被C#等編程語言使用）。createprocselectProcasselect*fromyirengoexecselectProc這種存儲過程可以用來實現(xiàn)“帶有參數(shù)的視圖”，在上面【傳遞參數(shù)】中舉出的例子就是?！敬鎯^程的常用功能】從存儲過程參數(shù)和返回值的用法我們可以看出，存儲過程通常用來處理一些對數(shù)據(jù)庫的更新操作、或者是按照特定的需要從數(shù)據(jù)庫中查詢信息，并以變量的形式（而不是結(jié)果集的形式）返回給調(diào)用處、或者是以結(jié)果集的形式返回，但并不能被調(diào)用處的語句所使用。我們可以使用return的數(shù)值來監(jiān)控存儲過程執(zhí)行得是否順利?！驹贘ava中使用“帶有參數(shù)的視圖”】我們沒法像使用視圖那樣從一個返回select結(jié)果集的存儲過程中進(jìn)行二度查詢，那么它究竟應(yīng)該如何使用，我們現(xiàn)在來舉一個例子：importjava.sql.*;classProgram{

publicstaticvoidmain(String[]args)throwsClassNotFoundException,SQLException{

//別忘了首先建立系統(tǒng)DSN，如：.\HYGJ

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connectioncon=DriverManager.getConnection("jdbc:odbc:Gohan","sa","111111");

Statementstmt=con.createStatement();

ResultSetrs=stmt.executeQuery("selectProc");while(rs.next())

System.out.println(rs.getString("xingming"));

con.close();

}}上面演示的是最基本的訪問實例。可以看到，當(dāng)這類存儲過程沒有參數(shù)的時候，調(diào)用的方法和一條普通select語句沒有什么區(qū)別。但是當(dāng)帶有參數(shù)的時候就不一樣了。不要著急，讓我們回到存儲過程的使用話題上面來。以后我們再學(xué)習(xí)Java如何調(diào)用帶參數(shù)的存儲過程。五、存儲過程的嵌套調(diào)用在一個存儲過程中還可以執(zhí)行另一個存儲過程，即嵌套調(diào)用。可以多次嵌套，但最多32層。可以用@@NESTLEVEL來查看嵌套當(dāng)前層數(shù)：createprocproc_1--獲取藝人id@yidintoutputas

selecttop1@yid=yirenidfromyiren

wherenicheng='芙蓉姐姐'

print'存儲過程1在第'+cast(@@NESTLEVELasvarchar)+'層'go

createprocproc_1_1--獲取粉絲id@fidintoutputas

declare@yidint

execproc_1@yidoutput

select@fid=yonghuidfromfensi

whereyirenid=@yid

orderbyyonghuiddesc

print'存儲過程1_1在第'+cast(@@NESTLEVELasvarchar)+'層'gocreateprocproc_1_1_1--獲得芙蓉姐姐的粉絲as

declare@fidint

execproc_1_1@fidoutput

select*fromyonghu

whereyonghuid=@fid

print'存儲過程1_1_1在第'+cast(@@NESTLEVELasvarchar)+'層'goexecproc_1_1_1go六、系統(tǒng)存儲過程系統(tǒng)存儲過程是SQLSERVER2005系統(tǒng)創(chuàng)建的存儲過程，其作用是方便查詢系統(tǒng)信息或完成系統(tǒng)管理任務(wù)。常用系統(tǒng)sp如下（更多內(nèi)容查閱聯(lián)機(jī)叢書）：sp_databases

列出服務(wù)器上的所有數(shù)據(jù)庫（無參數(shù)）sp_server_info

列出服務(wù)器信息（可以有參數(shù)）sp_stored_procedures

列出當(dāng)前環(huán)境中的所有存儲過程（可以有參數(shù)）sp_tables

返回當(dāng)前環(huán)境下可查詢的對象的列表（無參數(shù)）sp_configure

顯示或更改當(dāng)前服務(wù)器的全局配置設(shè)置sp_help

顯示有關(guān)數(shù)據(jù)庫對象的信息（可以有參數(shù)）如sp_helpyirensp_helpdb

顯示有關(guān)數(shù)據(jù)庫的信息（可以有參數(shù)）如sp_helpdbSuperStarsp_helptext

顯示規(guī)則、默認(rèn)值、未加密的存儲過程、用戶定義函數(shù)、觸發(fā)器

或視圖的文本（有參數(shù)）如sp_helptextproc_1sp_renamedb

重命名數(shù)據(jù)庫（有參數(shù)）如sp_renamedb'pubs','出版社'七、存儲過程的注意事項【存儲過程的優(yōu)點】只在創(chuàng)建時編譯，執(zhí)行速度快效率高減少網(wǎng)絡(luò)傳輸流量提高安全性模塊式編程，可以重復(fù)使用統(tǒng)一每次的操作流程【偷偷說一句】前面不管學(xué)什么都要提一下缺點，但是存儲過程這里卻沒提。這并不意味著存儲過程沒有任何缺點，畢竟任何東西都不能濫用，但是可以看出來，存儲過程的確是T-SQL編程的核心內(nèi)容，是最重要的部分。而它本身又是如此地容易掌握，相信你現(xiàn)在的心情不錯吧？【在存儲過程中使用事務(wù)】存儲過程中是可以使用事務(wù)的，這毫無疑問。在存儲過程中使用事務(wù)不必用goto語句，在rollback或者commit語句之后直接return即可終止存儲過程的執(zhí)行。前面我們也提到過：在普通批處理中實際上也是可以使用return語句的。八、使用存儲過程實現(xiàn)分頁查詢createprocqueryPage

@tablenamenvarchar(50),--用于傳入表名

@idnamenvarchar(50),--用于傳入字段名

@pagesizeint,--用于傳入每頁記錄數(shù)

@currentpageint,--用于傳入希望查看的頁面編號

@totalpagesintoutput--用于傳出頁面總數(shù)as--聲明保存查詢語句的局部變量：declare@sqlasnvarchar(1000)--聲明保存記錄總數(shù)的局部變量：declare@rowcountasint--獲得記錄總數(shù)：set@sql='select@rc=count(*)from'+@tablename--不要直接執(zhí)行select@rowcount=count(*)from@tablename--將參數(shù)傳入語句：execsp_executesql@sql,N'@rcintoutput',@rc=@rowcountoutput--將根據(jù)每頁的行數(shù)得到的總頁數(shù)保存到輸出參數(shù)中：set@totalpages=ceiling(cast(@rowcountasfloat)/cast(@pagesizeasfloat))if@currentpage>1begin

if@currentpage>@totalpages

begin

set@currentpage=@totalpages--則顯示最后一頁

end

set@sql='selecttop'+cast(@pagesizeasvarchar)

+'*from'+@tablename+'where'+@idname+'notin(selecttop'

+cast(@pagesize*(@current1)asvarchar)

+''+@idname+'from'+@tablename+'orderby'+@idname+')orderby'+@idnameendelse--只選第一頁就不必使用子查詢了，提高性能begin

set@sql='selecttop'+cast(@pagesizeasvarchar)

+'*from'+@tablename+'orderby'+@idnameendexec(@sql)--執(zhí)行查詢語句gosp_executesql這個系統(tǒng)存儲過程是另一種執(zhí)行sql語句的方法。它比exec(@sql)的功能高級一些。在這里我們?yōu)樗鼈魅?個參數(shù)，分別是等待處理的@sql（必須是nvarchar類型的）、使用字符串為@sql語句定義新的變量名、為字符串中定義的新變量賦值（@rc是字符串中定義的新變量，把它賦為@rowcount，又因為希望通過@rc為@rowcount返回值，所以指定為output）。ceiling函數(shù)得到大于某小數(shù)的最小整數(shù)，如ceiling(3.5)會得到4。這里把@rowcount和@pagesize相除，得到的數(shù)字是個小數(shù)。小數(shù)部分無法組成一個完整的分頁但是不代表沒有記錄，所以使用ceiling函數(shù)，讓@totalpages的值能夠正確賦值。當(dāng)傳入的currentpage并不在正確的分頁編號范圍內(nèi)時，我們的代碼做出了處理，給@currentpage賦了一個正確范圍內(nèi)的數(shù)值。調(diào)用上面的存儲過程：declare@totalpagesintexecqueryPage'yiren','yirenid',5,13,@totalpagesoutputprint'一共'+cast(@totalpagesasvarchar)+'行'游標(biāo)一、遇到的問題實際上，也不算什么太大的問題O(∩_∩)O：我們有時候可能希望在批處理或者存儲過程中直接對select結(jié)果集進(jìn)行加工，這個時候，我們需要一種能夠讓我們逐條處理每一行記錄的數(shù)據(jù)庫對象。二、游標(biāo)的概念解決上面的問題，我們可以使用一種叫做“游標(biāo)”的數(shù)據(jù)庫對象。游標(biāo)(Cursor)可以看做一種數(shù)據(jù)類型，它可以用來遍歷結(jié)果集，相當(dāng)于指針，或者是數(shù)組中的下標(biāo)。它處理結(jié)果集的方法有以下幾種：定位到結(jié)果集的某一行從當(dāng)前結(jié)果集的位置搜索一行或一部分行對結(jié)果集中的當(dāng)前行進(jìn)行數(shù)據(jù)修改三、游標(biāo)的使用方法（創(chuàng)建、打開、讀取、關(guān)閉、刪除）【創(chuàng)建游標(biāo)】和定義各種數(shù)據(jù)類型的方法有點像，但是注意，不要加“@”（實際上也有“游標(biāo)類型的變量”，和“游標(biāo)”的用法幾乎完全相同，而且定義時使用@符號）。下面是定義游標(biāo)的語句：declare游標(biāo)名cursor[local|global][forward_only|scroll]forselect查詢語句游標(biāo)分為局部游標(biāo)和全局游標(biāo)兩種，local表示局部游標(biāo)，global表示全局游標(biāo)（默認(rèn)值，可以省略）。當(dāng)指定forward_only（默認(rèn)值，可以省略）時，游標(biāo)是只進(jìn)的，也就是說只能從頭到尾地提取記錄，如果需要在行之間來回跳躍，需要指定為scroll?！臼褂糜螛?biāo)】只創(chuàng)建游標(biāo)但是不使用它，就沒有任何意義了。下面我們先舉個最簡單的例子來演示創(chuàng)建好游標(biāo)之后的幾步使用過程：--【創(chuàng)建游標(biāo)】declareC1cursorforselectxingmingfromyiren

declare@xingmingvarchar(20)

--【打開游標(biāo)】openC1--【讀取游標(biāo)】fetchnextfromC1into@xingming--while的特點就是要先寫一次

while(@@FETCH_STATUS=0)begin

print'姓名：'+@xingming

fetchnextfromC1into@xingmingend

--【關(guān)閉游標(biāo)】closeC1

--【刪除游標(biāo)】deallocateC1游標(biāo)的使用方法是不是和Java中的whle(rs.next()){}很像呢？實際上rs.next()執(zhí)行時就直接在結(jié)果集中向后移動一條了，如果沒有到達(dá)結(jié)果集的末端，仍然會執(zhí)行循環(huán)體。在這里使用游標(biāo)也是一樣，@@FETCH_STATUS的值為0時，游標(biāo)尚未走到結(jié)尾。當(dāng)它不為0了，游標(biāo)就走到了結(jié)尾，將退出循環(huán)。fetchnextfrom游標(biāo)名into變量名列表是一種固定形式的讀取游標(biāo)內(nèi)容的方法。當(dāng)查詢語句選擇了多個字段的時候，讀取時也需要借助這句話向多個變量賦值。于是寫成變量名列表?！救钟螛?biāo)和scroll游標(biāo)】前面提到全局游標(biāo)和scroll游標(biāo)，下面舉個例子：if(CURSOR_STATUS('global','CURSOR_2')!=-3)deallocateCURSOR_2declareCURSOR_2cursorscroll--全局的scroll游標(biāo)forselectxingming,nicheng,xingbiefromyiren--第一個T-SQL批開始o(jì)penCURSOR_2declare@seqint,@xingmingvarchar(20),@nichengvarchar(50),@xingbiencharset@seq=4fetchabsolute@seqfromCURSOR_2into@xingming,@nicheng,@xingbieif(@@FETCH_STATUS=0)begin

print'第'+cast(@seqasvarchar)+'個藝人是：'+@xingming

printcase@xingbiewhen'男'then'他'when'女'then'她'end+'的昵稱是：'+@nichengendcloseCURSOR_2go--第二個T-SQL批開始o(jì)penCURSOR_2declare@seqint,@xingmingvarchar(20),@nichengvarchar(50),@xingbiencharset@seq=5--分成了兩個批，需要再次定義@seqfetchabsolute@seqfromCURSOR_2into@xingming,@nicheng,@xingbieif(@@FETCH_STATUS=0)begin

print'第'+cast(@seqasvarchar)+'個藝人是：'+@xingming

printcase@xingbiewhen'男'then'他'when'女'then'她'end+'的昵稱是：'+@nichengendcloseCURSOR_2go--在第三個批中刪除游標(biāo)deallocateCURSOR_2當(dāng)開啟了scroll選項后，fetch可以用于讀next（后移）、prior（前移）、first（第一行）、last（最后一行）、absolute（以數(shù)值定位到絕對行）、relative（以數(shù)值定位到相對行）。全局游標(biāo)一旦被定義就會一直存在，所以每個批處理都能看到它。直到使用deallocate來刪除它，它才會消失。CURSOR_STATUS('global','CURSOR_2')可以檢查它的狀態(tài)。這個例子可以寫成存儲過程來調(diào)用，你試試吧。【游標(biāo)的嵌套】由于大大影響系統(tǒng)性能，簡單了解一下即可。if(CURSOR_STATUS('global','CURSOR_3')!=-3)deallocateCURSOR_3declareCURSOR_3cursorfor

selectyanchuidfromyanchu

openCURSOR_3declare@ycidintfetchnextfromCURSOR_3

into@ycidwhile(@@FETCH_STATUS=0)begin

print'參加第'+cast(@ycidasvarchar)+'次演出的藝人是：'

declareCURSOR_4cursorfor

selectxingmingfromyirenwhereyirenidin

(selectyirenidfromyanchuyirenwhereyanchuid=@ycid)

--這句使用了子查詢，實際上可以再嵌套一個游標(biāo)

declare@xingmingvarchar(50)

openCURSOR_4

fetchnextfromCURSOR_4into@xingming

while(@@FETCH_STATUS=0)

begin

print@xingming

fetchnextfromCURSOR_4into@xingming

end

closeCURSOR_4

deallocateCURSOR_4

fetchnextfromCURSOR_3

into@ycid

print''end