存儲(chǔ)過程函數(shù)和觸發(fā)器數(shù)據(jù)庫

1、存儲(chǔ)過程，觸發(fā)器和函數(shù)存儲(chǔ)過程，觸發(fā)器和函數(shù) T-SQL編程基礎(chǔ)存儲(chǔ)過程觸發(fā)器自定義函數(shù) 小結(jié)T-SQL語言基礎(chǔ)主要數(shù)據(jù)類型： smallint，int，numeric，decimal，char， varchar，nchar，nvarchar ，datetime運(yùn)算符及優(yōu)先級(jí)常用函數(shù)變量： 局部變量（內(nèi)存變量，字段變量） 全局變量（系統(tǒng)變量）內(nèi)容回顧在SQL Server中，可以使用兩種類型的注釋：以“ - - ”開頭 ANSI標(biāo)準(zhǔn)的注釋符，用于單行注釋。 /* 注釋內(nèi)容 */ 用于單行或多行注釋。T-SQL編程基礎(chǔ)注 釋輸出語句PRINT作用：是在屏幕上顯示指定信息。語法：PRINT 常量

2、| 變量| 表達(dá)式| 函數(shù)SELECT作用：以列表結(jié)構(gòu)形式在屏幕上顯示指定信息。語法：SELECT 常量 | 變量| 表達(dá)式 | 函數(shù) AS 別名, n示例:PRINT 服務(wù)器的名稱： + SERVERNAMESELECT SERVERNAME AS 服務(wù)器名稱T-SQL編程基礎(chǔ)批是包含一個(gè)或多個(gè) Transact-SQL 語句的集合，從應(yīng)用程序一次性地發(fā)送到服務(wù)器執(zhí)行。SQL Server 將批處理語句編譯成一個(gè)可執(zhí)行單元，此單元稱為執(zhí)行計(jì)劃。一般是將一些邏輯相關(guān)的業(yè)務(wù)操作語句放置在同一批中，這完全由代碼編寫者決定。GO是批處理結(jié)束標(biāo)志。 批處理T-SQL編程基礎(chǔ)CREATE 語句不能在批處

3、理中與其他語句組合使用。不能在同一個(gè)批處理中更改表，然后引用新列。不能在同一個(gè)批中刪除一個(gè)數(shù)據(jù)庫對(duì)象又重建它。不能在同一個(gè)批中將規(guī)則或默認(rèn)綁定給列又使用它們。如果 EXECUTE 語句是批處理中的第一句，則不需要 EXECUTE 關(guān)鍵字。（1） 批處理使用應(yīng)遵守的規(guī)則T-SQL編程基礎(chǔ) 批處理編譯錯(cuò)誤使執(zhí)行計(jì)劃無法編譯，會(huì)導(dǎo)致批處理中的任何語句都無法執(zhí)行。運(yùn)行時(shí)錯(cuò)誤產(chǎn)生以下兩種影響之一：停止執(zhí)行批處理中當(dāng)前語句和它之后的語句。停止執(zhí)行當(dāng)前語句，繼續(xù)執(zhí)行其它所有語句。 在遇到運(yùn)行時(shí)錯(cuò)誤之前執(zhí)行的語句不受影響。（2）批處理運(yùn)行時(shí)錯(cuò)誤的影響T-SQL編程基礎(chǔ) 批處理語法： BEGIN 執(zhí)行的SQL語

4、句 END說明：定義一組語句，通常在IF、ELSE或WHILE后面。 （1）復(fù)合語句（BEGINEND）（2） 判斷語句（IFELSE）語法：IF 條件表達(dá)式 SQL語句1 ELSE SQL語句2 說明：當(dāng)條件為真時(shí)，執(zhí)行一條或一組語句。 T-SQL編程基礎(chǔ)流程控制語句示例：求英語課程的平均分?jǐn)?shù)。如果在80以上，則顯示 “成績(jī)達(dá)到預(yù)期目標(biāo)”，并查詢高于平均分的學(xué)號(hào)和分?jǐn)?shù)；否則，顯示還需繼續(xù)努力”，并查詢低于平均分的學(xué)號(hào)和分?jǐn)?shù)。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO SET NOCOUNT ON - - 不顯示統(tǒng)計(jì)信息 DECLARE 平均分 FLOAT SELECT 平均分=AVG(分?jǐn)?shù)) FRO

