9.4SQLServer存儲過程本章主要內(nèi)容：（1）SQLServer概述（2）

SQLServer中，SQL語句的使用（4）掌握SQLServer存儲過程的編程（5）掌握SQLServer觸發(fā)器的編程（6）SQLServer設(shè)置每天定時自動做數(shù)據(jù)庫備份（3）SQLServer編程基礎(chǔ)，包括游標對象Cursor的使用9.4.1存儲過程的創(chuàng)建及調(diào)用表9-9創(chuàng)建存儲過程語法結(jié)構(gòu)If(exists(select*fromsys.objectswherename='cp_name'))/*若指定的存儲過程cp_name存在*/Dropprocedurecp_name/*刪除存儲過程cp_name*/GoCreateprocedurecp_name@param_nameparam_type[=default_value][out]AsBeginSQL語句塊[Return@return_value]End9.4.1存儲過程的創(chuàng)建及調(diào)用幾點說明：（1）sys.objects為當前數(shù)據(jù)庫的系統(tǒng)表，它記錄了當前數(shù)據(jù)庫中的所有數(shù)據(jù)對象。

（2）存儲過程可以沒有參數(shù)，也可以有多個輸入?yún)?shù)，參數(shù)之間用逗號隔開，定義輸入?yún)?shù)時，可以指定初始值，參數(shù)的數(shù)據(jù)類型可以是SQLServer支持的任何數(shù)據(jù)類型。其中out表示為輸出參數(shù)。

（3）存儲過程可以沒有返回值。如果有，可以用“Return”語句，指定存儲過程的返回值。

（4）調(diào)用存儲過程的方法為：exec存儲過程名。9.4.1存儲過程的創(chuàng)建及調(diào)用例9-5編寫一段程序，創(chuàng)建一個簡單的存儲過程，查詢學(xué)生的學(xué)號、姓名、性別。例9-5編寫一個簡單的存儲過程，查詢學(xué)生的學(xué)號、姓名、性別

9.4.2帶輸出參數(shù)的存儲過程帶輸出參數(shù)的存儲過程，定義時要標明“out或output”，調(diào)用時，要標明“output”。例9-6編寫一個帶有輸入?yún)?shù)、輸出參數(shù)的存儲過程，然后再調(diào)用它。

基本思路：下面程序，創(chuàng)建了一個名為cp_sc2的存儲過程，輸入?yún)?shù)為學(xué)號：@sNo，輸出參數(shù)為該學(xué)生選修課程的門數(shù)：@iCount。調(diào)用時，輸出參數(shù)要加上“output”。例9-6帶有輸入?yún)?shù)、輸出參數(shù)的存儲過程，然后再調(diào)用它。123456789Createprocedurecp_sc2@sNochar(8),@iCountintoutas/*-------------輸入?yún)?shù)：@sNo為學(xué)號，輸出參數(shù)：@iCount為選課記錄數(shù)-------------*/BeginSelect@iCount=count(*)fromscwheresNo=@sNo/*該學(xué)號的選課門數(shù)*/End

declare@yint/*聲明一個局部變量*/exec

cp_sc2'01071102',@youtput/*調(diào)用存儲過程，其中@y為輸出變量*/Print'選課門數(shù)：'+str(@y)/*打印輸出變量的值*/

9.4.3帶Return返回語句的存儲過程

在T-SQL中，存儲過程遇到Return語句，會立即返回，不再執(zhí)行Return后面的程序。Return的右邊可以帶一個返回值，該返回值只能為一個整數(shù)值，表示存儲過程的執(zhí)行狀態(tài)。存儲過程中可以沒有Return語句。此時，默認情況下，返回值0表示存儲過程執(zhí)行成功，-1表示存儲過程執(zhí)行中出現(xiàn)錯誤。存儲過程中也可以出現(xiàn)多個Return語句?？梢酝ㄟ^指定不同的返回值，標識存儲過程不同的執(zhí)行狀態(tài)。調(diào)用時，獲得存儲過程的返回值的語法為：

Declare@iReturnintExec@iReturn=存儲過程名

9.4.3帶Return返回語句的存儲過程例如，例9-6的存儲過程cp_sc2中沒有Return語句，但調(diào)用時，可以獲得其返回值，方法如下，結(jié)果如圖9-29所示。1234declare@yint/*聲明一個局部變量*/declare@iReturnintexec@iReturn=cp_sc2'01071102',@youtput/*調(diào)用存儲過程，其中@y為輸出變量*/select'返回值：',@iReturn/*打印返回值*/

9.4.3帶Return返回語句的存儲過程例9-7編寫一個帶有Return語句的存儲過程，然后再調(diào)用它。123456789101112Createprocedurecs_insert@sNochar(8),@sNamevarchar(30),@sexchar(1)AsBegindeclare@iReturnintsetnocounton/*不顯示SQL語句執(zhí)行的結(jié)果*/InsertintoS(sNo,sName,sex,dtBirthdate)values(@sNo,@sName,@sex,‘2001-01-06’)set@iReturn=@@error/*獲取執(zhí)行SQL語句的錯誤號*/if@iReturn<>0/*@@error!=0表示發(fā)生意外*/beginRaisError(‘插入記錄失敗!’,16,1)/*函數(shù)RaisError用于拋出一個自定義錯誤*/endreturn@iReturnEnd

基本思路：下面程序，創(chuàng)建了一個名為cp_insert的存儲過程，輸入?yún)?shù)為@sNo（學(xué)號）、，@sName（姓名）、@sex（姓名），返回值為@iReturn（執(zhí)行SQL語句的錯誤號）。

