图书管理系统数据库设计报告实例

图书管理系统

数据库设计报告

文件状态： [√] 草稿 [ ] 正式发布 [ ] 正在修改

文件标识： LMS-DATABASE 当前版本： 1.0 作 者： XXX 完成日期： 2011-12-24 项目名称，《数据库设计报告》

版 本 历 史

版本/状态

作者 参与者 起止日期 备注

? k7yeqs 2011

Page 2 of 32

项目名称，《数据库设计报告》

目 录

0. 文档介绍 ............................................................................................................................ 4 0.1 文档目的 ....................................................................................................................... 4 0.2 文档范围 ....................................................................................................................... 4 0.3 读者对象 ....................................................................................................................... 4 0.4 参考文献 ....................................................................................................................... 4 0.5 术语与缩写解释 ............................................................................................................ 4 1. 数据库环境说明 ................................................................................................................. 4 2. 数据库的命名规则 ............................................................................................................. 5 3.概念设计 .............................................................................................................................. 6 4. 逻辑设计 ............................................................................................................................ 6 5.物理模型 .............................................................................................................................. 8 5.0 表汇总 ........................................................................................................................... 8 5.1 表USERS ........................................................................................................................ 9 5.2 表USERROLE ................................................................................................................. 9 5.3 表USERRIGHTS ............................................................................................................ 10 5.4 表OVERLIMITRECORD ................................................................................................. 10 5.5 表LOSTRECORD........................................................................................................... 10 5.6 表ORDERRECORD ........................................................................................................ 11 5.7 表BORROWRECORD ..................................................................................................... 11 5.8 表BOOK ....................................................................................................................... 11 5.9 表LIABRARY ................................................................................................................ 12 5.10 表BOOKCATEGORY .................................................................................................... 12 5.11 表BOOKKIND ............................................................................................................ 13 5.12表SYSTEMLOG ........................................................................................................... 13 6. 安全性设计 ....................................................................................................................... 13 6.1 防止用户直接操作数据库的方法 .............................................................................. 13 6.2 用户帐号密码的加密方法 .......................................................................................... 14 6.3 角色与权限 ................................................................................................................. 14 7. 优化 .................................................................................................................................. 14 8. 数据库管理与维护说明 ................................................................................................... 15 8.1数据库日常检查 .......................................................................................................... 15 8.2 数据库备份方案 .......................................................................................................... 15 9. 数据库对象 ................................................................................................................... 16

? k7yeqs 2011

Page 3 of 32

项目名称，《数据库设计报告》

0. 文档介绍

0.1 文档目的

本文档为图书管理系统的数据库设计文档，主要介绍系统的数据库设计，给出了系统的逻辑设计和物理设计，同时给出了数据库环境的说明，确定了数据库命名规则，给出了数据库的优化方案、安全性设计方案和数据库维护、管理等内容，本文档可作为项目开发人员在数据库实现时的参考和规范。

0.2 文档范围

本文档适用于项目开发的设计阶段，在项目开发阶段可按照本文档检验数据库的实施情况。 0.3 读者对象

1.图书管理系统的开发人员 2.指导老师

3.图书管理系统的测试人员 4.图书管理系统的客户

0.4 参考文献

【01】王珊 萨师煊，《数据库系统概论》，高等教育出版社，2006-05-04

0.5 术语与缩写解释

缩写、术语 SPP SD 解 释 精简并行过程，Simplified Parallel Process 系统设计，System Design 把一种计划、规划、设想通过视觉的形式通过概念、判断、推理、论证来理解和区分客观世界的思维传达出来的活动过程 物理设计 设计数据库的物理结构，根据数据库的逻辑结构来选定RDBMS，并设计和实施数据库的存储结构、存取方式等。 逻辑设计

1. 数据库环境说明

数据库系统：MySQL

数据库设计工具：Power Designer

Page 4 of 32

? k7yeqs 2011

项目名称，《数据库设计报告》

编程工具：Power Designer 详细配置： 2. 数据库的命名规则

2.1数据库涉及字符规则

采用26个英文字母（区分大小写）和0 －9这十个自然数，加上下划线_组成，共63个字符。不能出现其他字符（注释除外）。 2.2数据库对象命名规则

数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约束。对象名字由前缀和实际名字组成，长度不超过30。

前缀：使用小写字母。如： 表 视图 存储过程 函数 实际名字

实际名字尽量描述实体的内容，由单词或单词组合，每个单词的首字母大写，其他字母小写，不以数字和_开头。因此，合法的对象名字类似如下。

表 视图 存储过程 2.3数据库表命名规则

字段由前缀和实际名字组成。实际名字中首单词一个系统尽量采取同一单词。 前缀：使用小写字母tb，表示表。

例如：tbMember tbMember_Info t bForum_Board tbForum_Thread1 2.4字段命名规则

数字、字符、日期/时间、lob(大对象)、杂项，字段由表的简称、下划线，实际名字加后缀组成。

后缀：使用小写字母，代表该字段的属性。

例如: User_Idint User_Namestr User_RegDatedtm 2.5视图命名规则

字段由前缀和实际名字组成，中间用下划线连接。 前缀：使用小写字母vi，表示视图。 例如：vi_User vi_UserInfo 2.6存储过程命名规则

字段由前缀和实际名字组成，中间用下划线连接。 前缀：使用小写字母sp，表示存储过程。 例如：sp_User 2.7 SQL语句规则

? k7yeqs 2011

Page 5 of 32

tb vi sp fn tbUser_Info、tbMessage_Detail vi_MessageList sp_MessageAdd

项目名称，《数据库设计报告》

所有SQL语句的关键词全部大写或首字母大写，比如SELECT,UPDATE,FROM, ORDER,BY等。

