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

管工学院·计算机系·《数据库原理课程设计》实验报告第1页2015-11-30存储过程与触发器实验日期和时间：2016年5月13日、星期五第节实验室：DJ2-信息管理实验室班级：学号：姓名：实验环境：1.硬件：笔记本电脑2.软件：SQLServer2012实验原理：存储过程概念：存储过程是事先编好的，存储在数据库中的一组被编译了的T-SQL命令集合，这些命令用来完成对数据库的指定操作。存储过程可以接受用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输入参数。触发器概念：触发器（trigger）是SQLserver提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。实验任务：此作业成绩得分根据你完成的任务的难度和数量评分，完成后在实验室给老师演示验收，课后提交电子版报告。如额外完成自拟题目应当事先将所拟题目提交给老师或在报告中明确标注题意。假定有学校的图书馆管理信息系统，可以用于日常管理书库和同学们的借还书工作。以下列出参考的库表情况：根据管理的业务需求来分析，该管理信息系统的数据库应至少包括如下数据表：（打★号的是必须有的表）1.★图书现有库存表。作用：记录图书的现有库存情况。至少包括：书号、书名、作者、简介、类别、价格、出版社、出版日期、现有库存数量、最小库存量、库存总量、库存位置等。2.★读者信息表。作用：记录读者信息。至少包括：读者编号、证件类型、证件号码、姓名、性别、职业（可填写教师、学生、教工、其它……）、所属单位、地址、联系电话等。3.★借书记录表。作用：记录借书情况,以及是否归还。至少包括：借阅ID（主键，可设置为自动编号）、书号、读者编号、借阅数量、借阅日期、是否归还、管理员编号……等。4.★还书记录表。作用：记录还书情况。至少包括：还书ID（主键，可设置为自动编号）、书号、读者编号、归还数量、归还日期、是否超期（超过假设45天为超期）、超期天数、管理员编号……等。（附：为简化操作，续借可视为归还后再借）。5.管理员信息表。作用：记录负责管理书库和借书还书工作的管理员信息。至少包括：管理员编号、职工编号（在职工档案表中的职工编号）、用户名、密码、管理管工学院·计算机系·《数据库原理课程设计》实验报告第2页2015-11-30员级别等……。6.职工档案表表。作用：记录职工档案。至少包括:职工编号、姓名、性别、单位、职称、职务、出生日期、学历、……其它字段自拟。7.入库单表。作用：记录图书馆购买图书补充书库图书时的入库书单。包括字段：入库单ID、入库日期、入库书号、书名、入库数量……其它请根据图书现有库存表自拟……经手人编号等。实验步骤1、设计并创建数据库。①)设计数据库，绘制ERD设计图。②)根据ERD创建数据库中的所有表，并根据业务需求正确设置主键、外键、约束条件、默认值等。③)创建关系图，建立表之间的联系以保证参照完整性。注意，建表时，表中的外键的数据类型应当与其所参照的主表中的主键数据类型一致。（至少创建题目所需要的表）④)基本数据录入。可以直接录入，也可将其它格式的表中的数据导入，或查询其它表中可利用的数据并插入到现有的表中。ERD设计图图书现有库存表PK书号书名作者简介类别价格出版社出版日期现有库存数最小库存量库存总量库存位置读者信息表PK读者编号证件类型证件号码姓名性别职业所属单位地址联系电话借书记录表PK借阅IDFK1书号FK2读者编号借阅数量借阅日期是否归还管理员编号还书记录表PK还书IDFK1书号FK2读者编号归还数量归还日期是否超期超期天数FK3管理员编号管理员信息表PK管理员编号FK1职工编号用户名密码管理员级别职工档案表PK职工编号姓名性别单位职称职务出生日期学历入库单表PK入库单ID入库日期入库书号书名入库数量经手人编号原代码：创建数据库、所有表，并根据业务需求正确设置主键、外键、约束条件、默认值管工学院·计算机系·《数据库原理课程设计》实验报告第3页2015-11-30createdatabase图书管理系统;use图书管理系统;createtable图书现有库存表(书号char(10)primarykey,书名char(10)notnull,作者char(10),简介varchar,类别char(10),价格moneynotnull,出版社char,出版日期datetime,现有库存数int,最小库存量int,库存总量int,库存位置varchardefault'3F45');createtable读者信息表(读者编号nchar(10)primarykey,证件类型nchar(4),证件号码nchar(13),姓名char(10),性别char(2)default'男',check(性别in('男','女')),职业char(4),所属单位char(8),地址char(10),联系电话char(11))createtable借书记录表(借阅IDintidentity(1,1)primarykey,读者编号char(5),借阅数量int,借阅日期datetimedefaultgetdate(),是否归还char(2)default'否',书号char(10),管理员编号char(10),foreignkey(书号)references图书现有库存表(书号),foreignkey(管理员编号)references管理员信息表(管理员编号),);createtable还书记录表(还书IDintidentity(1,1)primarykey,书号char(10),foreignkey(书号)references图书现有库存表(书号),归还数量int,管工学院·计算机系·《数据库原理课程设计》实验报告第4页2015-11-30归还日期datetimedefaultgetdate(),是否超期char(2),超期天数char(5),管理员编号char(10),foreignkey(管理员编号)references管理员信息表(管理员编号),);createtable管理员信息表(管理员编号char(10)primarykey,职工编号char(10),foreignkey(职工编号)references职工档案表(职工编号),用户名char(10)notnull,密码char(6)notnull,管理员级别char(4));createtable职工档案表(职工编号char(10)primarykey,姓名char(10),性别char(2)default'男',check(性别='男'or性别='女'),单位char(10),职称char(4),职务char(10),出生日期datetime,学历char(10));createtable入库单表(入库单IDchar(10),入库日期datetime,入库书号char(10),书名char(10),入库数量int);关系图和数据录入情况、其它测试方案及数据：实验步骤2、创建存储过程。（任选一题）①创建可以按“书号”（参数）进行图书库存信息查询的存储过程。②创建可以按“类别”（参数）进行某类图书库存信息查询的存储过程。③创建可以按“读者编号”进行读者信息查询的存储过程。④自拟题管工学院·计算机系·《数据库原理课程设计》实验报告第5页2015-11-30先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或全做或额外完成自拟题，请自己依照格式添加栏目，自拟题请写清题意。我的存储过程选题：（描述题目和欲实现的功能）1.创建可以按“书号”（参数）进行图书库存信息查询的存储过程。能够使得用户输入书号，就可以查询这本书的信息原代码：创建存储过程按照书号进行查询（ashcx），参数为shuhao定义为char（20）createprocashcx@shuhaochar(20)asbeginselect*from图书现有库存表where书号=@shuhaoend调用存储过程，查询书号为‘30001’图书的信息execashcx@shuhao=30001测试方案及数据：创建存储过程，并查询书号为30001的图书信息execashcx@shuhao=30001测试结果：(文字说明、原代码、结果贴图)查询书号为3001书的信息管工学院·计算机系·《数据库原理课程设计》实验报告第6页2015-11-30实验步骤3、创建触发器。（任选一题）①创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。②创建“还书记录”表的插入触发器，每当有读者归还图书时，插入一条还书记录，同时计算是否超期并在“是否超期”和“超期天数”字段填写结果。并根据还书时提供的读者编号和书号，将“借书记录”表中的对应借阅记录的“是否归还”中原来的“否”置为“是”（注意考虑特殊情况，比如一本书由同一个读者反复多次借阅或一次借阅多本的）；根据“归还数量”增加该图书的“现有库存数量”（图书现有库存表）。③创建“入库单”表的插入触发器。在该表中插入图书入库记录时，在填写“图书编号”和“入库数量”时，通过触发器的作用，能判断在“图书现有库存表”是否存在该图书的库存记录，如果有，则自动更新该图书的现有库存数量，如果现有库存表中不存在该图书的库存记录（有可能是原来没有的新书），则在“图书现有库存表”中自动插入该图书的库存记录。对于书库中已经存在的图书（此次只是补充图书数量）能通过触发器的作用自动填写入库单该记录中其它的未填的该书的对应信息（提示，根据书号在图书现有库存表查询）。④自拟题目。先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或全做或额外完成自拟题，请自己依照格式添加栏目，自拟题请写清题意。我的触发器选题：（描述题目和欲实现的功能）①创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。原代码：/*①创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。*/--use图书管理系统altertriggercrcfqon借书记录表forinsertasbegin--声明变量declare@zdbhint,@shchar(10),@dzbhchar(5),@jyslint,@glybhchar(10)declare@xykcsint管工学院·计算机系·《数据库原理课程设计》实验报告第7页2015-11-30--查询库存数量select@zdbh=inserted.借阅ID,@sh=inserted.书号,@dzbh=inserted.读者编号,@jysl=inserted.借阅数量,@xykcs=图书现有库存表.现有库存数from图书现有库存表joininsertedoninserted.书号=图书现有库存表.书号if(@jysl=@xykcs)begin--根据插入的销售记录的数量更新库存量update图书现有库存表set现有库存数=现有库存数-@jyslwhere@sh=书号endelsebeginprint'库存量不足！！！'rollbacktransactionendend测试方案及数据：在借书记录表中插入数据：借书记录表(书号,读者编号,借阅数量)values('30002','000