實(shí)驗(yàn)9存儲(chǔ)過(guò)程的基本操作實(shí)驗(yàn)?zāi)康?/p>

背景知識(shí)實(shí)驗(yàn)示例實(shí)驗(yàn)內(nèi)容（選做）

實(shí)驗(yàn)?zāi)康?/p>

學(xué)習(xí)與實(shí)踐對(duì)存儲(chǔ)過(guò)程的創(chuàng)建、修改、使用、刪除等基本操作。背景知識(shí)實(shí)驗(yàn)示例1、創(chuàng)建存儲(chǔ)過(guò)程2、修改存儲(chǔ)過(guò)程3、執(zhí)行存儲(chǔ)過(guò)程4、查看存儲(chǔ)過(guò)程5、刪除存儲(chǔ)過(guò)程6、存儲(chǔ)過(guò)程的交互式操作菜單1、創(chuàng)建存儲(chǔ)過(guò)程

您可以使用T-SQL語(yǔ)句CREATEPROCEDURE來(lái)創(chuàng)建存儲(chǔ)過(guò)程。其語(yǔ)法為：CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]sp_executesql用于執(zhí)行可以多次重復(fù)使用或動(dòng)態(tài)生成的T-SQL語(yǔ)句或批處理。T-SQL語(yǔ)句或批處理可以包含嵌入?yún)?shù)。 其語(yǔ)法:sp_executesql[@stmt=]stmt[{,[@params=]N'@parameter_namedata_type[[OUT[PUT][,...n]'}{,[@param1=]'value1'[,...n]}]例9-1建立含參數(shù)的SQL命令字符串，并指定參數(shù)值并執(zhí)行。 DECLARE@IntVariableint,@SQLStringnvarchar(500),@ParmDefinitionnvarchar(500); SET@SQLString=N'SELECT*FROMAdventureWorks.HumanResources.EmployeeWHEREManagerID=@ManagerID';--建立SQL命令字符串 SET@ParmDefinition=N'@ManagerIDtinyint'; SET@IntVariable=197;/*用參數(shù)值執(zhí)行SQL命令字符串*/ EXECUTEsp_executesql@SQLString,@ParmDefinition,@ManagerID=@IntVariable; SET@IntVariable=109;/*用另一個(gè)參數(shù)值執(zhí)行SQL命令字符串*/ EXECUTEsp_executesql@SQLString,@ParmDefinition,@ManagerID=@IntVariable;例9-2使用帶有復(fù)雜SELECT的簡(jiǎn)單過(guò)程，下面的存儲(chǔ)過(guò)程返回某個(gè)視圖中的所有雇員（提供了姓名）、他們的職務(wù)和部門名稱。該存儲(chǔ)過(guò)程不使用任何參數(shù)。如圖9-1。 UseAdventureWorks IFOBJECT_ID('HumanResources.usp_GetAllEmployees','P')ISNOTNULLDROPPROCEDUREHumanResources.usp_GetAllEmployees; GO CREATEPROCEDUREHumanResources.usp_GetAllEmployees ASSELECTLastName,FirstName,JobTitle,Department FROMHumanResources.vEmployeeDepartment; GO--usp_GetAllEmployees存儲(chǔ)過(guò)程可通過(guò)以下方法執(zhí)行： EXECUTEHumanResources.usp_GetAllEmployees; GO--或如下： EXECHumanResources.usp_GetAllEmployees; GO--或如下：如果存儲(chǔ)過(guò)程是批處理中的第一條命令 HumanResources.usp_GetAllEmployees;9-1例9-3使用帶有參數(shù)的簡(jiǎn)單過(guò)程，下面的存儲(chǔ)過(guò)程只從視圖中返回指定的雇員（提供名字和姓氏）及其職務(wù)和部門名稱。該存儲(chǔ)過(guò)程接受與傳遞的參數(shù)精確匹配的值。 CREATEPROCEDUREHumanResources.usp_GetEmployees@lastnamevarchar(40),@firstnamevarchar(20) ASSELECTLastName,FirstName,JobTitle,DepartmentFROMHumanResources.vEmployeeDepartmentWHEREFirstName=@firstnameANDLastName=@lastname; GO--usp_GetEmployees存儲(chǔ)過(guò)程可通過(guò)以下方式執(zhí)行： EXECUTEHumanResources.usp_GetEmployees'錢','力';--或 EXECHumanResources.usp_GetEmployees@lastname='錢',@firstname='力'; GO--或如下： EXECUTEHumanResources.usp_GetEmployees@firstname='力',@lastname='錢'; GO--或如下：如果存儲(chǔ)過(guò)程是批處理中的第一條命令 HumanResources.usp_GetEmployees'錢','力';2、修改存儲(chǔ)過(guò)程例9-9如修改前已創(chuàng)建的存儲(chǔ)過(guò)程HumanResources.usp_GetAllEmployees，修改命令如下： ALTERPROCEDUREHumanResources.usp_GetAllEmployees ASSELECTFirstName+''+LastNameAS'姓名',JobTitle'職務(wù)',DepartmentAS'部門名稱' FROMHumanResources.vEmployeeDepartment; GO EXECUTEHumanResources.usp_GetAllEmployees;--執(zhí)行修改后的存儲(chǔ)過(guò)程3、執(zhí)行存儲(chǔ)過(guò)程下列示例：