3.概念设计

4. 逻辑设计

? k7yeqs 2011

Page 6 of 32

项目名称，《数据库设计报告》

? k7yeqs 2011

Page 7 of 32

项目名称，《数据库设计报告》

5.物理模型

5.0 表汇总

表名 Users UserRole UserRights OverLimitRecord LostRecord OrderRecord BorrowRecord Book Library ? k7yeqs 2011

功能说明 记录用户的基本信息，包括姓名、性别、单位、联系方式等 记录用户的角色，不同的角色拥有不同的权限 记录图书管理系统中用户可拥有的所有权限 记录超期的图书借阅记录 记录图书遗失的记录 记录读者预约图书的情况 记录读者借阅图书的情况 记录图书的基本信息，包括书名、出版社、页数、是否可借阅等 记录图书馆分馆的基本情况，包括分馆地址、电话等信息 Page 8 of 32

项目名称，《数据库设计报告》

BookCategory BookKinds SystemLog 记录图书的类别及相关属性，划分不同类别可方便用户借阅查找 记录图书的种类及相关属性，不同种类的书籍有不同的赔付价格 记录系统的运行情况 5.1 表Users

表名 列名 UserID UserName UserNum UserSex UserRole Users 数据类型 Int nvchar int 空/非空 非空 非空 非空 主键 无 无 只能是\男\或是\女\只能为“用户角色”表中已有项的角色编号 UserUnit UserEmail UserPhone UserPsw nvchar nvchar 可以为空 可以为空 无 格式为“XX@XX.XX” 位数为11位，全部字符为数字 长度至少为6，最多为12位的数字、字母的组合 UserIsBorrow int 非空 无 用户是否可借阅书籍 UserBorroeCnt int 非空 无 用户当前可借阅的书籍数量 补充说明 1.UserRole为外键，关联着用户角色表中的角色编号 2.UserIsBorrow的初始值为1，当UserBorrowCnt的值为0时其值为0，当用户出现超期事务未处理时，UserIsBorrow的值为-1，出现遗失事务未处理时其值-2，当同时有UserBorrowCnt为0和超期事务未处理时其值为-3，当同时有UserBorrowCnt为0和遗失事务未处理时其值为-4，当同时有遗失和超期事务未处理时其值为-5，只有当其值为1时表示当前可借，否则当前不可借阅。 用户所在单位 用户邮箱 用户联系电话 用户登录密码 约束条件 表示含义 用户编号 用户姓名 用户学号 用户性别 用户所属角色 nvchar（2） 可以为空 int 非空 nvchar（11） 可以为空 nvchar（12） 非空 5.2 表UserRole

表名 列名 RoleID RoleName RoleDescribe RoleBorrowCnt RoleBorrowDays UserRole 数据类型 int nvchar nvchar int int 空/非空 非空 非空 可以为空 非空 非空 主键 最大为256个字符 无 无 无 约束条件 表示含义 角色编号 角色名称 角色描述 可借阅书籍的最大数量 可借阅书籍的最大天数 ? k7yeqs 2011

Page 9 of 32

项目名称，《数据库设计报告》

RoleBorrowTimes int 非空 非空 无 无 可续借的次数 续借一次可多借阅书籍的天数 RoleContinueDays int RoleRights int 非空 只能为权限表中已有项的编号 角色所拥有的权限 补充说明 RoleRights为外键，关联着权限表中的权限编号 5.3 表UserRights

表名 列名 RightsId RightsName RightsDescribe 补充说明 无 UserRights 数据类型 int nvchar nvchar 空/非空 非空 非空 可以为空 约束条件 主键 最大为256个字符 无 表示含义 权限编号 权限名称 权限描述 5.4 表OverLimitRecord

表名 列名 OLRID OLRUserID OLRBookID OverDays IsFine OverCost 补充说明 OverLimitRecord 数据类型 int int int int boolean int 空/非空 非空 非空 非空 非空 非空 非空 约束条件 主键 无 无 无 无 无 表示含义 超期记录编号 超期借阅书籍的读者编号 超期被借阅的书籍编号 超期天数 是否已经缴纳超期罚款的标识 超期应缴纳的罚款金额 OLRUserID和OLRBookID为外键，分别关联用户表中的用户编号和图书表中的图书编号 5.5 表LostRecord

表名 列名 LostRecID LostRecUserID LostRecDate LostIsFine LostReBuy 补充说明 ? k7yeqs 2011

LostRecord 数据类型 int int date boolean boolean 空/非空 非空 非空 非空 非空 非空 非空 约束条件 主键 无 无 无 无 无 表示含义 书籍遗失记录编号 遗失的书籍编号 遗失书籍的用户编号 书籍遗失事务等级日期 是否已经缴纳遗失书籍赔付款 是否已经重新购入该书籍 LostRecBookID int LostRecUserID和LostRecBookID为外键，分别关联用户表中的用户Page 10 of 32

项目名称，《数据库设计报告》

编号和图书表中的图书编号 5.6 表OrderRecord

表名 列名 OrderRecID OrderRecBookID OrderRecUserID OrderRecDate OrderIsFinish 补充说明 OrderRecord 数据类型 int int int date boolean 空/非空 非空 非空 非空 非空 非空 约束条件 表示含义 主键 无 无 无 无 预约图书记录的编号 被预约图书的编号 预约图书的用户编号 预约图书的日期 预约活动是否完成 OrderRecUserID和OrderRecBookID为外键，分别关联用户表中的用户编号和图书表中的图书编号 5.7 表BorrowRecord

