【正文】 網(wǎng)絡(luò)數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù) 存儲(chǔ)過(guò)程和觸發(fā)器 張蕾 2 什么是存儲(chǔ)過(guò)程 ? SQL Server提供了一種方法，它可以將一些固定的操作集中起來(lái)由 SQL Server數(shù)據(jù)庫(kù)服務(wù)器來(lái)完成，以實(shí)現(xiàn)某個(gè)任務(wù)，這種方法就是存儲(chǔ)過(guò)程。 3 存儲(chǔ)過(guò)程的類型 (1) 系統(tǒng)存儲(chǔ)過(guò)程 系統(tǒng)存儲(chǔ)過(guò)程是由系統(tǒng)提供的存儲(chǔ)過(guò)程，作為命令執(zhí)行各種操作。 (2) 本地存儲(chǔ)過(guò)程 本地存儲(chǔ)過(guò)程是指在用戶數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程完成特定數(shù)據(jù)庫(kù)操作任務(wù)，其名稱不能以 sp_為前綴。 (3) 臨時(shí)存儲(chǔ)過(guò)程 臨時(shí)存儲(chǔ)過(guò)程屬于本地存儲(chǔ)過(guò)程。如果本地存儲(chǔ)過(guò)程的名稱前面有一個(gè)“ ” ，該存儲(chǔ)過(guò)程就稱為局部臨時(shí)存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程只能在一個(gè)用戶會(huì)話中使用。 (4) 遠(yuǎn)程存儲(chǔ)過(guò)程 遠(yuǎn)程存儲(chǔ)過(guò)程指從遠(yuǎn)程服務(wù)器上調(diào)用的存儲(chǔ)過(guò)程。 (5) 擴(kuò)展存儲(chǔ)過(guò)程 在 SQL Server環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)稱為擴(kuò)展存儲(chǔ)過(guò)程，其前綴是sp_。使用時(shí)需要先加載到 SQL Server系統(tǒng)中，并且按照使用存儲(chǔ)過(guò)程的方法執(zhí)行。 4 存儲(chǔ)過(guò)程 使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)： (1) 存儲(chǔ)過(guò)程在服務(wù)器端運(yùn)行，執(zhí)行速度快。 (2) 存儲(chǔ)過(guò)程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲(chǔ)器，在以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，提高了系統(tǒng)性能。 (3) 確保數(shù)據(jù)庫(kù)的安全。使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并可通過(guò)編程方式控制上述操作對(duì)數(shù)據(jù)庫(kù)信息訪問(wèn)的權(quán)限。 (4) 自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過(guò)程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，大大方便了用戶的使用，可以自動(dòng)完成一些需要預(yù)先執(zhí)行的任務(wù)。 5 ? 函數(shù)有返回值，而存儲(chǔ)過(guò)程的返回值僅說(shuō)明該過(guò)程是否成功完成。 存儲(chǔ)過(guò)程與函數(shù)的比較 ? 函數(shù)與存儲(chǔ)過(guò)程基本類似，調(diào)用時(shí)函數(shù)要用表達(dá)式方式，而存儲(chǔ)過(guò)程不能直接調(diào)用，必須采用“ EXEC 存儲(chǔ)過(guò)程名 ” 或 “ CALL 存儲(chǔ)過(guò)程名 ” 。 6 創(chuàng)建存儲(chǔ)過(guò)程 ? 在 SQL Server中，可以使用三種方法創(chuàng)建存儲(chǔ)過(guò)程 ： ①使用創(chuàng)建存儲(chǔ)過(guò)程向?qū)?chuàng)建存儲(chǔ)過(guò)程。 ②利用 SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。 ③使用 TransactSQL語(yǔ)句中的 CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程。 7 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行 ? 在用戶存儲(chǔ)過(guò)程的定義中不能使用下列對(duì)象創(chuàng)建語(yǔ)句： CREATE VIEW CREATE DEFAULT CREATE RULE 1．通過(guò) SQL命令創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程 如果要通過(guò) SQL命令定義一個(gè)存儲(chǔ)過(guò)程查詢 XSCJ數(shù)據(jù)庫(kù)中每個(gè)同學(xué)各門功課的成績(jī) 定義如下存儲(chǔ)過(guò)程 USE XSCJ Go CREATE PROCEDURE student_grade AS SELECT , , , FROM XS,XS_KC,KC WHERE = AND = Go 8 存儲(chǔ)過(guò)程 ? 調(diào)用存儲(chǔ)過(guò)程 EXEC student_grade GO 1) 創(chuàng)建存儲(chǔ)過(guò)程 語(yǔ)法格式 : CREATE { PROC | PROCEDURE } procedure_name /*定義過(guò)程名 */ [ { @parameter data_type } /*定義參數(shù)的類型 */ [ VARYING ] [ = default ] [ OUTPUT ] ] /*定義參數(shù)的屬性 */ [ ,...n1 ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }] /*定義存儲(chǔ)過(guò)程的處理方式 */ AS sql_statement [ ...n2 ] /*執(zhí)行的操作 */ 9 存儲(chǔ)過(guò)程 對(duì)于存儲(chǔ)過(guò)程要注意下列幾點(diǎn)： (1) 用戶定義的存儲(chǔ)過(guò)程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建。 (2) 成功執(zhí)行 CREATE PROCEDURE 語(yǔ)句后，過(guò)程名稱存儲(chǔ)在sysobjects 系統(tǒng)表中，而 CREATE PROCEDURE 語(yǔ)句的文本存儲(chǔ)在 sysments 中。 (3) 自動(dòng)執(zhí)行存儲(chǔ)過(guò)程 SQL Server 啟動(dòng)時(shí)可以自動(dòng)執(zhí)行一個(gè)或多個(gè)存儲(chǔ)過(guò)程。這些存儲(chǔ)過(guò)程必須由系統(tǒng)管理員在 master 數(shù)據(jù)庫(kù)中創(chuàng)建，并在 sysadmin 固定服務(wù)器角色下作為后臺(tái)過(guò)程執(zhí)行。 (4)一個(gè)存儲(chǔ)過(guò)程的最大尺寸為 128M。 (5)創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫(kù)所有者，該所有者可將此權(quán)限授予其他用戶。 10 存儲(chǔ)過(guò)程 2）存儲(chǔ)過(guò)程的執(zhí)行 通過(guò) EXEC命令可以執(zhí)行一個(gè)已定義的存儲(chǔ)過(guò)程。 語(yǔ)法格式： [ EXEC [ UTE ] ] { [ @return_status = ] { procedure_name [ 。number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,...n ] [ WITH RECOMPILE ] ｝ 存儲(chǔ)過(guò)程的執(zhí)行要注意下列幾點(diǎn)： (1) 如果存儲(chǔ)過(guò)程名的前三個(gè)字符為 sp_， SQL Server 會(huì)在 Master 數(shù)據(jù)庫(kù)中尋找該過(guò)程。如果沒(méi)能找到合法的過(guò)程名， SQL Server 會(huì)尋找所有者名稱為 dbo 的過(guò)程。 (2) 參數(shù)可以通過(guò) value 或 @parameter_name = value 提供。 (3) 執(zhí)行存儲(chǔ)過(guò)程時(shí)，若語(yǔ)句是批處理中的第一個(gè)語(yǔ)句，則不一定要指定EXECUTE 關(guān)鍵字。 11 存儲(chǔ)過(guò)程 3) 舉例 (1) 設(shè)計(jì)簡(jiǎn)單的存儲(chǔ)過(guò)程 [例 ]從 XSCJ數(shù)據(jù)庫(kù)的三個(gè)表中查詢，返回學(xué)生學(xué)號(hào)、姓名、課程名、成績(jī)、學(xué)分。該存儲(chǔ)過(guò)程不使用任何參數(shù)。 USE XSCJ CREATE PROCEDURE student_info/*創(chuàng)建存儲(chǔ)過(guò)程 */ AS SELECT , 姓名 , 課程名 , 成績(jī) , 學(xué)分 FROM XS a INNER JOIN XS_KC b ON = INNER JOIN KC t ON = GO 12 存儲(chǔ)過(guò)程 (2) 使用帶參數(shù)的存儲(chǔ)過(guò)程 [例 ]從 XSCJ數(shù)據(jù)庫(kù)的三個(gè)表中查詢某人指定課程的成績(jī)和學(xué)分。該存儲(chǔ)過(guò)程接受與傳遞參數(shù)精確匹配的值。 USE XSCJ CREATE PROCED