數(shù)據(jù)庫(kù)原理與應(yīng)用：第14章 存儲(chǔ)過(guò)程

數(shù)據(jù)庫(kù)原理與應(yīng)用第14章存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是SQL語(yǔ)句和可選控制流程語(yǔ)句的預(yù)編譯集合，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。存儲(chǔ)過(guò)程存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)，可由應(yīng)用程序通過(guò)一個(gè)調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。存儲(chǔ)過(guò)程可包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值。

存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)：

1、可以在單個(gè)存儲(chǔ)過(guò)程中執(zhí)行一系列SQL語(yǔ)句。

2、可以從自己的存儲(chǔ)過(guò)程內(nèi)引用其它存儲(chǔ)過(guò)程，這可以簡(jiǎn)化一系列復(fù)雜語(yǔ)句。

3、存儲(chǔ)過(guò)程在創(chuàng)建時(shí)即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來(lái)比單個(gè)SQL語(yǔ)句快。

14.1存儲(chǔ)過(guò)程的分類(1)系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程是由系統(tǒng)提供的存儲(chǔ)過(guò)程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲(chǔ)過(guò)程定義在系統(tǒng)數(shù)據(jù)庫(kù)master中，其前綴是sp_。(2)本地存儲(chǔ)過(guò)程本地存儲(chǔ)過(guò)程是指在用戶數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程完成用戶指定的數(shù)據(jù)庫(kù)操作，其名稱不能以sp_為前綴。

(3)臨時(shí)存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程屬于本地存儲(chǔ)過(guò)程。如果本地存儲(chǔ)過(guò)程的名稱前面有一個(gè)“#”，該存儲(chǔ)過(guò)程就稱為局部臨時(shí)存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程只能在一個(gè)用戶會(huì)話中使用。

(4)遠(yuǎn)程存儲(chǔ)過(guò)程遠(yuǎn)程存儲(chǔ)過(guò)程指從遠(yuǎn)程服務(wù)器上調(diào)用的存儲(chǔ)過(guò)程。(5)擴(kuò)展存儲(chǔ)過(guò)程在SQLServer環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)稱為擴(kuò)展存儲(chǔ)過(guò)程，其前綴是sp_。使用時(shí)需要先加載到SQLServer系統(tǒng)中，并且按照使用存儲(chǔ)過(guò)程的方法執(zhí)行。14.2用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行1.通過(guò)SQL命令創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程步驟如下：(1)定義如下存儲(chǔ)過(guò)程USEschoolGOCREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoGO(2)調(diào)用存儲(chǔ)過(guò)程EXECaGO14.2用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行1)創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)法格式：CREATEPROC[EDURE]procedure_name[;number]/*定義存儲(chǔ)過(guò)程名*/[{@parameterdata_type} /*定義參數(shù)的類型*/[VARYING][=default][OUTPUT]

] /*定義參數(shù)的屬性*/[,...n] [WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] /*定義存儲(chǔ)過(guò)程的處理方式*/[FORREPLICATION]ASsql_statements /*執(zhí)行的操作*/14.2用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行2)存儲(chǔ)過(guò)程的執(zhí)行通過(guò)EXEC命令可以執(zhí)行一個(gè)已定義的存儲(chǔ)過(guò)程。語(yǔ)法格式：[EXEC[UTE]]

