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

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

2、o表中學(xué)號字段St_ID的前兩位與之對應(yīng)，則D_Info表與St_Info表之間的聯(lián)系通過這兩個(gè)字段的運(yùn)算構(gòu)成連接條件。1. 分別執(zhí)行存儲過程getPractice，查詢“法學(xué)院”和“材料科學(xué)與工程學(xué)院”的學(xué)生中參與“實(shí)踐”課程的所有學(xué)生學(xué)號、姓名、所學(xué)課程編號和課程名稱。create procedure getPracticed_name varchar(30)asselect st_info.st_id,st_info.st_name,s_c_info.c_no,c_info.c_namefrom st_info,d_info,s_c_info,c_infowhere d_info.d_n

3、ame=d_name and st_info.st_id=s_c_info.st_id andd_info.d_id=left(st_info.st_id,2)and s_c_info.c_no=c_info.c_no and c_info.c_type='實(shí)踐'Goexec getPractice '法學(xué)院'exec getPractice '材料科學(xué)與工程學(xué)院'2. 利用系統(tǒng)存儲過程sp_rename將getPractice更名為getPctStusp_rename getpractice,getPctStu3. 修改存儲過程getPctSt

4、u，返回指定院系中參與實(shí)踐課程的學(xué)生人次數(shù)，并利用該存儲過程以“法學(xué)院”為輸入?yún)?shù)驗(yàn)證執(zhí)行的結(jié)果alter procedure getPctStud_name varchar(30)asselect count(s_c_info.st_id)from s_c_info,c_info,d_info,st_infowhere d_info.d_name=d_name and st_info.st_id=s_c_info.st_id and d_info.d_id=left(st_info.st_id,2) and s_c_info.c_no=c_info.c_no and c_info.c_typ

5、e='實(shí)踐'goexec getPctStu '法學(xué)院'4. 再修改存儲過程getPctStu，返回指定院系中參與實(shí)踐課程的學(xué)生人數(shù)。注：“人數(shù)”和“人次數(shù)”是不同的，對某一學(xué)生而言，如果參與了多門實(shí)踐課程，則“人次數(shù)”是指其參與的課程門數(shù)，而“人數(shù)”仍為1。alter procedure getPctStud_name varchar(30)asselect count(distinct s_c_info.st_id)from s_c_info,c_info,d_info,st_infowhere d_info.d_name=d_name and st_inf

6、o.st_id=s_c_info.st_id and d_info.d_id=left(st_info.st_id,2)and s_c_info.c_no=c_info.c_no and c_info.c_type='實(shí)踐' goexec getPctStu '法學(xué)院'(二) 觸發(fā)器1.在studentdb數(shù)據(jù)庫中建立一個(gè)具有審計(jì)功能的觸發(fā)器：觸發(fā)器名為tr_sc，功能要求：審計(jì)在s_c_info表中對score字段的更新和插入操作，將這些操作記錄到sc_log表中，sc_log表中有如下字段：操作類型type，學(xué)號st_id，課程號c_no，舊成績oldsco

7、re，新成績newscore，操作員uname，操作時(shí)間udate，其中操作員設(shè)定默認(rèn)值為user，操作時(shí)間默認(rèn)值為系統(tǒng)時(shí)間。create table sc_log( type char(6), st_id char(8), c_no char(8), oldscore numeric(5,1), newscore numeric(5,1), uname varchar(20) default user, udate datetime default getdate()create trigger tr_sc on s_c_info for insert,updateasif update(s

8、core)begin if(select count(*) from deleted)<>0 insert into sc_log(type,st_id,c_no,oldscore,newscore) select 'update',i.st_id,i.c_no,d.score,i.score from inserted i ,deleted d where i.st_id=d.st_id and i.c_no=d.c_no else insert into sc_log(type,st_id,c_no,newscore) select 'insert

9、9;,st_id,c_no,score from insertedend2. 在s_c_info表上建立一個(gè)觸發(fā)器tr_updasc，用于監(jiān)控對成績的更新，要求更新后的成績不能比更新前低，如果新成績低則取消操作，給出提示信息，否則允許更新。create trigger tr_updasc on s_c_infofor updateas declare newscore numeric(5,1),oldscore numeric(5,1) select newscore=score from inserted select oldscore=score from deleted if (newscore<oldscore) begin print('新成績不能比舊成績低!') rollback transaction end(三)查看存儲過程和觸發(fā)器的信息1.用sp_help查看以上建立的存儲過程和觸發(fā)器的基本信息sp_help getPctStusp_help tr_updasc2. 用sp_helptext查看存儲過程和觸發(fā)器的代碼sp_helptext getPctStusp_helptext tr_updasc四、思考與練習(xí)1存儲過程如何加密？在創(chuàng)建存儲過程的語句as前添加語句with encry