5、M 教學(xué)成績(jī)表 WHERE 課程編號(hào)= ( SELECT 編號(hào) FROM 課程信息表 WHERE 名稱=英語) PRINT 英語課程平均分?jǐn)?shù) + STR(平均分,5,1)IF (平均分 80) BEGIN PRINT 成績(jī)達(dá)到預(yù)期目標(biāo)! SELECT 學(xué)號(hào),分?jǐn)?shù) FROM 教學(xué)成績(jī)表 WHERE 分?jǐn)?shù)平均分 AND 課程編號(hào)= ( SELECT 編號(hào) FROM 課程信息表 WHERE 名稱=英語) END ELSE BEGIN PRINT 還需繼續(xù)努力！低于平均分的學(xué)生： SELECT 學(xué)號(hào),分?jǐn)?shù) FROM 教學(xué)成績(jī)表 WHERE 分?jǐn)?shù)=90 AND 分?jǐn)?shù)=80 AND 分?jǐn)?shù)=70 AND 分

6、數(shù)=60 AND 分?jǐn)?shù)70 THEN 及格 ELSE 不及格 END SELECT * FROM #CJB GO示例2：根據(jù)分?jǐn)?shù)計(jì)算出成績(jī)等級(jí)。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO SET NOCOUNT ON SELECT 學(xué)號(hào),分?jǐn)?shù),成績(jī) INTO #CJB FROM 教學(xué)成績(jī)表 UPDATE #CJB SET 成績(jī)= CASE FLOOR(分?jǐn)?shù)/10) WHEN 10 THEN 優(yōu)秀 WHEN 9 THEN 優(yōu)秀 WHEN 8 THEN 良好 WHEN 7 THEN 中等 WHEN 6 THEN 及格 ELSE 不及格 END SELECT * FROM #CJB GO語法： WHILE

7、條件表達(dá)式 SQL語句 | SQL語句組說明： 設(shè)置重復(fù)執(zhí)行 SQL 語句或語句組的條件，只要指定的條件為真，就重復(fù)執(zhí)行語句。在循環(huán)內(nèi)部可使用如下控制語句： BREAK :跳出循環(huán)之后執(zhí)行CONTINUE :轉(zhuǎn)到循環(huán)開始之處執(zhí)行（4）循環(huán)語句（WHILE）T-SQL編程基礎(chǔ) 流程控制語句USE 教學(xué)成績(jī)管理數(shù)據(jù)庫GOSET NOCOUNT ONSELECT 學(xué)號(hào),分?jǐn)?shù) INTO #CJB FROM 教學(xué)成績(jī)表 WHERE 課程編號(hào)=900002DECLARE n intWHILE(1=1) BEGIN SELECT n=COUNT(*) FROM #CJB WHERE 分?jǐn)?shù)55 IF (n0)

8、 UPDATE #CJB SET 分?jǐn)?shù)=分?jǐn)?shù)+5 WHERE 分?jǐn)?shù)95 ELSE BREAK ENDSELECT * FROM #CJB ORDER BY 分?jǐn)?shù)GO 編寫存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器是SQL Server程序設(shè)計(jì)的靈魂，應(yīng)用好它們，將使數(shù)據(jù)庫的管理和應(yīng)用更加方便和靈活。存儲(chǔ)過程，觸發(fā)器和函數(shù) T-SQL編程基礎(chǔ) 存儲(chǔ)過程 觸發(fā)器自定義函數(shù)小 結(jié)在數(shù)據(jù)庫實(shí)際應(yīng)用中，存在有帶變量數(shù)據(jù)處理需求，如：某班學(xué)生信息表、某老師帶過的學(xué)生、某班某門課不及格學(xué)生等。如何更好實(shí)現(xiàn)這樣的應(yīng)用功能？可采用SQL Server中應(yīng)用最廣泛、最靈活的存儲(chǔ)過程技術(shù)來實(shí)現(xiàn)。存儲(chǔ)過程 存儲(chǔ)過程是存儲(chǔ)在SQL

9、Server服務(wù)器中的一組預(yù)編譯的T-SQL語句 ，用于完成某項(xiàng)任務(wù)。 存儲(chǔ)過程是一個(gè)獨(dú)立的數(shù)據(jù)庫對(duì)象，可被應(yīng)用程序調(diào)用，以減少重復(fù)編寫代碼。SQL Server系統(tǒng)為用戶提供一組實(shí)用的系統(tǒng)存儲(chǔ)過程，同時(shí)支持用戶自定義的存儲(chǔ)過程。 存儲(chǔ)過程存儲(chǔ)過程 由系統(tǒng)提供，用以管理和顯示有關(guān)數(shù)據(jù)庫和用戶的信息。存儲(chǔ)在master數(shù)據(jù)庫中并以sp_為前綴，在任何數(shù)據(jù)庫中都可以調(diào)用。 存放在用戶數(shù)據(jù)庫中以dt_為前綴的存儲(chǔ)過程也是一種系統(tǒng)存儲(chǔ)過程。 這類存儲(chǔ)過程一般在用戶創(chuàng)建數(shù)據(jù)庫時(shí)由系統(tǒng)自動(dòng)生成，主要用于處理用戶數(shù)據(jù)庫中系統(tǒng)自動(dòng)生成的一些系統(tǒng)表，起到實(shí)時(shí)維護(hù)的作用 系統(tǒng)存儲(chǔ)過程存儲(chǔ)過程以sp_為前綴的系統(tǒng)存

