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

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

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

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

4、否超期（超过假设45天为超期）、超期天数、管理员编号等。（附：为简化操作，续借可视为归还后再借）c5. 管理员信息表。作用：记录负责管理书库和借书还书工作的管理员信息。至少包括：管理员编号、职工编号（在职工档案表中的职工编号）、用户名、密码、管理2015-11-30管工学院计算机系数据库原理课程设计实验报告员级别等。6. 职工档案表表。作用：记录职工档案。至少包括：职工编号、姓名、性别、单位、职称、职务、出生日期、学历、其它字段自拟。7. 入库单表。作用：记录图书馆购买图书补充书库图书时的入库书单。包括字段：入库单ID、入库日期、入库书号、书名、入库数量其它请根据图书现有库存表自拟经手人编号等

5、。实验步骤1、设计并创建数据库。）设计数据库，绘制ERDi计图。）根据ERD创建数据库中的所有表，并根据业务需求正确设置主键、外键、约束条件、默认值等。）创建关系图，建立表之间的联系以保证参照完整性。注意，建表时，表中的外键的数据类型应当与其所参照的主表中的主键数据类型一致。（至少创建题目所需要的表）基本数据录入。可以直接录入，也可将其它格式的表中的数据导入，或查询其它表中可利用的数据并插入到现有的表中。ERD®计图图书现有库存表PK书号书名作者简介类别价格出版社出版日期现有库存数最小库存量库存总量库存位置读E信息表PK读者编号证件类型证件号码姓名性别职业所属单位地址联系电话借书记录

6、表PK借阅IDFK1FK2书号读者编号借阅数量借阅日期是否归还管理员编号还书记录表PK还书IDFK1FK2FK3书号读者编号归还数量归还日期是否超期超期天数管理员编号管理员信息表PK管理员编号FK1职工编号用户名密码管理员级别职工档案表PK职工编号姓名性别单位职称职务出生日期学历原代码:创建数据库、所有表，并根据业务需求正确设置主键、外键、约束条件、默认值2015-11-30管工学院计算机系数据库原理课程设计实验报告createdatabase图书管理系统；use图书管理系统；createtable图书现有库存表(书号char(10)primarykey,书名char(10)notnull,作

7、者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)

8、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

9、(书号)references图书现有库存表(书号)，归还数量int,第3页|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)

