1、自定義函數(shù)、存儲過程和觸發(fā)器,技能目標：本章教學(xué)內(nèi)容是SQL Server程序設(shè)計的靈魂，掌握和使用好它們對數(shù)據(jù)庫的開發(fā)與應(yīng)用非常重要。通過學(xué)習(xí)，掌握以下操作技能： 編寫創(chuàng)建、使用簡單的自定義函數(shù)、存儲過程、觸發(fā)器程序。,1 自定義函數(shù),1.1 標量函數(shù) 1.2 內(nèi)嵌表值函數(shù),概念,在SQL Server中，除了系統(tǒng)內(nèi)置的函數(shù)外，用戶還可以自己定義函數(shù)，來補充和擴展系統(tǒng)支持的內(nèi)置函數(shù)。 自定義函數(shù)是由一個或多個 Transact-SQL 語句組成的子程序，可用于封裝代碼以便重復(fù)使用。,標量函數(shù)、內(nèi)嵌表值函數(shù)、多語句表值函數(shù),種類,1.1 標量函數(shù)(1),【演練8.1】使用企業(yè)管理器管理：創(chuàng)建、

2、查看、刪除自定義函數(shù)。在“教學(xué)成績管理數(shù)據(jù)庫”中創(chuàng)建用戶定義函數(shù)“dbo.is中文字符串”(判斷給定的字符串自變量是否是純中文)。,【演練8.1】使用企業(yè)管理器管理：創(chuàng)建、查看、刪除自定義函數(shù)。在“教學(xué)成績管理數(shù)據(jù)庫”中創(chuàng)建用戶定義函數(shù)“dbo.is中文字符串”(判斷給定的字符串自變量是否是純中文)。,1.1 標量函數(shù)(2),【知識點】,(1)自定義函數(shù)是由一個或多個T-SQL語句組成的子程序，可用于封裝代碼以便重復(fù)使用。自定義函數(shù)的輸入?yún)?shù)可以為零個或最多1024個，輸入?yún)?shù)能夠是除了時間戳(timestamp)、游標(cursor)和表(table)以外的其他變量。,(2)標量函數(shù)是給定n

3、個(0=n1024)自變量按returns子句定義的類型返回單個數(shù)據(jù)值(return子句)的自定義函數(shù)，使用標量函數(shù)如同使用系統(tǒng)內(nèi)置函數(shù)一樣。,1.1 標量函數(shù)(3),【知識點】,(1)創(chuàng)建標量函數(shù)的主要語法格式: create function 所有者.自定義函數(shù)名(參數(shù)n) returns 返回參數(shù)的類型 as begin 函數(shù)體 return 函數(shù)返回的值 end,【導(dǎo)例8.1】創(chuàng)建一個自定義函數(shù)“is中文字符串”，判斷自變量是否是純中文字符串，返回字符串：是或否。,1.1 標量函數(shù)(4),(2)函數(shù)體中可使用的語句類型包括: declare語句，該語句可用于定義函數(shù)局部的數(shù)據(jù)變量和游標

4、。 set語句，給局部變量賦值。 游標操作，允許在函數(shù)中聲明、打開、關(guān)閉和釋放局部游標，但不允許使用fetch語句將數(shù)據(jù)返回到客戶端，僅允許使用fetch語句通過into子句給局部變量賦值。（下章介紹） 控制流程語句。 select語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦于函數(shù)的局部變量。 insert、update和delete語句，這些語句修改函數(shù)的局部table變量。 execute語句，該語句調(diào)用擴展存儲過程。,1.1 標量函數(shù)(5),【知識點】,修改標量函數(shù)的T-SQL語句的語法格式類似于創(chuàng)建標量函數(shù)的T-SQL語句的語法格式，僅是將create變成alter。,【導(dǎo)

