1、数据库原理实验报告学号 姓名 班级 日期实验五：触发器、存储过程和函数实验六：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。该存储过程的作用是：当任意输入一个工程代号
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(2)执行存
3、储过程如下：declare solution intexec solution = dbo.jsearchsearchingfor_jno = NJ1select solution = solutiongo(3)结果：(2) 使用 S 表，为其创建一个加密的存储过程jmsearch。该存储过程的作用是：当执行该存储过程时，将返回北京供应商的所有信息。（10 分）create procedure jmsearchwith encryptionasbeginselect *from Swhere CITY = 北京end(3) 使用系统存储过程 sp_helptext 查看 jsearch, jm
4、search 的文本信息。（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 分）drop procedure jmsearch3. 针对 Student 数据库，创建和执
5、行如下的触发器：（共计 40 分）(1) 删除 SC 表上的外键约束，针对 SC 表创建一个名为 insert_s 的 INSERT 触发器。该触发器的功能：当用户向 SC 表中插入记录时，如果插入的 cno 值不是 C 表中Cno 的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。触发器创建成功之后，向 SC 表插入记录，验证触发器是否正常工作。(5分)(1)创建触发器：use studentgocreate trigger insert_son scafter insertasif(exists(select * from inserted where cno !=
6、 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 表创建一个名为 dele_s1 的 DELETE 触发器，该触发器的作用是禁止删除 S 表中的记录。触发器创建成功之后，删除 S 表中的记录，验证触发器是否正常工作。(5 分)(1)创建触发器：use studentgocreate trigge
7、r 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 sc where sc.sn
8、o in(select sno from deleted)(2)验证：（前提：删除触发器 dele_s1,否则不能删除 S表记录）delete from swhere s.sno=95001select * from sc结果为：(4) 为 S 表创建一个名为 update_s 的 UPDATE 触发器，该触发器的作用是禁止更新 S表中“sdept”字段的内容。触发器创建成功之后，更新 S 表中“sdept”字段的内容，验证触发器是否正常工作。(5 分)(1)创建触发器：use studentgocreate trigger update_son safter updateasif updat
9、e(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) 创建
10、一个新的课程成绩统计表 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 in
11、t,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=c
12、noselect 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=avggradewhere cno=cnoend(3)测试：A、（测试插入）use studentgoinsert into scvalues(95005,1,88)结果为：（CAvgGrade表）（sc 表） B、 （测试删除
13、）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 位
14、的数字，前四位表示插入员工数据的当前年份，后四位按照从 0001 到 9999 的顺序增长。例如 2015 年插入的第一条数据是 20050001，所有 1000 条员工 ID 分别是 20150001-20151000。调用该函数实现自动插入 1000 条数据。（注意插入数据的时候员工姓名可以为任意值，工资是 2000-5000 之间的数字）(1)自定义：use worksgocreate procedure generateEIDasbegindeclare for intset for = 0while(for 1000)begininsert into employee values(
15、20050001+for,name+CAST(for as NCHAR(20),2000+CAST(FLOOR(rand()*3001) as int)set for=for+1endenduse worksgoDECLARE return_value intEXEC return_value = dbo.generateEIDSELECT Return Value = return_valueGO(2)结果：(2) 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：工资增长规则如下： 工资在 3000 元以下，每月涨 300 元； 工资在 30
16、00-4000 元之间，每月涨 200 元； 工资大于或者等于 4000 元，每月涨 50 元；(1)语句如下：declare mycursor cursor for select salary from empoyeeopen mycursordeclare salary intfetch next from mycursor into salarywhile FETCH_STATUS=0beginif(salary3000)beginupdate empoyee set salary=salary+300 where current of mycursorendelse if(salary
17、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 触发器，即只有当执行 updat
18、e,delete,insert 的时候这个触发器才会被使用到，触发器的使用为数据库操作真的提供了很多便利。实验六: ODBC/JDBC 数据库编程一、实验内容1. ODBC 配置以及程序调试：（50 分）(1) 配置一个 ODBC 数据源，要求数据源名称：student，其中包含 s(学生信息)表。1、 设置属性：2、 更改默认数据库：3、 测试数据源：4、 测试成功：5、 结果为：(2) 阅读并运行实验给出的例子程序，理解 ODBC 编程，要求简单写出自己对这段程序的理解或者流程图，并且请给出程序运行结果示例图。一、对程序的流程的理解以下面的步骤展开：1、 定义句柄跟变量。其中句柄有环境句柄
19、、连接句柄、语句句柄；2、 初始化环境（1） 分配环境句柄；（2） 设置管理环境属性；（3） 连接数据库。3、 建立连接（1） 分配连接句柄；（2） 连接若失败则返回错误值。4、 初始化语句句柄。设置了语句选项。5、 执行语句（1） 预编译带有参数语句；（2） 预先声明插入语句；（3） 执行 SQL 语句。6、 处理结果集合的同时执行预编译后的语句。7、 中止处理（1） 释放语句句柄；（2） 释放环境句柄；（3） 断开连接。二、运行结果：点了“执行”之后：2. 参考给出的 ODBC 编程示例，使用 ODBC 技术，编写一个简单的程序，包含对数据库SPJ 的连接，查询，插入，修改和删除。（50
20、分）（1）插入数据：代码：insert into S values(95005,刘鹏飞,男,1992-05-16,IS)结果为：（2）删除数据：代码：delete from S where Sname = 刘鹏飞结果为：3. 用 JDBC 实现对数据库 student 的连接，查询，插入，修改和删除。(选做实验)（1）插入数据：（2）删除数据：（3）数据的查询：二、实验反思ODBC 是一种使用 SQL 的程序设计接口，这次实验主要就是利用 ODBC 编程，使用 ODBC 让应用程序的编写者避免了与数据源相联的复杂性，很不错的一种技术，但是要利用 C+程序编写时显得能力不足，因为涉及的方面太广了，语言很复杂，经过老师给的例子程序才勉强理解一些，后来看了很多博客才渐渐有一些眉目。