[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]一、設(shè)計(jì)簡(jiǎn)單的存儲(chǔ)過(guò)程例14.1利用school數(shù)據(jù)庫(kù)中的school表、score表和course表，編寫(xiě)一無(wú)參存儲(chǔ)過(guò)程用于查詢所有學(xué)生的選修課程成績(jī)記錄。CREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoEXECa二、使用帶有參數(shù)的存儲(chǔ)過(guò)程例14.2編寫(xiě)一存儲(chǔ)過(guò)程，根據(jù)school數(shù)據(jù)庫(kù)的student、score、course三個(gè)表查詢指定學(xué)生的選修課程成績(jī)情況。CREATEPROCEDUREb@snochar(5)ASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDstudent.sno=@snoEXECb'101'三、帶有通配符參數(shù)的存儲(chǔ)過(guò)程例14.3利用shool數(shù)據(jù)庫(kù)中student表、score、course表創(chuàng)建一存儲(chǔ)過(guò)程c，查詢指定課程名的選修情況。該存儲(chǔ)過(guò)程在參數(shù)中使用了模糊查詢，如果沒(méi)有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。CREATEPROCEDUREc@cnamevarchar(16)='%計(jì)算機(jī)%'ASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDcnameLIKE@cnameEXECcEXECc'操作%'EXECc'%計(jì)%'四、帶有OUTPUT參數(shù)的存儲(chǔ)過(guò)程例14.4編寫(xiě)一存儲(chǔ)過(guò)程，統(tǒng)計(jì)指定課程選修人數(shù)，存儲(chǔ)過(guò)程中使用了輸入和輸出參數(shù)。CREATEPROCEDUREm@cnamechar(16),@numintoutputASSELECT@num=COUNT(sno)FROMscore,courseWHEREscore.cno=course.cnoANDcnameLIKE@cnameDECLARE@numintEXECm'操作系統(tǒng)',@numoutputSELECT@numAS'選修次數(shù)'五、帶有OUTPUT游標(biāo)參數(shù)的存儲(chǔ)過(guò)程例14.5在school數(shù)據(jù)庫(kù)的student表上聲明并打開(kāi)一個(gè)游標(biāo)。CREATEPROCEDUREstudent_cursor@student_curCURSORVARYINGOUTPUTASSET@student_cur=CURSORFORWARD_ONLYSTATICFORSELECTsno,sname,ssex,classFROMstudentOPEN@student_curDECLARE@mycursorCURSOREXECstudent_cursor@student_cur=@mycursorOUTPUTFETCHNEXTFROM@mycursorWHILE@@FETCH_STATUS=0FETCHNEXTFROM@mycursorCLOSE@mycursorDEALLOCATE@mycursor六、使用WITHENCRYPTION選項(xiàng)例14.6創(chuàng)建加密過(guò)程（使用sp_helptext系統(tǒng)存儲(chǔ)過(guò)程獲取關(guān)于加密的存儲(chǔ)過(guò)程的信息）。CREATEPROCEDUREencryption_thisWITHENCRYPTIONASSELECT*FROMstudentEXECsp_helptext'encryption_this'七、創(chuàng)建用戶定義的系統(tǒng)存儲(chǔ)過(guò)程例14.7創(chuàng)建存儲(chǔ)過(guò)程sp_showtable,顯示以student開(kāi)頭的所有表名及其對(duì)應(yīng)的索引名。如果沒(méi)有指定參數(shù)，該存儲(chǔ)過(guò)程將返回以course開(kāi)頭的所有表名及對(duì)應(yīng)的索引名。CREATEPROCEDUREsp_showtable@tablevarchar(20)='course%'ASSELECTsysobjects.name,sysindexes.nameFROMsysobjects,sysindexesWHEREsysobjects.id=sysindexes.idANDsysobjects.nameLIKE@tableANDindid<>0ANDindid<>255EXECsp_showtableEXECsp_showtable'student%'14.2用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行2.利用企業(yè)管理器創(chuàng)建用戶存儲(chǔ)過(guò)程(1)在SQLServer

企業(yè)管理器的目錄樹(shù)中，選中school數(shù)據(jù)庫(kù)文件夾下的存儲(chǔ)過(guò)程圖標(biāo)右擊，出現(xiàn)一快捷菜單，選擇菜單項(xiàng)“新建存儲(chǔ)過(guò)程”，進(jìn)入新建存儲(chǔ)過(guò)程窗口，如圖所示；(2)在新建存儲(chǔ)過(guò)程窗口輸入定義的存儲(chǔ)過(guò)程，然后選擇“確定”按鈕。14.3用戶存儲(chǔ)過(guò)程的編輯修改使用ALTERPROCEDURE命令可修改已存在的存儲(chǔ)過(guò)程。語(yǔ)法格式：ALTERPROC[EDURE]procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT]][,...n][WITH

{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}][FORREPLICATION]AS

sql_statements14.4用戶存儲(chǔ)過(guò)程的刪除

如果確認(rèn)一個(gè)數(shù)據(jù)庫(kù)的某個(gè)