★ FEATURED ARTICLE 简介这份文档是面向 Oracle 数据库学习者的图书管理系统数据库设计完整方案适合高校数据库课程设计、毕业设计或自学参考。文档从系统分析入手依次展开需求分析、设计目标与项目规划并系统介绍数据库概念结构设计、逻辑结构设计与物理结构设计再落到表空间、数据表、视图、序列、索引、存储过程和触发器的创建管理以及数据查询、更新、合并等访问操作几乎覆盖数据库设计全流程。资源仅包含1个doc文件共319KB文件虽小但目录结构完整从系统分析到数据库访问共四章可作为数据库课程设计的模板与实验参考。目前已有281人学习下载适合需要快速理解 Oracle 数据库建模与实现步骤的初学者。文档整体逻辑清晰尤其适合作为 Oracle 课程设计的起步参考。1. Oracle图书管理系统数据库设计与实现这不是一张表是一套数据契约oracle图书管理系统数据库设计与实现这六个字放进需求文档里是一句话落到你桌面上就是一套数据契约。它解决的不是“Oracle能不能跑图书馆业务”而是借书、还书、续借、逾期、统计这些动作如何对应到表结构、约束、索引、存储过程和分页查询里。适合正在做课程设计、要给别人交付设计文档、或者接手老系统准备重构的工程师。设计文档里值钱的不是ER图那张图而是每个字段的长度、每个约束的边界、每条SQL在特定数据量下能不能走对执行计划。2. 先建模再建库从ER图到Oracle数据字典的落地映射设计文档的第一步永远是实体关系模型。很多教程画完ER图直接开写CREATE TABLE中间跳过了两件事实体之间的约束关系怎么落到数据库对象上以及设计完怎么回头看Oracle数据字典验证一致性。这两件事不做文档就是墙上的画库里是另一套。2.1 核心实体与关系读者、图书、借阅三张主表不能省图书管理系统再复杂主干也是三张业务表读者表、图书表、借阅记录表。读者和图书之间是多对多关系一个读者可以借多本书一本书可以被不同读者在多个时间点借出所以借阅记录不能直接挂在图书表的某个字段下它必须是一张独立的关系实体表。常见字段设计是这样的实体核心字段约束/说明读者表reader_id, reader_no, name, user_type, statusreader_no 唯一user_type 区分学生/教师图书表book_id, isbn, title, author, category_id, stockisbn 不唯一同书多名副本所以要 book_id借阅记录borrow_id, reader_id, book_id, borrow_date, due_date, return_date外键指向读者和图书状态字段控制生命周期最容易犯的错是把“读者当前借了哪本书”直接存成读者表的一个字段比如 reader.current_book_id。这样做的代价是续借和还书时要去改读者表一次借三本书就没法建模更谈不上查历史记录。借阅记录表一定只存一次借阅动作的开始和结束而不是“现状”。设计文档里还应写明业务规则借阅周期是30天还是15天能否续借逾期每天罚多少。这些规则最后要落成CHECK约束或存储过程里的判断不能只写在文档的“需求分析”段落里。Oracle做这件事的优势是约束和事务都由数据库兜住应用层偶然漏掉一次判断数据也不会错。2.2 用数据字典反向校验设计USER_TABLES 和 USER_CONSTRAINTS 的用途设计文档写得再整齐实际库里有没有照着建要靠Oracle数据字典来验证。我们常说“Oracle数据库”和“数据库设计”之间的桥梁就是USER_TABLES、USER_TAB_COLUMNS、USER_CONSTRAINTS这些视图。每次交付前我都会跑下面这段SQL把文档里的表名单和执行结果对一遍-- 查看当前用户下所有业务表num_rows 是统计信息里的行数不代表实时数据 SELECT table_name, num_rows, last_analyzed FROM user_tables ORDER BY table_name; -- 查看约束名、类型、状态重点看 R 外键是否生效 SELECT constraint_name, constraint_type, table_name, status, r_constraint_name FROM user_constraints WHERE table_name IN (READER, BOOK, BORROW_RECORD) ORDER BY table_name, constraint_name;说明一下第二段SQL里的constraint_typeP代表主键R代表外键U代表唯一键C代表CHECK约束。设计文档里写了“借阅记录必须引用存在的读者”库里就对应到BORROW_RECORD上的外键约束状态必须是ENABLED。如果实际库里没有这个外键哪怕是程序一直正常验收时DBA看一眼数据字典就翻车了。另一个实用习惯是给表加注释和字段注释。Oracle里的COMMENT ON TABLE和COMMENT ON COLUMN会把说明写进数据字典查询时通过USER_TAB_COMMENTS、USER_COL_COMMENTS就能看到。文档能丢数据字典不会丢。2.3 主键方案序列加触发器还是直接用 Oracle 12c 标识列主键生成是图书管理系统设计里最常见的分叉路。Oracle 12c以前标准做法是序列加触发器常见于很多老项目。12c以后可以用GENERATED BY DEFAULT AS IDENTITYOracle 19c单实例也一样支持。两种我都用过建议按目标库版本选。-- 方式一Oracle 12c 起支持建表直接声明标识列 CREATE TABLE book ( book_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, isbn VARCHAR2(20) NOT NULL, title VARCHAR2(200) NOT NULL, author VARCHAR2(100), category_id NUMBER(4), stock NUMBER(6) DEFAULT 1, created_time DATE DEFAULT SYSDATE ); -- 方式二老库兼容用序列 触发器 CREATE SEQUENCE seq_book_id START WITH 1 INCREMENT BY 1 NOCACHE; CREATE OR REPLACE TRIGGER tri_book_id BEFORE INSERT ON book FOR EACH ROW WHEN (NEW.book_id IS NULL) BEGIN :NEW.book_id : seq_book_id.NEXTVAL; END; /需要注意标识列和序列生成的数字都可能有间隙。比如事务回滚或触发器执行失败序列已经取走了一个值不会回填。所以业务规则上不能把book_id当成“第几本书”的编号展示给读者它只是内部主键。对外编号应该用独立的book_no字段。我更推荐在目标版本是Oracle 19c或更高时直接使用IDENTITY列代码更短少一个触发器。但如果你交付的文档里写了“兼容11g”那老老实实保留序列和触发器。设计文档必须把版本选择写明确不能写“使用Oracle数据库”就完事。3. 从设计文档到可运行库建表语句、初始化数据与权限隔离设计文档的验收现场不是看有没有一张ER图而是看能不能在干净的环境里按文档步骤把库建出来。真正可落地的设计文档建表语句必须能复制执行初始化数据必须能重复跑权限必须和业务账号分离。3.1 建表语句怎么写才算过得了 DBA 的眼DBA看建表语句最在意三件事字段类型是否合理约束是否完整是否预留了扩展空间。很多入门项目把图书价格用VARCHAR2存把借阅状态用中文“未还”/“已还”存我在评审时都会打回。Oracle里应该用NUMBER存数值用CHAR(1)存状态码再用CHECK约束把取值范围锁死。-- 读者表 CREATE TABLE reader ( reader_id NUMBER(8) NOT NULL, reader_no VARCHAR2(20) NOT NULL, name VARCHAR2(50) NOT NULL, phone VARCHAR2(20), email VARCHAR2(100), user_type CHAR(1) DEFAULT 0 NOT NULL, -- 0学生 1教师 status CHAR(1) DEFAULT 1 NOT NULL, -- 1正常 0冻结 created_time DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT pk_reader PRIMARY KEY (reader_id), CONSTRAINT uk_reader_no UNIQUE (reader_no), CONSTRAINT ck_reader_status CHECK (status IN (0, 1)), CONSTRAINT ck_reader_type CHECK (user_type IN (0, 1)) ); -- 借阅记录表 CREATE TABLE borrow_record ( borrow_id NUMBER(10) NOT NULL, reader_id NUMBER(8) NOT NULL, book_id NUMBER(8) NOT NULL, borrow_date DATE DEFAULT SYSDATE NOT NULL, due_date DATE NOT NULL, return_date DATE, renew_count NUMBER(2) DEFAULT 0 NOT NULL, status CHAR(1) DEFAULT B NOT NULL, -- B借阅中 R已归还 CONSTRAINT pk_borrow PRIMARY KEY (borrow_id), CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader(reader_id), CONSTRAINT fk_borrow_book FOREIGN KEY (book_id) REFERENCES book(book_id), CONSTRAINT ck_borrow_status CHECK (status IN (B, R)) );这里的VARCHAR2长度不是随便定的。phone给20位是要容纳手机号前面带国家码VARCHAR2单位是字符不是字节所以中文字段也放得下。status列用CHAR(1)而不是NUMBER是为了后面代码里读写一眼能看出含义也避免状态码和数字主键混淆。due_date是借出时算出来的应还日期不依赖应用层临时计算。借阅表上的外键是必须的。有人为了插入性能去掉外键结果是应用层代码私自插入一条不存在的reader_id查报表时关联出空值还得回来补脏数据。这个教训我见过不止一次。3.2 初始化数据管理员账号、图书分类、测试数据怎么造设计文档一般会留一章“系统初始数据”。常见做法是建一张图书分类字典表再放几个管理员账号。管理员账号不要和读者表混在一起业务上两者权限不同硬塞进同一张表会让角色控制非常别扭。-- 图书分类字典 CREATE TABLE book_category ( category_id NUMBER(4) NOT NULL, category_name VARCHAR2(100) NOT NULL, parent_id NUMBER(4), CONSTRAINT pk_book_category PRIMARY KEY (category_id) ); -- 管理员表 CREATE TABLE sys_user ( user_id NUMBER(8) NOT NULL, login_name VARCHAR2(50) NOT NULL, password VARCHAR2(200) NOT NULL, -- 存哈希值不存明文 user_name VARCHAR2(50) NOT NULL, status CHAR(1) DEFAULT 1 NOT NULL, CONSTRAINT pk_sys_user PRIMARY KEY (user_id), CONSTRAINT uk_sys_user_login UNIQUE (login_name) );初始化数据时我最烦的是脚本不能重复执行。第一次跑成功第二次跑报主键冲突。所以初始化脚本里我习惯用MERGE而不是裸INSERT。MERGE的意思是“存在就更新不存在就插入”。比如初始分类MERGE INTO book_category t USING (SELECT 1 AS category_id, 文学 AS category_name FROM dual) s ON (t.category_id s.category_id) WHEN NOT MATCHED THEN INSERT (category_id, category_name) VALUES (s.category_id, s.category_name);这个写法看着啰嗦但在演示环境反复初始化时就是后悔药。测试数据也一样造读者、造图书、造借阅记录脚本跑三遍都不会重复。Oracle的dual表在这里派上大用场它保证每一条MERGE只针对一条初始记录。3.3 权限与同义词业务账号和 DBA 账号的边界很多课程设计图省事全程用system或者sys建表。这样做在单机练习环境没问题一旦要交付到真实项目等保和审计会直接拒绝。业务应用应该连一个只拥有业务表权限的账号而不是sys。-- 创建业务管理账号 CREATE USER library_mgr IDENTIFIED BY 你的复杂密码; GRANT CONNECT, RESOURCE TO library_mgr; GRANT UNLIMITED TABLESPACE TO library_mgr; -- 创建只读报表账号 CREATE USER library_report IDENTIFIED BY 报表账号密码; GRANT CONNECT TO library_report; GRANT SELECT ON library_mgr.reader TO library_report; GRANT SELECT ON library_mgr.book TO library_report; GRANT SELECT ON library_mgr.borrow_record TO library_report;如果不想让应用侧记住“library_mgr.book”这种带模式名的写法可以建同义词。Oracle里同义词就是给对象起一个短别名应用连接后能直接用BOOK访问。CREATE SYNONYM app_reader FOR library_mgr.reader; CREATE SYNONYM app_book FOR library_mgr.book;权限隔离的意义不只是安全。它还能让你在账号密码泄露时快速定位影响范围不需要把所有表都暴露给同一个连接。设计文档里单独写一节“权限矩阵”是很加分的部分。4. 图书管理系统的Oracle查询与存储过程分页、逾期、统计三件套设计文档除了建表还要回答“功能怎么实现”。图书管理系统里最高频的功能就是查询借阅记录、处理还书、统计报表对应到Oracle里就是分页、存储过程和常用函数。这部分写得越具体后面开发越省事。4.1 分页查询ROWNUM陷阱与标准写法图书列表、借阅记录列表都要分页。很多入门的Oracle写法是这样先ORDER BY再ROWNUM结果发现前几页正常越翻越乱。原因是ROWNUM在排序之前就给结果集编号了直接加ROWNUM条件会先截断再排序。稳妥的分页写法是用ROW_NUMBER窗口函数生成行号外层再过滤。-- 任意 Oracle 版本可用的分页写法 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.borrow_date DESC) AS rn FROM borrow_record t WHERE t.status B ) WHERE rn BETWEEN 1 AND 20;这里第一次查询先按借出日期倒序再给每行生成从1开始的序号外层截取第1到20行。页码变了BETWEEN后面的两个数字就跟着变。Oracle 12c以上可以用OFFSET FETCH比如OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY但为了兼容旧库我一般保留ROW_NUMBER写法。分页查询还有一个隐藏问题如果借阅记录表达到几十万行排序字段必须有索引否则翻到后面的页码会明显变慢。这个坑在下一章单独说。4.2 存储过程还书业务和逾期罚款别让应用层算还书不是一个UPDATE就能完成的。它要改借阅记录状态、算应还日期和实还日期的差值、可能生成罚款。这些逻辑放在存储过程里比放在PHP、Java或者任何应用代码里都更安全。因为数据库事务边界清晰一个存储过程就是一个原子操作。CREATE OR REPLACE PROCEDURE proc_return_book ( p_borrow_id IN NUMBER, p_operator IN VARCHAR2, p_fine OUT NUMBER ) IS v_due_date DATE; v_overdue_days NUMBER; BEGIN SELECT due_date INTO v_due_date FROM borrow_record WHERE borrow_id p_borrow_id AND status B FOR UPDATE; UPDATE borrow_record SET return_date SYSDATE, status R, operator p_operator WHERE borrow_id p_borrow_id; v_overdue_days : TRUNC(SYSDATE) - TRUNC(v_due_date); IF v_overdue_days 0 THEN p_fine : v_overdue_days * 0.1; ELSE p_fine : 0; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, 借阅记录不存在或已归还); END proc_return_book; /说明几点。FOR UPDATE是给这条借阅记录加锁防止两个人同时点击还书。TRUNC(SYSDATE)把时间归零只按天数算不会因为“多还了几个小时”多罚一天钱。p_fine是OUT参数应用层拿到它展示“本次还书产生罚款”。Oracle存储过程不难写难在参数命名和事务提交位置。我的习惯是存储过程里只做业务不塞打印日志日志另写一张表。4.3 统计报表借阅排行、类别分布和常用函数设计文档最后总要配上几个统计场景热门图书排行、读者借阅次数、逾期清单。这里用到的Oracle函数并不多翻来覆去就是TRUNC、TO_CHAR、RANK、NVL、DECODE这几个。网上搜“oracle函数大全及举例”很容易看花眼但图书管理系统里真正需要手写的是带窗口函数的聚合查询。-- 热门图书排行按借阅次数排名取前10 SELECT * FROM ( SELECT b.book_id, b.title, COUNT(*) AS borrow_count, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_no FROM borrow_record br JOIN book b ON b.book_id br.book_id WHERE br.status R GROUP BY b.book_id, b.title ) WHERE rank_no 10;RANK函数允许并列名次比如第三名有两本书下个名次是第五名这符合大多数榜单预期。如果业务要求不许并列就换ROW_NUMBER。GROUP BY后面列必须和SELECT里的非聚合列一致这在Oracle里报错很直接看ORA-00979就能定位。再比如统计每个月的借阅量按TO_CHAR(borrow_date, YYYY-MM)分组即可SELECT TO_CHAR(borrow_date, YYYY-MM) AS borrow_month, COUNT(*) AS total_count FROM borrow_record GROUP BY TO_CHAR(borrow_date, YYYY-MM) ORDER BY borrow_month;到了这一步设计文档就不再只是“表结构说明书”而是把查询逻辑也定下来了。5. Oracle图书管理系统避坑与排查从字符集、监听到分页慢查询每个Oracle项目都有几个固定坑位图书管理系统也不例外。下面这几条都是实操中容易踩的每条我都按“现象→原因→解决”写方便你直接对照。5.1 字符集不一致导致中文乱码现象PL/SQL Developer里中文显示正常Java应用插入后查出来是问号或者反过来客户端看着是乱码库里其实是对的。原因数据库字符集、客户端NLS_LANG、应用连接字符集三者不一致。最常见的是库用了AL32UTF8客户端还是ZHS16GBK或者服务端环境变量没导入。解决先确认数据库字符集再用同一套字符集配置客户端。SELECT USERENV(language) FROM dual;输出类似SIMPLIFIED CHINESE_CHINA.AL32UTF8。如果确认库是AL32UTF8Linux客户端就设置export NLS_LANGAMERICAN_AMERICA.AL32UTF8Windows客户端也要在系统环境变量里改成一致。建库前最好就把字符集定成AL32UTF8换库不是小事。5.2 监听出问题连接时快时慢甚至ORA-12541现象sqlplus登录Oracle数据库出现缓慢或者直接报ORA-12541、ORA-12537监听服务无法启动按system用户登录没有反应。原因Oracle监听依赖主机名解析。服务器hostname改过了/etc/hosts里没有对应条目或者监听端口被防火墙挡住。还有一种常见原因是listener.log已经涨到几个G监听日志写入慢连接自然跟着慢。解决按先后顺序执行下面三件事。# 检查监听状态 lsnrctl status # 看监听日志是否异常 tail -n 200 $ORACLE_HOME/network/log/listener.log # hostname 对应关系梳理 cat /etc/hosts如果hostname变了把127.0.0.1 主机名写进/etc/hosts再用lsnrctl reload。如果日志过大可以定期清理或者在监听配置里打开日志轮转。这个排查顺序能覆盖八成连接问题。5.3 借阅记录分页越翻越慢执行计划走了全表扫描现象第一页秒开翻到100页要等好几秒EXPLAIN PLAN发现sort order by和table access by rowid后面跟着全表扫描。原因分页主子段是borrow_date但没有对应索引。每次翻页都要把所有符合条件的记录抓出来排序再截取。数据量只有一万条时感觉不明显十万条以后体感明显。解决给排序和过滤条件建复合索引。CREATE INDEX idx_borrow_status_date ON borrow_record (status, borrow_date DESC);status列做前缀是因为WHERE里先按状态过滤再把borrow_date倒序拿出来排序。图书管理系统里“正在借阅的记录”通常只占少部分这个索引在还书和分页场景都能用上。建完索引立刻看执行计划EXPLAIN PLAN FOR SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.borrow_date DESC) rn FROM borrow_record t WHERE t.status B ) WHERE rn BETWEEN 1 AND 20; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);执行计划里出现INDEX RANGE SCAN就说明索引生效了。5.4 触发器里做太多事批量插入性能被拖垮现象单条插入正常用PL/SQL批量插入一万条图书数据特别慢像卡住一样。原因每一行触发序列触发器触发器里还有多余的SELECT查询和DBMS_OUTPUT行级触发器被放大了一万倍。解决行级触发器只做必要赋值不要查表不要打印输出。如果要批量初始化测试数据关掉DBMS_OUTPUT或者直接放弃触发器用IDENTITY列。-- 初始化大批量测试数据时先关掉会话输出 SET SERVEROUTPUT OFF; DECLARE TYPE t_book_id IS TABLE OF NUMBER; v_ids t_book_id; BEGIN SELECT book_id BULK COLLECT INTO v_ids FROM book; -- 这里只是演示批量业务过程尽量用数组操作 END; /口诀是触发器的代码越短越好长业务放存储过程。6. 收尾给“数据库设计与实现.doc”加一份能说服验收的验证清单设计文档写到最后我会单独放一节“验证清单”不是空话而是能在新库上重复执行的检查脚本。这样验收方不用肉眼找跑一遍SQL就能确认设计落地了。验证清单至少包含四类检查表是否存在、约束是否启用、基础数据是否完整、关键查询是否能在合理时间返回。下面这段SQL是我常用的开场SELECT READER AS table_name, COUNT(*) AS cnt FROM reader UNION ALL SELECT BOOK, COUNT(*) FROM book UNION ALL SELECT BORROW_RECORD, COUNT(*) FROM borrow_record;如果三张表都是0行说明初始化脚本没跑。如果借阅记录有值但读者表没有说明外键可能没生效回到USER_CONSTRAINTS查。最后一件事是给连接账号做一次最小权限确认。用报表账号登录试着执行UPDATE和DELETE应该报权限不足。能用SELECT查到业务数据但改不了这才是交付状态。我自己的习惯是把这个验证清单和建表脚本放在同一个目录命名成verify.sql交付时一起给。因为口头说“库没问题”没人信脚本能跑出结果才有说服力。希望这个思路帮你在下一个Oracle图书管理系统项目里少翻几次车。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