10、儲(chǔ)過程存儲(chǔ)過程以dt_為前綴的系統(tǒng)存儲(chǔ)過程存儲(chǔ)過程存儲(chǔ)過程的概念 用戶自定義存儲(chǔ)過程由數(shù)據(jù)庫開發(fā)人員或管理員編寫的完成特定任務(wù)的存儲(chǔ)過程，存儲(chǔ)在創(chuàng)建時(shí)的數(shù)據(jù)庫中。我們將重點(diǎn)學(xué)習(xí)自定義存儲(chǔ)過程的創(chuàng)建和使用方法。存儲(chǔ)過程-單個(gè) SELECT 語句SELECT 語句塊可以包含SELECT語句與邏輯控制語句存儲(chǔ)過程中的語句 存儲(chǔ)過程 創(chuàng)建存儲(chǔ)過程（SSMS）存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程的模板，只需修改相應(yīng)的參數(shù)就可創(chuàng)建存儲(chǔ)過程存儲(chǔ)過程 創(chuàng)建存儲(chǔ)過程（SSMS） 創(chuàng)建存儲(chǔ)過程（T-SQL語句）CREATE PROCDURE 存儲(chǔ)過程名 參數(shù) 參數(shù)的數(shù)據(jù)類型 =默認(rèn)值 OUTPUT ,.nAS 任意數(shù)量的T-SQ

11、L 語句 CREATE PROCEDURE 語句中可以聲明一個(gè)或多個(gè)變量作為參數(shù) 參數(shù)分兩種：輸入?yún)?shù):用來向存儲(chǔ)過程中傳入值，執(zhí)行存儲(chǔ)過程時(shí)必須提供參數(shù)的值，除非定義了參數(shù)的默認(rèn)值。 輸出參數(shù):由“ OUTPUT ”標(biāo)記，存放存儲(chǔ)過程執(zhí)行后的返回值并提供給調(diào)用者使用。存儲(chǔ)過程 創(chuàng)建不帶參數(shù)的存儲(chǔ)過程示例：設(shè)計(jì)存儲(chǔ)過程補(bǔ)考表顯示所有課程的補(bǔ)考學(xué)生信息。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO CREATE PROCEDURE 補(bǔ)考表 AS SELECT 學(xué)號(hào),姓名,課程名稱,分?jǐn)?shù) FROM 教學(xué)成績(jī)表視圖 WHERE 分?jǐn)?shù)60 ORDER BY 課程名稱,學(xué)號(hào)存儲(chǔ)過程示例：設(shè)計(jì)存儲(chǔ)過程某班某門課程成

12、績(jī)表，按學(xué)號(hào)排序。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO CREATE PROCEDURE 某班某門課程成績(jī)表 班名 VARCHAR(16),課程名 VARCHAR(50) AS SELECT 學(xué)號(hào),姓名,課程名稱,分?jǐn)?shù) FROM 教學(xué)成績(jī)表視圖 WHERE 課程名稱 = 課程名 AND 班級(jí)=班名 ORDER BY 學(xué)號(hào) 創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程存儲(chǔ)過程示例：設(shè)計(jì)存儲(chǔ)過程實(shí)現(xiàn)統(tǒng)計(jì)某班某課程學(xué)生成績(jī)分布。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO CREATE PROCEDURE 某班某門課程成績(jī)分布表 班名 VARCHAR(16), 課程名 VARCHAR(50), 人數(shù) INT OUTPUT, 優(yōu) I

13、NT OUTPUT, 良 INT OUTPUT, 中 INT OUTPUT, 及 INT OUTPUT, 不 INT OUTPUT AS BEGIN DECLARE 成績(jī) CHAR(10), 分?jǐn)?shù) DECIMAL SET 優(yōu) = 0 SET 良 = 0 創(chuàng)建帶輸入和輸出參數(shù)的存儲(chǔ)過程存儲(chǔ)過程(續(xù)） SET 中 = 0 SET 及 = 0 SET 不 = 0 DECLARE c成績(jī)游標(biāo) CURSOR KEYSET FOR SELECT 成績(jī), 分?jǐn)?shù) FROM 教學(xué)成績(jī)表視圖 WHERE RTRIM(班級(jí))=班名 AND RTRIM(課程名稱)=課程名 OPEN c成績(jī)游標(biāo) SET 人數(shù) = cu

