第14章存儲(chǔ)過程存儲(chǔ)過程概念存儲(chǔ)過程操作14存儲(chǔ)過程14.1存儲(chǔ)過程的作用14.2存儲(chǔ)過程的類型14.3創(chuàng)建存儲(chǔ)過程14.4執(zhí)行存儲(chǔ)過程14.5修改存儲(chǔ)過程14.6刪除存儲(chǔ)過程14.1存儲(chǔ)過程的作用存儲(chǔ)過程是一組完成特定功能的SQL語句集合，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫中。存儲(chǔ)過程作為一個(gè)單元進(jìn)行處理并以一個(gè)名稱來標(biāo)識(shí)。它能向用戶返回?cái)?shù)據(jù)、向數(shù)據(jù)庫表中寫入或修改數(shù)據(jù)、執(zhí)行系統(tǒng)函數(shù)和進(jìn)行管理操作。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行它。存儲(chǔ)過程的作用執(zhí)行速度快。采用模塊化程序設(shè)計(jì)。減少網(wǎng)絡(luò)通信量。保證系統(tǒng)的安全性。14.2存儲(chǔ)過程的類型內(nèi)建存儲(chǔ)過程目錄存儲(chǔ)過程：用來顯示數(shù)據(jù)庫對(duì)象的信息，如數(shù)據(jù)庫內(nèi)建哪些表、表內(nèi)哪些列。常用的有sp_helpdb、sp_help、sp_helptext等存儲(chǔ)過程。系統(tǒng)存儲(chǔ)過程：用來處理SQLServer2008的系統(tǒng)管理工作，如新建用戶、設(shè)置選項(xiàng)、設(shè)置密碼等。常用的有sp_adduser、sp_dboption、sp_password等存儲(chǔ)過程。復(fù)制存儲(chǔ)過程：用來處理有關(guān)數(shù)據(jù)庫復(fù)制方面的事務(wù)。事務(wù)存儲(chǔ)過程：用來進(jìn)行創(chuàng)建系統(tǒng)工作，如安排數(shù)據(jù)庫復(fù)制、安排工作執(zhí)行的時(shí)間或系統(tǒng)警告信息等.擴(kuò)充存儲(chǔ)過程：用于加載和執(zhí)行DLL動(dòng)態(tài)鏈接庫，擴(kuò)充存儲(chǔ)過程名稱通常以“xp_”為前綴。14.2存儲(chǔ)過程的類型本地存儲(chǔ)過程永久存儲(chǔ)過程：在當(dāng)前數(shù)據(jù)庫中創(chuàng)建，存儲(chǔ)過程名存儲(chǔ)于當(dāng)前數(shù)據(jù)庫的系統(tǒng)表sysobject中，文本保存在數(shù)據(jù)庫的系統(tǒng)注釋表中。臨時(shí)存儲(chǔ)過程：如果在存儲(chǔ)過程名稱之前加上“#”或“##”前綴，代表此存儲(chǔ)過程是一臨時(shí)保存的存儲(chǔ)過程，這些存儲(chǔ)過程創(chuàng)建在tempdb數(shù)據(jù)庫中，與所有臨時(shí)對(duì)象一樣在SQLServer2008結(jié)束運(yùn)行時(shí)會(huì)被刪除。14.3創(chuàng)建存儲(chǔ)過程CREATEPROCEDUREprocedure_name[;number]

/*定義過程名*/[{@parameter_namedata_type}[VARYING][=default][OUTPUT]][,…n]

/*參數(shù)聲明*/ [WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]/*定義存儲(chǔ)過程的處理方式*/ [FORREPLICATION] ASsql_statement

