數(shù)據(jù)庫原理試驗報告S10-數(shù)據(jù)庫存儲過程

1、數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告實驗10數(shù)據(jù)庫存儲過程的建立實驗日期和時間：|實驗室：班級：|學(xué)號：|姓名：實驗環(huán)境：1 .硬件：2 .軟件：實驗原理：1. 理解存儲過程的概念、優(yōu)點和使用原則2. 掌握存儲過程的創(chuàng)建、執(zhí)行、查看、修改和刪除方法一、存儲過程的概念SQLSERVER中的存儲過程類似于編程語言中的過程或函數(shù)。-以下為創(chuàng)建一個簡單的用戶存儲過程createprocp_test(aint,bintoutput)assetb=a+1-以下為調(diào)用declareaint,bintselecta=1,b=0execp_testa,boutputselecta,b在使用Transact-

2、SQL語言編程的過程中，可以將某些需要多次調(diào)用的實現(xiàn)某個特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由SQLServer服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲過程。存儲過程在創(chuàng)建時就被編譯和優(yōu)化，調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時可以直接執(zhí)行。存儲過程有以下特點：存儲過程中可以包含一條或多條Transact-SQL語句。存儲過程可以接受輸入?yún)?shù)并可以返回輸出值。在一個存儲過程中可以調(diào)用另一個存儲過程。存儲過程可以返回執(zhí)行情況的狀態(tài)代碼給調(diào)用它的程序。存儲過程的優(yōu)點：實現(xiàn)了模塊化編程，一個存儲過程可以被多個用戶共享和重用。通過通用編程結(jié)構(gòu)和過程重用實現(xiàn)編程框架。存

3、儲過程具有對數(shù)據(jù)庫立即訪問的功能。使用存儲過程可以通過本地存儲、代碼預(yù)編譯和緩存技術(shù)實現(xiàn)高性能的數(shù)據(jù)操作，加快程序的運行速度。使用存儲過程可以減少網(wǎng)絡(luò)流量。使用存儲過程通過隔離和加密的方法提高數(shù)據(jù)庫的安全性。第1頁2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告存儲過程的分類：在SQLServer中的存儲過程分為兩類：即系統(tǒng)提供的存儲過程、擴展存儲過程和用戶自定義的存儲過程。系統(tǒng)存儲過程：由系統(tǒng)自動創(chuàng)建，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫的sys構(gòu)架中。并且都帶有sp_前綴。在SQLServer2005中，可將GRANT、DENY和REVOKE權(quán)限應(yīng)用于系統(tǒng)存儲過程

4、。用戶自定義存儲過程：是指封裝了可重用代碼的模塊或例程，由用戶創(chuàng)建，能完成某一特定的功能?？梢越邮茌斎?yún)?shù)，返回輸出參數(shù)。在SQLServer2005中，用戶自定義存儲過程有兩種類型：Transact-SQL存儲過程和CLR存儲過程。Transact-SQL存儲過程：是指保存的Transact-SQL語句集合。CLR存儲過程：是指對Microsoft.NETFramework公共語言運行時（CLR）方法的引用，可以接受和返回用戶提供的參數(shù)。、存儲過程的創(chuàng)建方法在SQLServer中，可以使用兩種方法創(chuàng)建存儲過程：1. 使用SQLServer管理控制平臺創(chuàng)建存儲過程。在SQLServer管理控制

5、臺中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的何編程性”文件夾，右擊其中的存儲過程”，在彈出的快捷菜單中選擇新建存儲過程”選項，此時出現(xiàn)創(chuàng)建存儲過程窗口。在文本框中可以看到系統(tǒng)自動給出了創(chuàng)建存儲過程的格式模板語句，可以根據(jù)模板格式進行修改來創(chuàng)建新的存儲過程?；蛘咴趧?chuàng)建存儲過程的窗口中單擊“查詢”菜單，選擇“指定模板參數(shù)的值”，會彈出“指定模板參數(shù)的值”對話框，做后續(xù)的操作。2. 使用Transact-SQL中的CREATEPROCEDURE創(chuàng)建存儲過程。CREATEPROC|PROCEDUREschema_cedure_nameparametertype_schema_name

6、.data_typeVARYING=defaultOUT|OUTPUT,.nWITHENCRYPTIONAS;.n;:=BEGINstatementsEND其中，各參數(shù)的意義如下：schema_name：過程所屬架構(gòu)的名稱。procedure_name：新存儲過程的名稱。parameter：過程中的參數(shù)。type_schema_name.data_type：參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型。VARYING:指定作為輸出參數(shù)支持的結(jié)果集。僅適用于cursor參數(shù)。default：參數(shù)的默認(rèn)值。OUTPUT:指示參數(shù)是輸出參數(shù)。ENCRYPTION:將CREATEPROCEDURE語句的原始文本力口密。

