【正文】 2021年 11月 11日星期四 14 第 5章 存儲過程與觸發(fā)器 ? 例 55：創(chuàng)建根據(jù)返回情況的不同而返回不同返回碼的存儲過程 ? 創(chuàng)建一個根據(jù)其姓名獲得其家庭地址的存儲過程 ? 該過程中各種返回情況的返回碼值如表所示 值 含義 0 成功執(zhí)行 1 未指定所需的參數(shù)值 2 指定的參數(shù)值無效 3 獲得地址值時出現(xiàn)錯誤 4 發(fā)現(xiàn)地址值為 NULL 存儲過程 2021年 11月 11日星期四 15 第 5章 存儲過程與觸發(fā)器 存儲過程 按照學生姓名查詢該學生的家庭地址 創(chuàng)建過程，輸入?yún)?shù)為學生姓名，得到其家庭地址，返回碼表示各種返回情況 CREATE PROCEDURE GetAddress @StuName varchar(20) = NULL AS DECLARE @StuAddress VARCHAR(50) IF @StuName IS NULL 判斷是否未提供姓名參數(shù) BEGIN PRINT 39。錯誤：請?zhí)峁W生的姓名！ 39。 RETURN(1) END ELSE BEGIN 判斷該學生是否存在 IF (SELECT COUNT(*) FROM 學生基本信息 AS S WHERE = @StuName) = 0 BEGIN PRINT 39。提示：該學生名字有誤，或該學生不存在！ 39。 RETURN(2) END 2021年 11月 11日星期四 16 第 5章 存儲過程與觸發(fā)器 ? 續(xù)前頁代碼 存儲過程 查詢該學生的家庭地址，賦給輸出參數(shù) @StuAddress SELECT @StuAddress=家庭地址 FROM 學生基本信息 AS S WHERE =@StuName 判斷查詢中是否存在問題 IF @@ERROR 0 BEGIN PRINT 39。提示：查詢過程中出錯！ 39。 RETURN(3) END ELSE 判斷該學生的家庭地址是否為 NULL IF @StuAddress IS NULL BEGIN PRINT 39。提示：該學生家庭地址為空！ 39。 RETURN(4) END 成功查詢出該學生的家庭地址！ ELSE BEGIN PRINT 39。查詢成功！ 39。 RETURN(0) END END GO 2021年 11月 11日星期四 17 第 5章 存儲過程與觸發(fā)器 ? 執(zhí)行語句若為 ? EXEC GetAddress，則在 “ 消息 ” 格中顯示 “ 錯誤：請?zhí)峁W生的姓名！ ” ? 執(zhí)行語句若為 ： ? DECLARE @returnval int ? EXEC @returnval=GetAddress 39。高得非 39。 ? print @returnval ? 則在 “ 消息 ” 窗格中顯示 ? 提示：該學生家庭地址為空！ ? 4 ? 執(zhí)行語句若為 ? DECLARE @returnval int ? EXEC @returnval=GetAddress 39。張軍 39。 ? print @returnval ? 則在 “ 消息 ” 窗格中顯示 ? 查詢成功！ ? 0 存儲過程 2021年 11月 11日星期四 18 第 5章 存儲過程與觸發(fā)器 ? 例 56：使用 LIKE、通配符和參數(shù)默認值的存儲過程 存儲過程 求 “ 龍 ” 姓學生的基本情況 USE 學生學籍數(shù)據(jù)庫 GO IF EXISTS(SELECT name FROM sysobjects WHERE name =39。GetStuInfo39。AND type=39。P39。) DROP PROCEDURE GetStuInfo GO 創(chuàng)建過程，返回 “ 龍 ” 姓學生的家庭地址 CREATE PROCEDURE GetStuInfo @SNAME VARCHAR(20) = 39。龍 %39。 AS SELECT S.* FROM 學生基本信息 AS S WHERE LIKE @SNAME GO 執(zhí)行過程，得到 “ 龍 ” 姓學生的基本情況 EXEC GetStuInfo GO 2021年 11月 11日星期四 19 第 5章 存儲過程與觸發(fā)器 ? 例 57：使用參數(shù)默認值 NULL創(chuàng)建存儲過程 ? 將參數(shù)默認值指定為 NULL值，來判斷存儲過程調(diào)用時，用戶是否提供參數(shù)值 存儲過程 求某學生的出生日期 創(chuàng)建過程，輸入某學生的姓名，得到該學生的出生日期。 當未輸入學生姓名時，顯示 “ 請輸入學生的姓名！ ” CREATE PROCEDURE GetStuBirth @SNAME VARCHAR(20) = NULL AS IF @SNAME IS NULL PRINT 39。請輸入學生的姓名！ 39。 ELSE SELECT , S. 出生日期 FROM 學生基本信息 AS S WHERE = @SNAME GO 執(zhí)行過程 USE 學生學籍數(shù)據(jù)庫 EXEC GetStuBirth 未提供參數(shù)時，會顯示：請輸入學生的姓名！ GO EXEC GetStuBirth 39。張軍 39。 2021年 11月 11日星期四 20 第 5章 存儲過程與觸發(fā)器 ? 使用企業(yè)管理器創(chuàng)建存儲過程 存儲過程 2021年 11月 11日星期四 21 第 5章 存儲過程與觸發(fā)器 ? 使用企業(yè)管理器的 “ 創(chuàng)建存儲過程向導 ” 創(chuàng)建存儲過程 存儲過程 2021年 11月 11日星期四 22 第 5章 存儲過程與觸發(fā)器 存儲過程 2021年 11月 11日星期四 23 第 5章 存儲過程與觸發(fā)器 ? 執(zhí)行存儲過程 ? [EXEC[UTE]] 存儲過程名 ? [[@參數(shù) 1=] 值 1 ,[@參數(shù) 2=] 值 2,… |@變量 1 [OUTPUT]|[DEFAULT]] ? 其中 ? 值 ：過程中參數(shù)的值 ? 變量 ：用來保存參數(shù)或者返回參數(shù)的變量 ? OUTPUT：指定存儲過程必須返回一個參數(shù) ? DEFAULT：根據(jù)過程的定義，提供參數(shù)的默認值 存儲過程 2021年 11月 11日星期四 24 第 5章 存儲過程與觸發(fā)器 ? 修改存儲過程 ? 使用企業(yè)管理器修改和重命名存儲過程 ? 使用 ALTER PROCEDURE更改存儲過程 ? 可以刪除重建 ? 可以在原有存儲過程基礎上修改 存儲過程 2021年 11月 11日星期四 25 第 5章 存儲過程與觸發(fā)器 ? 例 514：檢索所有學分為 4的課程，修改該過程為檢索所有學分為3的課程 USE 學生學籍數(shù)據(jù)庫 GO IF EXISTS(SELECT name FROM sysobjects WHERE name=39。GetCredit39。AND type = 39。P39。) DROP PROCEDURE GetCredit GO USE 學生學籍數(shù)據(jù)庫 GO CREATE PROCEDURE GetCredit AS SELECT * FROM 課程基本信息 WHERE 學分 = 4 GO 修改該存儲過程 ALTER PROCEDURE GetCredit AS SELECT * FROM 課程基本信息 WHERE 學分 = 3 GO 2021年 11月 11日星期四 26 第 5章 存儲過程與觸發(fā)器 ? 觸發(fā)器 ? 觸發(fā)器（ Trigger）是一種特殊類型的存儲過程，用戶或應用程序不直接調(diào)用執(zhí)行它，而是在手工或通過應用程序對表或視圖進行插入、刪除和更新操作 ? 執(zhí)行 UPDATE、 INSERT 或 DELETE命令時自動執(zhí)行 ? 主要功能是維護表中數(shù)據(jù)的完整性，檢查對表所做的修改是否允許或進行一些特殊的自動化操作 ? 一個表或視圖可以有多個觸發(fā)器 觸發(fā)器 2021年 11月 11日星期四 27 第 5章 存儲過程與觸發(fā)器 ? 觸發(fā)器的創(chuàng)建 ? 使用企業(yè)管理器創(chuàng)建觸發(fā)器 2021年 11月 11日星期四 28 第 5章 存儲過程與觸發(fā)器 ? 使用 TSQL創(chuàng)建觸發(fā)器 ? CREATE TRIGGER 觸發(fā)器名 ON 表名 | 視圖名 ? { FOR|AFTER|INSTEAD OF} {[DELETE][,][INSERT][,][UPDATE]} ? AS ? SQL語句序列 ? 其中 ? 表名 | 視圖名 ：在其上執(zhí)行觸發(fā)器的表或視圖，稱為觸發(fā)器表或視圖 ? FOR子句指定觸發(fā)器的執(zhí)行時間 ? AFTER：觸發(fā)器在 SQL語句中的所有操作成功執(zhí)行后以及所有引用級聯(lián)操作