数据库原理课程设计实验[创建存储过程与触发器]

1、.存储过程与触发器实验日期和时间 ：实验室 ： DJ2- 信息管理实验室星2016年5月13日、五第节期班级：学号：姓名：实验环境 ：1.硬件 ：笔记本电脑2.软件 ： SQL Server 2012实验原理 ：存储过程概念 ：存储过程是事先编好的 ，存储在数据库中的一组被编译了的T-SQL 命令集合，这些命令用来完成对数据库的指定操作。存储过程可以接受用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 （ DDL ）和数据操作语言（ DML ）语句，然后返回输入参数 。触发器概念 ：触发器 （ trigger ）是 SQL server提供给程序员和数据分析员来保证数据完整性

2、的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是 手 工 启 动 ， 而 是 由 事 件 来 触 发 ， 比 如 当 对 一个 表 进 行 操 作 （insert ， delete ，update ）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。实验任务 ：此作业成绩得分根据你完成的任务的难度和数量评分，完成后 在实验室给老师演示验收，课后 提交电子版报告。如额外完成自拟题目应当事先将所拟题目提交给老师或在报告中明确标注题意 。.专业 .专注.假定有学校的图书馆管理信息系统，可以用于日常管理书库和同学们的借还书工作。以下列出参考的库表情况：根据管

3、理的业务需求来分析，该管理信息系统的数据库应至少包括如下数据表：（ 打 号的是必须有的表）1. 图书现有库存表。作用：记录图书的现有库存情况。至少包括 ：书号 、书名、作者 、简介 、类别 、价格 、出版社 、出版日期 、现有库存数量、最小库存量 、库存总量 、库存位置等 。2. 读者信息表 。作用 ：记录读者信息 。至少包括 ：读者编号 、证件类型 、证件号码、姓名、性别、职业（可填写教师 、学生、教工、其它 ）、所属单位 、地址、联系电话等 。3. 借书记录表。作用 ：记录借书情况,以及是否归还。至少包括 ：借阅ID （主键，可设置为自动编号 ）、 书号、读者编号 、借阅数量 、借阅日期

4、、是否归还 、管理员编号 等。4. 还书记录表 。 作用 ：记录还书情况 。 至少包括 ：还书 ID （主键 ，可设置为自动编号 ）、书号 、读者编号 、归还数量 、归还日期 、是否超期 （超过假设 45 天为超期 ）、 超期天数 、管理员编号 等。（ 附：为简化操作 ，续借可视为归还后再借 ）。5.管理员信息表。作用 ：记录负责管理书库和借书还书工作的管理员信息。至少包括：管理员编号 、职工编号 （在职工档案表中的职工编号）、 用户名 、密码 、管理员级别等 。6. 职工档案表表 。 作用 ：记录职工档案 。 至少包括 : 职工编号 、姓名 、性别 、单位、职称、职务、出生日期 、学历、其它

5、字段自拟 。.专业 .专注.7.入库单表 。作用 ：记录图书馆购买图书补充书库图书时的入库书单。包括字段 ：入库单ID 、入库日期 、入库书号 、书名 、入库数量 其它请根据图书现有库存表自拟 经手人编号等 。实验步骤 1、设计并创建数据库 。 )设计数据库 ，绘制 ERD 设计图 。 )根据 ERD 创建数据库中的所有表，并根据业务需求正确设置主键、外键、约束条件 、默认值等 。 )创建关系图 ，建立表之间的联系以保证参照完整性。注意 ，建表时 ，表中的外键的数据类型应当与其所参照的主表中的主键数据类型一致。（至少创建题目所需要的表） )基本数据录入 。 可以直接录入 ，也可将其它格式的表中

6、的数据导入，或查询其它表中可利用的数据并插入到现有的表中 。ERD 设计图.专业 .专注. .图书现有库存表PK书号书名作者简介类别价格出版社出版日期现有库存数最小库存量库存总量库存位置还书记录表PK还书 IDFK1书号FK2读者编号归还数量归还日期是否超期超期天数FK3管理员编号. .读者信息表PK 读者编号借书记录表证件类型PK借阅 ID证件号码FK1书号姓名性别FK2读者编号职业借阅数量所属单位借阅日期地址是否归还联系电话管理员编号入库单表管理员信息表职工档案表PK 入库单 IDPK管理员编号PK职工编号入库日期FK1职工编号入库书号姓名用户名书名性别密码入库数量单位管理员级别经手人编号

