第11章 視圖、存儲(chǔ)過(guò)程和觸發(fā)器的建立和使用

1、數(shù)據(jù)庫(kù)系統(tǒng)原理數(shù)據(jù)庫(kù)系統(tǒng)原理與應(yīng)用技術(shù)與應(yīng)用技術(shù)主講：陳漫紅主講：陳漫紅 北京聯(lián)合大學(xué)師范學(xué)院北京聯(lián)合大學(xué)師范學(xué)院 電氣信息系電氣信息系第第11章視圖、存儲(chǔ)過(guò)程和觸發(fā)器的建章視圖、存儲(chǔ)過(guò)程和觸發(fā)器的建立和使用立和使用 n11.1視圖視圖 n11.2 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程 n11.3 觸發(fā)器觸發(fā)器 n11.4 本章小結(jié)本章小結(jié)11.1 視圖11.1.1 視圖概念和作用和定義11.1.2 通過(guò)視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù) 11.1.3 視圖的管理11.1.1 視圖概念n視圖可以被看成是虛擬表。n視圖中的數(shù)據(jù)不物理地存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)。nSELECT 語(yǔ)句的結(jié)果集構(gòu)成了視圖的內(nèi)容?；颈砘颈?基本

2、表基本表2視圖視圖視圖的作用n簡(jiǎn)化數(shù)據(jù)查詢語(yǔ)句n使用戶能從多角度看到同一數(shù)據(jù)n提高了數(shù)據(jù)的安全性n提供了一定程度的邏輯獨(dú)立性11.1.1 視圖的創(chuàng)建視圖的創(chuàng)建創(chuàng)建視圖時(shí)應(yīng)該注意：（1）只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建視圖，在視圖中最多只能引用1024列，視圖中記錄的數(shù)目限制只由其基表中的記錄數(shù)決定。（2）如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。（3）如果視圖中某一列是函數(shù)、數(shù)學(xué)表達(dá)式、常量或者來(lái)自多個(gè)表的列名相同，則必須為列定義名稱。（4）不能在視圖上創(chuàng)建索引，不能在規(guī)則、默認(rèn)、觸發(fā)器的定義中引用視圖。（5）當(dāng)通過(guò)視圖查詢數(shù)據(jù)時(shí)，SQL Server要檢查以確保

3、語(yǔ)句中涉及的所有數(shù)據(jù)庫(kù)對(duì)象存在，每個(gè)數(shù)據(jù)庫(kù)對(duì)象在語(yǔ)句的上下文中有效，而且數(shù)據(jù)修改語(yǔ)句不能違反數(shù)據(jù)完整性規(guī)則。（6）視圖的名稱必須遵循標(biāo)識(shí)符的規(guī)則，且對(duì)每個(gè)用戶必須是惟一的。利用利用SQL Server管理平臺(tái)創(chuàng)建視圖管理平臺(tái)創(chuàng)建視圖（1）在SQL Server管理平臺(tái)中，展開指定的服務(wù)器，打開要?jiǎng)?chuàng)建視圖的數(shù)據(jù)庫(kù)文件夾，右擊該數(shù)據(jù)庫(kù)圖標(biāo)，從彈出的快捷菜單中依次選擇“新建（New）視圖”選項(xiàng) 。利用利用SQL Server管理平臺(tái)創(chuàng)建視圖管理平臺(tái)創(chuàng)建視圖選中“視圖”節(jié)點(diǎn)單擊右鍵，在彈出的快捷菜單中依次選擇“新建視圖” 。利用利用SQL Server管理平臺(tái)創(chuàng)建視圖管理平臺(tái)創(chuàng)建視圖（2）選擇好創(chuàng)建視

4、圖所需的表、視圖、函數(shù)后，如選擇student,sc,course表，通過(guò)單擊字段左邊的復(fù)選框選擇需要的字段。 利用利用SQL Server管理平臺(tái)創(chuàng)建視圖管理平臺(tái)創(chuàng)建視圖n單擊工具欄中的“保存”按鈕，或者單擊鼠標(biāo)右鍵，從快捷菜單中選擇保存選項(xiàng)保存視圖，出現(xiàn)選擇名稱對(duì)話框，輸入視圖名，即可完成視圖的創(chuàng)建。 .使用使用Transact-SQL語(yǔ)句創(chuàng)建視圖語(yǔ)句創(chuàng)建視圖n語(yǔ)法： CREATE VIEW （視圖列名表）AS 查詢語(yǔ)句 WITH CHECK OPTION 定義單源表視圖n建立信息系學(xué)生的視圖。CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage

