數(shù)據(jù)庫原理與應(yīng)用實驗12(二版)

1、第二篇課堂實驗實驗十二 存儲過程與觸發(fā)器一、實驗?zāi)康募耙蟊緦嶒炛饕康氖亲寣W(xué)生通過實驗掌握存儲過程的創(chuàng)建及使用方式、觸發(fā)器的創(chuàng)建，具體要求有：l 理解存儲過程的概念及類型，深刻理解存儲過程的優(yōu)點l 掌握創(chuàng)建各種存儲過程的方法l 掌握調(diào)用存儲過程的方法l 掌握查看、修改、刪除存儲過程的方法l 理解觸發(fā)器的概念與類型l 理解觸發(fā)器的功能及工作原理l 掌握創(chuàng)建、更改、刪除觸發(fā)器的方法l 掌握利用觸發(fā)器維護(hù)數(shù)據(jù)完整性的方法二、實驗原理及背景知識（一）存儲過程知識存儲過程是一種數(shù)據(jù)庫對象，將執(zhí)行計劃存儲在數(shù)據(jù)庫的服務(wù)器中。它的運行速度比獨立運行同樣的程序要快。1、存儲過程類型1）系統(tǒng)存儲過程存儲在ma

2、ster數(shù)據(jù)庫中，以sp_為前綴?？梢栽谄渌麛?shù)據(jù)庫中對其進(jìn)行調(diào)用。2）用戶自定義存儲過程由用戶創(chuàng)建并能完成某一特定功能的存儲過程，或稱本地存儲過程。 2、創(chuàng)建存儲過程存儲過程的三個組成部分：（1）所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。（2）被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。（3）返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。3、T-SQL創(chuàng)建存儲過程的基本語法格式：CREATE PROCEDURE 存儲過程名稱 參數(shù)定義AS SQL語句（二）觸發(fā)器知識觸發(fā)器是一種特殊類型的存儲過程，主要通過事件進(jìn)行觸發(fā)執(zhí)行的。可以實現(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性

3、；強(qiáng)化約束；跟蹤變化；級聯(lián)運行；存儲過程的調(diào)用等功能。1、觸發(fā)器的種類1）AFTER觸發(fā)器：只有對表執(zhí)行某一操作這后，才能被觸發(fā)?？梢詾楸淼耐徊僮鞫x多個觸發(fā)器，其觸發(fā)次序可使用sp_settriggerorder來完成。2）INSTEAD OF觸發(fā)器：并不執(zhí)行其所定義的操作而僅是執(zhí)行觸發(fā)器本身，對同一操作只能定義一個instead of 觸發(fā)器。2、INSERTED和DELETED臨時表插入一行時，INSERTED表保存了一份插入行的拷貝。刪除一行時，DELETED表保存了刪除行的拷貝。更新一行時，INSERTED表中保存了一份新行的拷貝，DELETED表中保存了一份舊行的拷貝。3、語法格

4、式：CREATE TRIGGER 觸發(fā)器名稱ON 表或視圖 FOR | AFTER | INSTEAD OF DELETE，INSERT，UPDATEAS SQL語句 三、實驗內(nèi)容及步驟:：基本實驗內(nèi)容在SQL Server 2000中創(chuàng)建存儲過程和觸發(fā)器雖然可以通過企業(yè)管理器進(jìn)行，但是企業(yè)管理器僅僅是提供了一個利用T-SQL編寫存儲過程和觸發(fā)器的簡單模板，大部分內(nèi)容都是需要自己手工編寫的。而且在SQL Server 2005中只能通過T-SQL來編寫存儲過程和觸發(fā)器。因此，本實驗只要求通過T-SQL來創(chuàng)建并管理存儲過程和觸發(fā)器，可視化方式請參考相關(guān)資料。-3-（一）存儲過程的創(chuàng)建和執(zhí)行1、創(chuàng)

5、建下面的存儲過程并執(zhí)行，然后分析其功能1）創(chuàng)建CREATE PROCEDURE list_stu_courseASSELECT s.sno, s.sname, s.ssex, o, ame, skc.grade , k.kcreditFROM student s,course c, kaike k,skcWHERE s.sno=skc.sno AND skc.kno=k.kno AND o=oGO2)執(zhí)行EXEC list_stu_course 或者list_stu_course3）功能分析_2、帶參數(shù)存儲過程并執(zhí)行1）創(chuàng)建CREATE PROC list_student_department