7、:要包含在過程中的一個或多個Transact-SQL語句。使用CREATEPROCEDURE命令創(chuàng)建存儲過程，考慮下列幾個事項：CREATEPROCEDURE語句不能與其他SQL語句在單個批處理中組合使用。必須具有數(shù)據(jù)庫的CREATEPROCEDURE權(quán)限。默認(rèn)情況下，創(chuàng)建存儲過程的許可權(quán)歸數(shù)據(jù)庫的所有者，數(shù)據(jù)庫的所有者可以把許可權(quán)給其他用戶。第2頁2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。不要創(chuàng)建任何使用sp_乍為前綴的存儲過程。SQL_Server使用sp_前綴指定系統(tǒng)存儲過程。當(dāng)創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分：1.所有的輸入

8、參數(shù)以及傳給調(diào)用者的輸出參數(shù)。2,被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。3.返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。實驗主要任務(wù)：附加或?qū)雽W(xué)生成績數(shù)據(jù)庫，對數(shù)據(jù)庫實施如下操作：一、創(chuàng)建存儲過程（一）無參存儲過程的創(chuàng)建：形如：CREATEPROCEDURE存儲過程名ASSELECT子句1 .利用SQL-SERVER管理控制臺：創(chuàng)建一個名稱為“StuInfo的存儲過程，要求完成以下功能：查詢06級學(xué)生的學(xué)號、姓名、班級名稱，班級號和學(xué)院編號五個字段的內(nèi)容2 .利用Transact-創(chuàng)建一個存儲過程StuScoreInfo,完成的功能是查詢艾宏迪同學(xué)的班級、學(xué)號、姓名、

9、課程名稱、考試分?jǐn)?shù)。（二）有參存儲過程的創(chuàng)建：形如：CREATEPROCEDURE存儲過程名居量名1變量類型，變量名2變量類型，變量名3變量類型OUTPUT-聲明變量，一個是局部變量，兩個L全局變量，是系統(tǒng)預(yù)定義的。如ERROR回最后執(zhí)行的SQL語句的錯誤代碼，OUTPUT作為輸出變量關(guān)鍵字。ASSELECT子句3 .一個有參變量（輸入）的存儲過程的建立：利用Transact-SQL創(chuàng)建一個存儲過程StuScoreInfo2,完成的功能是傳入學(xué)生的姓名，如分別傳入艾宏迪，安然，白冰等同學(xué)的姓名，查看該同學(xué)的班級、學(xué)號、姓名、課程名稱、考試分?jǐn)?shù)。4 .多個有參變量（輸入）的存儲過程的建立：利用T

10、ransact-SQL創(chuàng)建一個存儲過程Stu_Class_Lastname,完成的功能是傳入某班班級號，某種姓氏，查相應(yīng)同學(xué)的班級名稱、學(xué)號、姓名、課程名稱、考試分?jǐn)?shù)。（多個變量之間用逗號分隔）5 .帶有返回（輸出）參數(shù)的存儲過程的建立。2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告/*當(dāng)需要從存儲過程中返回一個或多個值時，可以在存儲過程的語句中定義這些輸出參數(shù)，此時需要在CREATEPROCEDRUE語句中使用OUTPUT關(guān)鍵字說明是輸出參數(shù)*/5.1 創(chuàng)建一個帶有輸出參數(shù)的存儲過Stu_Classnum的存儲過程，在2003學(xué)生名單中使得它能夠根據(jù)用戶給定的班級名稱統(tǒng)計該班的學(xué)

11、生人數(shù)，并將學(xué)生人數(shù)返回給用戶5.2 創(chuàng)建一個帶有參數(shù)的存儲過程Stu_Age,該存儲過程根據(jù)傳入的姓名，在2003學(xué)生名單表中計算此學(xué)生的年齡，并根據(jù)程序的執(zhí)行結(jié)果返回不同的值，程序執(zhí)行成功，返回整數(shù)0,如果執(zhí)行出錯，則返回錯誤號。（多個變量之間用逗號分隔）二、執(zhí)行存儲過程存儲過程創(chuàng)建成功后，保存在數(shù)據(jù)庫中。在SQLServer中可以使用EXECUTE命令來直接執(zhí)行存儲過程，語法形式如下：EXECUTEreturn_status=procedure_name|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.n其中，各選項的含

12、義如下：EXECUTE:執(zhí)行存儲過程的命令關(guān)鍵字。return_status:是一個可選的整型變量，保存存儲過程的返回狀態(tài)。procedure_name：指定執(zhí)行的存儲過程的名稱。procedure_name_var：是局部定義變量名，代表存儲過程名稱。parameter：是在創(chuàng)建存儲過程時定義的過程參數(shù)。（一）、無參存儲過程的執(zhí)行：6 .利用EXECUTE執(zhí)行名稱為“StuInfo的存儲過程7 .利用EXECUTE執(zhí)行名稱為StuScoreInfo的存儲過程（二）、有參存儲過程的執(zhí)行8 .一個有參變量（輸入）的存儲過程的執(zhí)行：利用EXECUT執(zhí)行名稱為StuScoreInfo2”的存儲過程，