5、FROM Student WHERE Sdept = 信息系 定義多源表視圖n建立信息系選修了c01號(hào)課程的學(xué)生的視圖。CREATE VIEW V_IS_S1(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01 在已有視圖上定義新視圖n建立信息系選修了課程且成績(jī)?cè)?0分以上的學(xué)生的視圖。CREATE VIEW V_IS_S2ASSELECT V_IS_S1.Sno, V_IS_S1.

6、Sname, sc.Grade FROM V_IS_S1 join sc on V_IS_S1.sno=sc.sno WHERE sc.Grade = 90 定義帶表達(dá)式的視圖n定義一個(gè)反映學(xué)生出生年份的視圖。CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2010-Sage FROM Student 含分組統(tǒng)計(jì)信息的視圖n定義一個(gè)存放每個(gè)學(xué)生的學(xué)號(hào)及平均成績(jī)的視圖。CREATE VIEW S_G(Sno, AverageGrade)AS SELECT Sno, AVG(Grade) FROM SCGROUP BY Sno 對(duì)

7、視圖進(jìn)行加密對(duì)視圖進(jìn)行加密n創(chuàng)建一個(gè) 名為“v綜合信息”的視圖，用于查詢學(xué)生的學(xué)號(hào)、姓名、專業(yè)名、課程名、成績(jī)等信息。并對(duì)視圖的定義進(jìn)行加密。CREATE VIEW V綜合信息WITH ENCRYPTION ASSELECT student.sno,sname,sdept,cname,grade FROM student, sc , course WHERE student.sno = sc.sno and sc. cno= o使用視圖模板創(chuàng)建視圖使用視圖模板創(chuàng)建視圖 （1）在）在SQL Server管理平臺(tái)中，選擇管理平臺(tái)中，選擇“視圖視圖”菜單中的菜單中的“模板資源管理器模板資源管理器 ”

8、選項(xiàng)，如圖選項(xiàng)，如圖11-7所示。所示。使用視圖模板創(chuàng)建視圖使用視圖模板創(chuàng)建視圖（2）在出現(xiàn)的“模板資源管理器”選項(xiàng)中選擇“視圖”中的“創(chuàng)建視圖”選項(xiàng) 。（3）按照模板提示輸入視圖名稱，select語(yǔ)句后，執(zhí)行此語(yǔ)句，即可創(chuàng)建視圖。 11.1.2 通過(guò)視圖進(jìn)行的查詢、插入、通過(guò)視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù) 1.查詢視圖數(shù)據(jù)利用select語(yǔ)句或SQL Server管理平臺(tái)可以查看視圖的輸出數(shù)據(jù)。在SQL Server管理平臺(tái)中，右擊某個(gè)視圖的名稱，從彈出的快捷菜單中選擇“打開視圖”選項(xiàng)，就會(huì)顯示該視圖的輸出數(shù)據(jù)。還可以用SELECT查詢命令查看視圖數(shù)據(jù)。 11.1.2 通

9、過(guò)視圖進(jìn)行的查詢、插入、通過(guò)視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù)2.插入視圖數(shù)據(jù) 可使用INSERT插入語(yǔ)句向視圖中插入數(shù)據(jù)。例如向信息系的視圖插入數(shù)據(jù)。INSERT INTO IS_ student VALUES（090051,李力,22,信息系）再執(zhí)行：SELECT * FROM IS_ student 例如例如:首先創(chuàng)建一個(gè)包含限制條件的視圖首先創(chuàng)建一個(gè)包含限制條件的視圖v_student2，限制條，限制條件為年齡件為年齡20，然后插入了一條不滿足限制條件的記錄，再，然后插入了一條不滿足限制條件的記錄，再用用SELECT語(yǔ)句檢索視圖和表。程序清單如下：語(yǔ)句檢索視圖和表。程序

10、清單如下：CREATE VIEW v_student2ASSELECT * FROM studentWHERE sage20GOINSERT INTO v_ student2VALUES（090052,王則, 女，10 ,信息系）GOSELECT * FROM student GOSELECT * FROM v_ student 2GO11.1.2 通過(guò)視圖進(jìn)行的查詢、插入、通過(guò)視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù)3.使用視圖更新和刪除數(shù)據(jù) n使用視圖更新記錄，要注意的是，更新的只是數(shù)據(jù)庫(kù)中的基表。n使用視圖刪除記錄，可以刪除任何基表中的記錄，直接利用DELETE語(yǔ)句刪除記錄即可

