實(shí)驗(yàn)六存儲(chǔ)過(guò)程和觸發(fā)器

1、 實(shí)驗(yàn)六 存儲(chǔ)過(guò)程和觸發(fā)器一、目的與要求1. 掌握編寫(xiě)數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的方法。2. 掌握建立數(shù)據(jù)庫(kù)觸發(fā)器的方法，通過(guò)實(shí)驗(yàn)觀察觸發(fā)器的作用和觸發(fā)條件設(shè)置等相關(guān)操作。二、實(shí)驗(yàn)準(zhǔn)備1. 了解編寫(xiě)存儲(chǔ)過(guò)程和調(diào)用的T-SQL語(yǔ)法； 2. 了解觸發(fā)器的作用；3. 了解編寫(xiě)觸發(fā)器的T-SQL語(yǔ)法。三、實(shí)驗(yàn)內(nèi)容(一) 存儲(chǔ)過(guò)程在studentdb數(shù)據(jù)庫(kù)中建立存儲(chǔ)過(guò)程getPractice，查詢(xún)指定院系(名稱(chēng))(作為存儲(chǔ)過(guò)程的輸入?yún)?shù))中參與“實(shí)踐”課程學(xué)習(xí)的所有學(xué)生學(xué)號(hào)、姓名、所學(xué)課程編號(hào)和課程名稱(chēng)，若院系不存在，返回提示信息。提示：D_Info表中存儲(chǔ)了院系代碼D_ID，而St_Info表中學(xué)號(hào)字段St_ID

2、的前兩位與之對(duì)應(yīng)，則D_Info表與St_Info表之間的聯(lián)系通過(guò)這兩個(gè)字段的運(yùn)算構(gòu)成連接條件。1. 分別執(zhí)行存儲(chǔ)過(guò)程getPractice，查詢(xún)“法學(xué)院”和“材料科學(xué)與工程學(xué)院”的學(xué)生中參與“實(shí)踐”課程的所有學(xué)生學(xué)號(hào)、姓名、所學(xué)課程編號(hào)和課程名稱(chēng)。create procedure getPractice D_Name varchar(30) output asbeginif not exists (select *from D_Infowhere D_Name= D_Name )print 對(duì)不起，該院系不存在elseselect st_info.St_ID,C_Info.C_No,C_Na

3、mefrom s_c_info inner join st_info on st_info.St_ID=s_c_info.st_id inner join C_Info on s_c_info.c_no=C_Info.C_Nowhere st_info.St_ID in( select St_ID from st_info join D_Info on D_Info.D_ID =left(st_info.St_ID,2) where C_Info.C_Type=實(shí)踐 and D_Info.D_Name= D_Name ) end go2. 利用系統(tǒng)存儲(chǔ)過(guò)程sp_rename將getPracti

4、ce更名為getPctStuexecute sp_rename getPractice , getPctStu3. 修改存儲(chǔ)過(guò)程getPctStu，返回指定院系中參與實(shí)踐課程的學(xué)生人次數(shù)，并利用該存儲(chǔ)過(guò)程以“法學(xué)院”為輸入?yún)?shù)驗(yàn)證執(zhí)行的結(jié)果alter procedure getPctStu D_Name varchar(30) asbeginif not exists (select *from D_Infowhere D_Name= D_Name )print 對(duì)不起，該院系不存在elseselect st_info.St_ID,C_Info.C_No,C_Name,count (st_in

5、fo.St_ID) as 人次數(shù)from s_c_info inner join st_info on st_info.St_ID=s_c_info.st_id inner join C_Info on s_c_info.c_no=C_Info.C_Nowhere st_info.St_ID in( select St_ID from st_info join D_Info on D_Info.D_ID =left(st_info.St_ID,2) where C_Info.C_Type=實(shí)踐 and D_Info.D_Name= D_Name ) group by st_info.St_ID