13、如分別傳入艾宏迪，安然，白冰等同學(xué)的姓名，查看該同學(xué)的班級、學(xué)號、姓名、課程名稱、考試分?jǐn)?shù)。注意與無參數(shù)的存儲過程“StuScoreInfo”進行比較。9 .多個有參變量（輸入）的存儲過程的執(zhí)行：利用EXECUTE執(zhí)行名稱為Stu_Class_Lastname的存儲過程10 .利用EXECUTE執(zhí)行名稱執(zhí)行存儲過程Stu_Age,該存儲過程有一個輸入?yún)?shù)姓名”，另外，還有一個輸出參數(shù)Age。存儲過程執(zhí)行完后，有一個返回的狀態(tài)值，這個值可以從變量ErrorValue得到。三、修改存儲過程（一）使用SQLServer管理控制臺修改存儲過程使用SQLServer管理控制臺可以很方便地修改存儲過程的定

14、義。在SQLServer管理控制臺中，展開存儲過程，右擊要修改的存儲過程，從彈出的快捷菜單中選擇修改”選項，2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告則會出現(xiàn)與創(chuàng)建存儲過程時類似的窗口。在該窗口中，可以直接修改定義該存儲過程的Transact-SQL語句。（二）.使用Transact-SQL語句修改存儲過程使用ALTERPROCEDURE語句可以更改存儲過程，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。其語法形式如下：ALTERPROC|PROCEDUREschema_cedure_nameparametertype_schema_name.data_typ

15、eVARYING=defaultOUTPUT,.nWITHENCRYPTIONASsql_statement.n修改存儲過程時，應(yīng)該注意以下幾點：如果原來的過程定義是使用WITHENCRYPTION創(chuàng)建的，那么只有在ALTERPROCEDURE中也包含這個選項時，這個選項才有效。每次只能修改一個存儲過程。用ALTERPROCEDURE更改的存儲過程的權(quán)限保持不變。11 .修改前面創(chuàng)建的Stu_Info存儲過程，使之完成以下功能：根據(jù)班級名稱，查詢該班的班級、學(xué)號、姓名、性別、考試課程名稱和考試分?jǐn)?shù)，并執(zhí)行該存儲過程。（原StuInfo:利用SQL-SERVERT理控制臺：創(chuàng)建一個名稱為“Stu

16、Info”的存儲過程，要求完成以下功能：查詢06級學(xué)生的學(xué)號、姓名、班級名稱，班級號和學(xué)院編號五個字段的內(nèi)容）四、查看存儲過程（一）使用SQLServer管理控制臺查看用戶創(chuàng)建的存儲過程在SQLServer管理控制臺中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的可編程性”文件夾，單擊其中的存儲過程”，在右邊的窗口中就會顯示出當(dāng)前數(shù)據(jù)庫中的所有存儲過程。（二）使用系統(tǒng)存儲過程查看用戶創(chuàng)建的存儲過程sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型sp_helpobjname=name參數(shù)name為要查看的存儲過程的名稱。sp_helptext：用于顯示存儲過程的源代碼sp_helptextobjn

17、ame=namesp_depends：用于顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象sp_dependsobjname=object參數(shù)object為要查看依賴關(guān)系的存儲過程的名稱。12 .使用SQLServer管理控制臺查看用戶創(chuàng)建的所有存儲過程。13 .使用系統(tǒng)存儲過程sp_help查看Stu_Age存儲過程的參數(shù)及其數(shù)據(jù)類型。14 .使用系統(tǒng)存儲過程spdepends查看StuScoreInfo存儲過程的相關(guān)的數(shù)據(jù)庫對象。2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告五、重命名存儲過程（1） .使用SQLServer管理控制臺修改存儲過程名稱通過SQLServer管理控制臺可以修改存儲過

18、程的名稱。方法是：在SQLServer管理控制臺中，右擊要操作的存儲過程名稱，從彈出的快捷菜單中選擇重命名”選項，當(dāng)存儲過程名稱變成可輸入狀態(tài)時，就可以直接修改該存儲過程的名稱。（2） .使用系統(tǒng)存儲過程修改存儲過程名稱修改存儲過程的名稱也可以使用系統(tǒng)存儲過程sp_rename,其語法形式如下：sp_rename原存儲過程名稱，新存儲過程名稱15 .使用系統(tǒng)存儲過程sp_rename將StuInfo存儲過程的名稱修改為Stu_Info_SCORE。六、刪除存儲過程（1） .使用SQLServer管理控制臺刪除存儲過程在SQLServer管理控制臺中，右擊要刪除的存儲過程，從彈出的快捷菜單中選擇