表名 列名 BRD BRUserID BRBookID BRDate BRIsBack BRBackDate BRIsContinue BRIsOVerLimit 补充说明 OrderRecord 数据类型 int int int date boolean date boolean boolean 空/非空 非空 非空 非空 非空 非空 非空 非空 非空 非空 约束条件 表示含义 主键 无 无 无 无 无 无 无 无 借阅书籍记录的编号 借阅书籍的用户的编号 被借阅的书籍的编号 借阅书籍的时间 是否已经归还书籍的标识位 用户应该归还书籍的最大日期 用户是否可以续借书籍 用户还可以续借该书籍的次数 借阅的书籍是否已经超期 若书籍超期所应缴纳的超期款 BRContinueTimes int BROverLimitFine vnchar 可以为空 无 OrderRecUserID和OrderRecBookID为外键，分别关联用户表中的用户编号和图书表中的图书编号 5.8 表Book

表名 列名 BookID BookName BookAuthor ISBNID ? k7yeqs 2011

Book 数据类型 int nvchar nvchar nvchar 空/非空 非空 非空 非空 非空 主键 最大长度为256个字符 最大长度为256个字符 最大长度为256个字符 书籍编号 书籍名称 书籍的作者名 书籍的ISBN码 Page 11 of 32

约束条件 表示含义 项目名称，《数据库设计报告》

Publish PublishPlace PublishDate TotalWords TotalPage BookPrice BookAbstruct BookInDate BookLibrary IsBorrow BookKind 补充说明 nvchar nvchar date int int nvchar nvchar date int boolean int 非空 非空 非空 非空 非空 非空 非空 非空 非空 非空 非空 非空 最大长度为256个字符 最大长度为256个字符 无 无 无 无 无 无 无 无 无 无 书籍的出版社名 书籍的出版地 书籍的出版时间 书籍的总字数 书籍的总页数 书籍的定价 书籍的内容简介 书籍的类别 书籍的入库时间 书籍所在的图书分馆 标识书籍当前是否可借 书籍的种类 BookCategory int 1.BookCategory为外键，关联着图书类别表中的类别编号 2.BookKind为外键，关联这图书种类表中的种类编号 3.图书类别表示图书的内容的类别，如哲学类，文学类，设置图书类别图书可以方便用户进行查找和定位，图书的种类表示书籍的种类，如新书、珍藏版书籍、外文书、中文书，设置图书种类是可能不同种类的书籍在超期、遗失时的赔付额度不一样，如1980年前的书属于珍藏版的书，遗失后应该按定价的5倍赔付等 4.当书籍当前已经被借出或者已经遗失了，则该书籍当前不可借阅 5.书籍作者的最大长度设为256是因为可能一本书的作者为多个，名字的总长度较大 6.ISBN码为图书的分类编码，同样的书籍的ISBN码应该一样 5.9 表Liabrary

表名 列名 LiabraryID LibNam LibPhone LibAddress LibAbstruct 补充说明 Liabrary 数据类型 int nvchar nvchar nvchar nvchar 空/非空 非空 非空 非空 非空 非空 主键 最大长度为256个字符 最大长度为11 最大长度为256个字符 无 图书分馆编号 图书分馆名称 图书分馆的联系电话 图书分馆的地址 图书分馆的简介 约束条件 表示含义 一个图书管理系统可能同时管理位于不同位置的几个图书分馆，所以需要设置图书分馆表，一本图书也应该属于某一个图书分馆 5.10 表BookCategory

表名 ? k7yeqs 2011

BookCategory Page 12 of 32

项目名称，《数据库设计报告》

列名 CategoryID CategoryName 数据类型 int nvchar 空/非空 约束条件 非空 非空 非空 主键 最大长度为256个字符 无 表示含义 图书类别编号 图书类别名称 类别的简介 CategoryDescribe nvchar 补充说明 图书的类别是指图书内容的分类，如哲学类、历史类、计算机类等，将图书按类别归类可方便用户的查找和借阅 5.11 表BookKind

表名 列名 KindID KindName KindDescribe LostCost 补充说明 BookKind 数据类型 int nvchar nvchar nvchar 空/非空 非空 非空 非空 非空 约束条件 主键 表示含义 图书种类编号 图书种类的简介 图书超期的罚金额度 图书遗失的罚金额度 最大长度为256个字符 图书种类名称 无 无 可以为空 无 OverLimitCost nvchar 图书可能会被划分为新书、珍藏版书籍、外文书、中文书等种类，根据种类的不同书籍的超期、遗失的赔付金额也不同，如外文书遗失需要赔付两倍定价的金额，而一般的中文书只需按定价赔付等，所以需要划分不同的书籍种类 5.12表SystemLog

表名 列名 LogID LogContent LogTime IsSysRight 补充说明 SystemLog 数据类型 int nvchar boolean 是很重要的 空/非空 非空 非空 非空 约束条件 主键 无 无 无 表示含义 系统日志编号 系统日志内容 系统日志产生的时间 系统是否正常 date&time 非空 系统日志记录可监测系统是运行是否正确，这对于维护和恢复数据库6. 安全性设计

6.1 防止用户直接操作数据库的方法

1.用户权限控制

给每个用户角色限制权限，不同的角色拥有不同的权限，用户只能行使自己权限范

? k7yeqs 2011

Page 13 of 32

项目名称，《数据库设计报告》

围内的权限，如普通用户只能查看自己的借阅记录不能任意修改，而管理员和超级管理员则可以修改用户的借阅记录，通过角色，权限等的一系列授予和回收操作可以有效的进行用户的权限控制，从而防止用户恶意操作、篡改数据库信息。

2.数据库登录设密

给数据库设置登录账号和密码，只有拥有直接访问数据库权限的用户在键入正确的账号和密码后才能通过软件登录到数据库进行操作，可防止用户恶意操作数据库。

3.限制操作系统存取权限

