1、大数据库原理课程设计实验创建存储过程与触发器 存储过程与触发器实验日期和时间： 2016年5月13日、星期五第节实验室：DJ2-信息管理实验室班级：学号：实验环境：1. 硬件：笔记本电脑2. 软件：SQL Server 2012实验原理：存储过程概念：存储过程是事先编好的，存储在数据库中的一组被编译了的T-SQL命令集合，这些命令用来完成对数据库的指定操作。存储过程可以接受用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输入参数。触发器概念：触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性
2、的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。实验任务：此作业成绩得分根据你完成的任务的难度和数量评分，完成后在实验室给老师演示验收，课后提交电子版报告。如额外完成自拟题目应当事先将所拟题目提交给老师或在报告中明确标注题意。假定有学校的图书馆管理信息系统，可以用于日常管理书库和同学们的借还书工作。以下列出参考的库表情况：根据管理的业务需求来分析，该管理信息系统的数据库应至少包括如下数据表：（打号的是必须
3、有的表）1. 图书现有库存表。作用：记录图书的现有库存情况。至少包括：书号、书名、作者、简介、类别、价格、出版日期、现有库存数量、最小库存量、库存总量、库存位置等。2. 读者信息表。作用：记录读者信息。至少包括：读者编号、证件类型、证件、性别、职业（可填写教师、学生、教工、其它）、所属单位、地址、联系等。3. 借书记录表。作用：记录借书情况,以及是否归还。至少包括：借阅ID（主键，可设置为自动编号）、书号、读者编号、借阅数量、借阅日期、是否归还、管理员编号等。4. 还书记录表。作用：记录还书情况。至少包括：还书ID（主键，可设置为自动编号）、书号、读者编号、归还数量、归还日期、是否超期（超过假
4、设45天为超期）、超期天数、管理员编号等。（附：为简化操作，续借可视为归还后再借）。5. 管理员信息表。作用：记录负责管理书库和借书还书工作的管理员信息。至少包括：管理员编号、职工编号（在职工档案表中的职工编号）、用户名、密码、管理员级别等。6. 职工档案表表。作用：记录职工档案。至少包括: 职工编号、性别、单位、职称、职务、出生日期、学历、其它字段自拟。7. 入库单表。作用：记录图书馆购买图书补充书库图书时的入库书单。包括字段：入库单ID、入库日期、入库书号、书名、入库数量其它请根据图书现有库存表自拟经手人编号等。实验步骤1、设计并创建数据库。1) 设计数据库，绘制ERD设计图。2) 根据E
5、RD创建数据库中的所有表，并根据业务需求正确设置主键、外键、约束条件、默认值等。3) 创建关系图，建立表之间的联系以保证参照完整性。注意，建表时，表中的外键的数据类型应当与其所参照的主表中的主键数据类型一致。（至少创建题目所需要的表）4) 基本数据录入。可以直接录入，也可将其它格式的表中的数据导入，或查询其它表中可利用的数据并插入到现有的表中。ERD设计图原代码：创建数据库、所有表，并根据业务需求正确设置主键、外键、约束条件、默认值create database 图书管理系统;use 图书管理系统;create table 图书现有库存表(书号 char(10) primary key,书名
6、char(10) not null,作者 char(10),简介 varchar,类别 char(10),价格 money not null, char,出版日期 datetime,现有库存数 int,最小库存量 int,库存总量 int,库存位置 varchar default 3F45);create table 读者信息表(读者编号 nchar(10) primary key,证件类型 nchar(4),证件 nchar(13), char(10),性别 char(2) default 男,check(性别 in (男,女),职业 char(4),所属单位 char(8),地址 char
7、(10),联系 char(11)create table 借书记录表(借阅ID int identity(1,1) primary key,读者编号 char(5),借阅数量 int,借阅日期 datetime default getdate(),是否归还 char(2) default 否,书号 char(10), 管理员编号 char(10),foreign key(书号)references 图书现有库存表(书号), foreign key(管理员编号)references 管理员信息表(管理员编号),);create table 还书记录表(还书ID int identity(1,1)
8、 primary key,书号 char(10), foreign key(书号)references 图书现有库存表(书号),归还数量 int ,归还日期 datetime default getdate(),是否超期 char(2),超期天数 char(5), 管理员编号 char(10), foreign key(管理员编号)references 管理员信息表(管理员编号),);create table 管理员信息表( 管理员编号 char(10) primary key, 职工编号 char(10), foreign key(职工编号)references 职工档案表(职工编号),用户
9、名 char(10) not null,密码 char(6) not null,管理员级别 char(4);create table 职工档案表(职工编号 char(10) primary key, 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); 关系图和数据录
10、入情况、其它测试方案及数据：实验步骤2、创建存储过程。（任选一题）1 创建可以按“书号”（参数）进行图书库存信息查询的存储过程。2 创建可以按“类别”（参数）进行某类图书库存信息查询的存储过程。3 创建可以按“读者编号”进行读者信息查询的存储过程。4 自拟题先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或全做或额外完成自拟题，请自己依照格式添加栏目，自拟题请写清题意。我的存储过程选题：（描述题目和欲实现的功能）1.创建可以按“书号”（参数）进行图书库存信息查询的存储过程。能够使得用户输入书号，就可以查询这
11、本书的信息原代码：创建存储过程按照书号进行查询（ashcx），参数为shuhao 定义为char（20）create proc ashcx shuhao char(20)asbegin select * from 图书现有库存表 where 书号=shuhaoend调用存储过程，查询书号为30001图书的信息exec ashcx shuhao=30001测试方案及数据：创建存储过程，并查询书号为30001的图书信息exec ashcx shuhao=30001测试结果：(文字说明、原代码、结果贴图)查询书号为3001书的信息实验步骤3、创建触发器。（任选一题）1 创建“借书记录”表的插入触发器
12、，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。2 创建“还书记录”表的插入触发器，每当有读者归还图书时，插入一条还书记录，同时计算是否超期并在“是否超期”和“超期天数”字段填写结果。并根据还书时提供的读者编号和书号，将“借书记录”表中的对应借阅记录的 “是否归还”中原来的“否”置为“是”（注意考虑特殊情况，比如一本书由同一个读者反复多次借阅或一次借阅多本的）；根据“归还数量”增加该图书的“现有库存数量”（图书现有库存表）。3 创建“入库单”表的插入触发器。在该表中插入图书入库记录时，在填写“图书编号
13、”和“入库数量”时，通过触发器的作用，能判断在“ 图书现有库存表”是否存在该图书的库存记录，如果有，则自动更新该图书的现有库存数量，如果现有库存表中不存在该图书的库存记录（有可能是原来没有的新书），则在“图书现有库存表”中自动插入该图书的库存记录。对于书库中已经存在的图书（此次只是补充图书数量）能通过触发器的作用自动填写入库单该记录中其它的未填的该书的对应信息（提示，根据书号在图书现有库存表查询）。4 自拟题目。先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或全做或额外完成自拟题，请自己依照格式添加栏目，
14、自拟题请写清题意。我的触发器选题：（描述题目和欲实现的功能）1 创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。原代码：/* 创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。*/-use 图书管理系统alter trigger crcfqon 借书记录表for insertas begin-声明变量declare zdbh int, sh char(
15、10),dzbh char(5),jysl int,glybh char(10)declare xykcs int-查询库存数量select zdbh=inserted.借阅ID,sh=inserted.书号,dzbh=inserted.读者编号,jysl=inserted.借阅数量,xykcs=图书现有库存表.现有库存数from 图书现有库存表 join inserted on inserted.书号 =图书现有库存表.书号if(jysl=xykcs) begin -根据插入的销售记录的数量更新库存量 update 图书现有库存表 set 现有库存数=现有库存数-jysl where sh=
16、书号 endelse begin print库存量不足！ rollback transaction endend 测试方案及数据：在借书记录表中插入数据：借书记录表(书号,读者编号, 借阅数量 )values(30002,0001,1)在借书记录表中插入数据：借书记录表(书号,读者编号, 借阅数量 )values(30002,0001,55)测试结果：(文字说明、原代码、结果贴图)第一次插入数据：借书记录表(书号,读者编号, 借阅数量 )values(30002,0001,1)use 图书管理系统select * from 借书记录表goselect * from 图书现有库存表 goinse
17、rt into 借书记录表(书号,读者编号, 借阅数量 )values(30002,0001,55)goselect * from 借书记录表goselect * from 图书现有库存表 go第二次测试插入数据：借书记录表(书号,读者编号, 借阅数量 )values(30002,0001,55)输出结果 “库存量不足！”use 图书管理系统select * from 借书记录表goselect * from 图书现有库存表 goinsert into 借书记录表(书号,读者编号, 借阅数量 )values(30002,0001,55)goselect * from 借书记录表goselect
18、 * from 图书现有库存表 go本实验总结：1. 创建、修改、删除、调用存储过程的语法。创建存储过程语法：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 删除存储过程语法：DROPPROCEDURE存储过程名称调用存储过程的语法：EXECUTEProcedure_
19、Name22. 举例说明存储过程中参数的应用方法。创建存储过程时设置一个参数，调用时必须也要有参数create proc ashcx shuhao char(20)asbegin select * from 图书现有库存表 where 书号=shuhaoend调用存储过程exec ashcx shuhao=300013. 简述触发器的种类。For触发器和After触发器：要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器；Instead of 触发器：触发器表示并不执行其所定义的操作（INSERT、 U
20、PDATE、 DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，但对同一操作只能定义一个INSTEAD OF 触发器。4. 什么是幻表？举例说明其用途。幻表就是在创建触发器时自己生成临时表，分别是deleted 和inserted，Inserted表就是放新的记录，Delete表就是放旧的记录，当你插入时，要插入的记录是新的，所以可以在Insert表中找到。当你删除时，要删除的记录是旧的，所以可以在Deleted表中找到。例如借书记录表中的借阅触发器，插入书号，读者编号，借阅数量时，插入新的容就在inserted表
21、中5. 创建、修改、删除触发器的语法。CreateTRIGGER trigger_nameON table | view WITH ENCRYPTION FOR |AFTER | INSTEAD OF Insert , Update WITH APPEND NOT FOR REPLICATION AS IF Update ( column ) AND | or Update ( column ) .n | IF ( COLUMNS_UpdateD ( ) bitwise_operator updated_bitmask) comparison_operator column_bitmask .
22、n sql_statement .n 修改将create改成alter删除用drop 触发器名6. 举例说明测试触发器有效的方法。例如在此实验中的借书插入触发器，先查询图书现有库存表，再查询借书记录表，然后调用触发器，插入一条借书记录，再次查询图书现有库存表和借书记录表，观察其中茶如的数据是否对应。7. 你在实验中遇到的主要问题及解决方案。触发器的声明变量搞不明白，不知道什么变量需要声明，查资料书上网查找解决。8. 在本实验中你所参考的资料列表。SQL Server2012数据库应用与开发教程数据库系统概论要求：1. 本文件以“学号班级-数据库原理课程设计-实验2.docx”命名。文件保存并关闭后打包成RAR文件，提交到我bbwhs.。注意：你的学号放在前。打包时先关闭被打包的文件，以免提交的容丢失或打不开。2. 提交时间：从发布任务时间起两周完成。3. 报告格式和容要求： a. 容和格式整齐。大标题采用黑体四号字加粗，小标题采用小四号字加粗。正文文字不小于五号，建议采用宋体，单倍行距。 b. 贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”。 c. 不要在报告中写与实验容无关的话，容要有条理、完整、并能突出重点，要将遇到的主要问题说明。4. 主动查阅资料，坚持自己亲手完成实验，弄清每个步骤和相关原理。
