简介这份Oracle数据库课程设计文档面向学习数据库管理与开发的学生及IT从业者以“学生考勤系统”为案例串联需求分析、角色权限、请假与考勤记录、E-R模型及表结构设计等环节可用于课程作业参考、项目实训或答辩准备。压缩包内共1个doc文件约227KB正文按背景分析、用户与功能需求、系统模块划分、数据字典、数据库表逻辑结构、表空间与表创建、心得体会及参考文献展开便于快速把握Oracle课程设计的报告结构与实现脉络。该资料已有967人学习适合需要参考完整设计流程、补充建表与数据库对象创建案例或梳理考勤类系统数据建模思路的读者。1. 一份 Oracle 数据库课程设计能跑出什么学生考勤系统里 12 张表和 3 个数据库对象把一份 Oracle 数据库课程设计报告里的建表语句原样粘进 SQL*Plus十条有九条会当场报错——这是我拿到这份学生考勤系统设计稿时的第一反应。datetime不是 Oracle 的数据类型char(5) foreign key references这种连写也不算合法语法还有几张表的主外键长度根本对不上。但把这几处硬伤刨掉底下露出来的东西比预期完整六大类用户、三大功能模块、12 张业务表外加一个出勤统计存储过程、一个院系隔离视图、一个缺勤预警触发器从需求分析到 E-R 再到物理落地是一条闭合的链。它适合两类人正在做数据库课设、需要一个能照着从零搭起来的学生以及想找一套干净的教务类库表结构来改的后端开发。2. 从六类用户到 12 张表需求分析怎么落成一条外键链2.1 六类角色切成三大模块边界划在哪里这份设计的角色划分是它最值钱的部分。学生、任课老师、班主任、院系领导、学校领导、系统管理员六类人对应六种数据可见范围而不是六种登录界面皮肤。需求描述里有两处细节很关键一是学生请假超过三天要跳到院系领导那一级二是任课老师录入考勤时系统自动根据请假系统决定最终结果。翻译成数据语言就是——请假表和考勤表之间存在一次业务上的交叉引用考勤的旷课判定要先查同一时间段有没有已批准的请假单。三张模块边界可以这样切模块主要落表写入方读取方请假系统qingjia学生提交、班主任/院系领导改状态全体按范围裁剪考勤系统kaoqin_record任课老师学生本人、班主任、院系、学校后台管理admin/faculty/major/classes/course/teacher等基础表系统管理员全系统边界切清楚以后谁能看什么就不用写死在代码里而是变成视图和 WHERE 条件的事。比如学校领导看全校就是不加院系过滤院系领导看本院就是stu_faculty :dept_id。这一点在后面的视图章节会展开。2.2 E-R 图里每一条边最后都变成了一个外键报告里的 E-R 图有 12 个实体关系边看上去很密实际拆解下来只有三类第一类是组织层级链学院 → 专业 → 学生。这条链是严格的树major.major_faculty指向faculty.faculty_idstudent.stu_major指向major.major_id。第二类是班级链班主任 → 班级 → 学生。classes.classtea_no指向classteacher.classtea_nostudent.stu_class指向classes.class_no。第三类是业务链教师 课程 学生 → 考勤记录学生 → 请假单 → 两级审批人。问题出在第一类和第二类的交汇处。逻辑结构设计里student表同时存了stu_class、stu_major、stu_faculty三个字段而这三个值是递推关系——班级能推专业专业能推学院。这在教科书上是典型的传递依赖第三范式不满足。放在课程设计里问题不大因为查询方便、少一次 JOIN但要做成生产系统学生转专业、学院合并的时候你要同时改三张表加一张学生表漏一处就数据打架。提示课设阶段保留冗余字段是可以接受的取舍但要清楚代价。想改规范把student.stu_major和student.stu_faculty删掉查询时从classes → classteacher → major → faculty一路 JOIN 上去。2.3 数据字典到字段约束把「描述」翻译成 check、not null 和默认值数据字典那一段看着像走过场其实是整个报告里最该被认真读的部分。它把每个实体的属性列成编号 姓名 性别 所属学院这种自然语言描述物理设计要做的就是把描述逐条翻译成列定义。以请假条为例字典里的定义是 14 个属性请假代号、班级代号、学生学号、请假原因、开始时间、结束时间、请假天数、申请请假时间、班主任审批状态、班主任审批时间、院系领导审批状态、院系领导代号、院系领导审批时间。翻译成约束有这么几条必须落到 DDL 上请假原因NOT NULL长度给到VARCHAR2(200)写短了学生填两句就超起止时间都NOT NULL并且加一条CHECK (end_time start_time)报告里没写但少了这条后面统计天数全是负数请假天数NOT NULL且CHECK (day_number 0 AND day_number 31)需求里明确写了最长时间不能超过 1 个月两级审批状态用CHAR(1)存0/1/2配CHECK约束。报告里用CHAR(10)存状态浪费空间还容易存进空格。这里有个常被忽略的点状态码用字符还是数字。用CHAR(1)的好处是导出报表、拼 SQL 时不用转换坏处是前端传0和0会踩坑。我一般用NUMBER(1)加CHECK因为 Oracle 里0和0隐式转换虽然能过但索引和分区裁剪时会有隐式类型转换的隐患。3. 表空间与建表脚本12 张表的落地顺序和类型修正3.1 先建表空间存储参数不是抄的报告里给的建表空间语句是这样的create tablespace linpeng_data datafile /u01/oracle/oradata/tab01.dbf size 100M default storage(initial 512K next 128K minextents 2 maxextents 999 pctincrease 0) online;这段在字典管理表空间的年代是对的放到 10g 以后基本属于考古。DEFAULT STORAGE子句在本地管理表空间LOCAL下会被直接忽略PCTINCREASE更是早就废弃的参数。更现实的问题是/u01/oracle/oradata/这个路径在你自己机器上八成不存在直接执行会报 ORA-01119。我一般改写成这样-- 建一个独立表空间避免业务表和系统表抢空间 CREATE TABLESPACE t_kq_data DATAFILE /u01/app/oracle/oradata/ORCL/kq_data01.dbf SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 4G -- 自动扩但要设上限防止撑爆磁盘 EXTENT MANAGEMENT LOCAL AUTOALLOCATE -- 本地管理区大小由 Oracle 自动分配 SEGMENT SPACE MANAGEMENT AUTO; -- 段空间自动管理减少碎片参数逐个说SIZE 200M是初始大小按 12 张表加索引的数据量200M 绰绰有余AUTOEXTEND ON NEXT 100M MAXSIZE 4G表示不够时每次扩 100M最多长到 4G写死上限是为了防止一条死循环插入把磁盘写满EXTENT MANAGEMENT LOCAL是让位图管理区分配比数据字典管理快得多也不会产生uet$/fet$的递归争用SEGMENT SPACE MANAGEMENT AUTO用位图管理段内空闲块高并发插入时比手动pctfree/pctused稳。本地练习更省事的做法是压根不建表空间把脚本里所有TABLESPACE linpeng_data替换成TABLESPACE USERS或者整段删掉走默认。因为CREATE TABLESPACE需要 DBA 权限普通开发账号执行会直接 ORA-01031 权限不足。3.2 建表顺序决定了外键能不能过外键是硬约束被引用的列必须已经存在。所以 12 张表不能按报告里的书写顺序1 admin、2 student、3 faculty……执行——student引用classesclasses又在第 11 位第二条就炸。按依赖拓扑排序正确的落地顺序是顺序表名依赖的前置表1faculty无2majorfaculty3classteachermajor, faculty4classesclassteacher5studentclasses, major, faculty6teacherfaculty7collegeleaderfaculty8schoolleader无9admin无10course无11kaoqin_recordstudent, teacher, course12qingjiaclasses, student, classteacher, collegeleader有一个坑要提前说classteacher班主任表和classes班级表是互相引用的——班主任表里其实逻辑上该有个所带班级班级表里又有classtea_no。报告里是单向的班主任表只挂专业和学院班级表挂班主任所以能排开。如果哪天你想加反向引用就得拆成建表 → 加约束两步走用ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY补。3.3 三处必须改的字段类型第一处datetime。Oracle 没有这个类型MySQL 才有。所有datetime一律换成DATE精确到秒或TIMESTAMP精确到小数秒 时区感知。考勤时间sk_time用DATE就够请假起止时间如果跨国校区考虑时区再用TIMESTAMP WITH TIME ZONE。第二处char长度不一致。classes.class_no是CHAR(10)而student.stu_class写成了CHAR(13)faculty.faculty_id是NUMBERstudent.stu_faculty却写成CHAR(40)。Oracle 对参照完整性有硬要求——外键列和被引用列的类型必须兼容、长度必须一致否则 ORA-02267。统一原则是主键什么类型外键就什么类型一个字符都不能差。第三处number拼写。请假表的day_number写成了nubmer这种拼写错误执行时直接 ORA-00904 无效标识符。类似的还有teacher表里tea_faculty NUMBER引用faculty(faculty_id)但classteacher表里同一个含义的列叫classtea_faculty命名风格不统一后期写 JOIN 的时候容易手抖。改完的student表长这样CREATE TABLE student ( stu_no CHAR(10) NOT NULL, -- 学号主键 stu_name VARCHAR2(30) NOT NULL, -- 姓名 stu_sex CHAR(1) NOT NULL, -- 性别用 M/F 存避开中文字节长度问题 stu_class CHAR(10) NOT NULL, -- 所属班级长度必须和 classes.class_no 一致 stu_major NUMBER NOT NULL, -- 所属专业 stu_faculty NUMBER NOT NULL, -- 所属学院 CONSTRAINT pk_student PRIMARY KEY (stu_no), CONSTRAINT ck_stu_sex CHECK (stu_sex IN (M,F)), CONSTRAINT fk_stu_class FOREIGN KEY (stu_class) REFERENCES classes(class_no), CONSTRAINT fk_stu_major FOREIGN KEY (stu_major) REFERENCES major(major_id), CONSTRAINT fk_stu_faculty FOREIGN KEY (stu_faculty) REFERENCES faculty(faculty_id) ) TABLESPACE t_kq_data;把外键统一写成表级CONSTRAINT xxx FOREIGN KEY ... REFERENCES ...好处是约束有名字。出了问题报错信息里带约束名一眼能定位到是哪张表的哪条关系用列级匿名的写法报错只会给你SYS_C0012345这种系统名排查起来很痛苦。4. 数据库端三件套存储过程、视图、触发器的正确写法4.1 出勤统计存储过程参数命名和赋值符号报告里的原始版本是这样create or replace procedure getMessage(stu_no in varchar2, course_no in varchar2, total_times out number) as absence_times number; begin select count(*) into absence_times from kaoqin_record where stu_number stu_no and course_no course_no; total_times absence_times; end;这段有四个问题其中两个是致命的。第一个outnumber缺了空格PL/SQL 会把它当成一个叫outnumber的类型编译直接失败。第二个total_times absence_times是 SQL 的比较运算符PL/SQL 赋值必须用:。第三个最阴——参数名stu_no、course_no和表里的列名同名。PL/SQL 静态 SQL 的名字解析规则是列优先于变量所以where stu_number stu_no会被解析成where stu_number stu_number条件恒真前提是列非空统计出来是整张表的行数。这个坑不报错只给你一个看上去能跑的错误答案属于典型的黑匣子级 bug。血泪经验是存储过程参数统一加p_前缀杜绝重名。第四个语义。它统计的是该学生该课程的全部考勤记录数不是缺勤数。要统计缺勤得按stu_status过滤。改写版CREATE OR REPLACE PROCEDURE sp_absence_stat ( p_stu_no IN VARCHAR2, -- 学生学号 p_course_no IN VARCHAR2, -- 课程编号 p_total OUT NUMBER, -- 该课程累计考勤次数 p_absence OUT NUMBER -- 其中状态为旷课的次数 ) AS BEGIN SELECT COUNT(*), COUNT(CASE WHEN stu_status 旷课 THEN 1 END) -- 条件聚合一次扫描出两个数 INTO p_total, p_absence FROM kaoqin_record WHERE stu_number p_stu_no AND course_no p_course_no; END; /COUNT(CASE WHEN ... THEN 1 END)这个写法值得记住CASE没有ELSE时返回NULLCOUNT不计NULL所以它只数满足条件的行。相比写两条SELECT COUNT(*)一次全表扫描就出结果数据量大的时候差别很明显。参数说明里p_total和p_absence都是OUT模式调用方要先声明变量再接DECLARE v_total NUMBER; v_absence NUMBER; BEGIN sp_absence_stat(2018000001, C0001, v_total, v_absence); DBMS_OUTPUT.PUT_LINE(总考勤 || v_total || 次缺勤 || v_absence || 次); END; /执行前记得SET SERVEROUTPUT ON否则DBMS_OUTPUT的输出一个字都看不到——这是新手最常反馈的存储过程没反应。4.2 院系隔离视图硬编码学院 ID 的代价原始视图是为了让某学院领导只看本院学生create view rjxy as select kaoqin_record.kaoqin_id, kaoqin_record.sk_time, ... from kaoqin_record, student where student.stu_no kaoqin_record.stu_number and student.stu_faculty 5;思路完全正确问题在 5这个魔法数字。一个学院一个视图10 个学院就要建 10 个视图、维护 10 份几乎一样的 SQL。学院 ID 变了得挨个改。合理的做法是建一个不带过滤的基视图把过滤交给调用方-- 基视图只做关联不做权限裁剪 CREATE OR REPLACE VIEW v_kaoqin_detail AS SELECT k.kaoqin_id, k.sk_time, k.stu_number, s.stu_name, k.stu_status, k.teacher_no, k.course_no, c.course_name, s.stu_faculty FROM kaoqin_record k JOIN student s ON s.stu_no k.stu_number JOIN course c ON c.course_no k.course_no;应用层查的时候带上WHERE stu_faculty :dept_id把:dept_id从登录会话里取。这样一套视图服务所有学院新增学院不用动数据库。注意应用层过滤终究是信任客户端的做法。真要做强隔离Oracle 的行级安全VPD / DBMS_RLS才是正解——给视图挂一个策略函数函数里根据SYS_CONTEXT(USERENV,SESSION_USER)返回stu_faculty 当前用户所属学院的谓词无论谁怎么写 SQL 都绕不过去。课设阶段用不上但要知道这条路存在。4.3 缺勤预警触发器绕开 mutating table原始触发器想在插入考勤记录后如果该学生该课程缺勤累计到 3 次就打印警告。行级触发器里查自己所在的表Oracle 会直接抛 ORA-04091: table is mutating。原因是行级触发器执行时表正处于变更中状态Oracle 为了保证读一致性不允许你看到还没定型的中间结果。正确解法是用 11g 引入的复合触发器COMPOUND TRIGGER把逻辑拆成逐行收集 语句结束统一判断-- 先建一张预警记录表把提示落库而不是打印到控制台 CREATE TABLE kaoqin_warning ( warn_id NUMBER PRIMARY KEY, stu_no VARCHAR2(10) NOT NULL, course_no VARCHAR2(13) NOT NULL, warn_time DATE DEFAULT SYSDATE, warn_msg VARCHAR2(200) ) TABLESPACE t_kq_data; CREATE OR REPLACE TRIGGER trg_kaoqin_alert FOR INSERT ON kaoqin_record COMPOUND TRIGGER TYPE t_key IS RECORD (stu_no VARCHAR2(10), course_no VARCHAR2(13)); TYPE t_keys IS TABLE OF t_key INDEX BY PLS_INTEGER; g_keys t_keys; g_idx PLS_INTEGER : 0; -- 第一部分行级只收集数据不查表 AFTER EACH ROW IS BEGIN IF :NEW.stu_status 旷课 THEN g_idx : g_idx 1; g_keys(g_idx).stu_no : :NEW.stu_number; g_keys(g_idx).course_no : :NEW.course_no; END IF; END AFTER EACH ROW; -- 第二部分语句级此时表已定型可以安全查询 AFTER STATEMENT IS v_cnt NUMBER; BEGIN FOR i IN 1 .. g_idx LOOP SELECT COUNT(*) INTO v_cnt FROM kaoqin_record WHERE stu_number g_keys(i).stu_no AND course_no g_keys(i).course_no AND stu_status 旷课; IF v_cnt 3 THEN INSERT INTO kaoqin_warning (warn_id, stu_no, course_no, warn_time, warn_msg) VALUES (seq_warning.NEXTVAL, g_keys(i).stu_no, g_keys(i).course_no, SYSDATE, 该课程旷课累计 || v_cnt || 次已达取消考试资格标准); END IF; END LOOP; g_idx : 0; -- 清理避免同一会话内累积 END AFTER STATEMENT; END trg_kaoqin_alert; /三个关键点。g_keys用关联数组按PLS_INTEGER索引是因为批量插入多行时行级部分会重复触发得用集合暂存。AFTER STATEMENT段里查kaoqin_record是合法的此时语句已经执行完、表状态一致。最后g_idx : 0必须写复合触发器的状态变量在会话内是持续存在的不清空会导致下一次插入时把旧数据重新算一遍。另一处值得改的是把提示落库而不是DBMS_OUTPUT.PUT_LINE。控制台打印只在 SQL*Plus 里看得到应用层调用完全收不到等于白做。真要阻断插入用RAISE_APPLICATION_ERROR(-20001, ...)只做提醒就写表让前端轮询或消息队列去读。5. 踩坑排查这份脚本在你自己机器上必炸的五处5.1 ORA-00902: invalid datatype现象执行建表语句光标停在sk_time datetime not null那一行报数据类型无效。原因DATETIME是 SQL Server 和 MySQL 的类型Oracle 里没有这个类型名。报告里出现了 6 次覆盖考勤表和请假表。解决全部替换为DATE。涉及小数秒的用TIMESTAMP。替换后还要检查应用层的 JDBC 或 OCI 取值方式DATE取出来是java.sql.Timestamp别按String强转。5.2 ORA-02267: column type incompatible with referenced column type现象建student表时报外键类型不兼容但列名看着都对。原因student.stu_class CHAR(13)引用classes.class_no CHAR(10)长度不一致student.stu_faculty CHAR(40)引用faculty.faculty_id NUMBER类型都不一致。Oracle 要求外键列与被引用列类型兼容且长度相同CHAR的定长语义还会把短值补空格比较时更麻烦。解决统一写法主键CHAR(n)外键就CHAR(n)主键NUMBER外键就NUMBER。改完重新执行如果表已存在要先DROP TABLE ... CASCADE CONSTRAINTS。另外提醒一句CHAR存学号、编号这类定长值合适存姓名、备注必须用VARCHAR2CHAR(30)存个三字姓名会补 27 个空格LENGTH和LENGTHB结果对不上。5.3 存储过程跑出来是全表数据现象调用getMessage(2018000001,C0001, v)无论传什么学号返回的次数都是整张考勤表的行数。原因存储过程形参名stu_no、course_no和表列名重名。PL/SQL 静态 SQL 的名字解析优先级是列 变量where stu_number stu_no被解析成自比较条件退化成真。它不报错、不警告是最难查的一类翻车。解决形参统一加前缀p_stu_no/p_course_no。如果不想改参数名可以在过程里加PRAGMA RESTRICT_REFERENCES或者把 SQL 写成动态EXECUTE IMMEDIATE加绑定变量——后者治标不治本还是改名字干净。排查技巧是拿DBMS_OUTPUT把拼好的 WHERE 条件打出来一眼能看出问题。5.4 ORA-04091: table is mutating现象向kaoqin_record插入一条记录触发器报错提示表正在变更触发器看不到。原因AFTER INSERT ... FOR EACH ROW的行级触发器里去SELECT COUNT(*) FROM kaoqin_record读的是正在被修改的表。Oracle 为保护读一致性禁止这种操作。解决用COMPOUND TRIGGER行级只收集主键、语句级再做统计查询写法见 4.3。不想上 11g 的复合触发器另一条路是PRAGMA AUTONOMOUS_TRANSACTION开自治事务——但自治事务看不到未提交的当前行计数会少一得手动1补回来容易算错。还有一条最土的办法把累计校验从触发器挪到应用层插入完成后由 Java 或 Python 端查一次出问题能重试可控性反而更好。5.5 ORA-12899: value too large for column现象插入性别男报值太大超出列长度。原因stu_sex CHAR(2)的2默认按字节算NLS_LENGTH_SEMANTICSBYTE而 UTF-8 下一个汉字占 3 字节CHAR(2)只够放 2 字节直接超长。如果数据库建库时字符集是AL32UTF8现在基本都默认这个这个坑百分之百会踩。解决三条路。一是改列定义CHAR(2 CHAR)明确按字符算二是把长度给到VARCHAR2(6)留足字节余量三是最推荐的做法——性别别存中文用CHAR(1)存M/F或者NUMBER(1)存1/2字典表里映射显示文案。存中文状态值在跨库、跨编码、导出 CSV 时全是不确定性早点换掉省心。6. 二次开发把课程设计改成能演示的原型系统这份设计的骨架够用但要拿去给老师演示或者当毕设起点还差三样东西主键生成、状态机约束、学期维度。先补主键生成。请假表qingjia.id是NUMBER主键插入时没人给它值。11g 及以前用序列加触发器CREATE SEQUENCE seq_qingjia START WITH 1000 INCREMENT BY 1 NOCACHE; -- NOCACHE 避免实例宕机后跳号 CREATE OR REPLACE TRIGGER trg_qingjia_id BEFORE INSERT ON qingjia FOR EACH ROW BEGIN IF :NEW.id IS NULL THEN -- 允许调用方显式指定便于数据迁移 :NEW.id : seq_qingjia.NEXTVAL; END IF; END; /12c 以后可以直接id NUMBER GENERATED BY DEFAULT AS IDENTITY连触发器都省了。判断标准很简单SELECT * FROM v$version看到 12 开头就用 IDENTITY11g 老老实实用序列。再补状态机。原设计把审批状态丢给应用层自由填写数据库端没有任何约束。加一条 CHECK把两级审批的合法值锁死再顺手加一条业务规则——院系领导只在超过三天时才介入ALTER TABLE qingjia ADD CONSTRAINT ck_qj_status CHECK ( class_tea_sp_status IN (0,1,2) -- 0 待审 1 同意 2 驳回 AND coll_leader_sp_status IN (0,1,2) );最值得补的其实是学期维度。原始的kaoqin_record只有sk_timecourse表只有课程编号和名称没有学期、没有开课班级、没有授课教师。按现在的设计你没法回答这学期某门课的出勤率只能靠时间范围硬猜。改动量不大加一张开课表把课表安排落下来就行字段类型说明term_idNUMBER学期编号主键一部分course_noCHAR(13)课程外键class_noCHAR(10)开课班级外键teacher_noCHAR(10)授课教师外键week_day/sectionNUMBER周几、第几节有了这张表考勤记录的teacher_no和course_no就能反查是不是合法开课组合请假单也能按学期汇总。验证方式很土但有效造三条数据——一条正常出勤、一条旷课、一条有已批准请假单的旷课——跑一遍sp_absence_stat比对结果是不是3 / 1请假那条不算缺勤。数字对上了说明统计口径和业务规则是一致的对不上问题一定出在stu_status的枚举值或者视图的过滤条件上。从那以后我每次接手别人的库表脚本都强制先做三件事把所有类型跑一遍DESC确认没有伪类型把外键长度逐列比对一遍再把存储过程和触发器用USER_ERRORS查一遍编译告警。这三步花十分钟能省掉后面两小时的 ORA 代码翻查。这份课程设计报告的价值也在这儿——它足够完整能让你把这三步走完也能让你把一套教务类库表从纸面推到可运行。希望帮到你。本文还有配套的精品资源点击获取
简介这份Oracle数据库课程设计文档面向学习数据库管理与开发的学生及IT从业者以“学生考勤系统”为案例串联需求分析、角色权限、请假与考勤记录、E-R模型及表结构设计等环节可用于课程作业参考、项目实训或答辩准备。压缩包内共1个doc文件约227KB正文按背景分析、用户与功能需求、系统模块划分、数据字典、数据库表逻辑结构、表空间与表创建、心得体会及参考文献展开便于快速把握Oracle课程设计的报告结构与实现脉络。该资料已有967人学习适合需要参考完整设计流程、补充建表与数据库对象创建案例或梳理考勤类系统数据建模思路的读者。1. 一份 Oracle 数据库课程设计能跑出什么学生考勤系统里 12 张表和 3 个数据库对象把一份 Oracle 数据库课程设计报告里的建表语句原样粘进 SQL*Plus十条有九条会当场报错——这是我拿到这份学生考勤系统设计稿时的第一反应。datetime不是 Oracle 的数据类型char(5) foreign key references这种连写也不算合法语法还有几张表的主外键长度根本对不上。但把这几处硬伤刨掉底下露出来的东西比预期完整六大类用户、三大功能模块、12 张业务表外加一个出勤统计存储过程、一个院系隔离视图、一个缺勤预警触发器从需求分析到 E-R 再到物理落地是一条闭合的链。它适合两类人正在做数据库课设、需要一个能照着从零搭起来的学生以及想找一套干净的教务类库表结构来改的后端开发。2. 从六类用户到 12 张表需求分析怎么落成一条外键链2.1 六类角色切成三大模块边界划在哪里这份设计的角色划分是它最值钱的部分。学生、任课老师、班主任、院系领导、学校领导、系统管理员六类人对应六种数据可见范围而不是六种登录界面皮肤。需求描述里有两处细节很关键一是学生请假超过三天要跳到院系领导那一级二是任课老师录入考勤时系统自动根据请假系统决定最终结果。翻译成数据语言就是——请假表和考勤表之间存在一次业务上的交叉引用考勤的旷课判定要先查同一时间段有没有已批准的请假单。三张模块边界可以这样切模块主要落表写入方读取方请假系统qingjia学生提交、班主任/院系领导改状态全体按范围裁剪考勤系统kaoqin_record任课老师学生本人、班主任、院系、学校后台管理admin/faculty/major/classes/course/teacher等基础表系统管理员全系统边界切清楚以后谁能看什么就不用写死在代码里而是变成视图和 WHERE 条件的事。比如学校领导看全校就是不加院系过滤院系领导看本院就是stu_faculty :dept_id。这一点在后面的视图章节会展开。2.2 E-R 图里每一条边最后都变成了一个外键报告里的 E-R 图有 12 个实体关系边看上去很密实际拆解下来只有三类第一类是组织层级链学院 → 专业 → 学生。这条链是严格的树major.major_faculty指向faculty.faculty_idstudent.stu_major指向major.major_id。第二类是班级链班主任 → 班级 → 学生。classes.classtea_no指向classteacher.classtea_nostudent.stu_class指向classes.class_no。第三类是业务链教师 课程 学生 → 考勤记录学生 → 请假单 → 两级审批人。问题出在第一类和第二类的交汇处。逻辑结构设计里student表同时存了stu_class、stu_major、stu_faculty三个字段而这三个值是递推关系——班级能推专业专业能推学院。这在教科书上是典型的传递依赖第三范式不满足。放在课程设计里问题不大因为查询方便、少一次 JOIN但要做成生产系统学生转专业、学院合并的时候你要同时改三张表加一张学生表漏一处就数据打架。提示课设阶段保留冗余字段是可以接受的取舍但要清楚代价。想改规范把student.stu_major和student.stu_faculty删掉查询时从classes → classteacher → major → faculty一路 JOIN 上去。2.3 数据字典到字段约束把「描述」翻译成 check、not null 和默认值数据字典那一段看着像走过场其实是整个报告里最该被认真读的部分。它把每个实体的属性列成编号 姓名 性别 所属学院这种自然语言描述物理设计要做的就是把描述逐条翻译成列定义。以请假条为例字典里的定义是 14 个属性请假代号、班级代号、学生学号、请假原因、开始时间、结束时间、请假天数、申请请假时间、班主任审批状态、班主任审批时间、院系领导审批状态、院系领导代号、院系领导审批时间。翻译成约束有这么几条必须落到 DDL 上请假原因NOT NULL长度给到VARCHAR2(200)写短了学生填两句就超起止时间都NOT NULL并且加一条CHECK (end_time start_time)报告里没写但少了这条后面统计天数全是负数请假天数NOT NULL且CHECK (day_number 0 AND day_number 31)需求里明确写了最长时间不能超过 1 个月两级审批状态用CHAR(1)存0/1/2配CHECK约束。报告里用CHAR(10)存状态浪费空间还容易存进空格。这里有个常被忽略的点状态码用字符还是数字。用CHAR(1)的好处是导出报表、拼 SQL 时不用转换坏处是前端传0和0会踩坑。我一般用NUMBER(1)加CHECK因为 Oracle 里0和0隐式转换虽然能过但索引和分区裁剪时会有隐式类型转换的隐患。3. 表空间与建表脚本12 张表的落地顺序和类型修正3.1 先建表空间存储参数不是抄的报告里给的建表空间语句是这样的create tablespace linpeng_data datafile /u01/oracle/oradata/tab01.dbf size 100M default storage(initial 512K next 128K minextents 2 maxextents 999 pctincrease 0) online;这段在字典管理表空间的年代是对的放到 10g 以后基本属于考古。DEFAULT STORAGE子句在本地管理表空间LOCAL下会被直接忽略PCTINCREASE更是早就废弃的参数。更现实的问题是/u01/oracle/oradata/这个路径在你自己机器上八成不存在直接执行会报 ORA-01119。我一般改写成这样-- 建一个独立表空间避免业务表和系统表抢空间 CREATE TABLESPACE t_kq_data DATAFILE /u01/app/oracle/oradata/ORCL/kq_data01.dbf SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 4G -- 自动扩但要设上限防止撑爆磁盘 EXTENT MANAGEMENT LOCAL AUTOALLOCATE -- 本地管理区大小由 Oracle 自动分配 SEGMENT SPACE MANAGEMENT AUTO; -- 段空间自动管理减少碎片参数逐个说SIZE 200M是初始大小按 12 张表加索引的数据量200M 绰绰有余AUTOEXTEND ON NEXT 100M MAXSIZE 4G表示不够时每次扩 100M最多长到 4G写死上限是为了防止一条死循环插入把磁盘写满EXTENT MANAGEMENT LOCAL是让位图管理区分配比数据字典管理快得多也不会产生uet$/fet$的递归争用SEGMENT SPACE MANAGEMENT AUTO用位图管理段内空闲块高并发插入时比手动pctfree/pctused稳。本地练习更省事的做法是压根不建表空间把脚本里所有TABLESPACE linpeng_data替换成TABLESPACE USERS或者整段删掉走默认。因为CREATE TABLESPACE需要 DBA 权限普通开发账号执行会直接 ORA-01031 权限不足。3.2 建表顺序决定了外键能不能过外键是硬约束被引用的列必须已经存在。所以 12 张表不能按报告里的书写顺序1 admin、2 student、3 faculty……执行——student引用classesclasses又在第 11 位第二条就炸。按依赖拓扑排序正确的落地顺序是顺序表名依赖的前置表1faculty无2majorfaculty3classteachermajor, faculty4classesclassteacher5studentclasses, major, faculty6teacherfaculty7collegeleaderfaculty8schoolleader无9admin无10course无11kaoqin_recordstudent, teacher, course12qingjiaclasses, student, classteacher, collegeleader有一个坑要提前说classteacher班主任表和classes班级表是互相引用的——班主任表里其实逻辑上该有个所带班级班级表里又有classtea_no。报告里是单向的班主任表只挂专业和学院班级表挂班主任所以能排开。如果哪天你想加反向引用就得拆成建表 → 加约束两步走用ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY补。3.3 三处必须改的字段类型第一处datetime。Oracle 没有这个类型MySQL 才有。所有datetime一律换成DATE精确到秒或TIMESTAMP精确到小数秒 时区感知。考勤时间sk_time用DATE就够请假起止时间如果跨国校区考虑时区再用TIMESTAMP WITH TIME ZONE。第二处char长度不一致。classes.class_no是CHAR(10)而student.stu_class写成了CHAR(13)faculty.faculty_id是NUMBERstudent.stu_faculty却写成CHAR(40)。Oracle 对参照完整性有硬要求——外键列和被引用列的类型必须兼容、长度必须一致否则 ORA-02267。统一原则是主键什么类型外键就什么类型一个字符都不能差。第三处number拼写。请假表的day_number写成了nubmer这种拼写错误执行时直接 ORA-00904 无效标识符。类似的还有teacher表里tea_faculty NUMBER引用faculty(faculty_id)但classteacher表里同一个含义的列叫classtea_faculty命名风格不统一后期写 JOIN 的时候容易手抖。改完的student表长这样CREATE TABLE student ( stu_no CHAR(10) NOT NULL, -- 学号主键 stu_name VARCHAR2(30) NOT NULL, -- 姓名 stu_sex CHAR(1) NOT NULL, -- 性别用 M/F 存避开中文字节长度问题 stu_class CHAR(10) NOT NULL, -- 所属班级长度必须和 classes.class_no 一致 stu_major NUMBER NOT NULL, -- 所属专业 stu_faculty NUMBER NOT NULL, -- 所属学院 CONSTRAINT pk_student PRIMARY KEY (stu_no), CONSTRAINT ck_stu_sex CHECK (stu_sex IN (M,F)), CONSTRAINT fk_stu_class FOREIGN KEY (stu_class) REFERENCES classes(class_no), CONSTRAINT fk_stu_major FOREIGN KEY (stu_major) REFERENCES major(major_id), CONSTRAINT fk_stu_faculty FOREIGN KEY (stu_faculty) REFERENCES faculty(faculty_id) ) TABLESPACE t_kq_data;把外键统一写成表级CONSTRAINT xxx FOREIGN KEY ... REFERENCES ...好处是约束有名字。出了问题报错信息里带约束名一眼能定位到是哪张表的哪条关系用列级匿名的写法报错只会给你SYS_C0012345这种系统名排查起来很痛苦。4. 数据库端三件套存储过程、视图、触发器的正确写法4.1 出勤统计存储过程参数命名和赋值符号报告里的原始版本是这样create or replace procedure getMessage(stu_no in varchar2, course_no in varchar2, total_times out number) as absence_times number; begin select count(*) into absence_times from kaoqin_record where stu_number stu_no and course_no course_no; total_times absence_times; end;这段有四个问题其中两个是致命的。第一个outnumber缺了空格PL/SQL 会把它当成一个叫outnumber的类型编译直接失败。第二个total_times absence_times是 SQL 的比较运算符PL/SQL 赋值必须用:。第三个最阴——参数名stu_no、course_no和表里的列名同名。PL/SQL 静态 SQL 的名字解析规则是列优先于变量所以where stu_number stu_no会被解析成where stu_number stu_number条件恒真前提是列非空统计出来是整张表的行数。这个坑不报错只给你一个看上去能跑的错误答案属于典型的黑匣子级 bug。血泪经验是存储过程参数统一加p_前缀杜绝重名。第四个语义。它统计的是该学生该课程的全部考勤记录数不是缺勤数。要统计缺勤得按stu_status过滤。改写版CREATE OR REPLACE PROCEDURE sp_absence_stat ( p_stu_no IN VARCHAR2, -- 学生学号 p_course_no IN VARCHAR2, -- 课程编号 p_total OUT NUMBER, -- 该课程累计考勤次数 p_absence OUT NUMBER -- 其中状态为旷课的次数 ) AS BEGIN SELECT COUNT(*), COUNT(CASE WHEN stu_status 旷课 THEN 1 END) -- 条件聚合一次扫描出两个数 INTO p_total, p_absence FROM kaoqin_record WHERE stu_number p_stu_no AND course_no p_course_no; END; /COUNT(CASE WHEN ... THEN 1 END)这个写法值得记住CASE没有ELSE时返回NULLCOUNT不计NULL所以它只数满足条件的行。相比写两条SELECT COUNT(*)一次全表扫描就出结果数据量大的时候差别很明显。参数说明里p_total和p_absence都是OUT模式调用方要先声明变量再接DECLARE v_total NUMBER; v_absence NUMBER; BEGIN sp_absence_stat(2018000001, C0001, v_total, v_absence); DBMS_OUTPUT.PUT_LINE(总考勤 || v_total || 次缺勤 || v_absence || 次); END; /执行前记得SET SERVEROUTPUT ON否则DBMS_OUTPUT的输出一个字都看不到——这是新手最常反馈的存储过程没反应。4.2 院系隔离视图硬编码学院 ID 的代价原始视图是为了让某学院领导只看本院学生create view rjxy as select kaoqin_record.kaoqin_id, kaoqin_record.sk_time, ... from kaoqin_record, student where student.stu_no kaoqin_record.stu_number and student.stu_faculty 5;思路完全正确问题在 5这个魔法数字。一个学院一个视图10 个学院就要建 10 个视图、维护 10 份几乎一样的 SQL。学院 ID 变了得挨个改。合理的做法是建一个不带过滤的基视图把过滤交给调用方-- 基视图只做关联不做权限裁剪 CREATE OR REPLACE VIEW v_kaoqin_detail AS SELECT k.kaoqin_id, k.sk_time, k.stu_number, s.stu_name, k.stu_status, k.teacher_no, k.course_no, c.course_name, s.stu_faculty FROM kaoqin_record k JOIN student s ON s.stu_no k.stu_number JOIN course c ON c.course_no k.course_no;应用层查的时候带上WHERE stu_faculty :dept_id把:dept_id从登录会话里取。这样一套视图服务所有学院新增学院不用动数据库。注意应用层过滤终究是信任客户端的做法。真要做强隔离Oracle 的行级安全VPD / DBMS_RLS才是正解——给视图挂一个策略函数函数里根据SYS_CONTEXT(USERENV,SESSION_USER)返回stu_faculty 当前用户所属学院的谓词无论谁怎么写 SQL 都绕不过去。课设阶段用不上但要知道这条路存在。4.3 缺勤预警触发器绕开 mutating table原始触发器想在插入考勤记录后如果该学生该课程缺勤累计到 3 次就打印警告。行级触发器里查自己所在的表Oracle 会直接抛 ORA-04091: table is mutating。原因是行级触发器执行时表正处于变更中状态Oracle 为了保证读一致性不允许你看到还没定型的中间结果。正确解法是用 11g 引入的复合触发器COMPOUND TRIGGER把逻辑拆成逐行收集 语句结束统一判断-- 先建一张预警记录表把提示落库而不是打印到控制台 CREATE TABLE kaoqin_warning ( warn_id NUMBER PRIMARY KEY, stu_no VARCHAR2(10) NOT NULL, course_no VARCHAR2(13) NOT NULL, warn_time DATE DEFAULT SYSDATE, warn_msg VARCHAR2(200) ) TABLESPACE t_kq_data; CREATE OR REPLACE TRIGGER trg_kaoqin_alert FOR INSERT ON kaoqin_record COMPOUND TRIGGER TYPE t_key IS RECORD (stu_no VARCHAR2(10), course_no VARCHAR2(13)); TYPE t_keys IS TABLE OF t_key INDEX BY PLS_INTEGER; g_keys t_keys; g_idx PLS_INTEGER : 0; -- 第一部分行级只收集数据不查表 AFTER EACH ROW IS BEGIN IF :NEW.stu_status 旷课 THEN g_idx : g_idx 1; g_keys(g_idx).stu_no : :NEW.stu_number; g_keys(g_idx).course_no : :NEW.course_no; END IF; END AFTER EACH ROW; -- 第二部分语句级此时表已定型可以安全查询 AFTER STATEMENT IS v_cnt NUMBER; BEGIN FOR i IN 1 .. g_idx LOOP SELECT COUNT(*) INTO v_cnt FROM kaoqin_record WHERE stu_number g_keys(i).stu_no AND course_no g_keys(i).course_no AND stu_status 旷课; IF v_cnt 3 THEN INSERT INTO kaoqin_warning (warn_id, stu_no, course_no, warn_time, warn_msg) VALUES (seq_warning.NEXTVAL, g_keys(i).stu_no, g_keys(i).course_no, SYSDATE, 该课程旷课累计 || v_cnt || 次已达取消考试资格标准); END IF; END LOOP; g_idx : 0; -- 清理避免同一会话内累积 END AFTER STATEMENT; END trg_kaoqin_alert; /三个关键点。g_keys用关联数组按PLS_INTEGER索引是因为批量插入多行时行级部分会重复触发得用集合暂存。AFTER STATEMENT段里查kaoqin_record是合法的此时语句已经执行完、表状态一致。最后g_idx : 0必须写复合触发器的状态变量在会话内是持续存在的不清空会导致下一次插入时把旧数据重新算一遍。另一处值得改的是把提示落库而不是DBMS_OUTPUT.PUT_LINE。控制台打印只在 SQL*Plus 里看得到应用层调用完全收不到等于白做。真要阻断插入用RAISE_APPLICATION_ERROR(-20001, ...)只做提醒就写表让前端轮询或消息队列去读。5. 踩坑排查这份脚本在你自己机器上必炸的五处5.1 ORA-00902: invalid datatype现象执行建表语句光标停在sk_time datetime not null那一行报数据类型无效。原因DATETIME是 SQL Server 和 MySQL 的类型Oracle 里没有这个类型名。报告里出现了 6 次覆盖考勤表和请假表。解决全部替换为DATE。涉及小数秒的用TIMESTAMP。替换后还要检查应用层的 JDBC 或 OCI 取值方式DATE取出来是java.sql.Timestamp别按String强转。5.2 ORA-02267: column type incompatible with referenced column type现象建student表时报外键类型不兼容但列名看着都对。原因student.stu_class CHAR(13)引用classes.class_no CHAR(10)长度不一致student.stu_faculty CHAR(40)引用faculty.faculty_id NUMBER类型都不一致。Oracle 要求外键列与被引用列类型兼容且长度相同CHAR的定长语义还会把短值补空格比较时更麻烦。解决统一写法主键CHAR(n)外键就CHAR(n)主键NUMBER外键就NUMBER。改完重新执行如果表已存在要先DROP TABLE ... CASCADE CONSTRAINTS。另外提醒一句CHAR存学号、编号这类定长值合适存姓名、备注必须用VARCHAR2CHAR(30)存个三字姓名会补 27 个空格LENGTH和LENGTHB结果对不上。5.3 存储过程跑出来是全表数据现象调用getMessage(2018000001,C0001, v)无论传什么学号返回的次数都是整张考勤表的行数。原因存储过程形参名stu_no、course_no和表列名重名。PL/SQL 静态 SQL 的名字解析优先级是列 变量where stu_number stu_no被解析成自比较条件退化成真。它不报错、不警告是最难查的一类翻车。解决形参统一加前缀p_stu_no/p_course_no。如果不想改参数名可以在过程里加PRAGMA RESTRICT_REFERENCES或者把 SQL 写成动态EXECUTE IMMEDIATE加绑定变量——后者治标不治本还是改名字干净。排查技巧是拿DBMS_OUTPUT把拼好的 WHERE 条件打出来一眼能看出问题。5.4 ORA-04091: table is mutating现象向kaoqin_record插入一条记录触发器报错提示表正在变更触发器看不到。原因AFTER INSERT ... FOR EACH ROW的行级触发器里去SELECT COUNT(*) FROM kaoqin_record读的是正在被修改的表。Oracle 为保护读一致性禁止这种操作。解决用COMPOUND TRIGGER行级只收集主键、语句级再做统计查询写法见 4.3。不想上 11g 的复合触发器另一条路是PRAGMA AUTONOMOUS_TRANSACTION开自治事务——但自治事务看不到未提交的当前行计数会少一得手动1补回来容易算错。还有一条最土的办法把累计校验从触发器挪到应用层插入完成后由 Java 或 Python 端查一次出问题能重试可控性反而更好。5.5 ORA-12899: value too large for column现象插入性别男报值太大超出列长度。原因stu_sex CHAR(2)的2默认按字节算NLS_LENGTH_SEMANTICSBYTE而 UTF-8 下一个汉字占 3 字节CHAR(2)只够放 2 字节直接超长。如果数据库建库时字符集是AL32UTF8现在基本都默认这个这个坑百分之百会踩。解决三条路。一是改列定义CHAR(2 CHAR)明确按字符算二是把长度给到VARCHAR2(6)留足字节余量三是最推荐的做法——性别别存中文用CHAR(1)存M/F或者NUMBER(1)存1/2字典表里映射显示文案。存中文状态值在跨库、跨编码、导出 CSV 时全是不确定性早点换掉省心。6. 二次开发把课程设计改成能演示的原型系统这份设计的骨架够用但要拿去给老师演示或者当毕设起点还差三样东西主键生成、状态机约束、学期维度。先补主键生成。请假表qingjia.id是NUMBER主键插入时没人给它值。11g 及以前用序列加触发器CREATE SEQUENCE seq_qingjia START WITH 1000 INCREMENT BY 1 NOCACHE; -- NOCACHE 避免实例宕机后跳号 CREATE OR REPLACE TRIGGER trg_qingjia_id BEFORE INSERT ON qingjia FOR EACH ROW BEGIN IF :NEW.id IS NULL THEN -- 允许调用方显式指定便于数据迁移 :NEW.id : seq_qingjia.NEXTVAL; END IF; END; /12c 以后可以直接id NUMBER GENERATED BY DEFAULT AS IDENTITY连触发器都省了。判断标准很简单SELECT * FROM v$version看到 12 开头就用 IDENTITY11g 老老实实用序列。再补状态机。原设计把审批状态丢给应用层自由填写数据库端没有任何约束。加一条 CHECK把两级审批的合法值锁死再顺手加一条业务规则——院系领导只在超过三天时才介入ALTER TABLE qingjia ADD CONSTRAINT ck_qj_status CHECK ( class_tea_sp_status IN (0,1,2) -- 0 待审 1 同意 2 驳回 AND coll_leader_sp_status IN (0,1,2) );最值得补的其实是学期维度。原始的kaoqin_record只有sk_timecourse表只有课程编号和名称没有学期、没有开课班级、没有授课教师。按现在的设计你没法回答这学期某门课的出勤率只能靠时间范围硬猜。改动量不大加一张开课表把课表安排落下来就行字段类型说明term_idNUMBER学期编号主键一部分course_noCHAR(13)课程外键class_noCHAR(10)开课班级外键teacher_noCHAR(10)授课教师外键week_day/sectionNUMBER周几、第几节有了这张表考勤记录的teacher_no和course_no就能反查是不是合法开课组合请假单也能按学期汇总。验证方式很土但有效造三条数据——一条正常出勤、一条旷课、一条有已批准请假单的旷课——跑一遍sp_absence_stat比对结果是不是3 / 1请假那条不算缺勤。数字对上了说明统计口径和业务规则是一致的对不上问题一定出在stu_status的枚举值或者视图的过滤条件上。从那以后我每次接手别人的库表脚本都强制先做三件事把所有类型跑一遍DESC确认没有伪类型把外键长度逐列比对一遍再把存储过程和触发器用USER_ERRORS查一遍编译告警。这三步花十分钟能省掉后面两小时的 ORA 代码翻查。这份课程设计报告的价值也在这儿——它足够完整能让你把这三步走完也能让你把一套教务类库表从纸面推到可运行。希望帮到你。本文还有配套的精品资源点击获取