使用Oracle数据库系统是依存在操作系统之上的，如果操作系统被人侵入，那么通过修改配置文件等一系列方法，Oracle数据库的安全性也将荡然无存，所以对于安全性高的数据库，可以通过限制操作系统的存取权限来提高数据库的安全性。

6.2 用户帐号密码的加密方法

使用MD5加密方法对用户账号密码进行加密。

MD5方法采用单项加密的加密方法，即只能对数据进行加密，而不能对加密后的数据进行解密，这样经过MD5算法处理的账号和秘密存储在数据库中即使被人恶意获取到，也不可能知道密码的具体数据，即做到了对账号密码的更强的保护。

6.3 角色与权限

角色 超级管理员 管理员 可以访问的表与列 所有表及列 Users、UserRole、OverLimitRecord、LostRecord、BorrowRecord、Book、Library、BookCategory、BookKinds UserRights、SystemLog 普通用户 Users OverLimitRecord、LostRecord、查看 查看、修改、删除、增加信息 查看 增删改查 增删改查 操作权限 BorrowRecord、Book、Library 7. 优化

优先级 高 优化对象（目标） 数据库索引 措施 根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量 高 高 高 数据库碎片 数据库缓存 数据库对象 定期整理数据库产生的碎片，提高数据库的性能 优化数据库缓存方案 优化数据库对象的放置策略，尽量让数据库对象均匀地把数据分布在系统的磁盘中，平衡I/O访问，避免I/O瓶颈。 ? k7yeqs 2011

Page 14 of 32

项目名称，《数据库设计报告》

8. 数据库管理与维护说明 8.1数据库日常检查

1.检查数据库状态：确认数据库是否在open状态，正常提供服务。 SQL> select status, instance_role from v$instance; 2.数据库版本检查：数据库应该打了最新的patchset。 SQL>select * from v$version;

3.查看SGA区域：SGA区各项buffer之和应该小于系统物理内存的一半。 SQL>show sga；

4.回滚段竞争检查：如果是oracle8i版本返回值应该低于2%。如果该值过高，8i需要手工建立更多的回滚段，如果是9i,返回值应该为0，如果持续存在非零值，应该增加回滚表空间的大小。

8i： SQL> select 'The average of waits/gets is '||round((sum(waits) / sum(gets)) * 100,2)||'%'From v$rollstat;

9i： SQL>select distinct nospaceerrcnt as nospace From v$undostat Where begin_time>sysdate-(1/12);

5.查看日志文件：数据库运行时，可以通过查询v$log_history来观察log switch的切换时间。联机日志最好是业务非高峰期每小时切换一次，业务高峰期20分钟左右切换一次。

SQL>select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') change_time from v$log_history;

6.查看表空间：查看各个表空间所占的大小，剩余空间，使用空间百分比等信息，特别是当业务表空间的剩余空间低于50M或者使用空间百分比大于90%时，需要考虑增加表空间的大小。

SQL> select a.tablespace_name,a.bytes totalbytes,sum(b.bytes) freebytes from dba_data_files a,dba_free_space b where a.file_id = b.file_id group by a.tablespace_name, a.bytes ;

7.检查job状态：Broken列应该为N；如果Broken列为Y，检查oracle告警日志,分析job失败的原因。解决后运行：exec dbms_job.run(:job);

SQL>selectjob,what,to_char(next_date,yyyy/mm/ddhh24:mi:ss’),broken,failures from dba_jobs;

8.查看数据库连接用户：查看当前数据库连接的用户进程的具体信息，包括连接的数据库用户名，机器名，运行的程序名，进程状态。

select username,machine,program,status from v$session where osuser <> 'oracle';

8.2 数据库备份方案

添加任务计划，定期定时运行备份批处理命令，实现自动备份。

设计一个物理备份和逻辑备份相结合的数据库备份方案，以保证数据的完整性和0

? k7yeqs 2011

Page 15 of 32

项目名称，《数据库设计报告》

损失恢复。

逻辑备份：

星期一 完全备份 在00:00时备份 星期二 增量备份 在00:00时备份 星期三 增量备份 在00:00时备份 星期四 增量备份 在00:00时备份 星期五 累积备份 在00:00时备份 星期六 增量备份 在00:00时备份 星期日 增量备份 在00:00时备份

分析：每天在00：00时备份是因为在这个时段的访问量相对很少，星期一完全备份可以将数据库中的信息全部备份，而之后的几天采用增量备份可以减少备份量，加快备份速度。在在星期五选择累计备份，是考虑到数据库出错时恢复的考虑，当数据库在某一周有错误时，可以直接恢复到星期五的数据，而不用一天一天的按增量备份的数据恢复。

物理备份： 备份方法 本地热备 本地冷备 备份频率 1次/天 1次/一月 备份时间 00:00 12:00 备份目的 保留最近一周的备份数据 保留最近一周的备份数据 备注 每周星期五下午 保留数据库全部数据 异地导出备份 1次/天 9. 数据库对象 序列 序列名称 序列描述 序列实现 Users_seq 该序列用以完成用户编号的自增 CREATE SEQUENCE Users_seq INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE NOCACHE; 序列名称 序列描述 序列实现 UserRole_seq 创建序列，用以完成用户角色编号的自增 CREATE SEQUENCE UserRole_seq INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE NOCACHE; 序列名称 ? k7yeqs 2011

UserRight_seq Page 16 of 32

项目名称，《数据库设计报告》