6、 (department char(20)ASBEGINSELECT sno,sname,ssex,snative,mname,dname FROM student,major,departmentWHERE student.mno=major.mno AND major.dno=department.dno AND dname=departmentEND 2)執(zhí)行EXEC list_student_department department=計算機(jī)3）功能分析_3、根據(jù)功能要求完成下面的任務(wù)在stuManag數(shù)據(jù)庫中創(chuàng)建一個名為proc_1的存儲過程，產(chǎn)生某門課程的選課學(xué)生情況列表，其中包括

7、課程號、課程名、學(xué)號、姓名、成績。要求輸入某門課程的課程號，得到上述信息。1）創(chuàng)建：CREATE PROCEDURE proc_1_ASSELECT o,ame,s.sno, s.sname, skc.gradeFROM student s,course c,skc,kaike kWHERE s.sno=skc.sno AND skc.kno=k.kno AND o=o AND o = cnoGO2）執(zhí)行：EXEC proc_1 010101或EXEC proc_1 _4、根據(jù)功能要求，完成下面任務(wù)在stuManag數(shù)據(jù)庫中，定義存儲過程proc_2，要求實現(xiàn)輸入學(xué)生學(xué)號，根據(jù)該學(xué)生所選課程的

8、平均成績顯示提示信息，即如果平均成績在60分以上，顯示“此學(xué)生綜合成績合格！”，否則顯示“此學(xué)生綜合成績不合格！”。1）創(chuàng)建：CREATE PROCEDURE proc_2sno char(10)_ASSELECT savg = avg(grade) FROM skcWHERE skc.sno = snoIF savg>=60 PRINT 此學(xué)生綜合成績合格！ELSEPRINT 此學(xué)生綜合成績不合格！GO（二）管理存儲過程1、查看存儲過程代碼EXEC sp_helptext list_student_departmentEXEC sp_helptext objname=list_stud

9、ent_department2、修改存儲過程ALTER PROC list_student_departmentdepartment char(20)ASBEGINSELECT dname,mname,COUNT(*) 學(xué)生人數(shù)FROM student,major,departmentWHERE student.mno=major.mno AND major.dno=department.dno AND dname=departmentGROUP BY dname,mnameEND3、刪除存儲過程DROP PROCEDURE list_stu_course （三）觸發(fā)器操作1、觸發(fā)器

10、創(chuàng)建及驗證新建一個Test數(shù)據(jù)庫，然后在Test數(shù)據(jù)庫中完成下面內(nèi)容。在查詢分析器中輸入下面的代碼，思考其功能，運行后，查看輸出結(jié)果。并描述每一部分的功能或者輸出結(jié)果。1）創(chuàng)建觸發(fā)器CREATE TABLE TestTable1 ( -創(chuàng)建表TestTable1)c1 INT,c2 CHAR(30)CREATE TRIGGER trig1 ON TestTable1 FOR INSERT,UPDATE,DELETEas SELECT * FROM TestTable1-下面兩個語句分開執(zhí)行INSERT TestTable1 VALUES(1,林沖)UPDATE TestTable1 SET c

11、2=宋江 WHERE c1=12）inserted表和deleted表的使用CREATE TRIGGER trig1ON TestTable1FOR INSERT,UPDATE,DELETEASPRINT inserted表：SELECT * FROM INSERTEDPRINT deleted表：SELECT * FROM DELETEDGO-下面三個語句分開執(zhí)行INSERT TestTable1 VALUES(2,吳用)UPDATE TestTable1 SET c2=關(guān)勝 WHERE c1=2DELETE TestTable1 WHERE c1=23）insert和update觸發(fā)器CR