5、例8.2】修改“is中文字符串”函數(shù)：自變量的長度由50改為255。,1.1 標量函數(shù)(6),【知識點】,(1)刪除自定義函數(shù)語法格式： drop function 所有者.自定義函數(shù)名 ,【導(dǎo)例8.3】 在“教學(xué)成績管理數(shù)據(jù)庫”中使用已定義標量函數(shù)“is中文字符串”在創(chuàng)建“學(xué)院臨時信息表”時定義字段“名稱”、“簡稱”必須是漢字的約束。,(2)正在引用的自定義函數(shù)不能刪除。,標量函數(shù)的調(diào)用,在select語句中調(diào)用。調(diào)用形式： 所有者.函數(shù)名(實參1,實參n) 利用EXEC語句執(zhí)行。用該語句調(diào)用用戶函數(shù)時，實參的標示次序與函數(shù)定義中的參數(shù)標識次序可以不同。 調(diào)用形式： 所有者名.函數(shù)名 實參1

6、,實參n,1.2 內(nèi)嵌表值函數(shù),【演練8.2】【導(dǎo)例8.4】使用查詢分析器管理：創(chuàng)建、查看、使用、刪除自定義函數(shù)。在“教學(xué)成績管理據(jù)庫”中創(chuàng)建用戶定義函數(shù)“dbo.某班學(xué)生信息表”(從“學(xué)生信息表視圖”中查詢給定班級名稱的學(xué)生的所有信息)。,1.2 內(nèi)嵌表值函數(shù),【知識點】,(1)內(nèi)嵌表值函數(shù)返回一個select語句查詢結(jié)果的表。,(2)內(nèi)嵌表值函數(shù)可用于實現(xiàn)參數(shù)化視圖(查詢)的功能。,1.2 內(nèi)嵌表值函數(shù),【導(dǎo)例8.5】創(chuàng)建一個自定義函數(shù)：某班某課不及格表輸入?yún)?shù)班級名稱和課程名稱，并查詢。,【知識點】,(1)創(chuàng)建內(nèi)嵌表值函數(shù)T-SQL語句的主要語法格式: create function 所

7、有者.自定義函數(shù)名(參數(shù),n) returns table as return (select查詢語句),(2)修改內(nèi)嵌表值函數(shù)T-SQL語句與創(chuàng)建語句區(qū)別：create-alter。,(3)標量函數(shù)在表達式中調(diào)用，內(nèi)嵌表值函數(shù)在select語句的from子句中調(diào)用。在調(diào)用函數(shù)的時候需要指明函數(shù)的擁有者和函數(shù)的名稱。,內(nèi)嵌表值函數(shù)的調(diào)用,內(nèi)嵌表值函數(shù)只能通過SELECT語句調(diào)用，內(nèi)嵌表值函數(shù)調(diào)用時，可以僅使用函數(shù)名。 select * from dbo.st_score(110001),2 存儲過程,2.1 使用T-SQL語句管理存儲過程 2.2 使用企業(yè)管理器管理存儲過程 2.3 使用存儲過

8、程的優(yōu)點,【問題提出】用戶定義函數(shù)采用零個或最多可以有 1024 個輸入?yún)?shù)并返回單個標量值或單個表(記錄集)。但對于返回多個（或零個）標量值或多個（或零個）表(記錄集)問題，SQL Server如何解決呢？,存儲過程：系統(tǒng)提供的存儲過程和用戶自定義的存儲過程。 系統(tǒng)存儲過程存儲在master中,以sp_為前綴，在任何數(shù)據(jù)庫中都可以調(diào)用，在調(diào)用時不必在存儲過程前加上數(shù)據(jù)庫名。 用戶自定義的存儲過程是由用戶創(chuàng)建的，是用來完成某項任務(wù)的。本節(jié)介紹的主要內(nèi)容是用戶自定義的存儲過程。,1. 創(chuàng)建、調(diào)用用戶自定義存儲過程,2.1 使用T-SQL語句管理存儲過程(1),【導(dǎo)例8.6】在“教學(xué)成績管理數(shù)據(jù)庫