序列描述 序列实现 创建序列，用以完成用户权限编号的自增 CREATE SEQUENCE UserRight_seq INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE NOCACHE; 存储过程 存储过程名称 存储过程描述 InsertToUsers 向用户表中插入新的用户记录的存储过程 参数说明 依次表示：用户姓名,学号、性别、所属角色编号、单位、邮箱和电话 最后一个参数为插入结果的返回值，成功时返回1，否则返回出错代码 存储过程实现 CREATE or REPLACE PROCEDURE InsertToUsers( ItemName in varchar, ItemNum in integer, ItemSex in varchar, ItemRole in integer, ItemUnit in varchar, ItemEmail in varchar, ItemTel in varchar, ItemPsw in varchar, ItemIsBorrow in integer, ItemBorrowCnt in integer, InsertRes out integer )is BEGIN SAVEPOINT SP1; INSERT INTO Users (UserID,UserName,UserNum,UserSex, UserRole,UserUnit,UserEmail,UserTel,UserPsw,UserIsBorrow,UserBorrowCnt )VALUES ( Users_seq, ItemName, ItemNum, ItemSex, ItemRole, ItemUnit, ItemEmail, ItemTel, ItemPsw,ItemIsBorrow, ItemBorrowCnt ); InsertRes := 1; commit; EXCEPTION when DUP_VAL_ON_INDEX then InsertRes := SQLCODE; When OTHERS then InsertRes := SQLCODE; ? k7yeqs 2011

Page 17 of 32

项目名称，《数据库设计报告》

ROLLBACK TO SAVEPOINT SP1; END; 存储过程名称 存储过程描述 DelOnUsers 删除用户表记录 参数说明： 依次表示：用户姓名、学号、性别、所属角色编号、单位、邮箱和电话、密码 当不需要依据某一项来选择删除的用户时，如果为字符类型则传入NULL，如果为数字类型则传入-1如删除编号为1的用户时调用函数为DelOnUsers(1,NULL,-1,NULL,-1,NULL,NULL,NULL); 最后一个参数为删除记录的执行结果成功时返回1否则返回出错代码 存储过程实现 CREATE or REPLACE PROCEDURE DelOnUsers( ItemID in integer, ItemName in varchar, ItemNum in integer, ItemSex in varchar, ItemRole in integer, ItemUnit in varchar, ItemEmail in varchar, ItemTel in varchar, ItemPsw in varchar, ItemIsBorrow in integer, ItemBorrowCnt in integer, DelRes out integer )is tmpCondition varchar(500); BEGIN SAVEPOINT SP1; tmpCondition := NULL; ---如果需要根据用户的编号删除，则只需获取用户的编号，而不用再看用户的其他属性，因为用户编号是唯一 IF ItemID <> -1 THEN tmpCondition := tmpCondition || ItemID; ----如果是根据用户的其他属性删除时 ELSE IF ItemName IS NOT NULL THEN tmpCondition := tmpCondition ||'UserName = '||ItemName; END IF; IF ItemNum <> -1 THEN ? k7yeqs 2011

Page 18 of 32

项目名称，《数据库设计报告》

IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserNum = '||ItemNum; ELSE tmpCondition := tmpCondition || ' ADD UserNum = '||ItemNum; END IF; END IF; IF ItemSex IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserSex = '||ItemSex; ELSE tmpCondition := tmpCondition || ' ADD UserSex = '||ItemSex; END IF; END IF; IF ItemRole <> -1 THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserRole = '||ItemRole; ELSE tmpCondition := tmpCondition || ' ADD UserRole = '||ItemRole; END IF; END IF; IF ItemUnit IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserUnit = '||ItemUnit; ELSE tmpCondition := tmpCondition || ' ADD UserUnit = '||ItemUnit; END IF; END IF; IF ItemEmail IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserEmail = '||ItemEmail; ELSE tmpCondition := tmpCondition || ' ADD UserEmail = '||ItemEmail; END IF; END IF; IF ItemTel IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserTel = '||ItemTel; ELSE tmpCondition := tmpCondition || ' ADD UserTel = '||ItemTel; ? k7yeqs 2011

Page 19 of 32

项目名称，《数据库设计报告》

END IF; END IF; IF ItemPsw IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserPsw = '||ItemPsw; ELSE tmpCondition := tmpCondition || ' ADD UserPsw = '||ItemPsw; END IF; END IF; IF ItemPsw <> -1 THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserIsBorrow = '||ItemIsBorrow; ELSE tmpCondition := tmpCondition || ' ADD UserIsBorrow = '||ItemIsBorrow; END IF; END IF; IF ItemPsw <> -1 THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || 'UserBorrowCnt = '||ItemBorrowCnt; ELSE tmpCondition := tmpCondition || ' ADD UserBorrowCnt = '||ItemBorrowCnt; END IF; END IF; END IF; --执行删除语句 tmpCondition := 'DELETE FROM Users WHERE '||tmpCondition; execute immediate tmpCondition; DelRes := 1; commit; EXCEPTION When NO_DATA_FOUND then DelRes := SQLERRM; When OTHERS then DelRes := SQLERRM; ROLLBACK TO SAVEPOINT SP1; END; 存储过程名称 存储过程描述 UpdateOnUsers 更新记录项的存储过程 参数说明 ? k7yeqs 2011

Page 20 of 32

项目名称，《数据库设计报告》

