實驗六-存儲過程和觸發(fā)器

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

2、o表中學號字段St_ID的前兩位與之對應，則D_Info表與St_Info表之間的聯系通過這兩個字段的運算構成連接條件。1. 分別執(zhí)行存儲過程getPractice，查詢“法學院”和“材料科學與工程學院”的學生中參與“實踐”課程的所有學生學號、姓名、所學課程編號和課程名稱。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='實踐'Goexec getPractice '法學院'exec getPractice '材料科學與工程學院'2. 利用系統(tǒng)存儲過程sp_rename將getPractice更名為getPctStusp_rename getpractice,getPctStu3. 修改存儲過程getPctSt

4、u，返回指定院系中參與實踐課程的學生人次數，并利用該存儲過程以“法學院”為輸入參數驗證執(zhí)行的結果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='實踐'goexec getPctStu '法學院'4. 再修改存儲過程getPctStu，返回指定院系中參與實踐課程的學生人數。注：“人數”和“人次數”是不同的，對某一學生而言，如果參與了多門實踐課程，則“人次數”是指其參與的課程門數，而“人數”仍為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='實踐' goexec getPctStu '法學院'(二) 觸發(fā)器1.在studentdb數據庫中建立一個具有審計功能的觸發(fā)器：觸發(fā)器名為tr_sc，功能要求：審計在s_c_info表中對score字段的更新和插入操作，將這些操作記錄到sc_log表中，sc_log表中有如下字段：操作類型type，學號st_id，課程號c_no，舊成績oldsco

7、re，新成績newscore，操作員uname，操作時間udate，其中操作員設定默認值為user，操作時間默認值為系統(tǒng)時間。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表上建立一個觸發(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四、思考與練習1存儲過程如何加密？在創(chuàng)建存儲過程的語句as前添加語句with encry