SQLServer2005數(shù)據(jù)庫簡明教程第8章存儲過程的操作與管理

1、.第第8 8章章 存儲過程的操作與管理存儲過程的操作與管理 .存儲過程概述 存儲過程是為完成特定的功能而匯集在一起的一組SQL程序語句，經(jīng)編譯后存儲在數(shù)據(jù)庫中的SQL程序。 在 SQL Server 中使用存儲過程而不使用存儲在客戶端計算機本地的 Transact-SQL 程序的優(yōu)點包括： （1）存儲過程已在服務(wù)器注冊。 （2）存儲過程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加到它們的證書。 （3）存儲過程可以強制應(yīng)用程序的安全性。 （4）存儲過程允許模塊化程序設(shè)計。 （5）存儲過程是命名代碼，允許延遲綁定。 （6）存儲過程可以減少網(wǎng)絡(luò)通信流量。 .8.1 創(chuàng)建存儲過程 在SQL Se

2、rver中，可以使用兩種方法創(chuàng)建存儲過程：（1）使用創(chuàng)建存儲過程模板創(chuàng)建存儲過程；（2）利用SQL Server 管理平臺創(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)建存儲過程 CREATE PROCEDURE的語法形式如下： CREATE PROC|PROCEDURE schema_cedure_name;number parametertype_schema_name.

3、data_type VARYING=defaultOUTPUT,.n WITH ,.n FOR REPLICATION AS ;.n|; := ENCRYPTIONRECOMPILE EXECUTE_AS_Clause := BEGIN statements END := EXTERNAL NAME assembly_name.class_name.method_name .8.1.1 使用模板創(chuàng)建存儲過程 （1）在SQL Server 管理平臺中，選擇“視圖（View）”菜單中的“模板資源資源管理器(Template Explorer)”，出現(xiàn)“模板資源管理器(Template Explor

4、er)”窗口，選擇“存儲過程”中的“創(chuàng)建存儲過程”選項，如圖8-1所示。（2）在文本框中可以輸入創(chuàng)建存儲過程的Transact_SQL語句，單擊“執(zhí)行”按鈕，即可創(chuàng)建該存儲過程。圖8-1 創(chuàng)建存儲過程模板 .8.1.2使用管理平臺創(chuàng)建存儲過程 （1）在SQL Server管理平臺中，展開指定的服務(wù)器和數(shù)據(jù)庫，然后展開程序，右擊存儲過程選項，在彈出的快捷菜單中依次選擇“新建存儲過程”選項，如圖8-2所示，出現(xiàn)創(chuàng)建存儲過程窗口。（2）在文本框中可以輸入創(chuàng)建存儲過程的Transact_SQL語句，單擊“執(zhí)行”按鈕，即可創(chuàng)建該存儲過程。 圖8-2 新建存儲過程.8.1.2使用管理平臺創(chuàng)建存儲過程 例8

5、-1創(chuàng)建一個帶有SELECT語句的簡單過程，該存儲過程返回所有員工姓名，Email地址，電話等。該存儲過程不使用任何參數(shù)程序清單如下。USE adventureworksGOCREATE PROCEDURE au_infor_allASSELECT lastname, firstname, emailaddress, phone FROM person.contactGO .8.1.2使用管理平臺創(chuàng)建存儲過程 例8-2 創(chuàng)建一個存儲過程，以簡化對sc表的數(shù)據(jù)添加工作，使得在執(zhí)行該存儲過程時，其參數(shù)值作為數(shù)據(jù)添加到表中。程序清單如下：CREATE PROCEDURE dbo. pr1_sc_in

6、s Param1 char(10),Param2 char(2),Param3 real ASBEGINinsert into sc(sno,cno,score) values(Param1,Param2,Param3)END .8.1.2使用管理平臺創(chuàng)建存儲過程 例8-3創(chuàng)建一個帶有參數(shù)的簡單存儲過程，從視圖中返回指定的雇員（提供名和姓）及其職務(wù)和部門名稱，該存儲過程接受與傳遞的參數(shù)精確匹配的值程序清單如下。USE AdventureWorks;GOCREATE PROCEDURE GetEmployees lastname varchar(40), firstname varchar(20

7、) AS SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = firstname AND LastName = lastname;GO.8.1.2使用管理平臺創(chuàng)建存儲過程 例8-4下面的存儲過程從表person.contact中返回指定的一些員工姓名及其電話。該存儲過程對傳遞的參數(shù)進行模式匹配。如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認值（姓氏以字母D開頭）程序清單如下。USE AdventureWorks;GOCREATE PROCEDUR