参数一：需要更新的记录的查找条件，数组记录一次对应用户姓名、学号、性别、所属角色编号、单位、邮箱和电话、密码 如果不需要某一项作为查找条件时，该项对应位置传入NULL 参数二：记录需要更新的数据项，数组记录依次对应用户姓名、学号、性别、所属角色编号、单位、邮箱和电话、密码 如果不需要更新某一项的值时，该项对应位置传入NULL 最后一个参数为更新结果的返回值，成功时返回1，否则返回出错代码 存储过程实现 --创建一个数组类型，用以存储需要修改的记录的值 CREATE or replace TYPE Users_Arr AS varray(11) of varchar2(500); CREATE or REPLACE PROCEDURE UpdateOnUsers( ConditionArr in Users_Arr, ValueArr in Users_Arr, UpdateRes out integer)IS tmpCondition varchar(500); tmpValue varchar(500); BEGIN SAVEPOINT SP1; tmpCondition := NULL; tmpValue := NULL; IF ConditionArr(0) IS NOT NULL THEN tmpCondition := tmpCondition||' UserID = '||ConditionArr(0); END IF; IF ValueArr(0) IS NOT NULL THEN tmpValue := tmpValue||' UserID = '||ValueArr(0); END IF; IF ConditionArr(1) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserName = '||ConditionArr(1); ELSE tmpCondition := tmpCondition||' AND UserName = '||ConditionArr(1); END IF; END IF; IF ValueArr(1) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserName = '||ValueArr(1); ELSE tmpValue := tmpValue||', UserName = '||ValueArr(1); END IF; END IF; ? k7yeqs 2011

Page 21 of 32

项目名称，《数据库设计报告》

IF ConditionArr(2) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserNum = '||ConditionArr(2); ELSE tmpCondition := tmpCondition||' AND UserNum = '||ConditionArr(2); END IF; END IF; IF ValueArr(2) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserNum = '||ValueArr(2); ELSE tmpValue := tmpValue||', UserNum = '||ValueArr(2); END IF; END IF; IF ConditionArr(3) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserSex = '||ConditionArr(3); ELSE tmpCondition := tmpCondition||' AND UserSex = '||ConditionArr(3); END IF; END IF; IF ValueArr(3) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserRole = '||ValueArr(3); ELSE tmpValue := tmpValue||', UserRole = '||ValueArr(3); END IF; END IF; IF ConditionArr(4) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserRole = '||ConditionArr(4); ELSE tmpCondition := tmpCondition||' AND UserRole = '||ConditionArr(4); END IF; END IF; IF ValueArr(4) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserRole = '||ValueArr(4); ELSE ? k7yeqs 2011

Page 22 of 32

项目名称，《数据库设计报告》

tmpValue := tmpValue||', UserRole = '||ValueArr(4); END IF; END IF; IF ConditionArr(5) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserUnit = '||ConditionArr(5); ELSE tmpCondition := tmpCondition||' AND UserUnit = '||ConditionArr(5); END IF; END IF; IF ValueArr(5) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserUnit = '||ValueArr(5); ELSE tmpValue := tmpValue||', UserUnit = '||ValueArr(5); END IF; END IF; IF ConditionArr(6) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserEmail = '||ConditionArr(6); ELSE tmpCondition := tmpCondition||' AND UserEmail = '||ConditionArr(6); END IF; END IF; IF ValueArr(6) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserEmail = '||ValueArr(6); ELSE tmpValue := tmpValue||', UserEmail = '||ValueArr(6); END IF; END IF; IF ConditionArr(7) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserTel = '||ConditionArr(7); ELSE tmpCondition := tmpCondition||' AND UserTel = '||ConditionArr(7); END IF; END IF; IF ValueArr(7) IS NOT NULL THEN ? k7yeqs 2011

Page 23 of 32

项目名称，《数据库设计报告》

IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserTel = '||ValueArr(7); ELSE tmpValue := tmpValue||', UserTel = '||ValueArr(7); END IF; END IF; IF ConditionArr(8) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserPsw = '||ConditionArr(8); ELSE tmpCondition := tmpCondition||' AND UserPsw = '||ConditionArr(8); END IF; END IF; IF ValueArr(8) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserPsw = '||ValueArr(8); ELSE tmpValue := tmpValue||', UserPsw = '||ValueArr(8); END IF; END IF; IF ConditionArr(9) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserIsBorrow = '||ConditionArr(9); ELSE tmpCondition := tmpCondition||' AND UserIsBorrow = '||ConditionArr(9); END IF; END IF; IF ValueArr(9) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserIsBorrow = '||ValueArr(9); ELSE tmpValue := tmpValue||', UserIsBorrow = '||ValueArr(9); END IF; END IF; IF ConditionArr(10) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition||' UserBorrowCnt = '||ConditionArr(10); ELSE tmpCondition := tmpCondition||' AND UserBorrowCnt = '||ConditionArr(10); ? k7yeqs 2011

Page 24 of 32

项目名称，《数据库设计报告》

END IF; END IF; IF ValueArr(10) IS NOT NULL THEN IF tmpValue IS NOT NULL THEN tmpValue := tmpValue||' UserBorrowCnt = '||ValueArr(10); ELSE tmpValue := tmpValue||', UserBorrowCnt = '||ValueArr(10); END IF; END IF; strValues := 'UPDATE Users SET '||strValues||'WHERE '||strCondition; execute immediate strValues; UpdateRes := 1; commit; EXCEPTION WHEN NO_DATA_FOUND THEN UpdateRes := SQLCODE; WHEN OTHERS THEN UpdateRes := SQLCODE; ROLLBACK TO SAVEPOINT SP1; END; 存储过程名称 存储过程描述 SearchOnUsers 查找记录项的存储过程 参数说明 参数一：需要更新的记录的查找条件，数组记录一次对应用户姓名、学号、性别、所属角色编号、单位、邮箱和电话、密码 如果不需要某一项作为查找条件时，该项对应位置传入NULL 参数二：查找返回的结果数组，数组记录依次对应用户姓名、学号、性别、所属角色编号、单位、邮箱和电话、密码 最后一个参数为查找结果的返回值，成功时返回1，否则返回出错代码 ? k7yeqs 2011

Page 25 of 32

项目名称，《数据库设计报告》