7、职称职务出生日期学历原代码：创建数据库 、所有表 ，并根据业务需求正确设置主键、外键 、约束条件 、默认值create database图书管理系统 ;use图书管理系统 ;create table图书现有库存表 (书号 char (10) primarykey ,书名 char (10) not null,作者 char (10),简介 varchar ,类别 char (10),价格 money not null,出版社char,.专业 .专注.出版日期datetime ,现有库存数int ,最小库存量int ,库存总量int ,库存位置varchar default'3F45&#

8、39;);create table读者信息表 (读者编号nchar (10) primarykey,证件类型nchar (4),证件号码nchar (13),姓名 char (10),性别 char (2) default'男 ',check (性别 in ('男','女'),职业 char (4),所属单位char(8),地址 char (10),联系电话char(11)create table借书记录表 (借阅 ID int identity (1,1) primarykey ,读者编号char(5),借阅数量int ,借阅日期datetim

9、edefaultgetdate (),.专业 .专注.是否归还char(2) default'否' ,书号 char (10),管理员编号char (10),foreignkey(书号 )references图书现有库存表 (书号 ),foreignkey(管理员编号 )references管理员信息表 (管理员编号 ),);create table还书记录表 (还书 ID int identity (1,1) primarykey ,书号 char (10),foreignkey(书号 )references图书现有库存表 (书号 ),归还数量int ,归还日期datetim

10、edefaultgetdate (),是否超期char(2),超期天数char(5),管理员编号char (10),foreignkey(管理员编号 )references管理员信息表 (管理员编号 ),);create table管理员信息表 (管理员编号char (10) primarykey,职工编号char(10),foreignkey(职工编号 )references职工档案表 (职工编号 ),.专业 .专注.用户名char(10) not null,密码 char (6) not null,管理员级别char(4);create table职工档案表 (职工编号char(10) p

11、rimarykey ,姓名 char (10),性别 char (2)default'男 ',check (性别 = ' 男'or 性别 = '女'),单位 char (10),职称 char (4),职务 char (10),出生日期datetime ,学历 char (10);create table入库单表 (入库单 ID char (10),入库日期datetime ,入库书号char(10),书名 char (10),入库数量int);.专业 .专注.关系图和数据录入情况 、其它测试方案及数据 ：实验步骤 2、创建存储过程 。（任选一题

12、 ）创建可以按 “书号 ”（参数 ）进行图书库存信息查询的存储过程。创建可以按 “类别 ”（参数 ）进行某类图书库存信息查询的存储过程。创建可以按 “读者编号 ”进行读者信息查询的存储过程。自拟题先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况 、测试方案和数据 、测试结果等等 。如果选做多个或全做或额外完成自拟题 ，请自己依照格式添加栏目，自拟题请写清题意 。我的存储过程选题 ：（描述题目和欲实现的功能 ）1.创建可以按 “书号 ”（参数 ）进行图书库存信息查询的存储过程。能够使得用户输入书号，就可以查询这本书的信息原代码：创建存储过程按照书号进行

13、查询（ ashcx ），参数为 shuhao定义为 char （20 ）create proc ashcx shuhaochar(20)asbeginselect * from图书现有库存表where书号 = shuhao.专业 .专注.end调用存储过程，查询书号为 30001 图书的信息exec ashcx shuhao = 30001测试方案及数据 ：创建存储过程 ，并查询书号为30001 的图书信息exec ashcx shuhao = 30001测试结果 ：(文字说明 、原代码、结果贴图 )查询书号为3001 书的信息.专业 .专注.实验步骤 3、创建触发器 。（任选一题 ） 创建

14、“借书记录 ”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的 “现有库存数量”（图书现有库存表）， 图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。 创建 “还书记录 ”表的插入触发器，每当有读者归还图书时，插入一条还书记录，同时计算是否超期并在“是否超期 ”和 “超期天数 ”字段填写结果。并根据还书时提供的读者编号和书号 ，将 “借书记录 ”表中的对应借阅记录的“是否归还 ”中原来的 “否 ”置为 “是 ”（注意考虑特殊情况，比如一本书由同一个读者反复多次借阅或一次借阅多本的）；根据 “归还数量 ”增加该图书的 “现有库存数量”（图书现有库存表）。 创建 “入库单

15、”表的插入触发器。在该表中插入图书入库记录时，在填写 “图书编号 ”和“入库数量 ”时，通过触发器的作用，能判断在 “图书现有库存表”是否存在该图书的库存记录 ，如果有 ，则自动更新该图书的现有库存数量，如果现有库存表中不存在该图书的库存记录（有可能是原来没有的新书）， 则在 “图书现有库存表”中自动插入该图书的库存记录。对于书库中已经存在的图书（此次只是补充图书数量）能通过触发器的作用自动填写入库单该记录中其它的未填的该书的对应信息（提示 ，根据书号在图书现有库存表查询）。 自拟题目 。先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况 、测试方案和

16、数据 、测试结果等等 。如果选做多个或全做或额外完成自拟题 ，请自己依照格式添加栏目，自拟题请写清题意 。我的触发器选题 ：（描述题目和欲实现的功能 ）创建 “借书记录 ”表的插入触发器，每插入一条借阅记录就自动根据借阅数.专业 .专注.量减少该图书的“现有库存数量”（图书现有库存表）， 图书数量不足时可以报警 ，图书数量为零时拒绝插入借阅记录。原代码：/*创建 “借书记录 ”表的插入触发器 ，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量 ”（图书现有库存表 ），图书数量不足时可以报警 ，图书数量为零时拒绝插入借阅记录。*/-use图书管理系统alter triggercrc

17、fqon 借书记录表for insertasbegin- 声明变量declare zdbh int , sh char (10 ),dzbh char (5),jysl int ,glybhchar (10)declarexykcs int- 查询库存数量select zdbh = inserted .借阅 ID,sh= inserted .书号 ,dzbh = inserted .读者编号 ,jysl = inserted .借阅数.专业 .专注.量,xykcs = 图书现有库存表 .现有库存数from图书现有库存表join insertedon inserted .书号 = 图书现有库存表

18、 .书号if (jysl <= xykcs )begin- 根据插入的销售记录的数量更新库存量update图书现有库存表set 现有库存数 = 现有库存数 -jyslwhere sh = 书号endelsebeginprint '库存量不足 ！ 'rollbacktransactionendend测试方案及数据 ：在借书记录表中插入数据：借书记录表 (书号 ,读者编号 , 借阅数量)values('30002' ,'0001' ,1).专业 .专注.在借书记录表中插入数据：借书记录表 (书号 ,读者编号 , 借阅数量)values('

19、;30002' ,'0001' ,55)测试结果 ：(文字说明 、原代码、结果贴图 )第一次插入数据 ：借书记录表 (书号 ,读者编号 , 借阅数量)values('30002','0001',1)use 图书管理系统select * from借书记录表goselect * from图书现有库存表goinsert into借书记录表 (书号 ,读者编号 , 借阅数量)values ('30002' ,'0001' ,55)goselect * from借书记录表goselect * from图书现有库存表g

20、o.专业 .专注.第二次测试插入数据：借书记录表 ( 书号 ,读者编号 , 借阅数量)values('30002','0001',55)输出结果“库存量不足 ！”use 图书管理系统select * from借书记录表goselect * from图书现有库存表goinsert into借书记录表 (书号 ,读者编号 , 借阅数量)values ('30002' ,'0001' ,55)goselect * from借书记录表goselect * from图书现有库存表go.专业 .专注.本实验总结 ：1. 创建、修改、删除、调用

21、存储过程的语法 。创建存储过程语法：CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n 修改存储过程语法：ALTER PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n 删除存储过程语法：DROP PROCEDURE 存储过程名称调用存储过程的语法：EXECUTE Procedure_Name22. 举例说明存储过程中参数的应用方法 。创建存储过程时设

22、置一个参数，调用时必须也要有参数.专业 .专注.create proc ashcx shuhaochar(20)asbeginselect * from图书现有库存表where书号 = shuhaoend调用存储过程exec ashcx shuhao = 300013. 简述触发器的种类 。For 触发器和 After 触发器：要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器 ；Insteadof 触发器 ： 触发器表示并不执行其所定义的操作（ INSERT、UPDATE 、DELETE）， 而仅是执

23、行触发器本身。既可在表上定义INSTEAD OF触发器 ，也可以在视图上定义INSTEAD OF 触发器 ，但对同一操作只能定义一个INSTEAD OF触发器 。4.什么是幻表 ？举例说明其用途 。幻表就是在创建触发器时自己生成临时表，分别是 deleted和 inserted ， Inserted表就是放新的记录， Delete表就是放旧的记录，当你插入时，要插入的记录是新的， 所以可以在Insert 表中找到 。 当你删除时 ，要删除的记录是旧的，所以可以在Deleted 表中找到 。.专业 .专注.例如借书记录表中的借阅触发器，插入书号 ，读者编号 ，借阅数量时 ，插入新的内容就在inserted表中5. 创建、修改、删除触发器的语法 。CreateTRIGGER trigger_nameON table | view WITH ENCRYPTION FOR |AFTER | INSTEAD OF Insert , Update WITH APPEND NOT FOR REPLICATION A