【正文】 2021年 11月 11日星期四 1 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 主要內(nèi)容 ? 兩個(gè)重要的數(shù)據(jù)庫對象 ? 存儲(chǔ)過程 ? 存儲(chǔ)過程的基本概念 ? 存儲(chǔ)過程的各種創(chuàng)建和維護(hù)方法 ? 各種存儲(chǔ)過程的調(diào)用方法 ? 觸發(fā)器 ? 觸發(fā)器的基本概念 ? 各種類型觸發(fā)器的創(chuàng)建方法 ? 觸發(fā)器的應(yīng)用和維護(hù) 2021年 11月 11日星期四 2 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 存儲(chǔ)過程（ Stored Procedure） ? 具有一個(gè)名字、存儲(chǔ)在數(shù)據(jù)庫中、經(jīng)過預(yù)編譯的 TSQL語句集合 ? 包含邏輯、對數(shù)據(jù)庫的查詢 ? 支持控制流，具備過程處理能力，支持過程調(diào)用 ? 可以獨(dú)立執(zhí)行或通過應(yīng)用程序的調(diào)用來執(zhí)行 ? 一旦創(chuàng)建，在服務(wù)器上即被編譯，可在需要時(shí)執(zhí)行多次 ? 存儲(chǔ)過程的特點(diǎn) ? 代碼簡單化 ? 代碼重用性 ? 高速性 ? 網(wǎng)絡(luò)流量小 ? 安全性 存儲(chǔ)過程 2021年 11月 11日星期四 3 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 系統(tǒng)存儲(chǔ)過程（ System Stored Procedure） ? 存儲(chǔ)在 master數(shù)據(jù)庫中 ? 以 sp_為前綴 ? 通過從系統(tǒng)表中獲取信息提供給系統(tǒng)管理員 ? 執(zhí)行 SQL Server許多管理活動(dòng)的特殊存儲(chǔ)過程 ? 例： sp_adduser系統(tǒng)存儲(chǔ) ? 以 xp_為前綴稱為常規(guī)擴(kuò)展存儲(chǔ)過程 ? 為 SQL Server 與外部應(yīng)用程序之間的接口 ? 利用他們可進(jìn)行各種維護(hù)活動(dòng) ? 例： xp_cmdshell 存儲(chǔ)過程 2021年 11月 11日星期四 4 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 存儲(chǔ)過程與函數(shù)區(qū)別 ? 存儲(chǔ)過程可以返回一個(gè)稱為返回碼的整型值，說明過程是否成功完成 ? 而大多數(shù)函數(shù)可以返回其它值 ? 存儲(chǔ)過程要返回其它數(shù)據(jù)，包括結(jié)果集時(shí)，需使用輸出參數(shù) ? 存儲(chǔ)過程的調(diào)用語句若不是批處理的第一條語句，不能直接調(diào)用，必須用 “ EXEC 過程名（參數(shù)）或 CALL 過程名（參數(shù)） ” 的形式來調(diào)用 ? 而函數(shù)的調(diào)用可直接使用 “ 函數(shù)名（參數(shù)） ” 的形式調(diào)用 存儲(chǔ)過程 2021年 11月 11日星期四 5 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 存儲(chǔ)過程的創(chuàng)建 ? 建立存儲(chǔ)過程來執(zhí)行表的各種操作 ? 存儲(chǔ)過程作為數(shù)據(jù)庫的對象存在數(shù)據(jù)庫中 ? 創(chuàng)建存儲(chǔ)過程時(shí)，應(yīng)指定三項(xiàng)內(nèi)容 ? 所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù) ? 執(zhí)行數(shù)據(jù)庫操作（包括調(diào)用其它過程）的編程語句 ? 返回至調(diào)用過程或批處理以表明成功或失敗（以及失敗原因）的狀態(tài)值（返回碼） 存儲(chǔ)過程 2021年 11月 11日星期四 6 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 用 TSQL創(chuàng)建存儲(chǔ)過程 ? CREATE PROC[EDURE] 存儲(chǔ)過程名 ? [(@參數(shù) 1 數(shù)據(jù)類型 ,@參數(shù) 2 數(shù)據(jù)類型 ,… )] ? [默認(rèn)值 ] [ OUTPUT ]] ? AS SQL語句序列 2021年 11月 11日星期四 7 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 其中 ? @參數(shù) ：過程中的參數(shù)，參數(shù)名前為 @號(hào)。可聲明一個(gè)或多個(gè)參數(shù)。在未提供參數(shù)的默認(rèn)值時(shí)，調(diào)用過程時(shí)要提供參數(shù)值 ? 數(shù)據(jù)類型 ：參數(shù)的數(shù)據(jù)類型 ? 默認(rèn)值 ：參數(shù)的默認(rèn)值。指定默認(rèn)值后，過程執(zhí)行時(shí)用戶不必提供參數(shù)值。默認(rèn)值必須是常量或 NULL，若使用 LIKE關(guān)鍵字，則默認(rèn)值中可以使用通配符 ? OUTPUT：說明該參數(shù)為返回參數(shù)。該參數(shù)值可以將信息返回給調(diào)用程序 ? AS：指定過程要執(zhí)行的操作 ? SQL語句序列 ：過程中包含的 TSQL 語句 存儲(chǔ)過程 2021年 11月 11日星期四 8 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 例 51：創(chuàng)建第一個(gè)存儲(chǔ)過程 存儲(chǔ)過程 建立第一個(gè)存儲(chǔ)過程，查詢學(xué)生人數(shù) USE 學(xué)生學(xué)籍?dāng)?shù)據(jù)庫 GO 判斷存儲(chǔ)過程是否存在，若存在先刪除它 IF EXISTS (SELECT name FROM sysobjects WHERE name =39。MyFirstSP39。AND type=39。P39。) DROP PROCEDURE MyFirstSP GO CREATE PROCEDURE MyFirstSP AS DECLARE @Con INT SELECT @Con=COUNT(*) FROM 學(xué)生基本信息 PRINT @Con SELECT * FROM 學(xué)生基本信息 GO 執(zhí)行存儲(chǔ)過程 EXEC MyFirstSP 2021年 11月 11日星期四 9 第 5章 存儲(chǔ)過程與觸發(fā)器 ? sysobjects表 type字段的取值 值 含義 值 含義 C CHECK約束 R 規(guī)則 D 默認(rèn)值或 DEFAULT約束 S 系統(tǒng)表 F FOREIGN KEY約束 TF 表函數(shù) FN 標(biāo)量函數(shù) TR 觸發(fā)器 IF 內(nèi)嵌表函數(shù) U 用戶表 K PRIMARY KEY或 UNIQUE約束 V 視圖 P 存儲(chǔ)過程 X 擴(kuò)展存儲(chǔ)過程 存儲(chǔ)過程 2021年 11月 11日星期四 10 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 例 52：創(chuàng)建帶參數(shù)的存儲(chǔ)過程 ? 存儲(chǔ)過程通過其參數(shù)與調(diào)用程序進(jìn)行通訊 ? 每個(gè)參數(shù)都有名稱、數(shù)據(jù)類型、方向和默認(rèn)值 ? 當(dāng)程序執(zhí)行存儲(chǔ)過程時(shí)，通過輸入?yún)?shù)將數(shù)據(jù)傳遞給存儲(chǔ)過程，通過OUTPUT參數(shù)將數(shù)據(jù)返回至調(diào)用程序 存儲(chǔ)過程 建立存儲(chǔ)過程，按照性別查詢學(xué)生的姓名 USE 學(xué)生學(xué)籍?dāng)?shù)據(jù)庫 GO IF EXISTS (SELECT name FROM sysobjects WHERE name =39。FindSex39。AND type=39。P39。) DROP PROCEDURE FindSex GO 創(chuàng)建過程，輸入?yún)?shù)為學(xué)生性別，得到該性別的學(xué)生姓名 CREATE PROCEDURE FindSex(@StuSex CHAR(2)) AS SELECT FROM 學(xué)生基本信息 AS S WHERE = @StuSex GO 執(zhí)行過程，得到男同學(xué)的姓名 DECLARE @MS INT EXEC FindSex 39。男 39。 2021年 11月 11日星期四 11 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 例 53：創(chuàng)建帶 OUTPUT參數(shù)的存儲(chǔ)過程 存儲(chǔ)過程 求男同學(xué)的總?cè)藬?shù) USE 學(xué)生學(xué)籍?dāng)?shù)據(jù)庫 GO IF EXISTS(SELECT name FROM sysobjects WHERE name =39。GetMaleCount? AND type=39。P39。) DROP PROCEDURE GetMaleCount GO 創(chuàng)建過程，求男同學(xué)的總?cè)藬?shù) CREATE PROCEDURE GetMaleCount @MS INT OUTPUT AS SELECT @MS = COUNT(*) FROM 學(xué)生基本信息 AS S WHERE = 39。男 39。 GO 執(zhí)行過程，得到學(xué)生的總?cè)藬?shù) DECLARE @MS INT EXEC GetMaleCount @MS OUTPUT PRINT 39。男同學(xué)總?cè)藬?shù)為： 39。+ CAST(@MS AS CHAR) 結(jié)果為： 男同學(xué)的總?cè)藬?shù)為： 7 2021年 11月 11日星期四 12 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 例 54：創(chuàng)建帶返回值的存儲(chǔ)過程 ? 在存儲(chǔ)過程中使用 RETURN 語句，可將一個(gè)返回值返回給調(diào)用程序 ? 若 RETURN 未指定值，則存儲(chǔ)過程返回 0 ? RETURN 語句可無條件地終止存儲(chǔ)過程，其后的語句將不會(huì)被執(zhí)行。 ? 返回值表示執(zhí)行成功（ 0）或失?。ǚ?0） 存儲(chǔ)過程 按照學(xué)號(hào)查詢該學(xué)生姓名 創(chuàng)建過程，輸入?yún)?shù)為學(xué)生學(xué)號(hào)，返回碼為該學(xué)生是否存在 CREATE PROCEDURE GetName @StuNO INT AS DECLARE @ErrorSave INT 聲明變量 @ErrorSave用于存儲(chǔ)返回碼 SET @ErrorSave = 0 SELECT FROM 學(xué)生基本信息 AS S WHERE = @StuNO IF (@@ERROR 0) @@ERROR值存儲(chǔ)于 @ErrorSave SET @ErrorSave = @@ERROR RETURN @ErrorSave 若 SELECT語句未出現(xiàn)錯(cuò)誤，返回 0，否則返回錯(cuò)誤號(hào) GO 2021年 11月 11日星期四 13 第 5章 存儲(chǔ)過程與觸發(fā)器 ? 結(jié)果為 ? 在 “ 網(wǎng)格 ” 窗格中顯示該學(xué)生的姓名為 “ 齊霖 ” ? 在 “ 消息 ” 窗格中顯示 “ 返回碼為： 0 存儲(chǔ)過程 執(zhí)行過程，得到該學(xué)號(hào)的學(xué)生姓名及存儲(chǔ)過程返回值 DECLARE @ReturnStatus INT EXECUTE @ReturnStatus = GetName ?02021? 顯示返回值 PRINT 39。返回碼為： 39。 + CAST(@ReturnStatus AS CHAR(10)) GO