存储过程实现 CREATE or REPLACE PROCEDURE SearchOnUsers( ConditionArr in Users_Arr, ValueArr out Users_Arr, SearchRes out integer)IS tmpCondition varchar(500); BEGIN SAVEPOINT SP1; tmpCondition := NULL; IF ConditionArr(0) IS NOT NULL THEN tmpCondition := tmpCondition || ' UserID = '||ConditionArr(0); END IF; IF ConditionArr(1) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserName = '||ConditionArr(1); ELSE tmpCondition := tmpCondition || ' ADD UserName = '||ConditionArr(1); END IF; END IF; IF ConditionArr(2) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserNum = '||ConditionArr(2); ELSE tmpCondition := tmpCondition || ' ADD UserNum = '||ConditionArr(2); END IF; END IF; IF ConditionArr(3) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserSex = '||ConditionArr(3); ELSE tmpCondition := tmpCondition || ' ADD UserSex = '||ConditionArr(3); END IF; END IF; IF ConditionArr(4) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserRole = '||ConditionArr(4); ELSE tmpCondition := tmpCondition || ' ADD UserRole = '||ConditionArr(4); END IF; END IF; ? k7yeqs 2011

Page 26 of 32

项目名称，《数据库设计报告》

IF ConditionArr(5) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserUnit = '||ConditionArr(5); ELSE tmpCondition := tmpCondition || ' ADD UserUnit = '||ConditionArr(5); END IF; END IF; IF ConditionArr(6) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserEmail = '||ConditionArr(6); ELSE tmpCondition := tmpCondition || ' ADD UserEmail = '||ConditionArr(6); END IF; END IF; IF ConditionArr(7) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserTel = '||ConditionArr(7); ELSE tmpCondition := tmpCondition || ' ADD UserTel = '||ConditionArr(7); END IF; END IF; IF ConditionArr(8) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserPsw = '||ConditionArr(8); ELSE tmpCondition := tmpCondition || ' ADD UserPsw = '||ConditionArr(8); END IF; END IF; IF ConditionArr(9) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserIsBorrow = '||ConditionArr(9); ELSE tmpCondition := tmpCondition || ' ADD UserIsBorrow = '||ConditionArr(9); END IF; END IF; IF ConditionArr(10) IS NOT NULL THEN IF tmpCondition IS NOT NULL THEN tmpCondition := tmpCondition || ' UserBorrowCnt = '||ConditionArr(10); ELSE ? k7yeqs 2011

Page 27 of 32

项目名称，《数据库设计报告》

tmpCondition := tmpCondition || ' ADD UserBorrowCnt = '||ConditionArr(10); END IF; END IF; tmpCondition := 'SELECT UserID INTO ValueArr(0), UserName INTO ValueArr(1), UserNum INTO ValueArr(2),UserSex INTO ValueArr(3),UserRole INTO ValueArr(4),UserUnit INTO ValueArr(5), UserEmail INTO ValueArr(6),UserTel INTO ValueArr(7), UserPsw INTO ValueArr(8),UserIsBorrow INTO ValueArr(9),UserBorrowCnt INTO ValueArr(10) FROM Users WHERE '|| tmpCondition; execute immediate tmpCondition; SearchRes := 1; commit; EXCEPTION WHEN NO_DATA_FOUND THEN SearchRes := SQLCODE; WHEN OTHERS THEN SearchRes := SQLCODE; ROLLBACK TO SAVEPOINT SP1; END; 存储过程名称 存储过程描述 存储过程实现 InsertToUserRole --创建存储过程 --插入用户角色记录 CREATE or REPLACE PROCEDURE InsertToUserRole( ItemName in varchar, ItemDescribe in varchar, BorrowCnt in integer, BorrowDays in integer, ContinueCnt in integer, ContinueDays in integer, InsertRes out integer )is BEGIN SAVEPOINT SP1; INSERT TO UserRole (RoleID,RoleName,RoleDescribe, RoleBorrowCnt,RoleBrorowDays,RoleBorrowTimes,RoleContinueDays,RoleRight )VALUES (UserRole_seq, ItemName, ItemDescribe, BorrowCnt, BorrowDays, ContinueCnt, ContinueDays); InsertRes := 1; commit; ? k7yeqs 2011

Page 28 of 32

项目名称，《数据库设计报告》

EXCEPTION when DUP_VAL_ON_INDEX then InsertRes := SQLCODE; When OTHERS then InsertRes := SQLCODE; ROLLBACK TO SAVEPOINT SP1; END 存储过程名称 存储过程描述 存储过程实现 InsertToUserRole --创建存储过程，插入用户权限的记录 CREATE or REPLACE PROCEDURE InsertToUserRight( ItemName in varchar, ItemDescribe in varchar, InsertRes out integer )is BEGIN SAVEPOINT SP1; INSERT TO UserRight (RightID, RightName, RightDescribe) VALUES (UserRight_seq, ItemName, ItemDescribe); InsertRes := 1; commit; EXCEPTION when DUP_VAL_ON_INDEX then InsertRes := SQLCODE; When OTHERS then InsertRes := SQLCODE; ROLLBACK TO SAVEPOINT SP1; END 触发器 触发器名称 触发器描述 触发器实现 delUsersRole --在删除角色前将属于该角色的用户的角色设置为NULL CREATE or REPLACE TRIGGER delUsersRole BEFORE DELETE on UserRole for each row BEGIN UPDATE Users SET UserRole = NULL WHERE UserRole = :old.RoleID; END; 触发器名称 触发器描述 AddBorrowRecord --增加一条借阅记录时将该用户的可借阅书籍数量减一，如果减一后可借阅数量为0则置是否可借标记为false --同时将借阅的书籍状态更新为不可借阅状态 ? k7yeqs 2011