9、”中，設(shè)計查詢某班某門課程成績：按學(xué)號排序成績表、人數(shù)、最高分、最低分和平均分(返回1個數(shù)據(jù)表和4個標量值)。,【知識點】,(1)存儲過程是存儲在SQL服務(wù)器數(shù)據(jù)庫中的一組預(yù)編譯過的T-SQL語句，當?shù)谝淮握{(diào)用以后，就駐留在內(nèi)存中，以后調(diào)用時不必再進行編譯，因此它的運行速度比獨立運行同樣的程序要快。,(2)存儲過程可以容納對數(shù)據(jù)庫進行各種操作的編程語句，也可以調(diào)用其他的存儲過程。,2.1 使用T-SQL語句管理存儲過程(2),(3)創(chuàng)建, output將參數(shù)的值返回給調(diào)用語句: create procedure 存儲過程名 參數(shù) 參數(shù)的數(shù)據(jù)類型 output ,n as 任意數(shù)量的T-SQL

10、語句 創(chuàng)建存儲過程需要確定存儲過程的3個組成部分: 所有的輸入?yún)?shù)及執(zhí)行的輸出結(jié)果。 被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。 返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是否成功。,2.1 使用T-SQL語句管理存儲過程(3),(4)存儲過程可以接受參數(shù)，用戶通過指定存儲過程的名字并給出參數(shù)(如果該存儲過程帶有參數(shù))來執(zhí)行它。存儲過程與函數(shù)不同，存儲過程既不能在被調(diào)用的位置上返回數(shù)據(jù)，也不能被引用在語句當中。,(5)一個存儲過程是一個獨立的數(shù)據(jù)庫對象，可被客戶端應(yīng)用程序多次調(diào)用： execute 存儲過程名 參數(shù)1、參數(shù)n Exec stu_info lili,計算機基礎(chǔ) exec n

11、ame=lili,name=計算機基礎(chǔ),2.1 使用T-SQL語句管理存儲過程(4),2. 修改、刪除用戶自定義存儲過程,【知識點】,(1)用T-SQL語句修改存儲過程的語法格式類似create proc，即create換成alter。,(2)刪除存儲過程語法格式： drop procedure 所有者. 存儲過程 ,【導(dǎo)例8.7】修改【導(dǎo)例8.6】某班某門課程成績存儲過程，成績表按分數(shù)從大到小排序；刪除某班某門課程成績存儲過程。,2.2 使用企業(yè)管理器管理存儲過程(1),【演練8.3】【導(dǎo)例8.8】使用企業(yè)管理器管理：創(chuàng)建、查看、刪除自定義存儲過程。在“教學(xué)成績管理據(jù)庫”中創(chuàng)建用戶定義存儲過

12、程“某班同學(xué)表”(返回同學(xué)花名表、人數(shù)、男生人數(shù)和女生人數(shù))。,2.3 使用存儲過程的優(yōu)點,模塊化的程序設(shè)計。 執(zhí)行速度快。 減少網(wǎng)絡(luò)通信量。 保證系統(tǒng)的安全性。,自定義函數(shù)和存儲過程的區(qū)別,存儲過程，功能強大，可以執(zhí)行包括修改表等一系列數(shù)據(jù)庫操作，也可以創(chuàng)建為 SQL Server 啟動時自動運行的存儲過程。用戶定義函數(shù)不能用于執(zhí)行一組修改全局數(shù)據(jù)庫狀態(tài)的操作。 存儲過程，可返回記錄集。自定義函數(shù)，可以返回表變量。 存儲過程，其返回值不能被直接引用。自定義函數(shù)，其返回值可以被直接引用。 存儲過程，用 EXECUTE 語句執(zhí)行。自定義函數(shù)，在查詢語句中調(diào)用。 函數(shù)必須始終返回一個值(一個標量值

13、或一個表格)。而存儲過程可以返回一個標量值、一個表值或無需返回值。,3 觸發(fā)器,3.1 使用T-SQL語句管理觸發(fā)器 3.2 使用企業(yè)管理器管理觸發(fā)器 3.3 使用觸發(fā)器的優(yōu)點,【問題提出】當表或視圖中的某些重要數(shù)據(jù)發(fā)生變化 時，為了保持數(shù)據(jù)的一致性和完整性，可以自動執(zhí)行某段程序保證相關(guān)聯(lián)其他數(shù)據(jù)也跟著進行相應(yīng)的變化。能完成這種功能的程序就是觸發(fā)器。 如當修改“學(xué)院信息表”中編號01為08時，“系部信息表”中4位編號前2位為01的編號的前2位也應(yīng)該修改為08，“教研室信息表”中6位編號前2位為01的編號的前2位也應(yīng)該修改為08。能完成這種功能的程序就是觸發(fā)器。,3.1使用T-SQL語句管理觸發(fā)