11、。 【例如】創(chuàng)建了一個(gè)基于表student的視圖v_student2，然后通過(guò)該視圖修改表student中的記錄。 程序清單如下：UPDATE v_ student2SET name=張然WHERE name=張三SELECT * FROM student GOSELECT * FROM v_ student2GO【例11-7】利用視圖v_ student2刪除表student中姓名為“張三”的記錄。 程序清單如下：DELETE FROM v_ student2WHERE name=張然 SELECT * FROM student GOSELECT * FROM v_ student2GO我們

12、看到視圖v_ student2和表student的張然的記錄都做了刪除。11.1.2 通過(guò)視圖進(jìn)行的查詢、插入、通過(guò)視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù)n使用視圖修改數(shù)據(jù)時(shí)，需要注意：（1）不能同時(shí)修改兩個(gè)或者多個(gè)基表，可以對(duì)基于兩個(gè)或多個(gè)基表或者視圖的視圖進(jìn)行修改，但是每次修改都只能影響一個(gè)基表。（2）不能修改那些通過(guò)計(jì)算得到的字段。（3）如果在創(chuàng)建視圖時(shí)指定了WITH CHECK OPTION選項(xiàng)，那么使用視圖修改數(shù)據(jù)庫(kù)信息時(shí)，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。（4）執(zhí)行UPDATE、DELETE命令時(shí)，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。（5）如果視圖引用多個(gè)

13、表時(shí)，無(wú)法用DELETE命令刪除數(shù)據(jù)，若使用UPDATE命令則應(yīng)與INSERT操作一樣，被更新的列必須屬于同一個(gè)表。 11.1.3 視圖的管理視圖的管理1.重命名視圖（1）在SQL Server管理平臺(tái)中，選擇要修改名稱的視圖，并右擊該視圖，從彈出的快捷菜單中選擇“重命名”選項(xiàng)?；蛘咴谝晥D上再次單擊，也可以修改視圖的名稱。接著該視圖的名稱變成可輸入狀態(tài)，可以直接輸入新的視圖名稱。（2）使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename來(lái)修改視圖的名稱： sp_rename old_name,new_name11.1.3 視圖的管理視圖的管理（3）查看視圖信息 n查看視圖信息可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_help來(lái)

14、顯示視圖特征n使用sp_helptext來(lái)顯示視圖在系統(tǒng)表中的定義n使用sp_depends來(lái)顯示該視圖所依賴的對(duì)象。 11.1.3 視圖的管理視圖的管理（4）修改視圖的結(jié)構(gòu) n在SQL Server管理平臺(tái)中，右擊要修改的視圖，從彈出的快捷菜單中選擇“修改”選項(xiàng)，出現(xiàn)視圖修改對(duì)話框。n使用ALTER VIEW語(yǔ)句修改視圖，但首先必須擁有使用視圖的權(quán)限，語(yǔ)法形式如下： ALTER VIEW view_name （column,.n） WITH ENCRYPTION AS select_statement WITH CHECK OPTION 11.2 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程 n11.2.1 存儲(chǔ)過(guò)程的

15、類型 n11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行 n11.2.3 用戶存儲(chǔ)過(guò)程的查看、修改和刪除 11.2 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程n存儲(chǔ)過(guò)程是 SQL 語(yǔ)句和控制流語(yǔ)句的預(yù)編譯集合，它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，應(yīng)用程序可以通過(guò)調(diào)用的方法執(zhí)行存儲(chǔ)過(guò)程。n它使得對(duì)數(shù)據(jù)庫(kù)的管理和操作更加容易、效率更高。 11.2 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程n使用存儲(chǔ)過(guò)程有如下優(yōu)點(diǎn)：允許模塊化程序設(shè)計(jì) 改善性能 減少網(wǎng)絡(luò)流量 提供了安全機(jī)制 簡(jiǎn)化管理和操作 11.2.1 存儲(chǔ)過(guò)程的類型存儲(chǔ)過(guò)程的類型共分為五類：系統(tǒng)存儲(chǔ)過(guò)程、用戶定義的存儲(chǔ)過(guò)程、擴(kuò)展存儲(chǔ)過(guò)程、臨時(shí)存儲(chǔ)過(guò)程和遠(yuǎn)程存儲(chǔ)過(guò)程。系統(tǒng)存儲(chǔ)過(guò)程：在安裝SQL Server

