数据库实验报告三《数据库原理》实验报告实验五：触发器、存储过程和函数实验六：ODBC/JDBC数据库编程学号姓名班级日期2013302534 杨添文10011303 2015.10.31实验五:触发器、存储过程和函数一、实验内容1.使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。
（5分）（1）在原有数据库SPJ中，建立如下语句：exec sp_rename'V_SPJ','V_SPJ三建'（2）结果为：2.针对SPJ数据库，创建并执行如下的存储过程：（共计35分）(1)创建一个带参数的存储过程—jsearch。
该存储过程的作用是：当任意输入一个工程代号时，将返回供应该工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。
执行jsearch存储过程，查询“J1”对应的信息。
（10分）(1)存储过程为：create procedure jsearch(@searchingfor_jno nchar(20))asbeginselect J.JNAME,S.SNAME,P.PNAMEfrom S,P,J,SPJwhereSPJ.JNO = @searchingfor_jno and SPJ.JNO=J.JNO andSPJ.SNO=S.SNO and SPJ.PNO=P.PNOEnduse[SPJ]go(2)执行存储过程如下：declare @solution intexec @solution = [dbo].[jsearch]@searchingfor_jno =N'J1'select'solution'= @solutiongo(3)结果：(2)使用S表，为其创建一个加密的存储过程—jmsearch。
该存储过程的作用是：当执行该存储过程时，将返回北京供应商的所有信息。
（10分）create procedure jmsearchwith encryptionasbeginselect*from Swhere CITY ='北京'end(3)使用系统存储过程sp_helptext查看jsearch, jmsearch的文本信息。
（5分）(1)当输入为：exec sp_helptext'jsearch'结果是：(2)当输入为：exec sp_helptext'jmsearch'结果是：(4)执行jmsearch存储过程，查看北京供应商的情况。
（5分）(1)执行存储过程：use[SPJ]godeclare @solution intexec @solution = [dbo].[jmsearch]select'solution'= @solutiongo(2)结果为：(5)删除jmsearch存储过程。
（5分）drop procedure jmsearch3.针对Student数据库，创建和执行如下的触发器：（共计40分）(1)删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。
该触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。
触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。
(5分)(1)创建触发器：use studentgocreate trigger insert_son scafter insertasif(exists(select*from insertedwhere cno !='1'or cno !='2'or cno !='3'or cno !='6'))beginprint'不能插入记录这样的纪录'rollback transactionendelseprint'记录插入成功'（2）验证：use studentgoinsert intosc(sno,cno,grade)values('95001','5','88')结果：(2)为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是禁止删除S表中的记录。
触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作。
(5分)(1)创建触发器：use studentgocreate trigger dele_s1on safter deleteasrollback transactionprint'不能删除表中纪录'go(2)验证：当要删除表中记录时，出现如下图所示情况(3)为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。
触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作(SC表中的数据被正常删除)。
(5分)(1)创建触发器：use studentgocreate trigger dele_s2on sfor deleteasdelete from scwhere sc.sno in(select sno from deleted)(2)验证：（前提：删除触发器dele_s1,否则不能删除S表记录）delete from swhere s.sno='95001'select*from sc结果为：(4)为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容。
触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。
(5分)(1)创建触发器：use studentgocreate trigger update_son safter updateasif update(sdept)beginraiserror('sdept不能被更改',10,1)rollback transactionend(2)验证：当要进行数据更改时，出现如下图所示情况：(5)禁用update_s触发器。
禁用之后，更新S表中的“sdept”字段的内容，验证触发器是否还继续正常工作。
(5分)(1)禁用触发器：use studentgodisable trigger update_son sgo(2)验证：已经将“MA”更改为“IS”，如下图所示：(6)删除update_s触发器。
(5分)use studentgodrop trigger update_sgo(7)创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum,avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。
利用触发器实现如下的功能：当SC表中有记录插入、删除或者更新时，自动更新表CAvgGrade。
注意SC表中的grade 为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，需要计算该成绩。
(10分)(1)创建统计表CAvgGrade：create table CAvgGrade(cno char(10)primary key,snum int,examsnum int,avggrade float)(2)创建触发器：use studentgocreate trigger upd_ins_dele_CAvgGradeon scafter update,insert,deleteasbegindeclare @cno char(10)declare @snum intdeclare @examsum intdeclare @avggrade intselect @cno=cno from insertedselect @cno=cno from deletedselect @snum =COUNT (*)from sc where @cno=cnoselect @examsum =COUNT(*)from sc where @cno=cno andgrade>=0select @avggrade =AVG(grade)from sc where @cno=cno and grade>=0update CAvgGradesetsnum=@snum,cno=@cno,examsnum=@examsum,avggrade=@avggrade where cno=@cnoend(3)测试：A、（测试插入）use studentgoinsert into scvalues('95005','1',88)结果为：（CAvgGrade表）（sc表）B、（测试删除）use studentgodelete from scwhere cno='1'结果为：（CAvgGrade表）（sc表）C、（测试更改）update scset grade=99where cno='1'结果为：（CAvgGrade表）（sc表）4.创建一个works数据库，其中包含员工表empoyee(eID, eName,salary)，假设该表中有1000条员工数据，完成下列要求（总计20分，每题10分）。
(1)为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。
其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。
例如2015年插入的第一条数据是20050001，所有1000条员工ID分别是20150001-20151000。
调用该函数实现自动插入1000条数据。
（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）(1)自定义：use worksgocreate procedure generateEIDasbegindeclare @for intset @for = 0while(@for < 1000)begininsert intoemployeevalues(20050001+@for,'name'+CAST(@for asNCHAR(20)),2000+CAST(FLOOR(rand()*3001)as int))set @for=@for+1endenduse worksgoDECLARE @return_value intEXEC@return_value = [dbo].[generateEID]SELECT'Return Value'= @return_valueGO(2)结果：(2)该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：工资增长规则如下：●工资在3000元以下，每月涨300元；●工资在3000-4000元之间，每月涨200元；●工资大于或者等于4000元，每月涨50元；(1)语句如下：declare mycursor cursor forselect salary from empoyeeopen mycursordeclare @salary intfetch next from mycursor into @salarywhile@@FETCH_STATUS=0beginif(@salary<3000)beginupdate empoyee set salary=@salary+300 where current of mycursorendelse if(@salary<4000)beginupdate empoyee set salary=@salary+200 where current of mycursorendelsebeginupdate empoyee set salary=@salary+50 where current of mycursorendfetch next from mycursor into @salaryendclose mycursordeallocate mycursor(2)结果如下：二、实验反思触发器是对表进行插入、更新、删除的时候会自动执行的特殊存储过程，本章学习的触发器类型是after触发器，即只有当执行update,delete,insert 的时候这个触发器才会被使用到，触发器的使用为数据库操作真的提供了很多便利。