SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理課件

1、SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理第第8 8章章 存儲(chǔ)過(guò)程的操作與管理存儲(chǔ)過(guò)程的操作與管理 SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理存儲(chǔ)過(guò)程概述 存儲(chǔ)過(guò)程是為完成特定的功能而匯集在一起的一組SQL程序語(yǔ)句，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中的SQL程序。 在 SQL Server 中使用存儲(chǔ)過(guò)程而不使用存儲(chǔ)在客戶端計(jì)算機(jī)本地的 Transact-SQL 程序的優(yōu)點(diǎn)包括： （1）存儲(chǔ)過(guò)程已在服務(wù)器注冊(cè)。 （2）存儲(chǔ)過(guò)程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加到它們的證書。 （3）存儲(chǔ)過(guò)程可以強(qiáng)制應(yīng)用程序的安全性。 （4）存儲(chǔ)過(guò)程允許模塊化程序設(shè)計(jì)。 （5）存儲(chǔ)過(guò)程是命

2、名代碼，允許延遲綁定。 （6）存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)通信流量。 SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1 創(chuàng)建存儲(chǔ)過(guò)程 在SQL Server中，可以使用兩種方法創(chuàng)建存儲(chǔ)過(guò)程：（1）使用創(chuàng)建存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程；（2）利用SQL Server 管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程。 當(dāng)創(chuàng)建存儲(chǔ)過(guò)程時(shí)，需要確定存儲(chǔ)過(guò)程的三個(gè)組成部分： （1）所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。 （2）被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其他存儲(chǔ)過(guò)程的語(yǔ)句。 （3）返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。 SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1 創(chuàng)建存儲(chǔ)過(guò)程 CREATE P

3、ROCEDURE的語(yǔ)法形式如下： CREATE PROC|PROCEDURE schema_cedure_name;number parametertype_schema_name. 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 SQLServer數(shù)據(jù)庫(kù)

4、簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.1 使用模板創(chuàng)建存儲(chǔ)過(guò)程 （1）在SQL Server 管理平臺(tái)中，選擇“視圖（View）”菜單中的“模板資源資源管理器(Template Explorer)”，出現(xiàn)“模板資源管理器(Template Explorer)”窗口，選擇“存儲(chǔ)過(guò)程”中的“創(chuàng)建存儲(chǔ)過(guò)程”選項(xiàng)，如圖8-1所示。（2）在文本框中可以輸入創(chuàng)建存儲(chǔ)過(guò)程的Transact_SQL語(yǔ)句，單擊“執(zhí)行”按鈕，即可創(chuàng)建該存儲(chǔ)過(guò)程。圖8-1 創(chuàng)建存儲(chǔ)過(guò)程模板 SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.2使用管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 （1）在SQL Server管理平臺(tái)中，展開(kāi)指定的服務(wù)器

5、和數(shù)據(jù)庫(kù)，然后展開(kāi)程序，右擊存儲(chǔ)過(guò)程選項(xiàng)，在彈出的快捷菜單中依次選擇“新建存儲(chǔ)過(guò)程”選項(xiàng)，如圖8-2所示，出現(xiàn)創(chuàng)建存儲(chǔ)過(guò)程窗口。（2）在文本框中可以輸入創(chuàng)建存儲(chǔ)過(guò)程的Transact_SQL語(yǔ)句，單擊“執(zhí)行”按鈕，即可創(chuàng)建該存儲(chǔ)過(guò)程。 圖8-2 新建存儲(chǔ)過(guò)程SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.2使用管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 例8-1創(chuàng)建一個(gè)帶有SELECT語(yǔ)句的簡(jiǎn)單過(guò)程，該存儲(chǔ)過(guò)程返回所有員工姓名，Email地址，電話等。該存儲(chǔ)過(guò)程不使用任何參數(shù)程序清單如下。USE adventureworksGOCREATE PROCEDURE au_infor_allASSELECT

