數(shù)據(jù)庫原理實驗4存儲過程

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

snosnamesnosnamessexsagsdna1sDI王建平男21dD12s(M李偉里19d033sffi黃河男1S4s(K長江里20dgBEGINselect*fromstudentswheresnoNOTIN(selectsnofromreportswherecnoIN(selectcnofromcourseswherecname=@kcms))END執(zhí)行上述命令后，執(zhí)行executep2'數(shù)據(jù)結構'命令，其結果如右圖，顯示沒有參加數(shù)據(jù)結構課程的學生的信息。（3）創(chuàng)建帶有一個輸入?yún)?shù)和一個輸出參數(shù)的存儲過程P3，輸入學生的學號，返回姓名。其命令CREATEPROCEDUREP5@xhchar(10),@xmchar(10)OUTPUTASBEGINselect@xm=snamefromstudentswheresno=@xhEND執(zhí)行上述命令后，執(zhí)行如下命令declare@xmvarchar(10)executep1'101@xmOUTPUTprint@xm'顯示結果如右圖（4）創(chuàng)建帶有多個輸入?yún)?shù)和多個輸出參數(shù)的存儲過程P6，輸入學生的學號和課程號，返回姓名和成績。其命令為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