14、rsor_rows FETCH c成績(jī)游標(biāo) INTO 成績(jī), 分?jǐn)?shù) WHILE fetch_status = 0 BENGIN SET 成績(jī) = RTRIM(成績(jī))(續(xù)） IF 分?jǐn)?shù) = 80 SET 良 = 良 + 1 IF 分?jǐn)?shù) = 70 SET 中 = 中 + 1 IF 分?jǐn)?shù) = 60 SET 及 = 及 + 1 IF 分?jǐn)?shù) 60 AND (成績(jī) LIKE 0-9% OR 成績(jī)=不及格) SET 不 = 不 + 1 FETCH c成績(jī)游標(biāo) INTO 成績(jī), 分?jǐn)?shù) END CLOSE c成績(jī)游標(biāo) DEALLOCATE c成績(jī)游標(biāo)END存儲(chǔ)過程存儲(chǔ)過程的執(zhí)行（SSMS）填寫執(zhí)行過程必須的參數(shù)

15、自動(dòng)產(chǎn)生的執(zhí)行語句執(zhí)行結(jié)果 EXECUTE 存儲(chǔ)過程名 參數(shù)1，參數(shù)n 示例1:查看所有課程的補(bǔ)考情況 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO EXEC 補(bǔ)考表示例2:查看03電子商務(wù)班級(jí)的大學(xué)語文課程成績(jī)表 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO EXEC 某班某門課程成績(jī)表 03電子商務(wù),大學(xué)語文如果存儲(chǔ)過程是批處理中的第一條語句，該關(guān)鍵字可省略。存儲(chǔ)過程的執(zhí)行（T-SQL)存儲(chǔ)過程存儲(chǔ)過程的執(zhí)行示例3:查看03網(wǎng)絡(luò)工程班級(jí)的SQL Server管理與開發(fā)課程的成績(jī)分布情況。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO DECLARE y INT,l INT, z INT,j INT, b INT,rs INT

16、 EXEC 某班某門課程成績(jī)分布表 03網(wǎng)絡(luò)工程,SQL Server管理與開發(fā), rs OUTPUT,y OUTPUT,l OUTPUT, z OUTPUT, j OUTPUT, b OUTPUT PRINT 總?cè)藬?shù)：+ CONVERT( CHAR,rs ) PRINT 優(yōu)秀：+ CONVERT( CHAR,y) PRINT 良好：+ CONVERT( CHAR,l ) PRINT 及格：+ CONVERT( CHAR,y) PRINT 不及格：+ CONVERT( CHAR,b ) PRINT 其他：+ CONVERT( CHAR,rs-(y+l+z+j+b)存儲(chǔ)過程的執(zhí)行 要使用輸出參數(shù)

17、，必須在CREATE PRODECURE 語句和EXECUTE語句中指定OUTPUT關(guān)鍵字。在執(zhí)行存儲(chǔ)過程時(shí)，如果忽略O(shè)UTPUT關(guān)鍵字，存儲(chǔ)過程仍然會(huì)執(zhí)行但不返回值存儲(chǔ)過程存儲(chǔ)過程的修改（SSMS)選擇修改命令后自動(dòng)產(chǎn)生的語句組。完成存儲(chǔ)過程的修改后要執(zhí)行該組語句。示例:修改 某班某門課程成績(jī)表存儲(chǔ)過程，按分?jǐn)?shù)從大到小排序。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO ALTER PROCEDURE 某班某門課程成績(jī)表 班名 VARCHAR(16),課程名 VARCHAR(50) AS SELECT 學(xué)號(hào),姓名,課程名稱,分?jǐn)?shù) FROM 教學(xué)成績(jī)表視圖 WHERE 課程名稱 = 課程名 AND 班級(jí)=

