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

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

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

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

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

5、儲(chǔ)過(guò)程并執(zhí)行，然后分析其功能1）創(chuàng)建CREATE PROCEDURE list_stu_courseASSELECT s.sno, s.sname, s.ssex, cbbbo, cbbbame, skc.grade , k.kcreditFROM student s,course c, kaike k,skcWHERE s.sno=skc.sno AND skc.kno=k.kno AND kbbbo=cbbboGO2)執(zhí)行EXEC list_stu_course 或者list_stu_course3）功能分析_2、帶參數(shù)存儲(chǔ)過(guò)程并執(zhí)行1）創(chuàng)建CREATE PROC list_student

6、_department (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ì)算機(jī)3）功能分析_3、根據(jù)功能要求完成下面的任務(wù)在stuManag數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為proc_1的存儲(chǔ)過(guò)程，產(chǎn)生某門(mén)課程的選課

7、學(xué)生情況列表，其中包括課程號(hào)、課程名、學(xué)號(hào)、姓名、成績(jī)。要求輸入某門(mén)課程的課程號(hào)，得到上述信息。1）創(chuàng)建：CREATE PROCEDURE proc_1_ASSELECT cbbbo,cbbbame,s.sno, s.sname, skc.gradeFROM student s,course c,skc,kaike kWHERE s.sno=skc.sno AND skc.kno=k.kno AND kbbbo=cbbbo AND cbbbo = cnoGO2）執(zhí)行：EXEC proc_1 010101或EXEC proc_1 _4、根據(jù)功能要求，完成下面任務(wù)在stuManag數(shù)據(jù)庫(kù)中，定義存

8、儲(chǔ)過(guò)程proc_2，要求實(shí)現(xiàn)輸入學(xué)生學(xué)號(hào)，根據(jù)該學(xué)生所選課程的平均成績(jī)顯示提示信息，即如果平均成績(jī)?cè)?0分以上，顯示“此學(xué)生綜合成績(jī)合格！”，否則顯示“此學(xué)生綜合成績(jī)不合格！”。1）創(chuàng)建：CREATE PROCEDURE proc_2sno char(10)_ASSELECT savg = avg(grade) FROM skcWHERE skc.sno = snoIF savg>=60 PRINT 此學(xué)生綜合成績(jī)合格！ELSEPRINT 此學(xué)生綜合成績(jī)不合格！GO（二）管理存儲(chǔ)過(guò)程1、查看存儲(chǔ)過(guò)程代碼EXEC sp_helptext list_student_departmentEXE

9、C sp_helptext objname=list_student_department2、修改存儲(chǔ)過(guò)程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、刪除存儲(chǔ)過(guò)程DROP PROCEDURE lis

10、t_stu_course （三）觸發(fā)器操作1、觸發(fā)器創(chuàng)建及驗(yàn)證新建一個(gè)Test數(shù)據(jù)庫(kù)，然后在Test數(shù)據(jù)庫(kù)中完成下面內(nèi)容。在查詢(xún)分析器中輸入下面的代碼，思考其功能，運(yùn)行后，查看輸出結(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-下面兩個(gè)語(yǔ)句分開(kāi)執(zhí)行INSERT TestTable1 VALU

11、ES(1,林沖)UPDATE TestTable1 SET c2=宋江 WHERE c1=12）inserted表和deleted表的使用CREATE TRIGGER trig1ON TestTable1FOR INSERT,UPDATE,DELETEASPRINT inserted表：SELECT * FROM INSERTEDPRINT deleted表：SELECT * FROM DELETEDGO-下面三個(gè)語(yǔ)句分開(kāi)執(zhí)行INSERT TestTable1 VALUES(2,吳用)UPDATE TestTable1 SET c2=關(guān)勝 WHERE c1=2DELETE TestTable1

12、 WHERE c1=23）insert和update觸發(fā)器CREATE 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-下面三個(gè)語(yǔ)句分開(kāi)執(zhí)行INSERT TestTable2 VALUES (200,武松)INSERT T

13、estTable2 VALUES (15,武松)UPDATE 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í)行下面語(yǔ)句DELETE FROM

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

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

16、時(shí)表中，插入的新記錄在inserted臨時(shí)表中。因此，可以通過(guò)在查詢(xún)分析器的查詢(xún)窗口中輸入如下語(yǔ)句創(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：高級(jí)實(shí)驗(yàn)內(nèi)容基于stuManag數(shù)據(jù)庫(kù)完成下面的任務(wù)1、創(chuàng)建一個(gè)存儲(chǔ)過(guò)程：當(dāng)任意輸入一個(gè)學(xué)生的姓名時(shí)，將返回該學(xué)生的學(xué)號(hào)、選修的課程名稱(chēng)和課程成績(jī)。并執(zhí)行該存儲(chǔ)過(guò)程，查詢(xún)“劉永強(qiáng)”的學(xué)號(hào)、選修課程和課程成績(jī)。2、創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，要求實(shí)現(xiàn)如下功能：輸入學(xué)生學(xué)號(hào)，根據(jù)該學(xué)生所選課程的總學(xué)分顯示提示信息，如果總學(xué)分<9，則顯示“此學(xué)生所選總學(xué)分為XXX，學(xué)分不足！”，否則顯示“此學(xué)生所選總學(xué)分為XXX，學(xué)分已足！”。并調(diào)用修改后的存儲(chǔ)過(guò)程，顯示“02020101”學(xué)生的總學(xué)分情況。3、創(chuàng)建一個(gè)帶輸入?yún)?shù)和輸出參數(shù)的存儲(chǔ)過(guò)程，要求實(shí)現(xiàn)如下功能：輸入學(xué)生學(xué)號(hào)，然后輸出學(xué)生的選課門(mén)數(shù)、平均分以及所選學(xué)分。4、首先創(chuàng)建一個(gè)表stu_credit，用來(lái)存放學(xué)生的學(xué)