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