19、刪除”選項，會彈出刪除對象”對話框。在該對話框中，單擊確定”按鈕，即可完成刪除操作。（2） .使用Transact-SQL語句刪除存儲過程刪除存儲過程也可以使用Transact-SQL語言中的DROP命令，DROP命令可以將一個或者多個存儲過程或者存儲過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下：DROPPROC|PROCEDUREschema_cedure,.n16 .使用DROP命令刪除Stu_class_lastname和StuScoreInfo兩個存儲過程。將以上任務(wù)的實驗完成情況實驗結(jié)果、實驗原理、總結(jié)分欄一一填寫到下表中，格式參考任務(wù)1或者自定。任務(wù)1:完成情況（代碼及

20、運行結(jié)果評析）：代碼：運行結(jié)果評析：2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告小結(jié):任務(wù)2:完成情況（代碼及結(jié)果）：-2,利用Transact6建一個存儲過程StuScoreInfo,完成的功能是查詢艾宏迪同學(xué)的班級、學(xué)號、-姓名、課程名稱、考試分?jǐn)?shù)。createprocedureStuScoreInfoasselect班級名稱，學(xué)生,學(xué)號姓名，課程名成績from班級，學(xué)生，課程，成績where姓名二艾宏迪,and學(xué)生,班級號=班級,班級號and課程,課程號=成績,課程號and學(xué)生,學(xué)號=成績,學(xué)號2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告.jfl：K2L5ef

21、etL*r*；T5tuu。力考E司&程荃皆道會明目J/&軍旦，Lj%已占.1；k：半與省病-手可配Hm二手正H百.二工在配1出U如TtlSTIJ字對場一*三：三君醫(yī)-j可層回M栩tt理CfllttM用！4司elto.UuiHTnd%/u$wdrfq+Wdla5uScRwdnfc2j.-jFJtVlSx*TU*：*=J/J;叫I、r部+IWI愴1廠l砥中二堂空聲.專奪二HVMmnfR-fQlQwbrya-MrSCNW-JtUUlMLjqQlrrk-2.利用Tr-匚t-創(chuàng)建一1個存儲過程羨uScore工nfci,完成白姓名、課程名稱、考試分?jǐn)?shù)；1-createprc-cediLireStuScor

22、elnfo-=el&ct史級名稱，學(xué)生.學(xué)號.姓名，課程名，應(yīng)績-from班級，學(xué)生.課程，成繳-where姓名=，變宏迪，皿1學(xué)生班強號=電級.班級號如我-學(xué)生.學(xué)號=成績.學(xué)號qxqcstusccrsinfoJMtta-:“a俄主H5J!就百m蘭31建5*SON子式血.歲區(qū)部eissnem-Et豆工r.SWES)l*2m塞博阻他BRI5NHa)啟解SHWaixwInterBE)學(xué)生應(yīng)桔叩90：H3折總結(jié):（實驗結(jié)果及原理的分析）任務(wù)3:完成情況（代碼及結(jié)果）：-3.一個有參變量（輸入）的存儲過程的建立：利用Transact-SQL創(chuàng)建一個存儲過程-StuScoreInfo2,完成的功能是傳

23、入學(xué)生的姓名，如分別傳入艾宏迪，安然，白冰等-同學(xué)的姓名，查看該同學(xué)的班級、學(xué)號、姓名、課程名稱、考試分?jǐn)?shù)。createprocedureStuScoreInfo2SNAMENVARCHAR（5）asselect班級名稱，學(xué)生.學(xué)號姓名，課程名成績from班級，學(xué)生，課程，成績where姓名=SNAMEand學(xué)生.班級號=班級.班級號and課程.課程號=成績.課程號and學(xué)生.學(xué)號二成績.學(xué)號declareSNAMENVARCHAR(5)setSNAME=安然execStuScoreInfo2SNAME2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告總結(jié)：（實驗結(jié)果及原理的分析）任務(wù)4:完成情況（代碼及結(jié)果）：總結(jié)：（實驗結(jié)果及原理的分析）任務(wù)5:完成情況（代碼及結(jié)果）：總結(jié)：（實驗結(jié)果及原理的分析）任務(wù)6:完成情況（代碼及結(jié)果）：總結(jié)：（實驗結(jié)果及原理的分析）任務(wù)7:完成情況（代碼及結(jié)果）：總結(jié)：（實驗結(jié)果及原理的分析）2011年5月4日數(shù)據(jù)庫管理系統(tǒng)SQLServer實驗報告任務(wù)8:完成情況（代碼及結(jié)果）：總結(jié)：（實驗結(jié)果及原理的分析）任務(wù)9:完成情況（代碼及結(jié)果）：總結(jié)：（實驗結(jié)果及原理的分析）任