18、班名 ORDER BY 學(xué)號(hào) DESCALTER PROCDURE 存儲(chǔ)過程名 參數(shù) 參數(shù)的數(shù)據(jù)類型 =默認(rèn)值 OUTPUT ,.nAS 任意數(shù)量的T-SQL 語句 存儲(chǔ)過程存儲(chǔ)過程的修改（T-SQL)存儲(chǔ)過程存儲(chǔ)過程的刪除（SSMS)示例:刪除 補(bǔ)課表存儲(chǔ)過程。 USE 教學(xué)成績(jī)管理數(shù)據(jù)庫 GO DROP PROC 補(bǔ)考表DROP PROCEDURE 存儲(chǔ)過程名 ,. 存儲(chǔ)過程存儲(chǔ)過程的修改（T-SQL)一旦創(chuàng)建并存儲(chǔ)后可被任意多次調(diào)用，支持程序模塊化的設(shè)計(jì)。 第一次被調(diào)用后就駐留內(nèi)存，再次調(diào)用時(shí)不再需要編譯，執(zhí)行速率較高 。存儲(chǔ)過程存儲(chǔ)在后端數(shù)據(jù)庫中不需要通過網(wǎng)絡(luò)傳輸，可減少網(wǎng)絡(luò)流量 。即

19、使對(duì)于沒有直接執(zhí)行存儲(chǔ)過程中語句權(quán)限的用戶，也可授予他執(zhí)行該存儲(chǔ)過程的權(quán)限，可作為安全機(jī)制使用 。存儲(chǔ)過程的優(yōu)點(diǎn):存儲(chǔ)過程增加復(fù)雜性。當(dāng)業(yè)務(wù)邏輯比較復(fù)雜時(shí)不建議使用存儲(chǔ)過程，這樣會(huì)增加復(fù)雜性?？梢浦残缘?。（不同的數(shù)據(jù)庫系統(tǒng)）存儲(chǔ)過程的缺點(diǎn):存儲(chǔ)過程 課堂練習(xí)(1) 編寫一個(gè)存儲(chǔ)過程，輸入“學(xué)號(hào)”，顯示該學(xué)生的考試情況，包括：“姓名”、“課程名”，“分?jǐn)?shù)”。 (2) 編寫一個(gè)存儲(chǔ)過程，修改課程信息表中的課程名稱，帶兩個(gè)參數(shù)：“課程編號(hào)”和修改后的“課程名稱”。存儲(chǔ)過程，觸發(fā)器和函數(shù) T-SQL編程基礎(chǔ)存儲(chǔ)過程觸發(fā)器自定義函數(shù) 小結(jié) 觸發(fā)器是特殊類型的存儲(chǔ)過程，主要由操作事件(INSERT、UPD

20、ATE、DELETE) 觸發(fā)而被自動(dòng)執(zhí)行。 觸發(fā)器可以實(shí)現(xiàn)比約束更復(fù)雜的數(shù)據(jù)完整性，經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則。 觸發(fā)器本身是一個(gè)特殊的事務(wù)單位。 觸發(fā)器觸發(fā)器 與表相關(guān)聯(lián)：必須定義在表上。 自動(dòng)觸發(fā)：由INSERT、DELETE、UPDATE操作觸發(fā) 不能直接調(diào)用，也不能傳遞或接受參數(shù) 是事務(wù)的一部分：觸發(fā)器和觸發(fā)語句作為可在觸 發(fā)器內(nèi)回滾的單個(gè)事務(wù)。 觸發(fā)器的特點(diǎn)觸發(fā)器 按觸發(fā)時(shí)刻分 AFTER觸發(fā)器：執(zhí)行觸發(fā)操作和處理完約束后激發(fā)， 只能定義于表上，同一個(gè)表上可定義多個(gè)。 INSTEAD觸發(fā)器：由觸發(fā)器的程序代替觸發(fā)語句的執(zhí)行，在處 理約束之前激發(fā)，可在表或視圖上創(chuàng)建，同 一

21、個(gè)表或視圖只可定義一個(gè)。 按觸發(fā)語句分 INSERT觸發(fā)器、 UPDATE觸發(fā)器、 DELETE觸發(fā)器 觸發(fā)器的類型觸發(fā)器 觸發(fā)器被觸發(fā)時(shí)，系統(tǒng)自動(dòng)在內(nèi)存中創(chuàng)建deleted表或inserted表。只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除。 inserted表 和 deleted 表觸發(fā)器修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄-刪除(DELETE)記錄-存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄觸發(fā)器 觸發(fā)器的創(chuàng)建（SSMS）創(chuàng)建觸發(fā)器的模板，只需修改相應(yīng)的參數(shù)就可創(chuàng)建觸發(fā)器CREATE TRIGGER 觸發(fā)器名ON