16、2005時(shí)，系統(tǒng)創(chuàng)建了許多系統(tǒng)存儲(chǔ)過(guò)程，這些系統(tǒng)存儲(chǔ)過(guò)程存儲(chǔ)在master和msdb數(shù)據(jù)庫(kù)中。以“sp_”為前綴。 用戶定義的存儲(chǔ)過(guò)程：是由用戶為完成某一特定功能而編寫的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程存儲(chǔ)在當(dāng)前的數(shù)據(jù)庫(kù)中。 11.2.1 存儲(chǔ)過(guò)程的類型存儲(chǔ)過(guò)程的類型擴(kuò)展存儲(chǔ)過(guò)程：是對(duì)動(dòng)態(tài)鏈接庫(kù)（DLL）函數(shù)的調(diào)用，在SQL Server 2005環(huán)境外執(zhí)行，一般以“xp_”為前綴。臨時(shí)存儲(chǔ)過(guò)程：以“#”和“#”為前綴的過(guò)程，“#” 表示全局臨時(shí)存儲(chǔ)過(guò)程，它們存儲(chǔ)在tempdb數(shù)據(jù)庫(kù)中。遠(yuǎn)程存儲(chǔ)過(guò)程：是在遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫(kù)中創(chuàng)建和存儲(chǔ)的過(guò)程。 11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行 n使用使用SQL Se

17、rver管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 （1）選中某個(gè)SQL Server服務(wù)器中的數(shù)據(jù)庫(kù)，這里選中school數(shù)據(jù)庫(kù),展開可編程性節(jié)點(diǎn)。右鍵單擊數(shù)據(jù)庫(kù)下的“存儲(chǔ)過(guò)程”選項(xiàng)，彈出快捷菜單。 （2）在快捷菜單中選擇“新建存儲(chǔ)過(guò)程”命令如圖所示。 11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行 11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行（3）在“創(chuàng)建存儲(chǔ)過(guò)程模板”的文本框中輸入存儲(chǔ)過(guò)程名稱和程序語(yǔ)句。（4）單擊檢查語(yǔ)法按鈕，執(zhí)行語(yǔ)法正確性檢驗(yàn)。（5）單擊執(zhí)行按鈕，則在對(duì)象資源管理器窗口，可以看到所創(chuàng)建的存儲(chǔ)過(guò)程p_cj1。11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行n使用向?qū)?chuàng)建存儲(chǔ)過(guò)程使用向?qū)?chuàng)建存儲(chǔ)過(guò)

18、程 （1）選擇“視圖”菜單中的“模板資源管理器”菜單項(xiàng)，在系統(tǒng)屏幕的右側(cè)會(huì)彈出“模板資源管理器”窗口。（2）選中“存儲(chǔ)過(guò)程”選項(xiàng)中的“新建存儲(chǔ)過(guò)程” 11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行（3）出現(xiàn)創(chuàng)建存儲(chǔ)過(guò)程的模板。（4）按照模板提示輸入存儲(chǔ)過(guò)程名稱，以及存儲(chǔ)過(guò)程要執(zhí)行的語(yǔ)句后，點(diǎn)擊執(zhí)行命令按鈕，即可創(chuàng)建該存儲(chǔ)過(guò)程。 11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行n使用使用Transact-SQL語(yǔ)句創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程 （1）創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句格式：CREATE PROCEDURE 過(guò)程名 （， ） AS /*描述存儲(chǔ)過(guò)程的操作*/11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行（2）存

19、儲(chǔ)過(guò)程的執(zhí)行 在數(shù)據(jù)庫(kù)應(yīng)用程序中通過(guò)嵌入式SQL語(yǔ)句調(diào)用，不同的開發(fā)工具提供的調(diào)用形式不一樣； 通過(guò)DBMS（或第三方軟件）提供的數(shù)據(jù)庫(kù)管理工具調(diào)用，調(diào)用的語(yǔ)法格式取決于具體的管理工具。 11.2.2 用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行n執(zhí)行存儲(chǔ)過(guò)程的SQL語(yǔ)句的語(yǔ)法格式為： EXEC UTE 存儲(chǔ)過(guò)程名 實(shí)參 , OUTPUT , n n輸入?yún)?shù)的傳遞方式有兩種：（1）按位置傳遞：直接給出參數(shù)的值，實(shí)參與形參一一對(duì)應(yīng)（2）通過(guò)參數(shù)名傳遞：使用“參數(shù)名=參數(shù)值“的形式，參數(shù)可以任意順序給出。n注：SQL Server 2005采用EXECUTE 語(yǔ)句，可縮寫為EXEC。創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程n查詢計(jì)算機(jī)