/*執(zhí)行的操作*/【例14.2】編寫一個(gè)程序，創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過程stud_degree，用于檢索所有學(xué)生的成績(jī)記錄。解：對(duì)應(yīng)的程序如下：USEschoolGO--若存在存儲(chǔ)過程stud_degree，則刪除之IFEXISTS(SELECT*FROMsysobjectsWHEREname='stud_degree'ANDtype='P')DROPPROCEDUREstud_degreeGO --注意，CREATEPROCEDURE必須是一個(gè)批處理的第一個(gè)語句，故此GO不能缺--創(chuàng)建存儲(chǔ)過程stud_degreeCREATEPROCEDUREstud_degreeASSELECTstudent.學(xué)號(hào),student.姓名,course.課程名,score.分?jǐn)?shù)

FROMstudent,course,scoreWHEREstudent.學(xué)號(hào)=score.學(xué)號(hào)ANDcourse.課程號(hào)=score.課程號(hào)

ORDERBYstudent.學(xué)號(hào)GO對(duì)比函數(shù)的定義USEschoolGOIFEXISTS(SELECT*FROMsysobjects_--如果存在這樣的函數(shù)則刪除之

WHEREname='CubicVolume'ANDtype='FN')DROPFUNCTIONCubicVolumeGOCREATEFUNCTIONCubicVolume(@CubeLengthdecimal(4,1),@CubeWidthdecimal(4,1),--輸入?yún)?shù)

@CubeHeightdecimal(4,1))RETURNSdecimal(12,3)--返回立方體的體積

--返回單個(gè)值，這是標(biāo)量值函數(shù)的特征ASBEGINRETURN(@CubeLength*@CubeWidth*@CubeHeight)END把上例改成存儲(chǔ)過程？創(chuàng)建存儲(chǔ)過程中不能包含的語句CREATEAGGREGATECREATEDEFAULTCREATEPROCEDURECREATERULECREATESCHEMACREATETRIGGERCREATEVIEWSETPARSEONLYSETSHOWPLAN_TEXTSETSHOWPLAN_XMLSETSHOWPLAN_ALLUSEDATABASE14.4執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程使用EXECUTE語句，其格式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,…n]各個(gè)參數(shù)的含義@return_status保存存儲(chǔ)過程的返回狀態(tài)procedure_name調(diào)用的存儲(chǔ)過程的名稱@parameter過程參數(shù)Value過程參數(shù)的值@variable用來保存參數(shù)或返回參數(shù)的變量OUTPUT指定存儲(chǔ)過程必須返回的一個(gè)參數(shù)DEFAULT默認(rèn)參數(shù)值在調(diào)用存儲(chǔ)過程時(shí)的兩種傳遞參數(shù)的方式：方式一：

EXEC存儲(chǔ)過程名實(shí)參列表方式二：

EXEC存儲(chǔ)過程名實(shí)參1=值1,實(shí)參2=值2,…14.4執(zhí)行存儲(chǔ)過程舉例說明

存儲(chǔ)過程的輸入?yún)?shù)和返回值使用參數(shù)使用默認(rèn)參數(shù)使用返回參數(shù)存儲(chǔ)過程的返回值編寫一個(gè)程序，創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過程stud_degree，用于檢索所有學(xué)生的成績(jī)記錄。USEschoolGO--若存在存儲(chǔ)過程stud_degree，則刪除之IFEXISTS(SELECT*FROMsysobjectsWHEREname='stud_degree'ANDtype='P')DROPPROCEDUREstud_degreeGO --注意，CREATEPROCEDURE必須是一個(gè)批處理的第一個(gè)語句，故此GO不能缺

--創(chuàng)建存儲(chǔ)過程stud_degreeCREATEPROCEDUREstud_degreeASSELECTstudent.學(xué)號(hào),student.姓名,course.課程名,score.分?jǐn)?shù)

FROMstudent,course,scoreWHEREstudent.學(xué)號(hào)=score.學(xué)號(hào)ANDcourse.課程號(hào)=score.課程號(hào)

ORDERBYstudent.學(xué)號(hào)GO【例1】創(chuàng)建沒有輸入?yún)?shù)的存儲(chǔ)過程。設(shè)計(jì)一個(gè)存儲(chǔ)過程maxno，以學(xué)號(hào)為參數(shù)，輸出指定學(xué)號(hào)學(xué)生的所有課程中最高分和對(duì)應(yīng)的課程名。USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='maxno'ANDtype='P')DROPPROCEDUREmaxnoGOCREATEPROCEDUREmaxno(@nochar(10))AS/*聲明no為參數(shù)*/SELECTs.學(xué)號(hào),s.姓名,c.課程名,sc.分?jǐn)?shù)