22、表名或視圖名 FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETEAS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL語句觸發(fā)器 觸發(fā)器的創(chuàng)建（T-SQL） 主要參數(shù)說明 AFTER:執(zhí)行指定操作語句和處理完約束后激發(fā)觸發(fā)器。 FOR:如果僅指定 FOR 關(guān)鍵字，則 AFTER 是默認(rèn)設(shè)置。 INSTEAD OF:指定語句激發(fā)觸發(fā)器并被代替執(zhí)行。 DELETE、INSERT、UPDATE：指定激活觸發(fā)器的數(shù)據(jù)修改語句，必須至少指定一個(gè)選項(xiàng)，若指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。 AS：指定觸發(fā)器要執(zhí)行的操作。

23、 IF UPDATE(列名1) AND|OR UPDATE(列名2):指定何列數(shù)據(jù)修改時(shí)激發(fā)觸發(fā)器。不能用于DELETE語句。觸發(fā)器 創(chuàng)建觸發(fā)器時(shí)需要指定如下內(nèi)容: 觸發(fā)器名稱：觸發(fā)器名。 何處觸發(fā)：表名或視圖名。 何時(shí)激發(fā)：FOR|AFTER |INSTEAD OF 。 何種數(shù)據(jù)修改語句觸發(fā)：INSERT|UPDATE|DELETE 。 何列數(shù)據(jù)修改時(shí)觸發(fā): IF UPDATE(列名） 如何觸發(fā)：SQL語句指定觸發(fā)器觸發(fā)時(shí)所作的操作。觸發(fā)器示例1：在學(xué)院信息表中創(chuàng)建一個(gè)名為T修改學(xué)院信息表編號(hào)的觸發(fā)器實(shí)現(xiàn)：當(dāng)修改學(xué)院信息表中編號(hào)時(shí)則下列內(nèi)容自動(dòng)作出相應(yīng)的修改。 系部信息表中的編號(hào) 專業(yè)信息表

24、中的院系編號(hào) 課程信息表中的院系編號(hào) 教研信息表中的編號(hào) 教師信息表中的教研室編號(hào) 前2位是有效的學(xué)院編號(hào)有效的系部編號(hào)，外鍵有效的系部編號(hào)，外鍵前4位是有效的系部編號(hào)有效的教研室編號(hào)，外鍵觸發(fā)器CREATE TRIGGER T修改學(xué)院信息表編號(hào) on 學(xué)院信息表AFTER UPDATE AS IF UPDATE(編號(hào)) BEGIN SET NOCOUNT OFF DECLARE 舊編號(hào) CHAR(2), 新編號(hào) CHAR (2) SELECT 舊編號(hào)=編號(hào) FROM DELETED SELECT 新編號(hào)=編號(hào) FROM INSERTED UPDATE 系部信息表 SET 編號(hào)=新編號(hào)+SUB

25、STRING(編號(hào),3,2) WHERE 舊編號(hào)=LEFT(編號(hào),2) （續(xù)） UPDATE 專業(yè)信息表 SET 院系編號(hào)=新編號(hào)+SUBSTRING(院系 編號(hào),3,2) WHERE 舊編號(hào)=LEFT(院系編號(hào),2) UPDATE 課程信息表 SET 院系編號(hào)=新編號(hào)+SUBSTRING(院系 編號(hào),3,2) WHERE 舊編號(hào)=LEFT(院系編號(hào),2) UPDATE 教研室信息表 SET 編號(hào)=新編號(hào)+SUBSTRING(編號(hào),3,4) WHERE 舊編號(hào)=LEFT(編號(hào),2) UPDATE 教師信息表 SET 教研室編號(hào)=新編號(hào)+SUBSTRING(編 號(hào),3,4) WHERE 編號(hào)=L

26、EFT(教研室編號(hào),2) END（續(xù)）觸發(fā)器示例2：在學(xué)院信息表中創(chuàng)建一個(gè)名為T刪除學(xué)院信息表編號(hào)的觸發(fā)器。 CREATE TRIGGER T刪除學(xué)院信息表編號(hào) ON 學(xué)院信息表FOR DELETE AS BEGIN SET NOCOUNT OFF DECLARE BH CHAR(2) SELECT BH=編號(hào) FROM DELETED IF EXISTS (SELECT * FROM 系部信息表 WHERE BH=left(編號(hào),2) BENGIN RAISERROR ( 系部編號(hào)正在使用, 不可刪除!, 16, 1) ROLLBACK TRANSACTION ENDEND事務(wù)回滾，撤銷整個(gè)

27、觸發(fā)器已進(jìn)行的操作。 修改觸發(fā)器（SSMS）觸發(fā)器 修改觸發(fā)器（T-SQL） ALTER TRIGGER 觸發(fā)器名ON 表名或視圖名 FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETEAS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL語句觸發(fā)器 禁用或啟用觸發(fā)器（SSMS）觸發(fā)器ALTER TABLE 表名 ENABLE | DISABLE TRIGGER ALL | 觸發(fā)器名,n 示例：禁用學(xué)院信息表的T刪除學(xué)院信息表編號(hào)觸發(fā)器。 ALTER TABLE 學(xué)院信息表 DISABLE TRIGGER T刪除學(xué)院

28、信息表記錄 禁用或啟用觸發(fā)器（T-SQL）觸發(fā)器 刪除觸發(fā)器（SSMS）觸發(fā)器 如果刪除表，則表中所有的觸發(fā)器將被自動(dòng)刪除。 DROP TRIGGER 觸發(fā)器名 刪除觸發(fā)器（T-SQL）觸發(fā)器 觸發(fā)器使用限制: CREATE TRIGGER 必須是批處理中的第一條語句。 在同一個(gè)表中約束的執(zhí)行優(yōu)先于觸發(fā)器，若在操作中觸發(fā)器與約束發(fā)生沖突，觸發(fā)器將不執(zhí)行。 不能在臨時(shí)表上建立觸發(fā)器，但是在觸發(fā)器定義中可以引用臨時(shí)表。 觸發(fā)器中不允許以下 T-SQL 語句：ALTER DATABASECREATE DATABASEDISK INITDISK RESIZEDROP DATABASELOAD DATA

29、BASELOAD LOGRECONFIGURERESTORE DATABASERESTORE LOG觸發(fā)器存儲(chǔ)過程，觸發(fā)器和函數(shù) T-SQL編程基礎(chǔ)存儲(chǔ)過程觸發(fā)器自定義函數(shù) 小結(jié) 函數(shù)是由一個(gè)或多個(gè) Transact-SQL 語句組成的子程序，可用于封裝代碼以便重復(fù)使用。 在SQL Server中，除了系統(tǒng)內(nèi)置的函數(shù)外，用戶還可以自定義函數(shù)，用以補(bǔ)充和擴(kuò)展系統(tǒng)支持的內(nèi)置函數(shù)。 函數(shù)必須有返回值，根據(jù)函數(shù)返回值形式的不同，自定義函數(shù)分為：標(biāo)量值函數(shù)、內(nèi)聯(lián)表值函數(shù)、多語句表值函數(shù)三種關(guān)于自定義函數(shù)自定義函數(shù) 標(biāo)量值函數(shù)返回一個(gè)確定類型的標(biāo)量值，其返回值類型為除TEXT、 NTEXT、 IMAGE、

30、CURSOR、 TIMESTAMP 和TABLE 類型外的其它數(shù)據(jù)類型。 函數(shù)體語句定義在BEGIN-END語句內(nèi)，其中包含了可以返回值的T-SQL 命令。（1）標(biāo)量值函數(shù)自定義函數(shù)關(guān)于自定義函數(shù) 內(nèi)聯(lián)表值函數(shù)的返回值是一個(gè)表。 內(nèi)聯(lián)表值函數(shù)沒有由BEGIN-END 語句括起來的函數(shù)體，只有一個(gè) SELECT 語句。其返回的表位于RETURN 子句中的SELECT 語句從數(shù)據(jù)庫中篩選出來。 內(nèi)聯(lián)表值函數(shù)功能相當(dāng)于一個(gè)參數(shù)化的視圖。 自定義函數(shù)（1）內(nèi)聯(lián)表值函數(shù)關(guān)于自定義函數(shù) 是標(biāo)量型和內(nèi)聯(lián)表值型函數(shù)的結(jié)合體。它的返回值是一個(gè)表，但它和標(biāo)量型函數(shù)一樣有一個(gè)用BEGIN-END 語句括起來的函數(shù)體

31、，返回值的表數(shù)據(jù)是由函數(shù)體中的語句插入的。 它可以進(jìn)行多次查詢，對(duì)數(shù)據(jù)進(jìn)行多次篩選與合并，彌補(bǔ)了內(nèi)聯(lián)表值型函數(shù)的不足。 （1）多語句表值函數(shù)自定義函數(shù)關(guān)于自定義函數(shù)創(chuàng)建函數(shù)（SSMS）自定義函數(shù)創(chuàng)建函數(shù)的模板，只需修改相應(yīng)的參數(shù)就可創(chuàng)建自定義函數(shù)格式1： CREATE FUNCTION 所有者.自定義函數(shù)名(參數(shù)n) RETURNS 返回參數(shù)的類型 AS BEGIN 函數(shù)體 RETURN 函數(shù)返回的標(biāo)量值END 該語法格式用于創(chuàng)建標(biāo)量值函數(shù)； RETURN子句返回單個(gè)數(shù)據(jù)值，如同使用系統(tǒng)內(nèi)置的函數(shù)一樣。創(chuàng)建函數(shù)（T-SQL）自定義函數(shù)示例：創(chuàng)建一個(gè)自定義函數(shù)is_中文字符串，判斷自變量是否是純

32、中文字符串，返回字符串：是或否。 CREATE FUNCTION is中文字符串(字符串 NCHAR(255) RETURNS NCHAR(1) AS BEGIN DECLARE I TINYINT, J TINYINT SET I=LEN(字符串) SET J=1 WHILE (J=I) BEGIN IF (UNICODE(SUBSTRING(字符串,J,1)256) RETURN 否 SET J=J+1 END RETURN 是 END自定義函數(shù)格式2： CREATE FUNCTION 所有者.自定義函數(shù)名(參數(shù)n RETURNS TABLE AS RETURN ( SELECT 查詢語句

33、 ) 該語法格式用于創(chuàng)建表值函數(shù)； 返回值是一個(gè)Select語句查詢結(jié)果的表。自定義函數(shù)創(chuàng)建函數(shù)（T-SQL）示例：創(chuàng)建一個(gè)自定義函數(shù)：某班某課不及格表輸入?yún)?shù)班級(jí)名稱和課程名稱，返回：表。 CREATE FUNCTION 某班某課不及格表 (class VARCHAR(16),course VARCHAR(50) RETURNS TABLE AS RETURN ( SELECT 學(xué)號(hào),姓名,課程名稱,分?jǐn)?shù),學(xué)年學(xué)期,考試類別 FROM 教學(xué)成績(jī)表視圖 WHERE 班級(jí)=class AND 課程名稱=course AND 分?jǐn)?shù)60)自定義函數(shù)格式3： CREATE FUNCTION 所有者.自