12、EATE TABLE TestTable2(c1 INT,c2 CHAR(10)GOCREATE TRIGGER trig2 ON TestTable2 FOR INSERT,UPDATEASDECLARE c1_1 intSELECT c1_1=c1 FROM INSERTEDIF c1_1<1 OR c1_1>108BEGIN ROLLBACK RAISERROR(c1值必須在1到108之間！,16,1)ENDGO-下面三個語句分開執(zhí)行INSERT TestTable2 VALUES (200,武松)INSERT TestTable2 VALUES (15,武松)UPDATE

13、TestTable2 SET c1=300 WHERE c2=武松4）delete觸發(fā)器INSERT stu VALUES (03170356,陳功,男,17,廣東)INSERT study VALUES (03170356,1,88)INSERT study VALUES (03170356,2,99)GOCREATE TRIGGER trig3 ON stu FOR DELETEASDELETE study WHERE study.sno=(SELECT sno FROM DELETED)go-執(zhí)行下面語句DELETE FROM stu WHERE sno=031703565）instea

14、d of觸發(fā)器CREATE TRIGGER aa ON TestTable2 instead of INSERT, UPDATE, DELETE ASINSERT TestTable1 VALUES(100,1234)-執(zhí)行下面語句，然后查詢TestTable2表INSERT INTO TestTable2 VALUES (10,宋江)2、基于stuManag數(shù)據(jù)，根據(jù)功能要求，完成T-SQL語句1）創(chuàng)建觸發(fā)器trigger_1，實現(xiàn)當(dāng)修改學(xué)生表（student）中的數(shù)據(jù)時，顯示提示信息“學(xué)生表被修改了”。CREATE TRIGGER trigger_1 ON studentFOR_AS PR

15、INT 學(xué)生課程表被修改了GO2）創(chuàng)建觸發(fā)器trigger_2，實現(xiàn)當(dāng)刪除課程表中某門學(xué)生的記錄時，對應(yīng)學(xué)生成績表中所有有關(guān)該學(xué)生的記錄均刪除。CREATE TRIGGER trigger_2 ON studentFOR DELETEAS DELETEFROM skcWHERE skc.sno=( _)GO3）創(chuàng)建觸發(fā)器trigger_3，實現(xiàn)當(dāng)修改學(xué)生課程表（course）中的某門課的課程號時，對應(yīng)學(xué)生成績表（skc）中的課程號也作修改。當(dāng)修改學(xué)生課程表中記錄時，相當(dāng)于刪除一條舊記錄并插入一條新記錄，刪除的舊記錄在deleted臨時表中，插入的新記錄在inserted臨時表中。因此，可以通過

16、在查詢分析器的查詢窗口中輸入如下語句創(chuàng)建觸發(fā)器：CREATE TRIGGER trigger_3 ON courseFOR UPDATEAS BEGINUPDATE skc SET kno=(SELECT kno FROM _)WHERE kno.sno=(SELECT kno from _)ENDGO3、刪除觸發(fā)器trigger_1_ trigger_1數(shù)據(jù)庫原理與應(yīng)用實驗教材：高級實驗內(nèi)容基于stuManag數(shù)據(jù)庫完成下面的任務(wù)1、創(chuàng)建一個存儲過程：當(dāng)任意輸入一個學(xué)生的姓名時，將返回該學(xué)生的學(xué)號、選修的課程名稱和課程成績。并執(zhí)行該存儲過程，查詢“劉永強(qiáng)”的學(xué)號、選修課程和課程成績。2、創(chuàng)建一個存儲過程，要求實現(xiàn)如下功能：輸入學(xué)生學(xué)號，根據(jù)該學(xué)生所選課程的總學(xué)分顯示提示信息，如果總學(xué)分<9，則顯示“此學(xué)生所選總學(xué)分為XXX，學(xué)分不足！”，否則顯示“此學(xué)生所選總學(xué)分為XXX，學(xué)分已足！”。并調(diào)用修改后的存儲過程，顯示“02020101”學(xué)生的總學(xué)分情況。3、創(chuàng)建一個帶輸入?yún)?shù)和輸出參數(shù)的存儲過程，要求實現(xiàn)如下功能：輸入學(xué)生學(xué)號，然后輸出學(xué)生的選課門數(shù)、平均分以