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