6、lastname, firstname, emailaddress, phone FROM person.contactGO SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.2使用管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 例8-2 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以簡(jiǎn)化對(duì)sc表的數(shù)據(jù)添加工作，使得在執(zhí)行該存儲(chǔ)過(guò)程時(shí)，其參數(shù)值作為數(shù)據(jù)添加到表中。程序清單如下：CREATE PROCEDURE dbo. pr1_sc_ins Param1 char(10),Param2 char(2),Param3 real ASBEGINinsert into sc(sno,cno,score) values(Param1,Para

7、m2,Param3)END SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.2使用管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 例8-3創(chuàng)建一個(gè)帶有參數(shù)的簡(jiǎn)單存儲(chǔ)過(guò)程，從視圖中返回指定的雇員（提供名和姓）及其職務(wù)和部門名稱，該存儲(chǔ)過(guò)程接受與傳遞的參數(shù)精確匹配的值程序清單如下。USE AdventureWorks;GOCREATE PROCEDURE GetEmployees lastname varchar(40), firstname varchar(20) AS SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.v

8、EmployeeDepartment WHERE FirstName = firstname AND LastName = lastname;GOSQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.2使用管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 例8-4下面的存儲(chǔ)過(guò)程從表person.contact中返回指定的一些員工姓名及其電話。該存儲(chǔ)過(guò)程對(duì)傳遞的參數(shù)進(jìn)行模式匹配。如果沒(méi)有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值（姓氏以字母D開(kāi)頭）程序清單如下。USE AdventureWorks;GOCREATE PROCEDURE au_infor2lastname varchar(40) = D%, firstname va

9、rchar(20) = % AS SELECT firstname, lastname, phoneFROM person.contact WHERE firstname LIKE firstname AND lastname LIKE lastnameGOSQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.2使用管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 例8-5以下示例顯示有一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程中的第一個(gè)參數(shù)sname將接收由調(diào)用程序指定的輸入值(學(xué)生姓名)，第二個(gè)參數(shù)sscore（成績(jī)）將用于將該值返回調(diào)用程序。SELECT 語(yǔ)句使用sname參數(shù)獲取正確的sscore值，并

10、將該值分配給輸出參數(shù)。程序清單如下：CREATE PROCEDURE s_scoresname char(8),sscore real outputASSELECT sscore =score from sc join s on s.sno=sc.sno where sn=snameGO SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.3 執(zhí)行存儲(chǔ)過(guò)程 可以使用 Transact-SQL EXECUTE 語(yǔ)句來(lái)運(yùn)行存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程與函數(shù)不同，因?yàn)榇鎯?chǔ)過(guò)程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。執(zhí)行存儲(chǔ)過(guò)程必須具有執(zhí)行存儲(chǔ)過(guò)程的權(quán)限許可，才可以直接執(zhí)行存儲(chǔ)過(guò)程，直接執(zhí)行存儲(chǔ)

11、過(guò)程可以使用EXECUTE命令來(lái)執(zhí)行，語(yǔ)法形式如下： EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.1.3 執(zhí)行存儲(chǔ)過(guò)程 例8-6 執(zhí)行存儲(chǔ)過(guò)程au_infor_all。au_infor_all 存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXECUTE（EXEC） au_infor_all例8-7 使用 EXECUTE 命令傳遞參數(shù)，執(zhí)行例8-2定義的存儲(chǔ)

12、過(guò)程pr1_sc_ins。sc_ins存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXEC pr1_sc_ins 3130040101,c1,85當(dāng)然，在執(zhí)行過(guò)程中變量可以顯式命名：EXEC sc_ins Param1= 3130040101,Param2=c1, Param3=85例8-8 執(zhí)行例8-3定義的存儲(chǔ)過(guò)程GetEmployees 。GetEmployees存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXECUTE（EXEC） GetEmployees Dull, Ann 或者EXECUTE（EXEC） GetEmployees lastname = Dull, firstname = Ann 或者EXECU

