數(shù)據(jù)庫原理與應用實驗9

1、實驗九存儲過程與觸發(fā)器一、實驗目的及要求本實驗主要目的是讓學生通過實驗掌握存儲過程的創(chuàng)建及使用方式、觸發(fā)器的創(chuàng)建，具體要求 有：理解存儲過程的概念及類型，深刻理解存儲過程的優(yōu)點掌握創(chuàng)建各種存儲過程的方法掌握調(diào)用存儲過程的方法掌握查看、修改、刪除存儲過程的方法理解觸發(fā)器的概念與類型理解觸發(fā)器的功能及工作原理掌握創(chuàng)建、更改、刪除觸發(fā)器的方法掌握利用觸發(fā)器維護數(shù)據(jù)完整性的方法二、實驗原理及背景知識（一）存儲過程知識存儲過程是一種數(shù)據(jù)庫對象，將執(zhí)行計劃存儲在數(shù)據(jù)庫的服務器中。它的運行速度比獨立運行 同樣的程序要快。1、存儲過程類型1）系統(tǒng)存儲過程存儲在master數(shù)據(jù)庫中，以sp_為前綴?？梢栽谄渌麛?shù)

2、據(jù)庫中對其進行調(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)建存儲過程的基本語法格式：CREATEPROCEDURE存儲過程名稱參數(shù)定義AS SQL語句（二）觸發(fā)器知識觸發(fā)器是一種特殊類型的存儲過程，主要通過事件進行觸發(fā)執(zhí)行的?？梢詫崿F(xiàn)由主鍵和外鍵所 不能保證的參照完整性和數(shù)據(jù)的一致性；強化約束；跟蹤變化；級聯(lián)運行；存儲過程的調(diào)用等功能

3、。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、語法格式：CREATE TRIGGER觸發(fā)器名稱O

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

5、創(chuàng)建CREATE PROCEDURE List stu courseAS_SELECT s . sno s sname s ssex, c.enor c cname, sc.mark:, k:. kcreditFROM student s course c,kaike k.student_course scWHERE s sno= sc. sno AND sc. cno= k. eno AND k:. eno c. enoGO2）執(zhí)行EXEC list_stu_course或者list_stu_course3）功能分析2、帶參數(shù)存儲過程并執(zhí)行創(chuàng)建CREATE PROC 1 i s t_s tu

6、dent_departm.ent (department char(20)AS|BEGINSELECT sno sname ssex, snative mname FROM student,rmajor departmentWHERE studentnmo=major mno田云二二二 -i-二二二二三二三；二三定二*二 2)END執(zhí)行EXEC list_student_department department=工程與信息學院功能分析3、根據(jù)功能要求完成下面的任務在stuManag數(shù)據(jù)庫中創(chuàng)建一個名為proc_1的存儲過程，產(chǎn)生某門課程的選課學生情況列表，其 中包括課程號、課程名

7、、學號、姓名、成績。要求輸入某門課程的課程號，得到上述信息。1）創(chuàng)建：CREATE PROCEDURE proclASSELECT ccnor amer s.sno s.sname, sc.markFROM student s course cfstudent_course scWHERE s.sno = sc sno AND sc = 60PRINT ，此學生綜合成績合格！，ELSEprint ，此學生綜合成績不合格!GO2）執(zhí)行：EXEC proc_2 091650102,（二）管理存儲過程1、查看存儲過程代碼EXEC sp_helptext list_student_department

8、,EXEC sp_helptext objname=list_student_department*2、修改存儲過程1）修改alter proc 1ist_student_departmentdepartment char（20） as beginselect dname r mname r count （*）學生人數(shù) from student sf major department d.where s .mno=m.mno and. m,dno =d. dnoand d.n ame = dep a r tme ntgroup by dnamer nmame end.2）執(zhí)行（查詢工程與信息學

9、院情況）EXEC 3）功能分析：3、刪除存儲過程DROP PROCEDURE list_stu_course（三）觸發(fā)器操作1、觸發(fā)器創(chuàng)建及驗證新建一個Test數(shù)據(jù)庫，然后在Test數(shù)據(jù)庫中完成下面內(nèi)容。在查詢分析器中輸入下面的代碼， 思考其功能，運行后，查看輸出結果。并描述每一部分的功能或者輸出結果。1）創(chuàng)建觸發(fā)器創(chuàng)建表 TestTable1CREATE TAB崩 TestTablel（Cl INT, c2 CHAR（30）創(chuàng)建觸發(fā)器CREATE TRIGGER trigl ON TestTablelFOR INSERT.UPDATEDELETE asSELECT *FROM TestTab