9.4.3帶Return返回語句的存儲過程例9-7編寫一個帶有Return語句的存儲過程，然后再調(diào)用它。第一次按下面方式執(zhí)行該存儲過程，是成功的。第二次執(zhí)行時，出現(xiàn)錯誤，原因是“學(xué)號重復(fù)”，如圖9-30所示。

9.4.4存儲過程綜合案例

例9-8編寫一個存儲過程，給定一串漢字，生成并輸出對應(yīng)的拼音碼。

基本思路：利用例9-2中的漢字編碼表，再根據(jù)本章數(shù)據(jù)附件“例9-8insertSql_Chinese.txt”中的插入語句，生成其中的6836個漢字的拼音碼。然后利用這6836個漢字的拼音碼，給出一串漢字，依次從左邊開始，每次取單個漢字，查詢該漢字的拼音碼，再迭加輸出。123456789Createprocedurecs_ChineseCode@strNameaschar(60)AS/*------------------------------------------------------------------------------------------------------------------

生成參數(shù)：@strName（一串漢字）的拼音碼

@vcPYCode------------------------------------------------------------------------------------------------------------------*/Begindeclare@cChinesevarchar(2)/*單個漢字*/declare@cPYchar(1)/*單個漢字的拼音*/declare@iForasint=1/*循環(huán)計數(shù)器*/declare@iLengthasint/*一串漢字的長度，即：漢字的個數(shù)*/

例9-8編寫一個存儲過程，給定一串漢字，生成并輸出對應(yīng)的拼音碼。1011121314151617181920212223declare@vcPYCodeasvarchar(50)=''/*一串漢字的拼音碼*/

set@strName=ltrim(rtrim(@strName))/*消除參數(shù)左右空格*/set@iLength=Len(@strName)/*獲取參數(shù)中，字符的個數(shù)*/setnocounton/*執(zhí)行SQL語句，屏蔽執(zhí)行提示*/While@iFor<=@iLength/*從1開始，若循環(huán)計數(shù)器不大于字符的個數(shù)*/

Begin/*-------------------------------------開始循環(huán)-----------------------------------------------*/set@cChinese=Substring(@strName,@iFor,1)/*從左邊開始，依次取單個字符*/

select@cPY=isnull(cPY,'')fromsmchinesewherecChineseName=@cChinese

set@vcPYCode=ltrim(rtrim(@vcPYCode))+@cPY/*將單個漢字的拼音碼依次累加*/select@cChinese,@cPY/*測試：每循環(huán)一次，輸出單個漢字，及拼音碼*/set@iFor=@iFor+1/*計數(shù)器加1*/

End/*--------------------------------------結(jié)束循環(huán)-----------------------------------------------*/print@vcPYCode/*輸出：@strName對應(yīng)的拼音碼*/End調(diào)用存儲過程：execcs_ChineseCode'中華人民共和國'

輸出：ZHRMGHG

9.4.4存儲過程綜合案例例9-9根據(jù)例9-2中的圖書信息表：book（cBookNo,vcBookName,vcPYcode），編寫一個存儲過程，將所有圖書，根據(jù)圖書名稱，修改對應(yīng)的拼音碼（vcPYcode）?；舅悸罚合榷x一個游標，對應(yīng)的查詢語句為：

SelectcBookNo,vcBookNamefrombook然后依次從游標中讀取書名，再由書名生成對應(yīng)的拼音碼，進行數(shù)據(jù)更新。123456789CreateProcedurecp_book_PYcodeAs/*---------生成并修改book中的vcPYcode-------*/Begindeclare@vcBookNamevarchar(60)/*圖書名稱*/declare@cBookNochar(8)/*圖書編號*/declare@cChinesevarchar(2)/*單個漢字*/declare@cPYchar(1)/*單個漢字的拼音*/declare@iForint=1/*循環(huán)計數(shù)器*/declare@iLengthint/*圖書名稱的長度，即漢字的個數(shù)*/declare@vcPYCodevarchar(50)=''/*圖書名稱的拼音碼*/例9-9book（cBookNo,vcBookName,vcPYcode），生成書名的拼音碼1011121314151617181920212223declare@iRowsint/*游標中的記錄條數(shù)*/declare@iErrorint=0setnocounton/*執(zhí)行SQL語句，屏蔽執(zhí)行提示*/

Begintransaction/*開始事務(wù)*/declarecursorTempScrollcursorfor/*定義游標*/SelectcBookNo,ltrim(rtrim(vcBookName))frombook

opencursorTemp/*打開游標*/set@iRows=@@cursor_rows/*獲取游標中記錄行數(shù)*/while@iRows>0beginfetchnextfromcursorTempinto@cBookNo,@vcBookName/*獲取游標當前字段值*/set@iLength=Len(@vcBookName)/*圖書名稱的長度*/set@vcPYCode=''set@iFor=1例9-9book（cBookNo,vcBookName,vcPYcode），生成書名的拼音碼2425262728293031323334353637383940while@iFor<=@iLength/*從1開始，若循環(huán)計數(shù)器不大于圖書名稱的長度*/beginset@cChinese=Substring(@vcBookName,@iFor,1)/*從左邊開始,依次取單個字符*/

select@cPY=isnull(cPY,'')fromsmchinesewherecChineseName=@cChinese

set@vcPYCode=ltrim(rtrim(@vcPYCode))+@cPY/*將單個漢字的拼音碼依次累加*/set@iFor=@iFor+1/*計數(shù)器加1*/endselect@vcBookName,@vcPYCode/*測試：輸出書名，及拼音碼*/

updatebooksetvcPYCode=@vcPYCodewherecBookNo=@cBookNo/*修改拼音碼*/set@iError=@@error