数据库实验报告三

1、数据库原理实验报告实验五： 触发器、存储过程和函数实验六： ODBC/JDBC数据库编程学号姓名班级日期2013302534杨添文100113032015.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。该存储过程的作用是：当任意

2、输入一个工程代号时，将返回供应该工程零件的供应商的名称(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 and SPJ.SNO=S.SNO and SPJ.PNO=P.PNOEnduseSPJgo

3、(2)执行存储过程如下：declare solution intexec solution = dbo.jsearchsearchingfor_jno = N'J1'select 'solution' = solutiongo(3)结果：(2) 使用S表，为其创建一个加密的存储过程jmsearch。该存储过程的作用是：当执行该存储过程时，将返回北京供应商的所有信息。（10分）create procedure jmsearchwith encryptionasbeginselect *from Swhere CITY = '北京'end(3) 使用

4、系统存储过程sp_helptext查看jsearch, jmsearch的文本信息。（5分）(1)当输入为：exec sp_helptext 'jsearch'结果是：(2)当输入为：exec sp_helptext 'jmsearch'结果是：(4) 执行jmsearch存储过程，查看北京供应商的情况。（5分）(1)执行存储过程：useSPJgodeclare solution intexec solution = dbo.jmsearchselect 'solution' = solutiongo(2)结果为： (5) 删除jmsearch存

5、储过程。（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(sel

6、ect * from inserted where cno != '1' or cno != '2' or cno != '3' or cno != '6')begin print '不能插入记录这样的纪录'rollback transactionendelseprint '记录插入成功'（2）验证：use studentgoinsert intosc(sno,cno,grade)values('95001','5','88')结果：(2) 为S表创建

7、一个名为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表中的记录，验证触发器是否

8、正常工作(SC表中的数据被正常删除)。(5分) (1)创建触发器：use studentgocreate trigger dele_s2on sfor deleteasdelete from sc where 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”字段的内容。触发器创建

9、成功之后，更新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

10、 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时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是g

11、rade为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 avggr

12、ade 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=a

13、vggradewhere 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数据库，其

14、中包含员工表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)自

15、定义：use worksgocreate procedure generateEIDasbegindeclare for intset for = 0while(for < 1000)begininsert into employee values(20050001+for,'name'+CAST(for as NCHAR(20),2000+CAST(FLOOR(rand()*3001) as int)set for=for+1endenduse worksgoDECLAREreturn_value intEXECreturn_value = dbo.generateEI

16、DSELECT'Return Value' = return_valueGO (2)结果： (2) 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：工资增长规则如下：l 工资在3000元以下，每月涨300元；l 工资在3000-4000元之间，每月涨200元；l 工资大于或者等于4000元，每月涨50元； (1)语句如下：declare mycursor cursor for select salary from empoyeeopen mycursordeclare salary intfetch next from mycu

17、rsor 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

18、 next from mycursor into salaryendclose mycursordeallocate mycursor (2)结果如下： 二、实验反思触发器是对表进行插入、更新、删除的时候会自动执行的特殊存储过程，本章学习的触发器类型是after触发器，即只有当执行update,delete,insert的时候这个触发器才会被使用到，触发器的使用为数据库操作真的提供了很多便利。实验六: ODBC/JDBC数据库编程一、实验内容1. ODBC配置以及程序调试：（50分）(1) 配置一个ODBC数据源，要求数据源名称：student，其中包含s(学生信息)表。1、 设置属性：2、 更改默认数据库：3、 测试数据源：4、 测试成功：5、 结果为：(2) 阅读并运行实验给出的例子程序，理解ODBC编程，要求简单写出自己对这段程序的理解或者流程图，并且请给出程序运行结果示例图。一、对程序的流程的理解以下面的步骤展开：1、 定义句柄跟变量。其中句柄有环境句柄、连接句柄、语句句柄；2、 初始化环境（1） 分配环境句柄；（2） 设置管理环境属性；（3） 连接数据库。3、 建立连接（1） 分配连接句柄；（2） 连