數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)4存儲(chǔ)過(guò)程

多驗(yàn)報(bào)書(shū)課程名稱 數(shù)據(jù)庫(kù)原理 實(shí)驗(yàn)序號(hào) 4 實(shí)驗(yàn)項(xiàng)目 存儲(chǔ)過(guò)程 實(shí)驗(yàn)地點(diǎn) 實(shí)驗(yàn)學(xué)時(shí)實(shí)驗(yàn)類型 驗(yàn)證性指導(dǎo)教師實(shí)驗(yàn)員專業(yè)—班級(jí)學(xué)號(hào)—姓名年月日成績(jī)：A該學(xué)生這一次的實(shí)驗(yàn)報(bào)告進(jìn)步很大，實(shí)驗(yàn)步驟清晰，過(guò)程符合要求，實(shí)驗(yàn)結(jié)果正確，教分析和心得一目了然，完整地表達(dá)了通過(guò)實(shí)驗(yàn)所獲取的知識(shí)。質(zhì)量較高。師評(píng)語(yǔ)一、實(shí)驗(yàn)?zāi)康募耙?、 了解存儲(chǔ)過(guò)程的概念；2、 掌握系統(tǒng)存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程。3、 掌握存儲(chǔ)過(guò)程的創(chuàng)建、執(zhí)行方法，并學(xué)會(huì)對(duì)存儲(chǔ)過(guò)程進(jìn)行查看、修改和刪除。二、實(shí)驗(yàn)原理與內(nèi)容1、 存儲(chǔ)過(guò)程的概念存儲(chǔ)過(guò)程是存儲(chǔ)于數(shù)據(jù)庫(kù)中的一組T-SQL語(yǔ)句。可將常用的或很復(fù)雜的工作，預(yù)先用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定的名稱存儲(chǔ)起來(lái)，那么以后要叫數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí)，只需調(diào)用execute，即可自動(dòng)完成命令。存儲(chǔ)過(guò)程是一種數(shù)據(jù)庫(kù)對(duì)象，可以包含程序流、邏輯控制以及對(duì)數(shù)據(jù)庫(kù)的查詢，可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及狀態(tài)集，并可重用或嵌套。2、 系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程是由SQLServe系統(tǒng)創(chuàng)建的存儲(chǔ)過(guò)程，用戶可直接使用。系統(tǒng)存儲(chǔ)過(guò)程存儲(chǔ)在master數(shù)據(jù)庫(kù)中，以“sp_”開(kāi)頭命名。系統(tǒng)存儲(chǔ)過(guò)程主要用于系統(tǒng)管理、用戶登錄管理、權(quán)限設(shè)置、數(shù)據(jù)庫(kù)對(duì)象管理、數(shù)據(jù)復(fù)制等操作3、 常用的系統(tǒng)存儲(chǔ)過(guò)程p_help:報(bào)告有關(guān)數(shù)據(jù)庫(kù)對(duì)象（sysobjects表中列出的任何對(duì)象）、用戶定義數(shù)據(jù)類型或系統(tǒng)所提供的數(shù)據(jù)類型的信息。sp_addlogin：創(chuàng)建新的SQLServer登錄，該登錄允許用戶使用SQLServer身份驗(yàn)證連接到SQLServer實(shí)例。Sp_password：為MicrosoftSQLServer登錄名添加或更改密碼。sp_cursor_list：報(bào)告當(dāng)前為連接打開(kāi)的服務(wù)器游標(biāo)的屬性。Sp_adduser：向當(dāng)前數(shù)據(jù)庫(kù)中添加新的用戶。sp_addrole:在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建新的數(shù)據(jù)庫(kù)角色。sp_addrolemember：為當(dāng)前數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)角色添加數(shù)據(jù)庫(kù)用戶、數(shù)據(jù)庫(kù)角色、Windows登錄或Windows組。sp_droplogin：刪除SQLServer登錄名。這樣將阻止使用該登錄名對(duì)SQLServer實(shí)例進(jìn)行訪問(wèn)。sp_dropuser：從當(dāng)前數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)庫(kù)用戶。sp_droprole:從當(dāng)前數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)庫(kù)角色。spdroprolemember：從當(dāng)前數(shù)據(jù)庫(kù)的SQLServer角色中刪除安全帳戶。sp_addtype:創(chuàng)建別名數(shù)據(jù)類型。4、 用戶自定義存儲(chǔ)過(guò)程數(shù)據(jù)庫(kù)用戶可根據(jù)某一特定功能的需要，在用戶數(shù)據(jù)庫(kù)中由用戶創(chuàng)建的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程命名時(shí)不能以“sp_”開(kāi)頭。5、 存儲(chǔ)過(guò)程的創(chuàng)建CREATEPROCEDUREprocedure_name[;number][(^parameterdata_type}[VARYING][=default][OUTPUT]][,???][WITH(RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement語(yǔ)法說(shuō)明procedure_name：新創(chuàng)建的存儲(chǔ)過(guò)程名稱，過(guò)程名必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。;number：是可選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條DROPPROCEDURE語(yǔ)句即可將同組的過(guò)程一起除去。^parameter：過(guò)程中的參數(shù)。在CREATEPROCEDURE語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值(除非定義了該參數(shù)的默認(rèn)值，或者該值設(shè)置為等于另一個(gè)參數(shù))。使用@符號(hào)作為第一個(gè)字符來(lái)指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則。存儲(chǔ)過(guò)程最多可有2100個(gè)參數(shù)。data_type:參數(shù)的數(shù)據(jù)類型。除table之外的其他所有數(shù)據(jù)類型均可以用作存儲(chǔ)過(guò)程的參數(shù)。但是，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定cursor數(shù)據(jù)類型，則還必須指定VARYING和OUTPUT關(guān)鍵字。對(duì)于可以是cursor數(shù)據(jù)類型的輸出參數(shù)，沒(méi)有最大數(shù)目的限制。VARYING:指定作為輸出參數(shù)支持的結(jié)果集，僅適用于游標(biāo)參數(shù)。default:參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，則不必指定該參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或NULL。OUTPUT:表明參數(shù)是返回參數(shù)，可將信息返回給調(diào)用過(guò)程。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：RECOMPILE表明SQLServer不會(huì)緩存該過(guò)程的被引用的對(duì)象，該過(guò)程將在運(yùn)行時(shí)重新編譯。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE語(yǔ)句文本的條目。AS：指定過(guò)程要執(zhí)行的操作。sql_statement:過(guò)程中要包含的任意數(shù)目和類型的Transact-SQL語(yǔ)句。但有一些限制。6、 存儲(chǔ)過(guò)程的執(zhí)行存儲(chǔ)過(guò)程一般不會(huì)自動(dòng)執(zhí)行，用戶可使用EXECUTE命令來(lái)直接執(zhí)行存儲(chǔ)過(guò)程。執(zhí)行存儲(chǔ)過(guò)程必須具有執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。如果存儲(chǔ)過(guò)程是批處理中的第一個(gè)語(yǔ)句，EXECUTE可以省略。EXEC[UTE]][@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][，…][WITHRECOMPILE]語(yǔ)法說(shuō)明只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。@return_status=：是一個(gè)可選的整形變量，用來(lái)保存存儲(chǔ)過(guò)程的返回狀態(tài)。該變量在用于EXECUTE語(yǔ)句之前必須在批處理、存儲(chǔ)過(guò)程或函數(shù)中聲明。procedure_name：要調(diào)用的存儲(chǔ)過(guò)程名稱。;number：可選的整數(shù)，具體含義同CREATEPROCEDURE中的;number。@procedure_name_var：是局部定義變量名，代表存儲(chǔ)過(guò)程名稱。@parameter，value:是過(guò)程參數(shù)及其值。@variable:用來(lái)保存參數(shù)或返回參數(shù)的變量。OUTPUT:指定存儲(chǔ)過(guò)程必須返回一個(gè)參數(shù)。DEFAULT：根據(jù)過(guò)程的定義，提供參數(shù)的默認(rèn)值。WITHRECOMPILE:可強(qiáng)制重新編譯存儲(chǔ)過(guò)程代碼，但消耗較多的系統(tǒng)資源。7、存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的有關(guān)信息以及創(chuàng)建存儲(chǔ)的文本均被存儲(chǔ)在SQLServer數(shù)據(jù)庫(kù)中的系統(tǒng)表sysobjects和syscomments中，通過(guò)SELECT語(yǔ)句可直接查看存儲(chǔ)過(guò)程的定義（1） 存儲(chǔ)過(guò)程的查看electsysobjects.id,syscomments.textfromsysobjects,syscommentswheresysobjects.id=syscomments.idandsysobjects.type='P'andsysobjects,name='procedure_name'其中，procedure_name為要查看的存儲(chǔ)過(guò)程的名稱。（2） 存儲(chǔ)過(guò)程的修改ALTERPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,???][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement其中的參數(shù)和保留字的含義說(shuō)明與CREATEPROCEDURE語(yǔ)句一致。（3）存儲(chǔ)過(guò)程的刪除DROPPROCEDURE{procedure_name}[…]語(yǔ)法說(shuō)明：procedure_name：為要?jiǎng)h除的存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組的名稱。存儲(chǔ)過(guò)程分組后，將無(wú)法刪除組內(nèi)的單個(gè)存儲(chǔ)過(guò)程。刪除一個(gè)存儲(chǔ)過(guò)程將會(huì)把同組的所有存儲(chǔ)過(guò)程都刪除。三、實(shí)驗(yàn)軟硬件環(huán)境裝有MicrosoftSQLServer2008ManagementStudio的電腦四、實(shí)驗(yàn)過(guò)程（實(shí)驗(yàn)步驟、記錄、數(shù)據(jù)、分析）1、 使用系統(tǒng)存儲(chǔ)過(guò)程查詢某一內(nèi)容；2、 創(chuàng)建自定義的存儲(chǔ)過(guò)程，并執(zhí)行存儲(chǔ)過(guò)程，然后進(jìn)行查看、修改和刪除。五、測(cè)試/調(diào)試及實(shí)驗(yàn)結(jié)果分析1、系統(tǒng)存儲(chǔ)過(guò)程（1）使用存儲(chǔ)過(guò)程sp_help查看bank數(shù)據(jù)庫(kù)的信息。其命令為usestudent_misgoexecsp_helpgo執(zhí)行結(jié)果如下：