20、系學(xué)生的考試成績(jī)，列出學(xué)生的姓名、課程名和成績(jī)。CREATE PROCEDURE student_grade1AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = 計(jì)算機(jī)系 創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過(guò)程n查詢某個(gè)指定系學(xué)生的考試情況，列出學(xué)生的姓名、所在系、課程名和考試成績(jī)。CREATE PROCEDURE student_grade2 dept char(20)ASSELECT Sname, Sdept, Cnam

21、e, Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = dept 創(chuàng)建帶多個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程n查詢某個(gè)學(xué)生某門課程的考試成績(jī)，列出學(xué)生的姓名、課程名和成績(jī)。CREATE PROCEDURE student_grade3 student_name char(10), course_name char(20) AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno INN

22、ER JOIN course c ON o = o WHERE sname = student_name AND cname = course_name 參數(shù)的傳遞方式n按參數(shù)位置傳遞值 EXEC student_grade3 劉晨, VB n按參數(shù)名傳遞值 EXEC Student_grade3 student_name = 劉晨, course_name=VB 創(chuàng)建帶多個(gè)輸入?yún)?shù)并有默認(rèn)值的存儲(chǔ)過(guò)程n查詢某個(gè)學(xué)生某門課程的考試成績(jī)，若沒(méi)有指定課程，則默認(rèn)課程為“數(shù)據(jù)庫(kù)基礎(chǔ)”。CREATE PROCEDURE student_grade4 student_name char(10), cou

23、rse_name char(20) = 數(shù)據(jù)庫(kù)基礎(chǔ)AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE sname = student_name AND cname = course_name調(diào)用參數(shù)有默認(rèn)值的存儲(chǔ)過(guò)程EXEC student_grade4 吳賓n等價(jià)于執(zhí)行：EXEC student_grade4 吳賓, 數(shù)據(jù)庫(kù)基礎(chǔ)創(chuàng)建帶有多個(gè)輸入?yún)?shù)并均指定默認(rèn)值的存儲(chǔ)過(guò)程n查詢指定系、指定性別的學(xué)生中年齡大于等于指定年齡的學(xué)生的情況。系的默認(rèn)值為

24、“計(jì)算機(jī)系”，性別的默認(rèn)值為“男生”， 年齡的默認(rèn)值為20。CREATE PROC P_Student dept char(20) = 計(jì)算機(jī)系, sex char(2) = 男, age int = 20AS SELECT * FROM Student WHERE Sdept = dept AND Ssex = sex AND Sage = age執(zhí)行存儲(chǔ)過(guò)程n執(zhí)行1：不提供任何參數(shù)值。EXEC P_Studentn執(zhí)行2：提供全部參數(shù)值。EXEC P_Student 信息系, 女, 19n執(zhí)行3：只提供第二個(gè)參數(shù)的值。EXEC P_Student sex = 女n執(zhí)行4：只提供第一個(gè)和第三

25、個(gè)參數(shù)的值。EXEC P_Student sex=女 , age = 19創(chuàng)建帶有輸出參數(shù)的存儲(chǔ)過(guò)程n計(jì)算兩個(gè)數(shù)的和。CREATE PROCEDURE sum1var1 int, var2 int, var3 int outputAs Set var3 = var1 * var2 n執(zhí)行此存儲(chǔ)過(guò)程：Declare res intExecute sum1 5,7,res outputPrint res 創(chuàng)建帶輸入?yún)?shù)和一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程n統(tǒng)計(jì)指定課程的平均成績(jī)，并將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROCEDURE AvgGrade cn char(20), avg_grade in

26、t outputAS SELECT avg_grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = cn 創(chuàng)建帶輸入?yún)?shù)和多個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程n統(tǒng)計(jì)指定課程的平均成績(jī)和選課人數(shù)，將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROCEDURE Avg_Count cn char(20), avg_grade int output, total int outputASSELECT avg_grade = AVG(Grade), total = COUNT(*)FROM SC JOIN Course C ON