EXECsp_who;EXECmaster.dbo.sp_who;EXECmydatabase..sp_who;EXECdbo.sp_who;

EXECmydatabase.dbo.sp_who

說(shuō)明執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程的向后兼容方法。

下列示例: EXECsp_who;EXECmaster.dbo.sp_who;EXECmydatabase..sp_who;EXECdbo.sp_who; EXECmydatabase.dbo.sp_who;說(shuō)明執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程的向后兼容方法。

以下示例：EXECsys.sp_who; 說(shuō)明執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程的推薦方法。 SQLServer2005與系統(tǒng)過(guò)程名稱匹配時(shí)調(diào)用數(shù)據(jù)庫(kù)排序規(guī)則。因此，在應(yīng)用程序中應(yīng)始終使用系統(tǒng)過(guò)程名稱的正確大小寫(xiě)形式。 使用sys.system_objects和sys.system_parameters目錄視圖可以顯示確切的系統(tǒng)存儲(chǔ)過(guò)程名稱。

系統(tǒng)擴(kuò)展存儲(chǔ)過(guò)程以字符xp_開(kāi)頭。它們物理上存儲(chǔ)在資源數(shù)據(jù)庫(kù)中，但邏輯上出現(xiàn)在SQLServer實(shí)例的每個(gè)系統(tǒng)定義和用戶定義數(shù)據(jù)庫(kù)的sys架構(gòu)中。以下示例說(shuō)明執(zhí)行系統(tǒng)擴(kuò)展存儲(chǔ)過(guò)程的推薦方法：EXECsys.xp_subdirs'c:\'; 執(zhí)行用戶定義存儲(chǔ)過(guò)程（不管是在批處理中還是在模塊內(nèi)）時(shí)，強(qiáng)烈建議至少用架構(gòu)名稱限定存儲(chǔ)過(guò)程名稱。 以下示例： EXECdbo.uspGetEmployeeManagers50;--或-- EXECAdventureWorks.dbo.uspGetEmployeeManagers50;說(shuō)明執(zhí)行用戶定義存儲(chǔ)過(guò)程的推薦方法。 如果指定了非限定的用戶定義存儲(chǔ)過(guò)程，數(shù)據(jù)庫(kù)引擎將按以下順序搜索該過(guò)程： ①當(dāng)前數(shù)據(jù)庫(kù)的sys架構(gòu)； ②調(diào)用方的默認(rèn)架構(gòu)（如果在批處理或動(dòng)態(tài)SQL中執(zhí)行）或者，如果非限定的過(guò)程名稱出現(xiàn)在另一個(gè)過(guò)程定義的主體中，則接著搜索包含這一過(guò)程的架構(gòu)； ③當(dāng)前數(shù)據(jù)庫(kù)中的dbo架構(gòu)。4、查看存儲(chǔ)過(guò)程

有幾種系統(tǒng)存儲(chǔ)過(guò)程和目錄視圖可提供有關(guān)存儲(chǔ)過(guò)程信息。使用它們，您可以： ①查看存儲(chǔ)過(guò)程的定義； ②獲得有關(guān)存儲(chǔ)過(guò)程的信息； ③列出指定存儲(chǔ)過(guò)程所使用的對(duì)象及使用指定存儲(chǔ)過(guò)程的過(guò)程。 若要查看存儲(chǔ)過(guò)程的定義：sys.sql_modules、OBJECT_DEFINITION、sp_helptext。 查看有關(guān)存儲(chǔ)過(guò)程的信息：sys.objects、cedures、sys.parameters、sys.numbered_procedures、sys.numbered_procedure_parameters、sp_help。 查看存儲(chǔ)過(guò)程的依賴關(guān)系：sys.sql_dependencies、sp_depends。 查看有關(guān)擴(kuò)展存儲(chǔ)過(guò)程的信息：sp_helpextendedproc。5、刪除存儲(chǔ)過(guò)程