snosnamesnosnamessexsagsdna1sDI王建平男21dD12s(M李偉里19d033sffi黃河男1S4s(K長(zhǎng)江里20dgBEGINselect*fromstudentswheresnoNOTIN(selectsnofromreportswherecnoIN(selectcnofromcourseswherecname=@kcms))END執(zhí)行上述命令后，執(zhí)行executep2'數(shù)據(jù)結(jié)構(gòu)'命令，其結(jié)果如右圖，顯示沒(méi)有參加數(shù)據(jù)結(jié)構(gòu)課程的學(xué)生的信息。（3）創(chuàng)建帶有一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程P3，輸入學(xué)生的學(xué)號(hào)，返回姓名。其命令CREATEPROCEDUREP5@xhchar(10),@xmchar(10)OUTPUTASBEGINselect@xm=snamefromstudentswheresno=@xhEND執(zhí)行上述命令后，執(zhí)行如下命令declare@xmvarchar(10)executep1'101@xmOUTPUTprint@xm'顯示結(jié)果如右圖（4）創(chuàng)建帶有多個(gè)輸入?yún)?shù)和多個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程P6，輸入學(xué)生的學(xué)號(hào)和課程號(hào)，返回姓名和成績(jī)。其命令為CREATEPROCEDUREP6@xhchar(10),@kchchar(10),@xmchar(10)OUTPUT,@cjintOUTPUTASBEGINselect@xm=snamefromstudentswheresno=@xhselect@cj=gradefromreportswheresno=@xhandcno=@kchENDEND執(zhí)行上述命令后，執(zhí)行如下命令declare @xmvarchar(10),@cjintexecutep1'101201@xmOUTPUT,@cjOUTPUTprint@xmPrint@cj'由CREATEPROCEDUREP6@shGhan(10>2kuhch3ir(10>f@xnchartIDj0UTPmf3cjintOUTPinAS￡jBKGIPJselect?xir—anarneframtuid=ntswhe-x-?=sna—@xhFlselect—aradefromxepoztswriezesno=@xiiandaiia=3JcaH(ETTDdeclaregimvarcftar(ID)r8clintexeeure■1D1201@xrnOLTT