Page 29 of 32

项目名称，《数据库设计报告》

触发器实现 CREATE or REPLACE TRIGGER AddBorrowRecord AFTER INSERT On BorrowRecord for each row BEGIN tmpBorrowCnt integer; SELECT UserBorrowCnt INTO tmpBorrowCnt FROM Users WHERE UserID = :new.BRUserID; tmpBorrowCnt = tmpBorrowCnt - 1; UPDATE Users SET UserBorrowCnt = tmpBorrowCnt WHERE UserID = :new.BRUserID; IF tmpBorrowCnt = 0 THEN UPDATE Users SET UserIsBorrow = false WHERE UserID = :new.BRUserID; UPDATE Book SET IsBorrow = false WHERE BookId = :new.BRBookID; END; 触发器名称 触发器描述 触发器实现 UpdateBR --修改借阅记录时，判断借阅的书籍是否已经归还，如果已经归还则将对应用户的借阅书籍量加一 CREATE or REPLACE TRIGGER UpdateBR AFTER UPDATE OverLimitRecord for each row BEGIN tmpIsBorrow integer; tmpIsFine boolean; SELECT IsFine INTO tmpIsFine FROM OverLimitRecord WHERE OLRUserID = :new.OLRUserID; IF tmpIsFine = true THEN SELECT UserIsBorrow INTO tmpIsBorrow FROM Users WHERE UserID = :new.OLRUserID; IF tmpIsBorrow = -1 THEN UPDATE Users SET IsBorrow = 1 WHERE UserID = :new.OLRUserID; ELSE IF tmpIsBorrow = -3 THEN UPDATE Users SET IsBorrow = 0 WHERE UserID = :new.OLRUserID; ELSE IF tmpIsBorrow = -5 THEN UPDATE Users SET IsBorrow = -2 WHERE UserID = :new.OLRUserID; END IF; END IF; END; 触发器名称 触发器描述 触发器实现 ? k7yeqs 2011

AddOVerLimitRecord 增加一条图书超期记录,则将超期读者是否可借阅标识为置为不可借阅 CREATE or REPLACE TRIGGER AddOVerLimitRecord AFTER INSERT INTO Page 30 of 32

项目名称，《数据库设计报告》

OverLimitRecord for each row BEGIN tmpIsBorrow integer; SELECT UserIsBorrow INTO tmpIsBorrow FROM Users WHERE UserID = :new.BRUserID; IF tmpIsBorrow = 0 THEN UPDATE Users SET IsBorrow = -3 WHERE UserID = :new.BRUserID; ELSE IF tmpIsBorrow = -1 THEN UPDATE Users SET IsBorrow = -5 WHERE UserID = :new.BRUserID; END IF; END; 触发器名称 触发器描述 触发器实现 AddOLostRecord 增加一条图书遗失记录，则将遗失书籍的读者的是否可借阅标识为置为不可借阅 CREATE or REPLACE PROCEDURE AddOLostRecord AFTER INSERT INTO LostRecord for each row BEGIN tmpIsBorrow integer; SELECT UserIsBorrow INTO tmpIsBorrow FROM Users WHERE UserID = :new.LostRecUserID; IF tmpIsBorrow = 0 THEN UPDATE Users SET IsBorrow = -4 WHERE UserID = :new.LostRecUserID; ELSE IF tmpIsBorrow = -1 THEN UPDATE Users SET IsBorrow = -5 WHERE UserID = :new.LostRecUserID; END IF; END; 视图 视图名称 视图描述 视图实现 User_vw --查看用户基本信息的视图 CREATE or REPLACE NOFORCE VIEW User_vw( name, sex, unit, email, tel ) AS SELECT UserName, UserSex, UserUnit, UserEmai, UserTel FROM Users WITH READ ONLY; 视图名称 视图描述 视图实现 视图名称 ? k7yeqs 2011

Log_vw --查询系统日志记录 CREATE or REPLACE NOFORCE VIEW Log_vw( LogID, LogDescribe, LogTime ) AS SELECT LogID, LogDescribe, LogTime FROM SystemLog WITH READ ONLY; UserBorrowRecord_vw Page 31 of 32

项目名称，《数据库设计报告》

视图描述 视图实现 查看用户的简单借阅记录 CREATE or REPLACE VIEW UserBorrowRecord_vw( UserName, BookName, BorrowDate, IsReturn ) AS SELECT UserName, BookName, BRBorrowDate, BRIsReturn FROM Users, Book, BorrowRecord WHERE Users.UserID = BorrowRecord.UserID AND Book.BookID = BorrowRecord.BookID WITH READ ONLY; 视图名称 视图描述 视图实现 UserOverLimitRecord_vw --查看用户的简单超期记录 CREATE or REPLACE VIEW UserOverLimitRecord_vw( UserName, BookName, OverLimitDate, IsFine, FineCost ) AS SELECT UserName, BookName, OverLimitDate, OLRIsFine, OLRFineCost FROM Users, Book, OverLimitRecord WHERE Users.UserID = OverLimitRecord.UserID AND Book.BookID = OverLimitRecord.BookID WITH READ ONLY; 视图名称 视图描述 视图实现 UserOverOrderRecord_vw --查看用户的预订记录 CREATE or REPLACE VIEW UserOverOrderRecord_vw( UserName, BookName, OrderDate, IsFinish ) AS SELECT UserName, BookName, OrderDate, OrderIsFinish FROM Users, Book, OrderRecord WHERE Users.UserID = OrderRecord.UserID AND Book.BookID = OrderRecord.BookID WITH READ ONLY;

? k7yeqs 2011

Page 32 of 32

本文来源：https://www.bwwdw.com/article/zyto.html