不再需要存儲(chǔ)過(guò)程時(shí)可將其刪除。如果另一個(gè)存儲(chǔ)過(guò)程調(diào)用某個(gè)已被刪除的存儲(chǔ)過(guò)程，SQLServer2005將在執(zhí)行調(diào)用進(jìn)程時(shí)顯示一條錯(cuò)誤消息。但是，如果定義了具有相同名稱和參數(shù)的新存儲(chǔ)過(guò)程來(lái)替換已被刪除的存儲(chǔ)過(guò)程，那么引用該過(guò)程的其它過(guò)程仍能成功執(zhí)行。 例如，如果存儲(chǔ)過(guò)程proc1引用存儲(chǔ)過(guò)程proc2，而proc2已被刪除，但又創(chuàng)建了另一個(gè)名為proc2的存儲(chǔ)過(guò)程，現(xiàn)在proc1將引用這一新存儲(chǔ)過(guò)程。proc1也不必重新創(chuàng)建。 刪除存儲(chǔ)過(guò)程命令為：DROP{PROC|PROCEDURE}{[schema_name.]procedure}[,...n]； 刪除擴(kuò)展存儲(chǔ)過(guò)程的命令為：sp_dropextendedproc[@functname=]'procedure'。 例9-10刪除HumanResources.usp_GetAllEmployees存儲(chǔ)過(guò)程。 DROPPROCEDUREHumanResources.usp_GetAllEmployees;6、存儲(chǔ)過(guò)程的交互式操作菜單 在ManagementStudio中“對(duì)象資源管理器”中展開(kāi)各級(jí)目錄到“某數(shù)據(jù)庫(kù)”->“可編程性”->“存儲(chǔ)過(guò)程”，展開(kāi)“存儲(chǔ)過(guò)程”目錄后，能看到該數(shù)據(jù)庫(kù)的所有用戶定義存儲(chǔ)過(guò)程，在“存儲(chǔ)過(guò)程”目錄上或某具體存儲(chǔ)過(guò)程上按鼠標(biāo)右鍵，彈出的快捷菜單是交互式快捷操作存儲(chǔ)過(guò)程的簡(jiǎn)便方法。 從快捷菜單可知，它提供了多種功能菜單。讀者不妨逐一操作，來(lái)加強(qiáng)操作實(shí)踐。實(shí)驗(yàn)內(nèi)容（選做）1、創(chuàng)建存儲(chǔ)過(guò)程2、執(zhí)行存儲(chǔ)過(guò)程3、查看和修改存儲(chǔ)過(guò)程4、查看、重命名和刪除存儲(chǔ)過(guò)程 本實(shí)驗(yàn)（及實(shí)驗(yàn)10）中要用到這樣的“學(xué)生-課程”數(shù)據(jù)庫(kù)jxgl2，S、SC與C三表的關(guān)系圖如圖9-2所示（字段名及其含義可見(jiàn)于圖）。其創(chuàng)建于添加記錄SQL命令如下： CREATEDATABASEjxgl2 USEjxgl2 CREATETABLES(SNOCHAR(5)NOTNULLPRIMARYKEY,SNVARCHAR(8)NOTNULL,SEXCHAR(2)NOTNULLCHECK(SEXIN('男','女'))DEFAULT'男',AGESMALLINTNOTNULLCHECK(AGE>7),DEPTVARCHAR(20),CONSTRAINTSN_UUNIQUE(SN)); CREATETABLEC(CNOCHAR(5)NOTNULLPRIMARYKEY,CNVARCHAR(20),CTSMALLINTCHECK(CT>=1)) CREATETABLESC(SNOCHAR(5)NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESS(SNO),CNOCHAR(5)NOTNULL,SCORESMALLINTCHECK((SCOREISNULL)OR(SCOREBETWEEN0AND100)),CONSTRAINTS_C_PPRIMARYKEY(SNO,CNO),CONSTRAINTC_FFOREIGNKEY(CNO)REFERENCESC(CNO)) 可以利用類似如下的添加語(yǔ)句生成各表的記錄： INSERTINTOSVALUES('S1','李濤','男',19,'信息');--插入1學(xué)生記錄,其它略 INSERTINTOCVALUES('C1','C語(yǔ)言',4);--插入1選課記錄,其它略 INSERTINTOSCVALUES('S1','C1',90);--插入1課程記錄,其它略圖9-2“學(xué)生-課程”數(shù)據(jù)庫(kù)三表關(guān)系圖1、