13、TE（EXEC） GetEmployees firstname = Ann, lastname = Dull SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.2查看、修改和刪除存儲(chǔ)過(guò)程 8.2.1 查看存儲(chǔ)過(guò)程 8.2.2 修改存儲(chǔ)過(guò)程 8.2.3 重命名和刪除存儲(chǔ)過(guò)程 SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.2.1 查看存儲(chǔ)過(guò)程（1）使用SQL Server管理平臺(tái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程。在SQL Server管理平臺(tái)中，展開(kāi)指定的服務(wù)器和數(shù)據(jù)庫(kù)，選擇并依次展開(kāi)“程序存儲(chǔ)過(guò)程”，然后右擊要查看的存儲(chǔ)過(guò)程名稱，如圖8-3所示，從彈出的快捷菜單中，選擇“創(chuàng)建存儲(chǔ)過(guò)程腳本為

14、CREATE到新查詢編輯器窗口”，則可以看到存儲(chǔ)過(guò)程的源代碼。圖8-3 查看存儲(chǔ)過(guò)程 SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.2.1 查看存儲(chǔ)過(guò)程 （2）使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程 。可供使用的系統(tǒng)存儲(chǔ)過(guò)程及其語(yǔ)法形式如下：sp_help，用于顯示存儲(chǔ)過(guò)程的參數(shù)及其數(shù)據(jù)類型，其語(yǔ)法為： sp_help objname= name，參數(shù)name為要查看的存儲(chǔ)過(guò)程的名稱。sp_helptext，用于顯示存儲(chǔ)過(guò)程的源代碼，其語(yǔ)法為： sp_helptext objname= name，參數(shù)name為要查看的存儲(chǔ)過(guò)程的名稱。sp_depends，用于顯示和存儲(chǔ)過(guò)程相關(guān)的數(shù)

15、據(jù)庫(kù)對(duì)象，其語(yǔ)法為： sp_depends objname=object，參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過(guò)程的名稱。sp_stored_procedures，用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程列表，其語(yǔ)法為： sp_stored_proceduressp_name=name ,sp_owner=owner ,sp_qualifier = qualifier 其中，sp_name = name 用于指定返回目錄信息的過(guò)程名；sp_owner = owner 用于指定過(guò)程所有者的名稱；qualifier = qualifier 用于指定過(guò)程限定符的名稱。SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程

16、的操作與管理8.2.2 修改存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTER PROCEDURE語(yǔ)句可以更改先前通過(guò)執(zhí)行 CREATE PROCEDURE 語(yǔ)句創(chuàng)建的過(guò)程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器。修改存儲(chǔ)過(guò)程語(yǔ)法形式如下： ALTER PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATION AS sql_statement

17、.n SQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.2.2 修改存儲(chǔ)過(guò)程 例8-9創(chuàng)建了一個(gè)名為proc_person 的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程包含姓名和Email地址信息。然后，用ALTER PROCEDURE重新定義了該存儲(chǔ)過(guò)程，使之只包含姓名信息，并使用ENCRYPTION關(guān)鍵字使之無(wú)法通過(guò)查看syscomments表來(lái)查看存儲(chǔ)過(guò)程的內(nèi)容。程序清單如下。USE adventureworksGO/*創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程包含姓名和Email地址信息*/CREATE PROCEDURE proc_personAS SELECT firstname, lastname, emai

18、laddressFROM person.contactORDER BY lastname, firstnameGOSQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.2.2 修改存儲(chǔ)過(guò)程 下面對(duì)該存儲(chǔ)過(guò)程進(jìn)行重新定義。使之只包含姓名信息，并使用ENCRYPTION關(guān)鍵字使之無(wú)法通過(guò)查看syscomments表來(lái)查看存儲(chǔ)過(guò)程的內(nèi)容。程序清單如下： ALTER PROCEDURE proc_personWITH ENCRYPTIONAS SELECT firstname, lastnameFROM person.contactORDER BY lastname, firstnameGOSQLServer數(shù)據(jù)庫(kù)簡(jiǎn)明教程存儲(chǔ)過(guò)程的操作與管理8.2.3 重命名和刪除存儲(chǔ)過(guò)程 1. 重命名存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程的名稱可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename，其語(yǔ)法形式如下： sp_rename 原存儲(chǔ)過(guò)程名稱，新存儲(chǔ)過(guò)程名稱另外，通過(guò)SQL Server管理平臺(tái)也可以修改存儲(chǔ)過(guò)程的名稱。在SQL Server管理平臺(tái)中，右擊要操作的存儲(chǔ)過(guò)程名稱，從彈出的快捷菜單中選擇“重