27、 C.Cno = SC.CnoWHERE Cname = cn 創(chuàng)建刪除數(shù)據(jù)的存儲(chǔ)過(guò)程n刪除考試成績(jī)不及格學(xué)生的修課記錄。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade 60創(chuàng)建修改數(shù)據(jù)的存儲(chǔ)過(guò)程n將指定課程的學(xué)分增加2分。CREATE PROCEDURE p_UpdateCredit cn varchar(20)AS UPDATE course SET credit=credit+2 WHERE cname = cn11.2.3 用戶存儲(chǔ)過(guò)程的查看、修改和刪除 1.查看用戶存儲(chǔ)過(guò)程（1）使用SQL Server管理平臺(tái)查看用戶創(chuàng)

28、建的存儲(chǔ)過(guò)程。展開指定的服務(wù)器和數(shù)據(jù)庫(kù)，選擇并依次展開“程序存儲(chǔ)過(guò)程”，然后右擊要查看的存儲(chǔ)過(guò)程名稱。 11.2.3 用戶存儲(chǔ)過(guò)程的查看、修改和刪除（2）使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶存儲(chǔ)過(guò)程 。n用于顯示存儲(chǔ)過(guò)程的參數(shù)及其數(shù)據(jù)類型，其語(yǔ)法為： sp_help objname= name n用于顯示存儲(chǔ)過(guò)程的源代碼，其語(yǔ)法為： sp_helptext objname= namen用于顯示和存儲(chǔ)過(guò)程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象，其語(yǔ)法為： sp_depends objname=objectn用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程列表，其語(yǔ)法為： sp_stored_proceduressp_name=name ,sp_

29、owner=owner ,sp_qualifier = qualifier 11.2.3 用戶存儲(chǔ)過(guò)程的查看、修改和刪除2.修改用戶存儲(chǔ)過(guò)程ALTER PROCEDURE 存儲(chǔ)過(guò)程名 形參 數(shù)據(jù)類型 =默認(rèn)值 output ，n AS SQL語(yǔ)句11.2.3 用戶存儲(chǔ)過(guò)程的查看、修改和刪除3.存儲(chǔ)過(guò)程的刪除 DROP PROCEDURE 數(shù)據(jù)庫(kù)名. 11.3 觸發(fā)器n11.3.1 創(chuàng)建和使用DML觸發(fā)器 n11.3.2 創(chuàng)建和使用DDL觸發(fā)器 n11.3.3 觸發(fā)器的管理 n11.3.4 觸發(fā)器實(shí)例 11.3 觸發(fā)器n觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它在執(zhí)行操作事件時(shí)自動(dòng)觸發(fā)執(zhí)行。n觸發(fā)器與存儲(chǔ)過(guò)

30、程的區(qū)別：（1）觸發(fā)器是自動(dòng)執(zhí)行的，而存儲(chǔ)過(guò)程需要顯示調(diào)用才能執(zhí)行。（2）觸發(fā)器是建立在表或視圖之上的，而存儲(chǔ)過(guò)程是建立在數(shù)據(jù)庫(kù)之上的。刪除表就刪除了該表之上的所有觸發(fā)器，與該表有關(guān)的存儲(chǔ)過(guò)程仍然存在，只有刪除數(shù)據(jù)庫(kù)才能刪除該數(shù)據(jù)庫(kù)上的所有存儲(chǔ)過(guò)程。11.3 觸發(fā)器nSQL Server2005 包括兩大類觸發(fā)器 :（1）DML 觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言 (DML) 事件時(shí)將啟用。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語(yǔ)句、UPDATE 語(yǔ)句或 DELETE 語(yǔ)句。 （2）DDL 觸發(fā)器是 SQL Server 2005 的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)

31、言 (DDL) 事件時(shí)將調(diào)用這些觸發(fā)器。 11.3.1 創(chuàng)建和使用DML觸發(fā)器 nDML 觸發(fā)器的主要優(yōu)點(diǎn)如下：（1）DML 觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。（2）DML 觸發(fā)器可以防止惡意或錯(cuò)誤的 INSERT、UPDATE 以及 DELETE 操作，并強(qiáng)制執(zhí)行比 CHECK 約束定義的限制更為復(fù)雜的其他限制。與 CHECK 約束不同，DML 觸發(fā)器可以引用其他表中的列。（3）DML 觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。 11.3.1 創(chuàng)建和使用DML觸發(fā)器當(dāng)創(chuàng)建一個(gè)DML觸發(fā)器時(shí)必須指定如下選項(xiàng)：名稱；在其上定義觸發(fā)器的表；觸發(fā)器將何時(shí)激發(fā)；激活觸發(fā)器的數(shù)據(jù)