10、notnull,管理员级别char(4);createtable职工档案表(职工编号char(10)primarykey,姓名char(10),性另ijchar(2)default'男'，check(性另【J='男'or性别='女')，单位char(10),职称char(4),职务char(10),出生日期datetime,学历char(10);createtable入库单表(入库单IDchar(10),入库日期datetime,入库书号char(10),书名char(10),入库数量int);关系图和数据录入情况、其它测试方案及数据:实验步骤2

11、、创建存储过程。（任选-题） 创建可以按“书号”（参数）进行图书库存信息查询的存储过程。 创建可以按“类别”（参数）进行某类图书库存信息查询的存储过程。 创建可以按“读者编号”进行读者信息查询的存储过程。自拟题第4页|2015-11-30管工学院计算机系数据库原理课程设计实验报告先在卜面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或全做或额外完成自拟题，请自己依照格式添加栏目，自拟题请写清题意。我的存储过程选题：（描述题目和欲实现的功能）1.创建可以按“书号”（参数）进行图书库存信息查询的存储过程。能够使得用户

12、输入书号，就可以查询这本书的信息原代码：创建存储过程按照书号进行查询（ashcx）,参数为shuhaote义为char（20）createprocashcxshuhaochar（20）asbeginselect*from图书现有库存表where书号=shuhaoend调用存储过程，查询书号为30001'图书的信息execashcxshuha=30001测试方案及数据：创建存储过程，并查询书号为30001的图书信息execashcxshuha=30001测试结果：（文字说明、原代码、结果贴图）查询书号为3001书的信息造果k消息书臂书包作者尚介类别价格出版社出版日期现有库存盘4总量摩存位

13、置1壶而数字电子技术阎石工科用荆工科高等教育出版社20U-03-06an：M：m.DM4D6M2E2015-11-30管工学院计算机系数据库原理课程设计实验报告实验步骤3、创建触发器。（任选-题）创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。创建“还书记录”表的插入触发器，每当有读者归还图书时，插入一条还书记录，同时计算是否超期并在“是否超期”和“超期天数”字段填写结果。并根据还书时提供的读者编号和书号，将“借书记录”表中的对应借阅记录的“是否归还”中原来的“否”置为“

14、是"（注意考虑特殊情况，比如一本书由同一个读者反复多次借阅或一次借阅多本的）；根据“归还数量”增加该图书的“现有库存数量”（图书现有库存表）。创建“入库单”表的插入触发器。在该表中插入图书入库记录时，在填写“图书编号”和“入库数量”时，通过触发器的作用，能判断在“图书现有库存表”是否存在该图书的库存记录，如果有，则自动更新该图书的现有库存数量，如果现有库存表中不存在该图书的库存记录（有可能是原来没有的新书），则在“图书现有库存表”中自动插入该图书的库存记录。对于书库中已经存在的图书（此次只是补充图书数量）能通过触发器的作用自动填写入库单该记录中其它的未填的该书的对应信息（提示，根据书

15、号在图书现有库存表查询）。自拟题目。先在下面第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或全做或额外完成自拟题，请自己依照格式添加栏目，自拟题请写清题意。我的触发器选题：（描述题目和欲实现的功能）创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报警，图书数量为零时拒绝插入借阅记录。原代码：/*创建“借书记录”表的插入触发器，每插入一条借阅记录就自动根据借阅数量减少该图书的“现有库存数量”（图书现有库存表），图书数量不足时可以报

16、警，图书数量为零时拒绝插入借阅记录。- /-use图书管理系统altertriggercrcfqon借书记录表forinsertasbegin- -声明变量declarezdbhint,shchar（10）,dzbhchar（5）,jyslint,glybhchar（10）declarexykcsint第6页|2015-11-30管工学院计算机系数据库原理课程设计实验报告- -查询库存数量selectzdb=inserted.借阅ID,s=inserted.书号，dzb=inserted.读者编号,jysl=inserted.借阅数量，xykcs=图书现有库存表.现有库存数from图书现有库存

17、表joininsertedoninserted.书号=图书现有库存表.书号if（jysl<=xykcgbegin-根据插入的销售记录的数量更新库存量update图书现有库存表set现有库存数=现有库存数-jyslwheres=书号endelsebeginprint'库存量不足！'rollbacktransactionendend测试方案及数据：在借书记录表中插入数据：借书记录表（书号，读者编号，借阅数量）values（'30002','0001',1）在借书记录表中插入数据：借书记录表（书号，读者编号，借阅数量）values（'30

18、002','0001',55）测试结果：（文字说明、原代码、结果贴图）第一次插入数据：借书记录表（书号，读者编号，借阅数量）values（'30002','0001',1）use图书管理系统select*from借书记录表goselect*from图书现有库存表goinsertinto借书记录表（书号，读者编号，借阅数量）values（'30002','0001',55）goselect*from借书记录表goselect*from图书现有库存表2015-11-30管工学院计算机系数据库原理课程设计实验报告

19、goKMtfa-螂西|£3书号读吉博号册诩黝甲1R1HHB£吝否A3Qooiown110331flHK-L415:01:10M3201的HM-LB07.42:22.1572fl4-06-LSM3:如诩13】由3axeMtn30002州帆i书号30g初名勤亨电子技术作商百石御介工科用书类刷侑格工科558出196社总等软百出质粒出嫁日叫轴4-0X368网：旧DOD调书氏存技声flkimg5库存阮置Z£230002计算机操作系块与威丹却强优再社林计胤机33.00西宝电子出版20H-K-Oi00:00:00ooo49后603£330co。诺吉（I惇谈计理活睡1四

20、暹计箕机K.tn漕华大学出版20135AOSOOM00必20BN3M4.3m4傲手系大翔常理科4A.OO事出学±限201E-03HM00:00:00DDO5IDOi£IHiWiD1213书号德吉墉号1WQL30002oom店周款星情阉日期1RJ专HMT413QJ：H科31331fl-M-L6OT-4E：22L5?是否归进否£Y通道同日期同行.PC-201M1291eaM&SQLSERVER.PC-20160n9，fl23AdHi*四由百理至蜕OOiOaOO15t7第二次测试插入数据：借书记录表（书号，读者编号，借阅数量）values（'30002&

21、#39;,'0001',55）输出结果“库存量不足！”use图书管理系统select*from借书记录表goselect*from图书现有库存表goinsertinto借书记录表（书号，读者编号，借阅数量）values（'30002','0001',55）goselect*from借书记录表goselect*from图书现有库存表go100%M铝果-J清稳&行蚪衡）a行剧渐，消县迎电目i,第之行事务在触域55中靖京心，时忖里已中止“也行蚓域（4行脚1DO%*!好已光点.但甘惜JLIPC琨3即冢91的承赭&德屿flVFL_PCVBM

22、罪91皿孰AdiiniE图书答理至娩OOiDOtOO1后fir本实验总结：1 .创建、修改、删除、调用存储过程的语法。创建存储过程语法：CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT2015-11-30管工学院计算机系数据库原理课程设计实验报告，n修改存储过程语法：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT，n删除存储过程语法：DROPPROCEDURE存储过程名称调用存储过程的语法：E

23、XECUTEProcedure_Name22 .举例说明存储过程中参数的应用方法。创建存储过程时设置一个参数，调用时必须也要有参数createprocashcxshuhaochar(20)asbeginselect*from图书现有库存表where书号=shuhaoend调用存储过程execashcxshuha=300013 .简述触发器的种类。For触发器和After触发器：要求只有执行某一操作(insertupdatedelete)之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器；Insteadof触发器：触发器表示并不执行其所定义的操作(insert、upda

24、te、DELETE),而仅是执行触发器本身。既可在表上定义INSTEADOF触发器，也可以在视图上定义INSTEADOF触发器，但对同一操作只能定义一个INSTEADOF触发器。4 .什么是幻表？举例说明其用途。幻表就是在创建触发器时自己生成临时表，分别是deleted和inserted,Inserted表就是放新的记录，Delete表就是放旧的记录，当你插入时，要插入的记录是新的，所以可以在Insert表中找到。当你删除时，要删除的记录是旧的，所以可以在Deleted表中找到。例如借书记录表中的借阅触发器，插入书号，读者编号，借阅数量时，插入新的内容就在inserted表中5 .创建、修改、删除触发器的语法。CreateTRIGGERtrigger_nameONtable|viewWITHENCRYPTIO