实验六-存储过程和触发器

实验6存储过程和触发器一、目的和要求1.掌握如何编写数据库存储过程。2.了解如何设置数据库触发器，并通过实验观察相关操作，如设置触发器的作用和触发器条件。二、实验准备1.了解存储过程编写和调用的T-SQL语法；了解触发器的作用。3.了解创建触发器的T-SQL语法。三、实验内容(a)存储过程创建存储在Studentdb数据库中的过程getPractice，查询指定部门(名称) (存储过程的输入参数)中参与实践过程的所有学生学习编号、名称、学习的课程编号和课程名称，如果本科不存在，则返回提示信息。提示：D_Info表存储部门代码D_ID，如果与St_Info表中初中编号字段St_ID的前两位数匹配，则D_Info表和St_Info表之间的连接将通过两个段的运算构成连接条件。1.另外执行存储过程getPractice，查询“法学院”和“材料科学与工程系”的学生中参与“实践”课程的所有学生的学号、名字、课程编号、课程名称。Create procedure getPracticeD_Name varchar(30)OutputAsBeginIf not exists(select *From D_InfoWhere D_Name=D_Name)，以获取详细信息Print对不起。本科生不存在ElseSelect ST _ info.st _ id、c _ info.c _ no、c _ namefrom s _ c _ info inner joinst _ info on ST _ info . ST _ id=s _ c _ info . ST _ idinner join c _ info on s _ c _ info . c _ no=c _ info . c _ noWhere st_info。St_ID in(select St_IDfrom ST _ info join d _ info on d _ info . d _ id=left(ST _ info . ST _ id，2)Where C_Info。C_Type=实际and D_Info。D_Name=D_Name)，以获取详细信息End高2.使用系统存储过程sp_rename将getPractice重命名为getPctStuExecute sp _ rename get practice，getpctstu3.修改存储过程getPctStu，返回指定本科参加实践过程的学生数，并利用此存储过程作为输入参数验证“法学院”的执行结果Alter procedure getPctStuD_Name varchar(30)AsBeginIf not exists(select *From D_InfoWhere D_Name=D_Name)，以获取详细信息Print对不起。本科生不存在ElseSelect ST _ info.st _ id、c _ info.c _ no、c _ name、count (ST _ info.st _ id) as人数from s _ c _ info inner joinst _ info on ST _ info . ST _ id=s _ c _ info . ST _ idinner join c _ info on s _ c _ info . c _ no=c _ info . c _ noWhere st_info。St_ID in(select St_IDfrom ST _ info join d _ info on d _ info . d _ id=left(ST _ info . ST _ id，2)Where C_Info。C_Type=实际and D_Info。D_Name=D_Name)，以获取详细信息Group by ST _ info.st _ id、c _ info.c _ no或c _ nameEnd高Exec getPctStu法学院高4.再次修改存储过程getPctStu，返回指定学科中参与实践过程的学生数。注：“人数”和“人数”不同，如果一个学生参加多个实践课程，则“人数”是指他参与的课程门数，而“人数”仍然是1。(b)触发器1.在studentdb数据库中创建具有审计功能的触发器。触发器名为tr_sc，功能要求：在s_c_info表中，审计score字段的更新和插入操作，并将这些操作写入sc_log表。sc_log表格包含下列栏位：工作类型type、学习编号st_id、课程编号c_no、旧分数oldscore、新分数newscore、操作员uname、运营时间udate其中操作员设置默认值为user，运营时间默认值为系统时间。Create table sc_log(类型varchar(4)、St_id varchar(10)、C_no varchar(10)、Oldscore int、Newscore int、Uname varchar(10) default user、Udata datetime default getdate()，以获取详细信息高Create trigger tr_scOn s_c_infoFor insert，updateAsIf update(score)Begin选择计数(*)从选定项0Insert into sc _ log (type，ST _ id，c _ no，old score，new score)Select update、s _ c _ info.st _ id、s _ c _ info.c _ no、i.score、d.scoreFrom s _ c _ info、inserted I、deletedwhere s _ c _ info . ST _ id=I . ST _ id and I . ST _ id=d . ST _ idElseInsert into sc _ log (type，ST _ id，c _ no，new score)Select insert、st_id、c_no、score来自insertedEnd高3.在s_c_info表中创建触发器tr_updasc以监视结果更新。此时，更新后的表现不能低于更新前，如果新成绩低，取消工作并显示提示，否则允许更新。Create trigger tr_updascOn s_c_infoAfter updateAsDeclare cj1 int，cj2 intselect cj1=deleted . score from deletedselect cj2=inserted . score from insertedIf(cj2cj1)BeginRaiserror(新成绩低于现有成绩，取消工作，16，1)Rollback transactionEnd高(c)查看有关存储过程和触发器的信息1.要查看有关上面设置的存储过程和触发器的基本信息，请使用sp_helpSp _ helpttr _ sc高Sp _ helpdtr _ upd ASC高2.使用sp_helptext查看存储过程和触发器的代码Sp_helptext tr_sc高Sp_helptext tr_updasc高四、思维和实践1.如何加密存储过程？Create procedure encrypt_thisWith encryption -将此语句添加到存储过程中即可实现的加密ASSelect* from user_tb高查看存储过程存储的文本：Exec sp_helptext encrypt_this以下是结果集：the project s comments have been encrypted。-对象已加密触发器的优点和缺点是什么？使用触发器的优点：1.自动运行。触发程式会在表格中的资料修改后立即启用，例如手动输入或应用程式作业。2.级联更新。触发器可以通过数据库中的相关表级联更改，因此比直接在前台编写代码安全得多。3.加强约束。触发器可以引用其他表中的列，并且可以实施比CHECK约束条件更复杂的约束条件