32、修改語(yǔ)句，有效選項(xiàng)為 INSERT、UPDATE 或 DELETE，多個(gè)數(shù)據(jù)修改語(yǔ)句可激活同一個(gè)觸發(fā)器；執(zhí)行觸發(fā)操作的編程語(yǔ)句。 11.3.1 創(chuàng)建和使用DML觸發(fā)器nDML 觸發(fā)器所使用的 deleted 和 inserted 邏輯表 :Deleted表用于存儲(chǔ)delete，update語(yǔ)句所影響的行的副本。在執(zhí)行delete或update語(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。Inserted表用于存儲(chǔ)Insert或update語(yǔ)句所影響的行的副本，在一個(gè)插入或更新事務(wù)處理中，新建的行被同時(shí)添加到Inserted表和觸發(fā)器表中。Inserted表中的行是觸發(fā)器表中新行的副本

33、。 11.3.1 創(chuàng)建和使用DML觸發(fā)器nDML 觸發(fā)器的類型：insert觸發(fā)器：在表或視圖執(zhí)行插入記錄操作時(shí)觸發(fā)。update觸發(fā)器：在表或視圖執(zhí)行修改記錄操作時(shí)觸發(fā)。delete觸發(fā)器：在表或視圖執(zhí)行刪除記錄操作時(shí)觸發(fā)。DML 觸發(fā)器的創(chuàng)建 （1）使用）使用SQL Server管理平臺(tái)創(chuàng)建觸發(fā)器管理平臺(tái)創(chuàng)建觸發(fā)器 在SQL Server管理平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，然后展開表，選擇并展開要在其上創(chuàng)建觸發(fā)器的表，右擊觸發(fā)器選項(xiàng)，從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器創(chuàng)建編輯窗口。用戶可參照模板在其中輸入創(chuàng)建觸發(fā)器的T-SQL語(yǔ)句。最后，單擊“執(zhí)行”按鈕，即可成功

34、創(chuàng)建觸發(fā)器。 DML 觸發(fā)器的創(chuàng)建（2）使用）使用CREATE TRIGGER命令創(chuàng)建命令創(chuàng)建DML觸發(fā)器觸發(fā)器 CREATE TRIGGER schema_name.觸發(fā)器名 ON 表名|視圖名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS SQL 語(yǔ)句 .n DML 觸發(fā)器的創(chuàng)建例如：對(duì)school數(shù)據(jù)庫(kù)的sc表建立觸發(fā)器。說(shuō)明inserted和deleted表的作用。CREATE TRIGGER tr1ON scFOR INSERT, UPD

35、ATE, DELETE AS PRINT inserted表：Select * from insertedPRINT deleted表：Select * from deletedGo當(dāng)執(zhí)行插入操作：insert into sc values(0912103,c03,78,必修) DML 觸發(fā)器的創(chuàng)建n例如：在school數(shù)據(jù)庫(kù)的student表上創(chuàng)建一個(gè)名為tr_delete_stu的觸發(fā)器，當(dāng)要?jiǎng)h除指定學(xué)號(hào)的行時(shí)，激發(fā)該觸發(fā)器，撤消刪除操作，并給出提示信息“不能刪除student表中的信息!”。 程序清單如下：CREATE TRIGGER tr_delete_stu ON student A

36、FTER DELETEAS ROLLBACK TRANSACTION PRINT 不能刪除student表中的信息!GO當(dāng)執(zhí)行：DELETE student WHERE sno= 0912101 時(shí)，系統(tǒng)將提示 “不能刪除student表中的信息!” 的信息。DML 觸發(fā)器的創(chuàng)建例如：在student表上創(chuàng)建一個(gè)觸發(fā)器。當(dāng)更新了某位學(xué)生的學(xué)號(hào)信息時(shí)，就激活觸發(fā)器級(jí)聯(lián)更新sc表中相關(guān)成績(jī)記錄中的學(xué)號(hào)信息，并使用print語(yǔ)句返回一個(gè)提示信息。解決的方法有：創(chuàng)建外鍵約束(不允許修改，或者允許級(jí)聯(lián)更新)，用觸發(fā)器實(shí)現(xiàn)自動(dòng)級(jí)聯(lián)修改。 DML 觸發(fā)器的創(chuàng)建CREATE TRIGGER tr_update