8、E au_infor2lastname varchar(40) = D%, firstname varchar(20) = % AS SELECT firstname, lastname, phoneFROM person.contact WHERE firstname LIKE firstname AND lastname LIKE lastnameGO.8.1.2使用管理平臺創(chuàng)建存儲過程 例8-5以下示例顯示有一個輸入?yún)?shù)和一個輸出參數(shù)的存儲過程。存儲過程中的第一個參數(shù)sname將接收由調(diào)用程序指定的輸入值(學(xué)生姓名)，第二個參數(shù)sscore（成績）將用于將該值返回調(diào)用程序。SELECT

9、語句使用sname參數(shù)獲取正確的sscore值，并將該值分配給輸出參數(shù)。程序清單如下：CREATE PROCEDURE s_scoresname char(8),sscore real outputASSELECT sscore =score from sc join s on s.sno=sc.sno where sn=snameGO .8.1.3 執(zhí)行存儲過程 可以使用 Transact-SQL EXECUTE 語句來運行存儲過程。存儲過程與函數(shù)不同，因為存儲過程不返回取代其名稱的值，也不能直接在表達式中使用。執(zhí)行存儲過程必須具有執(zhí)行存儲過程的權(quán)限許可，才可以直接執(zhí)行存儲過程，直接執(zhí)行存儲

10、過程可以使用EXECUTE命令來執(zhí)行，語法形式如下： EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE .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存儲過程可以

11、通過以下方法執(zhí)行：EXEC pr1_sc_ins 3130040101,c1,85當(dāng)然，在執(zhí)行過程中變量可以顯式命名：EXEC sc_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 fir

12、stname = Ann, lastname = Dull .8.2查看、修改和刪除存儲過程 8.2.1 查看存儲過程 8.2.2 修改存儲過程 8.2.3 重命名和刪除存儲過程 .8.2.1 查看存儲過程（1）使用SQL Server管理平臺查看用戶創(chuàng)建的存儲過程。在SQL Server管理平臺中，展開指定的服務(wù)器和數(shù)據(jù)庫，選擇并依次展開“程序存儲過程”，然后右擊要查看的存儲過程名稱，如圖8-3所示，從彈出的快捷菜單中，選擇“創(chuàng)建存儲過程腳本為CREATE到新查詢編輯器窗口”，則可以看到存儲過程的源代碼。圖8-3 查看存儲過程 .8.2.1 查看存儲過程 （2）使用系統(tǒng)存儲過程來查看用戶創(chuàng)建

13、的存儲過程 ?？晒┦褂玫南到y(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ù)庫中的存儲過程列表

14、，其語法為： sp_stored_proceduressp_name=name ,sp_owner=owner ,sp_qualifier = qualifier 其中，sp_name = name 用于指定返回目錄信息的過程名；sp_owner = owner 用于指定過程所有者的名稱；qualifier = qualifier 用于指定過程限定符的名稱。.8.2.2 修改存儲過程 存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTER PROCEDURE語句可以更改先前通過執(zhí)行 CREATE PROCEDURE 語句創(chuàng)建的過程，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。修

15、改存儲過程語法形式如下： ALTER PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATION AS sql_statement .n .8.2.2 修改存儲過程 例8-9創(chuàng)建了一個名為proc_person 的存儲過程，該存儲過程包含姓名和Email地址信息。然后，用ALTER PROCEDURE重新定義了該存儲過程，使之只包含姓名信息，并使用ENCRYPTION關(guān)鍵字使之無

16、法通過查看syscomments表來查看存儲過程的內(nèi)容。程序清單如下。USE adventureworksGO/*創(chuàng)建一個存儲過程，該存儲過程包含姓名和Email地址信息*/CREATE PROCEDURE proc_personAS SELECT firstname, lastname, emailaddressFROM person.contactORDER BY lastname, firstnameGO.8.2.2 修改存儲過程 下面對該存儲過程進行重新定義。使之只包含姓名信息，并使用ENCRYPTION關(guān)鍵字使之無法通過查看syscomments表來查看存儲過程的內(nèi)容。程序清單如下：

17、 ALTER PROCEDURE proc_personWITH ENCRYPTIONAS SELECT firstname, lastnameFROM person.contactORDER BY lastname, firstnameGO.8.2.3 重命名和刪除存儲過程 1. 重命名存儲過程修改存儲過程的名稱可以使用系統(tǒng)存儲過程sp_rename，其語法形式如下： sp_rename 原存儲過程名稱，新存儲過程名稱另外，通過SQL Server管理平臺也可以修改存儲過程的名稱。在SQL Server管理平臺中，右擊要操作的存儲過程名稱，從彈出的快捷菜單中選擇“重命名”選項，當(dāng)存儲過程名稱變成可輸入狀態(tài)時，就可以