14、器(1),1 用CREATE TRIGGER命令創(chuàng)建觸發(fā)器,【演練8.4】【導(dǎo)例8.9】在學(xué)院信息表中創(chuàng)建一個名為“T修改學(xué)院信息表編號”的觸發(fā)器。當修改學(xué)院信息表中編號時，則修改“系部信息表”中“編號”、“專業(yè)信息表”中“院系編號”、“課程信息表”中“院系編號”、“教研室信息表”中“編號”、“教師信息表”中“教研室編號”的前2位為相應(yīng)的編號。,3.1使用T-SQL語句管理觸發(fā)器(2),【創(chuàng)建觸發(fā)器的知識點】,CREATE TRIGGER 觸發(fā)器名 ON 表名或視圖名 FOR|AFTER | INSTEAD OF INSERT ,UPDATE ,DELETE AS IF UPDATE(列名1)

15、 AND|OR UPDATE(列名2),. SQL語句,(1) 觸發(fā)器名稱：觸發(fā)器名。 (2) 何處觸發(fā)：表名或視圖名。 (3) 何時激發(fā)：FOR|AFTER指定為AFTER觸發(fā)器， INSTEAD OF指定為INSTEAD觸發(fā)器。 (4) 何種數(shù)據(jù)修改語句觸發(fā)：INSERT指定為INSERT觸發(fā)器；UPDATE 指定為UPDATE觸發(fā)器；DELETE指定為DELETE觸發(fā)器。 (5) 何列數(shù)據(jù)修改時觸發(fā)：可選項IF UPDATE(列名1) AND|OR UPDATE(列名2) .n 用于指定如果測試到在列名1且或列名2上進行的 INSERT 或 UPDATE 操作時觸發(fā)。不能用于 DELET

16、E 語句觸發(fā)器。 (6) 如何觸發(fā)：SQL語句指定觸發(fā)器觸發(fā)時所作的操作。,3.1使用T-SQL語句管理觸發(fā)器(3),執(zhí)行觸發(fā)器時，系統(tǒng)創(chuàng)立了兩個特殊的邏輯表：inserted和deleted表： （1）inserted邏輯表：當向表中插入數(shù)據(jù)時，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。 （2）deleted邏輯表：用于保存已從表中刪除的記錄，當觸發(fā)一個delete觸發(fā)器時，被刪除的記錄存放到deleted邏輯表中。 （3）修改一條記錄等于插入一條新記錄，同時刪除舊記錄。對定義了update觸發(fā)器的表記錄修改時，表中原紀錄移到了deleted表中 ，修改過的記

17、錄插入到inserted表中，觸發(fā)器可檢查deleted表、inserted表及被修改的表。,3.1使用T-SQL語句管理觸發(fā)器(3),觸發(fā)器在創(chuàng)建和使用中有如下限制: (1)create trigger 語句只能作為批處理的第一條語句。,3.2 使用企業(yè)管理器管理觸發(fā)器(4),(2)在表中如果既有約束又有觸發(fā)器，則在執(zhí)行中約束優(yōu)先于觸發(fā)器。而且如果在操作中觸發(fā)器與約束發(fā)生沖突，觸發(fā)器將不執(zhí)行。,(3)觸發(fā)器中不允許包含以下SQL語句：ALTER DATABASE、CREATE DATABAS、DROP DATABASE、RESTORE DATABASE、RESTORE LOG等。,(4)不能