創(chuàng)建存儲(chǔ)過(guò)程①利用ManagementStudio創(chuàng)建存儲(chǔ)過(guò)程

在對(duì)象資源管理器中，依次展開(kāi)數(shù)據(jù)庫(kù)服務(wù)器->數(shù)據(jù)庫(kù)->某數(shù)據(jù)庫(kù)->可編程性->存儲(chǔ)過(guò)程，在“存儲(chǔ)過(guò)程”節(jié)點(diǎn)上，按鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“新建存儲(chǔ)過(guò)程”菜單項(xiàng)，在出現(xiàn)的“新建存儲(chǔ)過(guò)程”的創(chuàng)建對(duì)話框中，可直接輸入存儲(chǔ)過(guò)程代碼。②利用模板創(chuàng)建存儲(chǔ)過(guò)程

在模板資源管理器中，展開(kāi)“StoredProcedure”->雙擊某創(chuàng)建存儲(chǔ)過(guò)程項(xiàng)，如“CreateProcedureBasicTemplate”。經(jīng)過(guò)正確連接后，在模板代碼窗口中修改完成存儲(chǔ)過(guò)程的創(chuàng)建。③利用createprocedure語(yǔ)句能創(chuàng)建存儲(chǔ)過(guò)程。

例9-11在JXGL數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名稱為Select_S的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程的功能是從數(shù)據(jù)表S中查詢所有女同學(xué)的信息，并執(zhí)行該存儲(chǔ)過(guò)程。 USEjxgl2--以下略本命令 CREATEPROCEDURESelect_SASSELECT*FROMSWHEREsex='女' GO ExecuteSelect_S--執(zhí)行該存儲(chǔ)過(guò)程

2、執(zhí)行存儲(chǔ)過(guò)程Query_S存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：Declare@SNVARCHAR(8),@AGESMALLINTexecuteQuery_S'S1',@SNOUTPUT,@AGEOUTPUTSELECT@SN,@AGE--執(zhí)行語(yǔ)句還可以是：executeQuery_S'S1',@SN=@SNOUTPUT,@AGE=@AGEOUTPUT--或--executeQuery_S'S1',@AGE=@AGEOUTPUT,@SN=@SNOUTPUT--或--execQuery_S'S1',@SNOUTPUT,@AGEOUTPUT。--如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：Query_S'S1',@SNOUTPUT,@AGEOUTPUT或Query_S'S1',@SN=@SNOUTPUT,@AGE=@AGEOUTPUT等方法執(zhí)行。3、查看和修改存儲(chǔ)過(guò)程

在對(duì)象資源管理器中，依次展開(kāi)數(shù)據(jù)庫(kù)服務(wù)器->數(shù)據(jù)庫(kù)->某數(shù)據(jù)庫(kù)->可編程性->存儲(chǔ)過(guò)程，在某存儲(chǔ)過(guò)程，如“InsRecToS”上按鼠標(biāo)右鍵，從彈出的快捷菜單中，選擇各功能菜單操作。按“編寫(xiě)存儲(chǔ)過(guò)程腳本為”或“修改”可以查看并修改存儲(chǔ)過(guò)程(如圖9-5所示)；按“重命名”能修改存儲(chǔ)過(guò)程名；按“刪除”能刪除不需要的存儲(chǔ)過(guò)程；其它操作功能還有：新建存儲(chǔ)過(guò)程、執(zhí)行存儲(chǔ)過(guò)程、查看存儲(chǔ)過(guò)程、查看存儲(chǔ)過(guò)程屬性等。 例9-14使用ALTERPROCEDURE命令，修改存儲(chǔ)過(guò)程InsRecToS，命令如下： ALTERPROCEDURE[dbo].[InsRecToS](@snochar(5),@snvarchar(8),@sexchar(2)='女',@agesmallint,@deptvar