【正文】 第 10章 存儲(chǔ)過程與觸發(fā)器 本章內(nèi)容 存儲(chǔ)過程概述 存儲(chǔ)過程的創(chuàng)建與使用 觸發(fā)器概述 觸發(fā)器的創(chuàng)建與使用 事務(wù)處理 SQL Server的鎖機(jī)制 存儲(chǔ)過程概述 ? 存儲(chǔ)過程是 SQL Server服務(wù)器上一組預(yù)編譯的 TransactSQL語句，用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。 存儲(chǔ)過程概述 ?SQL Server存儲(chǔ)過程的類型包括： ?系統(tǒng)存儲(chǔ)過程 ?用戶定義存儲(chǔ)過程 ?臨時(shí)存儲(chǔ)過程 ?擴(kuò)展存儲(chǔ)過程 1. 存儲(chǔ)過程的類型 存儲(chǔ)過程概述 (1)系統(tǒng)存儲(chǔ)過程 ? 是指由系統(tǒng)提供的存儲(chǔ)過程，主要存儲(chǔ)在 master數(shù)據(jù)庫中并 以 sp_為前綴 ，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理 SQL Server提供支持。 ? 通過系統(tǒng)存儲(chǔ)過程， SQL Server中的許多管理性或信息性的活動(dòng) (例如使用 sp_depends、 sp_helptexts可以了解數(shù)據(jù)數(shù)據(jù)庫對(duì)象、數(shù)據(jù)庫信息 )都可以順利有效地完成。盡管系統(tǒng)存儲(chǔ)過程被放在 master數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)庫中對(duì)其進(jìn)行調(diào)用 (調(diào)用時(shí)，不必在存儲(chǔ)過程名前加上數(shù)據(jù)庫名 )。當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)庫時(shí)，一些系統(tǒng)存儲(chǔ)過程會(huì)在新數(shù)據(jù)庫中被自動(dòng)創(chuàng)建。 存儲(chǔ)過程概述 (2)用戶定義存儲(chǔ)過程 ? 是由用戶創(chuàng)建并能完成某一特定功能 (例如查詢用戶所需數(shù)據(jù)信息 )的存儲(chǔ)過程。 ? 它處于用戶創(chuàng)建的數(shù)據(jù)庫中，存儲(chǔ)過程名前 沒有前綴 sp_。 存儲(chǔ)過程概述 (3)臨時(shí)存儲(chǔ)過程 ? 臨時(shí)存儲(chǔ)過程與臨時(shí)表類似，分為局部臨時(shí)存儲(chǔ)過程和全局臨時(shí)存儲(chǔ)過程，且可以分別向該過程名稱前面添加 “ ”或 “ ”前綴表示?！?”表示本地臨時(shí)存儲(chǔ)過程，“ ”表示全局臨時(shí)存儲(chǔ)過程。使用臨時(shí)存儲(chǔ)過程必須創(chuàng)建本地連接，當(dāng) SQL Server關(guān)閉后，這些臨時(shí)存儲(chǔ)過程將自動(dòng)被刪除。 ? 由于 SQL Server支持重新使用執(zhí)行計(jì)劃，所以連接到 SQL Server 2021的應(yīng)用程序應(yīng)使用 sp_executesql系統(tǒng)存儲(chǔ)過程，而不使用臨時(shí)存儲(chǔ)過程。 存儲(chǔ)過程概述 (4)擴(kuò)展存儲(chǔ)過程 ? 擴(kuò)展存儲(chǔ)過程是 SQL Server可以動(dòng)態(tài)裝載和執(zhí)行的動(dòng)態(tài)鏈接庫 (DLL)。當(dāng)擴(kuò)展存儲(chǔ)過程加載到SQL Server中，它的使用方法與系統(tǒng)存儲(chǔ)過程一樣。擴(kuò)展存儲(chǔ)過程只能添加到 master數(shù)據(jù)庫中，其 前綴是 xp_。 存儲(chǔ)過程概述 2. 存儲(chǔ)過程的功能特點(diǎn) ? SQL Server的存儲(chǔ)過程可實(shí)現(xiàn)以下功能： (1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個(gè)值。 (2)包含執(zhí)行數(shù)據(jù)庫操作的編程語句，包括調(diào)用其他過程。 (3)為調(diào)用過程或批處理返回一個(gè)狀態(tài)值，以表示成功或失敗 (及失敗原因 )。 存儲(chǔ)過程概述 存儲(chǔ)過程具有以下優(yōu)點(diǎn) ? (1)模塊化編程。 ? (2)快速執(zhí)行。 ? (3)減少網(wǎng)絡(luò)通信量。 ? (4)提供安全機(jī)制。 ? (5)保證操作一致性。 存儲(chǔ)過程的創(chuàng)建與使用 存儲(chǔ)過程的創(chuàng)建與使用 1. 使用企業(yè)管理器創(chuàng)建存儲(chǔ)過程 存儲(chǔ)過程創(chuàng)建 創(chuàng)建存儲(chǔ)過程 2. 使用向?qū)?chuàng)建存儲(chǔ)過程 3. 使用 CREATE PROCEDURE語句創(chuàng)建存儲(chǔ)過程 ? 使用 CREATE PROCEDURE語句創(chuàng)建存儲(chǔ)過程應(yīng)該考慮以下幾個(gè)方面： ? (1)在一個(gè)批處理中， CREATE PROCEDURE語句不能與其他 SQL語句合并在一起。 ? (2)數(shù)據(jù)庫所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。 ? (3)存儲(chǔ)過程作為數(shù)據(jù)庫對(duì)象其命名必須符合標(biāo)識(shí)符的命名規(guī)則。 ? (4)只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫的存儲(chǔ)過程。 創(chuàng)建存儲(chǔ)過程 創(chuàng)建存儲(chǔ)過程語句的語法格式如下： CREATE PROC[EDURE] procedure_name [。 number ] [{@parameter data_type } [VARYING] [=default] [OUTPUT]] [, ...n ] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [,...n ] 創(chuàng)建存儲(chǔ)過程 例 101 創(chuàng)建存儲(chǔ)過程，從表 goods和表goods_classification的聯(lián)接中返回商品名、商品類別、單價(jià)。 創(chuàng)建存儲(chǔ)過程 CREATE PROCEDURE goods_info AS SELECT goods_name, classification_name, unit_price FROM goods g INNER JOIN goods_classification gc ON = ? 存儲(chǔ)過程創(chuàng)建后，存儲(chǔ)過程的名稱存放在 sysobject表中，文本存放在 sysments表中。 執(zhí)行存儲(chǔ)過程 ? 執(zhí)行存儲(chǔ)過程的語法格式： [[EXEC[UTE]] {[@return_status=] procedure_name [。number]|@procedure_name_var} [[@parameter=]{value|@variable [OUTPUT]|[DEFAULT]] [ ,...n ] [WITH RECOMPILE ] 存儲(chǔ)過程的創(chuàng)建與使用 例如，執(zhí)行例 101的存儲(chǔ)過程 goods_info ? 在 SQL查詢分析器中輸入命令： EXEC goods_info ? 運(yùn)行的結(jié)果： 執(zhí)行存儲(chǔ)過程 存儲(chǔ)過程的創(chuàng)建與使用 修改存儲(chǔ)過程 1. 使用企業(yè)管理器修改存儲(chǔ)過程 2. 使用 ALTER PROCEDURE語句修改存儲(chǔ)過程 ? ALTER PROCEDURE的語法規(guī)則是： ALTER PROC[EDURE ] procedure_name [ 。 number ] [{@parameter data_type} [VARYING][=default] [OUTPUT]] [ ,...n ] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION ] AS sql_statement [ ...n ] 修改存儲(chǔ)過程 查看存儲(chǔ)過程的文本信息 SELECT , FROM sysobjects o INNER JOIN sysments c ON = WHERE = 39。P 39。 AND = 39。procedure_name 39。 GO 存儲(chǔ)過程的創(chuàng)建與使用 刪除存儲(chǔ)過程 2. 使用 DROP PROCEDURE刪除存儲(chǔ)過程 ? DROP PROCEDURE的語法如下： DROP PROCEDURE {procedure_name} [ ,...n ] ? 例如刪除例 101創(chuàng)建的存儲(chǔ)過程 goods_info： DROP PROCEDURE goods_info GO 存儲(chǔ)過程的創(chuàng)建與使用 存儲(chǔ)過程參數(shù)與狀態(tài)值 ? 存儲(chǔ)過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲(chǔ)過程返回的狀態(tài)值對(duì)存儲(chǔ)過程進(jìn)行管理。 ? 1. 參數(shù) ? 存儲(chǔ)過程的參數(shù)在創(chuàng)建過程時(shí)聲明。 ? SQL Server支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。 ? (1)輸入?yún)?shù) ? 輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯?chǔ)過程傳送數(shù)據(jù)值。要定義存儲(chǔ)過程的輸入?yún)?shù)，必須在 CREATE PROCEDURE語句中聲明一個(gè)或多個(gè)變量及類型。 例 103 創(chuàng)建帶參數(shù)的存儲(chǔ)過程，從表 employee、 sell_order、goods、 goods_classification的連接中返回 輸入的員工名 、該員工銷售的商品名、商品類別、銷售量等信息。 CREATE PROC sell_info @employee_name varchar(20) AS SELECT employee_name, goods_name,classification_name, order_num FROM employee e INNER JOIN