10、lel-下面兩個語句分開執(zhí)行INSERT TestTable1 VALUES（1, 林沖）UPDATE TestTable1 SET c2=宋江 WHERE c1=1輸出結果觸發(fā)器功能描述2) inserted表和deleted表的使用CREATE TRIGGER trig2 ON TestTablel FOR INSERTUPDATEDELETE ASPRINT r inserted: TSELECT * FROM INSERTEDPRINT deleted；, SELECT * FROM DELETEDGO-下面三個語句分開執(zhí)行INSERT TestTablel VALUES(2, 吳用)

11、UPDATE TestTable1 SET c2=關勝 WHERE c1=2DELETE TestTable1 WHERE c1=2insert 和 update 觸發(fā)器CREATE TABLE TestTable2(cl INTfc2 CHAR(LO)GOCREATE TRIGGER trig3 ON TestTable2FOR INSERT,UPDATEASDECLARE cl_l intSELECT C1_1=C1 FROM INSERTEDIF C1_11O8BEGIN ROLLBACKRAISERRORf Cl值必須在工到工08之間！ J , 16, 1) ENDGO-下面三個語句分

12、開執(zhí)行INSERT TestTable2 VALUES (200, 武松)INSERT TestTable2 VALUES (15, 武松)UPDATE TestTable2 SET c1=300 WHERE c2=* 武松輸出結果觸發(fā)器功能描述4) delete觸發(fā)器INSERT Stu VALUES (1 031703561 f T 陳功 1男17r 1 廣東，)INSERT study VALUES (r 03170356f x1 88)INSERT study VALUES (T 03170356f r 2,99) GOCREATE TRIGGER trig4 ON stu FOR D

13、ELETEASDELETE study WHERE study,sno=(SELECT sno FROM DELETED) go-執(zhí)行下面語句DELETE FROM stu WHERE sno=03170356輸出結果觸發(fā)器功能描述5)instead of觸發(fā)器CREATE TRIGGER trig5 ON TestTable2 instead Of INSERT, UPDATE, DELETE ASINSERT TestTablel VALUES(100, r1234 r)-執(zhí)行下面語句，然后查詢TestTable1表INSERT INTO TestTable2 VALUES (10, 宋江

14、)輸出結果觸發(fā)器功能描 2、基于stuManag數(shù)據(jù)，根據(jù)功能要求，完成T-SQL語句1）創(chuàng)建觸發(fā)器trigger_1，實現(xiàn)當修改學生表（student）中的數(shù)據(jù)時，顯示提示信息“學生表被修 改了”。CREATE TRIGGER trigger_l ON studentFOR ASPRINT,學生表被修改了， GO2）創(chuàng)建觸發(fā)器trigger_2,實現(xiàn)當刪除課程表中某門學生的記錄時，對應學生成績表中所有有關該 學生的記錄均刪除。CREATE TRIGGER trigger_2 ON student IFOR DELETE AS DELETEFROM stud.ent_cours eWHERE

15、student_course. sno= （） GO創(chuàng)建觸發(fā)器trigger_3，實現(xiàn)當修改學生課程表(course )中的某門課的課程號時，對應學生成 績表(student_course)中的課程號也作修改。當修改學生課程表中記錄時，相當于刪除一條舊記錄并插入一條新記錄，刪除的舊記錄在deleted 臨時表中，插入的新記錄在inserted臨時表中。因此，可以通過在查詢分析器的查詢窗口中輸入如 下語句創(chuàng)建觸發(fā)器：CREATE TRIGGER trigger_3 ON courseFOR UPDATE一ASBEGINUPDATE student_courseSET cno= (SELECT e

16、no FROM )WHERE cno= (SELECT eno from )END二3、刪除觸發(fā)器trigger_1 trigger_1II ：高級實驗內(nèi)容基于stuManag數(shù)據(jù)庫完成下面的任務1、創(chuàng)建一個存儲過程proc_name：當任意輸入一個學生的姓名時，將返回該學生的學號、選修 的課程名稱和課程成績。并執(zhí)行該存儲過程，查詢“蔡大炮”的學號、選修課程和課程成績。2、創(chuàng)建一個存儲過程proc_totalcredit，要求實現(xiàn)如下功能：輸入學生學號，根據(jù)該學生所 選課程的總學分顯示提示信息，如果總學分9,則顯示“此學生所選總學分為XXX，學分不足！ ”， 否則顯示“此學生所選總學分為XXX，學分已足！ ”。并調(diào)用修改后的存儲過程，顯示091650102” 學生的總學分情況。3、創(chuàng)建一個帶輸入?yún)?shù)和輸出參數(shù)的存儲過程，要求實現(xiàn)如下功能：輸入學生學號，然后輸出 學生的選課門數(shù)、平均分以及所選學分。4、首先創(chuàng)建一個表stu_credit，用來存放學生的學號及選修總學分。然后創(chuàng)建一個觸發(fā)器 trigger_scredit,實現(xiàn)如下功能：當在學生選課表(student_course)中刪除