FROMstudents,coursec,scorescWHEREs.學(xué)號(hào)=@noANDs.學(xué)號(hào)=sc.學(xué)號(hào)ANDc.課程號(hào)=sc.課程號(hào)ANDsc.分?jǐn)?shù)=(SELECTMAX(分?jǐn)?shù))FROMscoreWHERE學(xué)號(hào)=@no)GO【例2】

創(chuàng)建用輸入?yún)?shù)的存儲(chǔ)過程采用第1種方式執(zhí)行存儲(chǔ)過程maxno的程序如下：USEschoolGOEXECmaxno'103'GO采用第2種方式執(zhí)行存儲(chǔ)過程maxno的程序如下：USEschoolGOEXECmaxno@no='103'GO例2的調(diào)用方法設(shè)計(jì)類似例2功能的存儲(chǔ)過程maxno1，指定其默認(rèn)學(xué)號(hào)為'101'。USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='maxno1'ANDtype='P')DROPPROCEDUREmaxno1GOCREATEPROCEDUREmaxno1(@noint='101')AS/*聲明no為參數(shù)*/SELECTs.學(xué)號(hào),s.姓名,c.課程名,sc.分?jǐn)?shù)

FROMstudents,coursec,scorescWHEREs.學(xué)號(hào)=@noANDs.學(xué)號(hào)=sc.學(xué)號(hào)ANDc.課程號(hào)=sc.課程號(hào)ANDsc.分?jǐn)?shù)=(SELECTMAX(分?jǐn)?shù))FROMscoreWHERE學(xué)號(hào)=@no)GO【例3】

為例2存儲(chǔ)過程輸入?yún)?shù)加一個(gè)默認(rèn)值當(dāng)不指定實(shí)參調(diào)用maxno1存儲(chǔ)過程時(shí)，其結(jié)果如圖14.5所示。當(dāng)指定實(shí)參為'105'調(diào)用maxno1存儲(chǔ)過程時(shí)，其結(jié)果如圖14.6所示。結(jié)論：從執(zhí)行結(jié)果可以看到，當(dāng)調(diào)用存儲(chǔ)過程時(shí)，沒有指定參數(shù)值時(shí)就自動(dòng)使用相應(yīng)的默認(rèn)值。例3執(zhí)行結(jié)果創(chuàng)建一個(gè)存儲(chǔ)過程average，它返回兩個(gè)參數(shù)@st_name和＠st_avg，分別代表了姓名和平均分。USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='average'ANDtype='P')DROPPROCEDUREaverageGOCREATEPROCEDUREaverage( @st_noint, @st_namechar(8)OUTPUT, /*返回參數(shù)*/ @st_avgfloatOUTPUT /*返回參數(shù)*/)ASSELECT@st_name=student.姓名,@st_avg=AVG(score.分?jǐn)?shù))FROMstudent,scoreWHEREstudent.學(xué)號(hào)=score.學(xué)號(hào)

GROUPBYstudent.學(xué)號(hào),student.姓名

HAVINGstudent.學(xué)號(hào)=@st_noGO例4創(chuàng)建有返回值的存儲(chǔ)過程執(zhí)行該存儲(chǔ)過程，來查詢學(xué)號(hào)為“105”的學(xué)生姓名和平均分：DECLARE@st_namechar(10)DECLARE@st_avgfloatEXECaverage'105',@st_nameOUTPUT,@st_avgOUTPUTSELECT'姓名'=@st_name,'平均分'=@st_avgGO例4執(zhí)行結(jié)果14.5修改存儲(chǔ)過程使用ALTERPROCEDURE語句可以更改先前通過執(zhí)行CREATEPROCEDURE語句創(chuàng)建的過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器