37、_stu1 ON STUDENT AFTER UPDATEASDECLARE 原學(xué)號(hào) char(6), 新學(xué)號(hào) char(6)SELECT 原學(xué)號(hào)=deleted.sno, 新學(xué)號(hào)= inserted.snoFROM DELETED, INSERTED WHERE deleted.sname = inserted.snamePRINT 準(zhǔn)備級(jí)聯(lián)更新sc表中的學(xué)號(hào)信息.UPDATE sc SET sno=新學(xué)號(hào) WHERE sno=原學(xué)號(hào) PRINT 已經(jīng)級(jí)聯(lián)更新了sc表中原學(xué)號(hào)為+ 原學(xué)號(hào) +的成績(jī)信息。n建立了該觸發(fā)器后當(dāng)執(zhí)行：UPDATE student SET sno=0912111 w

38、here sno=0912101操作后，會(huì)看到 sc表中學(xué)號(hào)為0912101的記錄也都改為091211111.3.2 創(chuàng)建和使用DDL觸發(fā)器 nDDL觸發(fā)器一般用于以下目的：防止對(duì)數(shù)據(jù)庫(kù)結(jié)構(gòu)進(jìn)行某些更改。希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)結(jié)構(gòu)中的更改。要記錄數(shù)據(jù)庫(kù)結(jié)構(gòu)中的更改或事件。11.3.2 創(chuàng)建和使用DDL觸發(fā)器1.創(chuàng)建創(chuàng)建DDL觸發(fā)器觸發(fā)器 CREATE TRIGGER 觸發(fā)器名稱ON ALL SERVER|DATABASE FOR|AFTER event_type|event_group,nAS sql_statement11.3.2 創(chuàng)建和使用DDL觸發(fā)器2.DDL觸發(fā)器的應(yīng)用觸

39、發(fā)器的應(yīng)用n在響應(yīng)當(dāng)前數(shù)據(jù)庫(kù)或服務(wù)器中處理的T-SQL事件時(shí)，可以激發(fā)DDL觸發(fā)器。例如：在SCHOOL數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)DDL觸發(fā)器safe，用來(lái)防止該數(shù)據(jù)庫(kù)中的任一表被修改或刪除。11.3.2 創(chuàng)建和使用DDL觸發(fā)器程序清單如下：USE SCHOOLGOCREATE TRIGGER safeON DATABASEAFTER DROP_TABLE,ALTER_TABLEASBEGIN RAISERROR(不能修改表結(jié)構(gòu),16,2) ROLLBACKENDGOn當(dāng)執(zhí)行以下程序：USE SCHOOLGOALTER TABLE student ADD nation char(10)GO11.3.3觸

40、發(fā)器的管理 1.使用使用SQL Server管理平臺(tái)修改、禁用和刪除觸發(fā)管理平臺(tái)修改、禁用和刪除觸發(fā)器器 （1）啟動(dòng)SQL Server Management Studio,在“對(duì)象資源管理器”中依次展開數(shù)據(jù)庫(kù)表節(jié)點(diǎn)觸發(fā)器（2）擊鼠標(biāo)右鍵出現(xiàn)快捷菜單。在圖中選擇【修改】可以完成觸發(fā)器的修改操作。 （3）在圖中選擇【刪除】可以刪除指定的觸發(fā)器。 （4）在“禁用觸發(fā)器”對(duì)話框中，單擊【關(guān)閉】按鈕，即可禁用選定的觸發(fā)器，被禁用的觸發(fā)器的圖標(biāo)將變成灰色。 2. 使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的觸發(fā)器的觸發(fā)器 （1）sp_help。用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時(shí)間。其語(yǔ)法格式為：sp_help 觸發(fā)器名（2）sp_helptext。用于顯示觸發(fā)器的正文信息，其語(yǔ)法為： sp_helptext 觸發(fā)器名（3）sp_depends。用于顯示和觸發(fā)器相關(guān)的數(shù)據(jù)庫(kù)對(duì)象，其語(yǔ)法為 sp_depends 觸發(fā)器名3.利用利用T-SQL語(yǔ)句修改和刪除觸發(fā)器語(yǔ)句修改和刪除觸發(fā)器 （1）觸發(fā)器的修改 ALTER TRIGGER 觸發(fā)器名稱On 表名FOR | AFTER|INSERT OFINSERT,DELETE,UPDATEAS