6、,C_Info.C_No,C_Nameend Goexec getPctStu 法學(xué)院Go4. 再修改存儲(chǔ)過(guò)程getPctStu，返回指定院系中參與實(shí)踐課程的學(xué)生人數(shù)。注：“人數(shù)”和“人次數(shù)”是不同的，對(duì)某一學(xué)生而言，如果參與了多門(mén)實(shí)踐課程，則“人次數(shù)”是指其參與的課程門(mén)數(shù)，而“人數(shù)”仍為1。(二) 觸發(fā)器1.在studentdb數(shù)據(jù)庫(kù)中建立一個(gè)具有審計(jì)功能的觸發(fā)器：觸發(fā)器名為tr_sc，功能要求：審計(jì)在s_c_info表中對(duì)score字段的更新和插入操作，將這些操作記錄到sc_log表中，sc_log表中有如下字段：操作類(lèi)型type，學(xué)號(hào)st_id，課程號(hào)c_no，舊成績(jī)oldscore，新

7、成績(jī)newscore，操作員uname，操作時(shí)間udate，其中操作員設(shè)定默認(rèn)值為user，操作時(shí)間默認(rèn)值為系統(tǒng)時(shí)間。create table sc_log(type varchar(4),st_id varchar(10),c_no varchar(10),oldscore int,newscore int,uname varchar(10) default user,udata datetime default getdate()gocreate trigger tr_scon s_c_infofor insert,updateas if update(score)beginif(sele

8、ct count(*) from deleted)0insert into sc_log(type,st_id,c_no,oldscore,newscore)select update,s_c_info.st_id,s_c_info.c_no,i.score,d.score from s_c_info,inserted i,deleted dwhere s_c_info.st_id=i.st_id and i.st_id=d.st_idelseinsert into sc_log(type,st_id,c_no,newscore)select insert,st_id,c_no,scorefr

9、om insertedendgo3. 在s_c_info表上建立一個(gè)觸發(fā)器tr_updasc，用于監(jiān)控對(duì)成績(jī)的更新，要求更新后的成績(jī)不能比更新前低，如果新成績(jī)低則取消操作，給出提示信息，否則允許更新。create trigger tr_updascon s_c_infoafter updateas declare cj1 int,cj2 intselect cj1=deleted.score from deletedselect cj2=inserted.score from insertedif(cj2cj1)beginraiserror(新成績(jī)比老成績(jī)低，取消操作,16,1)rollbac

10、k transactionendgo(三)查看存儲(chǔ)過(guò)程和觸發(fā)器的信息1.用sp_help查看以上建立的存儲(chǔ)過(guò)程和觸發(fā)器的基本信息sp_help tr_scgosp_help tr_updascgo2.用sp_helptext查看存儲(chǔ)過(guò)程和觸發(fā)器的代碼sp_helptext tr_scgosp_helptext tr_updascgo四、思考與練習(xí)1存儲(chǔ)過(guò)程如何加密？Create procedure encrypt_thisWith encryption -加密,在存儲(chǔ)過(guò)程添加這一語(yǔ)句即可ASSelect* from user_tbGO查看存儲(chǔ)過(guò)程儲(chǔ)存的文本：Exec sp_helptext encrypt_this下面是結(jié)果集：The projects comments have been encrypted. -對(duì)象已經(jīng)被加密2觸發(fā)器有什么好處和壞處？使用觸發(fā)器的好處：1. 自動(dòng)執(zhí)行。觸發(fā)器在對(duì)表的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序的操作）之后立即被激活。2. 級(jí)聯(lián)更新。觸發(fā)器可以通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表進(jìn)行層疊更改，這比直接把代碼寫(xiě)在前臺(tái)的做法更安全合理。3. 強(qiáng)化約束。觸發(fā)器可以引用其它表中的列，能夠?qū)崿F(xiàn)比CHECK約束更為復(fù)雜的約束。4. 跟蹤變化。觸發(fā)器可