18、在視圖或臨時表上建立觸發(fā)器，但是在觸發(fā)器定義中可以引用視圖或臨時表。當觸發(fā)器引用視圖或臨時表，并產(chǎn)生兩個特殊的表：deleted表和 inserted 表。這兩個表由系統(tǒng)進行創(chuàng)建和管理，用戶不能直接修改其中的內(nèi)容，其結(jié)構(gòu)與觸發(fā)表相同，可以用于觸發(fā)器的條件測試。,3.1使用T-SQL語句管理觸發(fā)器(5),(1)觸發(fā)器是特殊類型的存儲過程，它能在任何試圖改變表或視圖中由觸發(fā)器保護的數(shù)據(jù)時執(zhí)行。觸發(fā)器主要通過操作事件(INSERT、UPDATE、DELETE)進行觸發(fā)而被自動執(zhí)行，不能直接調(diào)用執(zhí)行，也不能被傳送和接受參數(shù)。,(3)根據(jù)引起觸發(fā)的數(shù)據(jù)修改語句可分: INSERT、UPDATE和DELE

19、TE觸發(fā)器；,(2)觸發(fā)器與表或視圖是不能分開的，觸發(fā)器定義在一個表或視圖中，當在表或視圖中執(zhí)行插入(INSERT)、修改(UPDATE)、刪除(DELETE)操作時觸發(fā)器被觸發(fā)自動執(zhí)行。當表或視圖被刪除時與它關(guān)聯(lián)的觸發(fā)器也一同被刪除。,【觸發(fā)器的知識點】,(4)據(jù)觸發(fā)時刻分為:AFTER和INSTEAD觸發(fā)器。 AFTER(之后)觸發(fā)器是在執(zhí)行觸發(fā)操作(INSERT、UPDATE或DELETE)和處理完約束之后激發(fā)；,3.1使用T-SQL語句管理觸發(fā)器(6),INSTEAD(替代)觸發(fā)器是由觸發(fā)器的程序代替代替INSERT、UPDATE或DELETE語句執(zhí)行，在處理約束之前激發(fā)。,若執(zhí)行IN

20、SERT、UPDATE或DELETE語句違犯約束條件時，將不執(zhí)行AFTER觸發(fā)器；而在定義INSTEAD OF觸發(fā)器的表或視圖上執(zhí)行INSERT、UPDATE或 DELETE語句時，會激發(fā)觸發(fā)器而不執(zhí)行這些數(shù)據(jù)操作語句本身。,(5)一個表或視圖可以定義多個after觸發(fā)器，一個表或視圖只可以定義一個instead觸發(fā)器。,3.1使用T-SQL語句管理觸發(fā)器(7),(1)觸發(fā)器運行時SQL Server會在內(nèi)存中自動創(chuàng)建和管理deleted 表和 inserted 表，用于在觸發(fā)器內(nèi)部測試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件，用戶不能直接對表中的數(shù)據(jù)進行更改。,【應(yīng)用觸發(fā)器的知識點】,(2)

21、DELETE觸發(fā)器會將刪除舊行的內(nèi)容保存在deleted表中，INSERT觸發(fā)器會將添加新行的內(nèi)容保存在inserted表中，而UPDATE觸發(fā)器將替換舊行的內(nèi)容保存在deleted表中、替換的新行內(nèi)容保存inserted表中 語句中。,3.1使用T-SQL語句管理觸發(fā)器(8),2. 修改、刪除觸發(fā)器,【知識點】,(1)修改觸發(fā)器語法格式類似create trigger，只需將create換成alter。,(2)sp_rename 舊的觸發(fā)器名 新的觸發(fā)器名,【導(dǎo)例8.10】在“學(xué)院信息表”中創(chuàng)建一個名為“T刪除學(xué)院信息表記錄”的觸發(fā)器。當要刪除“學(xué)院信息表”中的記錄時，檢查“系部信息表”中是否有該學(xué)院的系部，如果有則給出提示信息不允許刪除該條記錄。,(3)drop trigger 觸發(fā)器名 如果刪除表，則表中所有的觸發(fā)器將被自動刪除。,3.2 使用企業(yè)管理器管理觸發(fā)器(1),【演練8.5】使用企業(yè)管理器管理：創(chuàng)建、查看、刪除觸發(fā)器。在“教學(xué)成績管理數(shù)據(jù)庫”、“學(xué)院信息表”SQ