《数据库实验报告三》由会员分享，可在线阅读，更多相关《数据库实验报告三（8页珍藏版）》请在金锄头文库上搜索。
1、 .wd.数据库原理实验报告实验五： 触发器、存储过程和函数实验六： ODBC/JDBC数据库编程学号姓名班级日期2013302534杨添文100113032015.10.31实验五:触发器、存储过程和函数一、实验内容1. 使用系统存储过程(sp_rename)将视图“V_SPJ更名为“V_SPJ_三建。5分1在原有数据库SPJ中，建设如下语句：execsp_renameV_SPJ,V_SPJ三建2结果为：2. 针对SPJ数据库，创立并执行如下的存储过程：共计35分(1) 创立一个带参数的存储过程jsearch。该存储过程的作用是：当任意输入一个工程代号时，将返回供给该工程零件的供给商的名称(
2、SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。执行jsearch存储过程，查询“J1对应的信息。10分(1)存储过程为：createprocedure jsearch(searchingfor_jno nchar(20)asbeginselect J.JNAME,S.SNAME,P.PNAMEfrom S,P,J,SPJwhereSPJ.JNO = searchingfor_jno and SPJ.JNO=J.JNO and SPJ.SNO=S.SNO and SPJ.PNO=P.PNOEnduseSPJgo(2)执行存储过程如下：declare solution int
3、exec solution = dbo.jsearchsearchingfor_jno =NJ1selectsolution= solutiongo(3)结果：(2) 使用S表，为其创立一个加密的存储过程jmsearch。该存储过程的作用是：当执行该存储过程时，将返回北京供给商的所有信息。10分createprocedure jmsearchwithencryptionasbeginselect*from Swhere CITY =北京end(3) 使用系统存储过程sp_helptext查看jsearch, jmsearch的文本信息。5分(1)当输入为：execsp_helptextjsea
4、rch结果是：(2)当输入为：execsp_helptextjmsearch结果是：(4) 执行jmsearch存储过程，查看北京供给商的情况。5分(1)执行存储过程：useSPJgodeclare solution intexec solution = dbo.jmsearchselectsolution= solutiongo(2)结果为：(5) 删除jmsearch存储过程。5分dropprocedure jmsearch3. 针对Student数据库，创立和执行如下的触发器：共计40分(1) 删除SC表上的外键约束，针对SC表创立一个名为insert_s的INSERT触发器。该触发器的
5、功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录，否则提示“记录插入成功。触发器创立成功之后，向SC表插入记录，验证触发器是否正常工作。(5分)(1)创立触发器：use studentgocreatetrigger insert_son scafterinsertasif(exists(select*from inserted where cno !=1or cno !=2or cno !=3or cno !=6)beginprint不能插入记录这样的纪录rollbacktransactionendelseprint记录插入成功2
6、验证：use studentgoinsertintosc(sno,cno,grade)values(95001,5,88)结果：(2) 为S表创立一个名为dele_s1的DELETE触发器，该触发器的作用是制止删除S表中的记录。触发器创立成功之后，删除S表中的记录，验证触发器是否正常工作。(5分)(1)创立触发器：use studentgocreatetrigger dele_s1on safterdeleteasrollbacktransactionprint不能删除表中纪录go(2)验证：当要删除表中记录时，出现如以以下图所示情况(3) 为S表创立一个名为dele_s2的DELETE触发器
7、，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创立成功之后，删除S表中的记录，验证触发器是否正常工作(SC表中的数据被正常删除)。(5分)(1)创立触发器：use studentgocreatetrigger dele_s2on sfordeleteasdeletefrom sc where sc.sno in(select sno from deleted)(2)验证：前提：删除触发器dele_s1,否则不能删除S表记录deletefrom swhere s.sno=95001select*from sc 结果为：(4) 为S表创立一个名为update_s的UPDA
8、TE触发器，该触发器的作用是制止更新S表中“sdept字段的内容。触发器创立成功之后，更新S表中“sdept字段的内容，验证触发器是否正常工作。(5分)(1)创立触发器：use studentgocreatetrigger update_son safterupdateasifupdate(sdept)beginraiserror(sdept不能被更改,10,1)rollbacktransactionend(2)验证： 当要进展数据更改时，出现如以以下图所示情况：(5) 禁用update_s触发器。禁用之后，更新S表中的“sdept字段的内容，验证触发器是否还继续正常工作。(5分)(1)禁用触
9、发器：use studentgodisabletrigger update_son sgo(2)验证：已经将“MA更改为“IS，如以以下图所示：(6) 删除update_s触发器。(5分)use studentgodroptrigger update_sgo(7) 创立一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当SC表中有记录插入、删除或者更新时，自动更新表CAvgGrade。注意SC表中的grade为NULL时说明该学生还未参加考试，计
10、算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，需要计算该成绩。(10分)(1)创立统计表CAvgGrade：createtable CAvgGrade( cno char(10)primarykey, snum int, examsnum int, avggrade float)(2)创立触发器：use studentgocreatetrigger upd_ins_dele_CAvgGradeon scafterupdate,insert,deleteasbegindeclare cno char(10)declare snum intdeclare examsum int
11、declare avggrade intselect cno=cno from insertedselect cno=cno from deletedselect snum =COUNT (*)from sc where cno=cnoselect examsum =COUNT(*)from sc where cno=cno and grade=0select avggrade =AVG(grade)from sc where cno=cno and grade=0update CAvgGradesetsnum=snum,cno=cno,examsnum=examsum,avggrade=av
12、ggradewhere cno=cnoend (3)测试：A、测试插入use studentgoinsertinto scvalues(95005,1,88)结果为： CAvgGrade表sc表 B、测试删除use studentgodeletefrom scwhere cno=1结果为：CAvgGrade表sc表C、测试更改update scset grade=99where cno=1 结果为： CAvgGrade表sc表4. 创立一个works数据库，其中包含员工表empoyee(eID, eName, salary)，假设该表中有1000条员工数据，完成以下要求总计20分，每题10分。
13、(1) 为了协助此题自动生成1000条员工数据，创立一个自动生成员工ID的用户自定义函数generateEID。其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。例如2015年插入的第一条数据是20050001，所有1000条员工ID分别是20150001-20151000。调用该函数实现自动插入1000条数据。注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字(1)自定义：use worksgocreateprocedure generateEIDasbegindeclare for intset for =
14、 0while(for 1000)begininsertintoemployee values(20050001+for,name+CAST(for asNCHAR(20),2000+CAST(FLOOR(rand()*3001)asint)set for=for+1endenduse worksgoDECLAREreturn_value intEXECreturn_value = dbo.generateEIDSELECTReturn Value= return_valueGO(2)结果：(2) 该公司方案为员工按照一定的规则涨工资，请使用游标创立一个存储过程，执行该存储过程完本钱次工资调整：工资增长规则如下：l 工资在3000元以下，每月涨300元；l 工资在3000-4000元之间，每月涨200元；l 工资大于
