第8章存儲過程的操作與管理

存儲過程概述存儲過程是為完成特定的功能而聚集在一起的一組SQL程序語句，經(jīng)編譯后存儲在數(shù)據(jù)庫中的SQL程序。在SQLServer中使用存儲過程而不使用存儲在客戶端計算機(jī)本地的Transact-SQL程序的優(yōu)點包括：〔1〕存儲過程已在效勞器注冊。〔2〕存儲過程具有平安特性〔例如權(quán)限〕和所有權(quán)鏈接，以及可以附加到它們的證書。〔3〕存儲過程可以強(qiáng)制應(yīng)用程序的平安性。〔4〕存儲過程允許模塊化程序設(shè)計。〔5〕存儲過程是命名代碼，允許延遲綁定。〔6〕存儲過程可以減少網(wǎng)絡(luò)通信流量。8.1創(chuàng)立存儲過程在SQLServer中，可以使用兩種方法創(chuàng)立存儲過程：〔1〕使用創(chuàng)立存儲過程模板創(chuàng)立存儲過程；〔2〕利用SQLServer管理平臺創(chuàng)立存儲過程。

當(dāng)創(chuàng)立存儲過程時，需要確定存儲過程的三個組成局部：〔1〕所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。〔2〕被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。〔3〕返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。

8.1創(chuàng)立存儲過程CREATEPROCEDURE的語法形式如下：

CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number]

[{@parameter[type_schema_name.]data_type}

[VARYING][=default][[OUT[PUT]][,...n][WITH<procedure_option>[,...n][FORREPLICATION]AS{<sql_statement>[;][...n]|<method_specifier>}[;]<procedure_option>::=[ENCRYPTION][RECOMPILE]

EXECUTE_AS_Clause]<sql_statement>::={[BEGIN]statements[END]}<method_specifier>::=EXTERNALNAME8.1.1使用模板創(chuàng)立存儲過程〔1〕在SQLServer管理平臺中，選擇“視圖〔View〕〞菜單中的“模板資源資源管理器(TemplateExplorer)〞，出現(xiàn)“模板資源管理器(TemplateExplorer)〞窗口，選擇“存儲過程〞中的“創(chuàng)立存儲過程〞選項，如圖8-1所示。〔2〕在文本框中可以輸入創(chuàng)立存儲過程的Transact_SQL語句，單擊“執(zhí)行〞按鈕，即可創(chuàng)立該存儲過程。圖8-1創(chuàng)立存儲過程模板使用管理平臺創(chuàng)立存儲過程〔1〕在SQLServer管理平臺中，展開指定的效勞器和數(shù)據(jù)庫，然后展開程序，右擊存儲過程選項，在彈出的快捷菜單中依次選擇“新建→存儲過程…〞選項，如圖8-2所示，出現(xiàn)創(chuàng)立存儲過程窗口。〔2〕在文本框中可以輸入創(chuàng)立存儲過程的Transact_SQL語句，單擊“執(zhí)行〞按鈕，即可創(chuàng)立該存儲過程。圖8-2新建存儲過程使用管理平臺創(chuàng)立存儲過程例8-1創(chuàng)立一個帶有SELECT語句的簡單過程，該存儲過程返回所有員工姓名，Email地址，等。該存儲過程不使用任何參數(shù)程序清單如下。USEadventureworksGOCREATEPROCEDUREau_infor_allASSELECTlastname,firstname,emailaddress,phoneFROMperson.contactGO使用管理平臺創(chuàng)立存儲過程例8-2創(chuàng)立一個存儲過程，以簡化對sc表的數(shù)據(jù)添加工作，使得在執(zhí)行該存儲過程時，其參數(shù)值作為數(shù)據(jù)添加到表中。程序清單如下：CREATEPROCEDURE[dbo].[pr1_sc_ins]@Param1char(10),@Param2char(2),@Param3realASBEGIN insertintosc(sno,cno,score)values(@Param1,@Param2,@Param3)END使用管理平臺創(chuàng)立存儲過程例8-3創(chuàng)立一個帶有參數(shù)的簡單存儲過程，從視圖中返回指定的雇員〔提供名和姓〕及其職務(wù)和部門名稱，該存儲過程接受與傳遞的參數(shù)精確匹配的值程序清單如下。USEAdventureWorks;GOCREATEPROCEDUREGetEmployees@lastnamevarchar(40),@firstnamevarchar(20)ASSELECTLastName,FirstName,JobTitle,DepartmentFROMHumanResources.vEmployeeDepartmentWHEREFirstName=@firstnameANDLastName=@lastname;GO使用管理平臺創(chuàng)立存儲過程例8-4下面的存儲過程從表person.contact中返回指定的一些員工姓名及其。該存儲過程對傳遞的參數(shù)進(jìn)行模式匹配。如果沒有提供參數(shù)，那么使用預(yù)設(shè)的默認(rèn)值〔姓氏以字母D開頭〕程序清單如下。USEAdventureWorks;GOCREATEPROCEDUREau_infor2@lastnamevarchar(40)='D%',@firstnamevarchar(20)='%'ASSELECTfirstname,lastname,phoneFROMperson.contactWHEREfirstnameLIKE@firstnameANDlastnameLIKE@lastnameGO使用管理平臺創(chuàng)立存儲過程例8-5以下例如顯示有一個輸入?yún)?shù)和一個輸出參數(shù)的存儲過程。存儲過程中的第一個參數(shù)@sname將接收由調(diào)用程序指定的輸入值(學(xué)生姓名)，第二個參數(shù)@sscore〔成績〕將用于將該值返回調(diào)用程序。SELECT語句使用@sname參數(shù)獲取正確的@sscore值，并將該值分配給輸出參數(shù)。程序清單如下：CREATEPROCEDUREs_score@snamechar(8),@sscorerealoutputASSELECT@sscore=scorefromscjoinsons.sno=sc.snowheresn=@snameGO8.1.3執(zhí)行存儲過程