34、定義函數(shù)名(參數(shù),n RETURNS 數(shù)據(jù)表名 TABLE (列名 數(shù)據(jù)類型,n) AS BEGIN 有效的語句 RETURN END 該語法格式用于創(chuàng)建多語句表值函數(shù)； 返回值是一個(gè)表。自定義函數(shù)創(chuàng)建函數(shù)（T-SQL）多語句表值函數(shù)的主體中有效的語句是： 賦值語句。 流程控制語句。 DECLARE 語句，定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。 SELECT 語句，該語句包含帶有表達(dá)式的選擇列表，其中的表達(dá)式將值賦予函數(shù)的局部變量。 游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。 INSERT、UPDATE 和 DELETE 語句，這些語句修改函數(shù)的局部 TALBE 變量。 EXECU

35、TE 語句調(diào)用擴(kuò)展存儲(chǔ)過程。自定義函數(shù)示例1：創(chuàng)建一個(gè)自定義函數(shù)：某班某課不及格表2輸入?yún)?shù)班級(jí)名稱和課程名稱，返回：表。 CREATE FUNCTION 某班某課不及格表2 (class VARCHAR(16),course VARCHAR(50) RETURNS bk TABLE (學(xué)號(hào) CHAR(6),姓名 NCHAR(4),課程名 VARCHAR(50),分?jǐn)?shù) NUMERIC(5,1) ,學(xué)年學(xué)期 CHAR(11) ,考試類別 NCHAR(4) AS BEGIN INSERT bk SELECT 學(xué)號(hào),姓名,課程名稱,分?jǐn)?shù),學(xué)年學(xué)期,考試類別 FROM 教學(xué)成績(jī)表視圖 WHERE 班級(jí)

36、=class AND 課程名稱=course AND 分?jǐn)?shù)60 RETURN END 示例2：創(chuàng)建一個(gè)自定義函數(shù)學(xué)生會(huì)干部表，建立學(xué)生會(huì)干部信息表并插入數(shù)據(jù)，返回表。 CREATE FUNCTION 學(xué)生會(huì)干部表() RETURNS gb TABLE(姓名 NVARCHAR(4),職務(wù) NVARCHAR(5),性別 NCHAR(1) AS BEGIN INSERT gb VALUES(任重,主席,男) INSERT gb VALUES(張馳,副主席, 男) INSERT gb VALUES(陳均, 組織部長(zhǎng), 女) INSERT gb VALUES(梁美娟,文藝部長(zhǎng), 女) INSERT gb VALUES(喬亮, 組織部長(zhǎng), 男) RETURN END自定義函數(shù)的調(diào)用 用戶自定