可以使用Transact-SQLEXECUTE語句來運(yùn)行存儲過程。存儲過程與函數(shù)不同，因為存儲過程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。執(zhí)行存儲過程必須具有執(zhí)行存儲過程的權(quán)限許可，才可以直接執(zhí)行存儲過程，直接執(zhí)行存儲過程可以使用EXECUTE命令來執(zhí)行，語法形式如下：[[EXEC[UTE]]

{

[@return_status=]

{procedure_name[;number]|@procedure_name_var}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}

[,...n]

[WITHRECOMPILE]8.1.3執(zhí)行存儲過程

例8-6執(zhí)行存儲過程au_infor_all。au_infor_all存儲過程可以通過以下方法執(zhí)行：EXECUTE〔EXEC〕au_infor_all例8-7使用EXECUTE命令傳遞參數(shù)，執(zhí)行例8-2定義的存儲過程pr1_sc_ins。sc_ins存儲過程可以通過以下方法執(zhí)行：EXECpr1_sc_ins‘3130040101’,’c1’,85當(dāng)然，在執(zhí)行過程中變量可以顯式命名：EXECsc_ins@Param1=’3130040101’,@Param2=’c1’,@Param3=85例8-8執(zhí)行例8-3定義的存儲過程GetEmployees。GetEmployees存儲過程可以通過以下方法執(zhí)行：EXECUTE〔EXEC〕GetEmployees'Dull','Ann'或者EXECUTE〔EXEC〕GetEmployees@lastname='Dull',@firstname='Ann'或者EXECUTE〔EXEC〕GetEmployees@firstname='Ann',@lastname='Dull'8.2查看、修改和刪除存儲過程

8.2.1查看存儲過程8.2.2修改存儲過程8.2.3重命名和刪除存儲過程8.2.1查看存儲過程〔1〕使用SQLServer管理平臺查看用戶創(chuàng)立的存儲過程。在SQLServer管理平臺中，展開指定的效勞器和數(shù)據(jù)庫，選擇并依次展開“程序→存儲過程〞，然后右擊要查看的存儲過程名稱，如圖8-3所示，從彈出的快捷菜單中，選擇“創(chuàng)立存儲過程腳本為→CREATE到→新查詢編輯器窗口〞，那么可以看到存儲過程的源代碼。圖8-3查看存儲過程

8.2.1查看存儲過程

〔2〕使用系統(tǒng)存儲過程來查看用戶創(chuàng)立的存儲過程。可供使用的系統(tǒng)存儲過程及其語法形式如下：sp_help，用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型，其語法為：sp_help[[@objname=]name]，參數(shù)name為要查看的存儲過程的名稱。sp_helptext，用于顯示存儲過程的源代碼，其語法為：sp_helptext[[@objname=]name]，參數(shù)name為要查看的存儲過程的名稱。sp_depends，用于顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象，其語法為：sp_depends[@objname=]’object’，參數(shù)object為要查看依賴關(guān)系的存儲過程的名稱。sp_stored_procedures，用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表，其語法為：sp_stored_procedures[[@sp_name=]'name']

[,[@sp_owner=]'owner']

[,[@sp_qualifier=]'qualifier']其中，[@sp_name=]'name'用于指定返回目錄信息的過程名；[@sp_owner=]'owner'用于指定過程所有者的名稱；[@qualifier=]'qualifier'用于指定過程限定符的名稱。8.2.2修改存儲過程

存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTERPROCEDURE語句可以更改先前通過執(zhí)行CREATEPROCEDURE語句創(chuàng)立的過程，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。修改存儲過程語法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]AS

sql_statement[...n]8.2.2修改存儲過程

例8-9創(chuàng)立了一個名為proc_person的存儲過程，該存儲過程包含姓名和Email地址信息。然后，用ALTERPROCEDURE重新定義了該存儲過程，使之只包含姓名信息，并使用ENCRYPTION關(guān)鍵字使之無法通過查看syscomments表來查看存儲過程的內(nèi)容。程序清單如下。USEadventureworksGO/*創(chuàng)立一個存儲過程，該存儲過程包含姓名和Email地址信息*/CREATEPROCEDUREproc_personASSELECTfirstname,lastname,emailaddressFROMperson.contactORDERBYlastname,firstnameGO8.2.2修改存儲過程

下面對該存儲過程進(jìn)行重新定義。使之只包含姓名信息，并使用ENCRYPTION關(guān)鍵字使之無法通過查看syscomments表來查看存儲過程的內(nèi)容。程序清單如下：ALTERPROCED