Oracle数据库课程设计实战：学生考勤系统建表与PL/SQL避坑指南

简介这份Oracle数据库课程设计资源面向学习数据库管理与开发的高校学生及IT从业者以「学生考勤系统」为完整案例帮助读者掌握从需求分析到物理实现的数据库设计全流程。压缩包内仅含1个doc文档约227KB为辽宁工程技术大学软件学院的课程设计报告结构完整、目录清晰。报告依次展开背景分析、多角色用户需求描述、请假与考勤及后台管理三大功能模块划分、E-R模型设计、数据字典设计、数据库表逻辑结构设计以及表空间创建、建表与触发器、存储过程等数据库对象的实现步骤并附有心得体会与参考文献。读者可借此理解学生、任课老师、班主任、院系领导、学校领导、系统管理员等不同角色的权限与功能划分学习主外键、索引设置及数据完整性保障思路也可作为课程设计选题与报告撰写的参考模板。目前已有962人学习下载适合需要完成数据库课程设计或希望系统梳理Oracle建库建表流程的读者参考。1. 一份 2009 年的 Oracle 课程设计为什么现在拆依然有价值翻到这份辽宁工程技术大学软件学院的学生考勤系统课程设计第一反应可能是年代久远。但如果你正在准备数据库课程设计、需要一套能跑通的 Oracle 建表脚本或者想找一个覆盖表空间、约束、视图、存储过程、触发器的完整练手项目这份材料反而比很多新教程更实在——它把从需求分析到物理建表的全链路都走了一遍而且每一步都有可执行的 SQL。这份资源的核心是一套学生考勤管理系统的 Oracle 实现方案涉及学生、任课老师、班主任、院系领导、学校领导、系统管理员六类用户功能上拆成请假、考勤、后台管理三个模块。它解决的不是高并发或分布式这类问题而是数据库课程设计最典型的诉求怎么把 E-R 图落成表结构、怎么用约束保证数据完整性、怎么用存储过程和触发器把业务逻辑下沉到数据库端。适合正在做数据库课程设计的学生也适合想复习 Oracle 基础 DDL 和 PL/SQL 的从业者。2. 从 E-R 图到十二张表逻辑结构设计的落地路径2.1 先理清实体和关系再动手建表这份设计的 E-R 模型里有几个关键实体学生、教师、班级、课程、学院、专业、班主任、院系领导、学校领导、请假条、考勤记录。关系上学生属于班级、班级属于专业、专业属于学院这是一条清晰的层级链教师通过开设关系关联课程学生通过考勤关系关联课程和教师请假条则同时关联学生、班级、班主任和院系领导。很多课程设计翻车就翻在这里——E-R 图画得热闹一到建表就发现外键指向的表还没创建。正确的建表顺序应该按依赖关系来先建被引用的基础表faculty、major、classes、classteacher再建引用它们的表student、teacher最后建业务表kaoqin_record、qingjia。这个顺序不是可选项Oracle 的外键约束会在插入时强制检查顺序错了直接报 ORA-02291。2.2 十二张表的字段设计和约束策略原始设计给出了完整的逻辑结构我把它整理成一张对照表方便你建表时逐字段核对表名主键外键关键约束adminadmin_no无性别 checkstudentstu_nostu_class→classes, stu_major→major, stu_faculty→faculty性别 checkfacultyfaculty_id无无majormajor_idmajor_faculty→faculty无teachertea_notea_faculty→faculty性别 checkclassteacherclasstea_noclasstea_major→major, classtea_faculty→faculty性别 checkcollegeleadercollegeleader_nocollegeleader_faculty→faculty性别 checkschoolleaderschoolleader_no无性别 checkkaoqin_recordkaoqin_idstu_number→student, teacher_no→teacher, course_no→course无coursecourse_no无无classesclass_noclasstea_no→classteacher无qingjiaidclass_id→classes, stu_no→student, class_tea_id→classteacher, coll_leader_id→collegeleader审批状态字段这张表里有个容易忽略的点student 表的 stu_class 字段在原始设计中写的是 char(5)但 classes 表的 class_no 是 char(10)类型长度不一致会导致外键创建失败。我一般会把两边统一成 char(10)或者干脆用 varchar2 避免定长补空格的坑。2.3 表空间设计和建表脚本的完整执行原始设计里先建了一个字典管理表空间 linpeng_data这个做法在课程设计里值得保留——它让你理解 Oracle 的表空间概念而不是把所有表都塞进 users 表空间。建表空间的语句如下-- 创建字典管理表空间指定数据文件路径和初始大小 create tablespace linpeng_data datafile /u01/oracle/oradata/tab01.dbf size 100M default storage( initial 512K -- 初始区大小 next 128K -- 下次分配区大小 minextents 2 -- 最小区数 maxextents 999 -- 最大区数 pctincrease 0 -- 区增长百分比0 表示不自动增长 ) online;这里有几个参数需要根据实际环境调整。datafile 路径必须是你机器上真实存在的目录Windows 下可能是D:\oracle\oradata\tab01.dbf这种格式。size 100M 对于课程设计够用但如果要插入大量测试数据建议改成 autoextend on next 10M maxsize 500M省得中途表空间满了报 ORA-01653。建完表空间后按依赖顺序建表。以 student 表为例-- 学生表外键分别指向班级、专业、学院 create table student( stu_no char(10) not null, stu_name varchar2(30) not null, stu_sex char(2) check (stu_sex男 or stu_sex女), stu_class char(10) references classes(class_no), stu_major number references major(major_id), stu_faculty number references faculty(faculty_id), constraint pk_student primary key(stu_no) ) tablespace linpeng_data;注意原始设计里写的是stu_class char(5) foreign key references classes(class_no)这个语法在 Oracle 里是不合法的——列级外键约束不能这么写要么用references关键字直接跟在列定义后要么用表级约束constraint fk_xxx foreign key(stu_class) references classes(class_no)。上面我改成了references的简写形式这是 Oracle 支持的列级约束语法。请假信息表 qingjia 是字段最多的表也是业务逻辑最集中的地方-- 请假信息表记录请假全流程的审批状态 create table qingjia( id number primary key, class_id char(10) references classes(class_no), stu_no char(10) references student(stu_no), leave_reason varchar2(200) not null, start_time date not null, end_time date not null, day_number number not null, qingjia_time date not null, class_tea_id char(5) references classteacher(classtea_no), class_tea_sp_status char(10), class_tea_sp_time date, coll_leader_sp_status char(10), coll_leader_id char(5) references collegeleader(collegeleader_no), coll_leader_sp_time date ) tablespace linpeng_data;原始设计里用的是datetime类型但 Oracle 没有 datetime 这个数据类型正确的是date包含日期和时间或timestamp。这个错误如果不改建表直接报 ORA-00902。审批状态字段用 char(10) 存等待审批同意不同意这类中文值在 GBK 字符集下没问题但如果数据库用的是 AL32UTF8一个中文字符占 3 字节char(10) 只能存 3 个汉字建议改成 varchar2(20)。3. 存储过程、视图、触发器把业务逻辑下沉到数据库端3.1 用存储过程封装考勤统计查询原始设计里有一个 getMessage 存储过程用于统计某学生某课程的缺勤次数。这个思路是对的——把统计逻辑封装在数据库端应用程序只需要调用不用每次拼复杂的 SQL。但原始代码有几个语法问题需要修正-- 统计指定学生指定课程的缺勤次数 create or replace procedure getMessage( p_stu_no in varchar2, -- 学生学号 p_course_no in varchar2, -- 课程编号 p_total out number -- 输出缺勤次数 ) as begin select count(*) into p_total from kaoqin_record where stu_number p_stu_no and course_no p_course_no and stu_status 缺勤; -- 只统计缺勤状态 end getMessage;原始代码里total_timesabsence_times缺少冒号PL/SQL 里赋值要用:。另外原始代码没有过滤 stu_status会把所有考勤记录都算进去包括出勤的。调用方式-- 在 SQL*Plus 中调用存储过程 var v_count number; execute getMessage(0820980113, ORACLE001, :v_count); print v_count;参数说明p_stu_no 和 p_course_no 是输入参数p_total 是输出参数。调用时需要用绑定变量接收输出值不能直接在 select 里调用。3.2 用视图实现院系级数据隔离原始设计里创建了一个 rjxy 视图让软件学院的领导只能看到本院学生的考勤信息。这个做法在实际项目里很常见——用视图做行级权限控制比在应用层过滤更安全-- 创建软件学院考勤视图假设软件学院 faculty_id 5 create or replace view v_rjxy_kaoqin as select k.kaoqin_id, k.sk_time, k.stu_number, k.stu_status, k.teacher_no, k.course_no from kaoqin_record k, student s where s.stu_no k.stu_number and s.stu_faculty 5;这个视图的关键在于关联条件s.stu_faculty 5它把数据范围限定在软件学院。如果其他学院也要类似的视图可以把这个数字参数化或者用存储过程动态拼接。注意视图本身不存储数据每次查询都会执行底层的 join如果 kaoqin_record 表数据量大建议在 stu_faculty 和 stu_number 上建索引。3.3 用触发器实现缺勤预警原始设计里有一个 alertMessage 触发器当学生某课程缺勤超过 3 次时输出提示。这个触发器用的是 after insert每次插入考勤记录后检查-- 缺勤超过 3 次时输出预警信息 create or replace trigger trg_alert_message after insert on kaoqin_record for each row declare v_current_times number; begin -- 统计该学生该课程在插入后的总缺勤次数 select count(*) into v_current_times from kaoqin_record where stu_number :new.stu_number and course_no :new.course_no and stu_status 缺勤; if v_current_times 3 then dbms_output.put_line(学号 || :new.stu_number || 的学生该门课程缺勤已达 || v_current_times || 次被取消考试资格); end if; end trg_alert_message;这里有个血泪经验dbms_output.put_line 的输出只有在 SQL*Plus 里执行set serveroutput on才能看到在 JDBC 或其他客户端里默认不显示。如果要在应用层收到预警应该把提示信息写入一张预警表而不是依赖 dbms_output。另外触发器里的 select 会查询 kaoqin_record 表本身这在行级触发器里是允许的但要注意不要造成递归触发——如果触发器里又对同一张表做 insert就会死循环。4. 避坑与排查建表和调试中最容易翻车的五个点4.1 外键建表顺序错误导致 ORA-02291现象执行建表语句时提示未找到父项关键字或者插入数据时报 ORA-02291。原因被引用的表还没创建或者引用列不是被引用表的主键/唯一键。比如先建 student 再建 classesstudent 的外键 references classes(class_no) 就会失败。解决按依赖顺序建表——faculty → major → classteacher → classes → student → teacher → course → kaoqin_record → qingjia。如果已经建错了先 drop 掉有问题的表再按顺序重建。4.2 数据类型不匹配导致外键创建失败现象建表时报 ORA-02267列类型与引用列类型不兼容。原因外键列和被引用列的数据类型或长度不一致。原始设计里 student.stu_class 是 char(5)classes.class_no 是 char(10)这种就会失败。解决建表前逐字段核对类型和长度建议统一用 varchar2 而不是 char避免定长补空格带来的比较问题。如果已经建了表用alter table student modify stu_class char(10)修改。4.3 表空间路径不存在导致 ORA-01119现象创建表空间时报 ORA-01119创建数据库文件时出错。原因datafile 指定的路径在操作系统上不存在或者 Oracle 进程没有写权限。解决先在操作系统层面创建目录Linux 下用mkdir -p /u01/oracle/oradataWindows 下确认盘符和目录存在。然后用show parameter db_create_file_dest查看默认路径或者直接用相对路径让 Oracle 自动创建。4.4 存储过程编译通过但调用报错现象show errors显示存储过程编译成功但调用时提示参数类型不匹配或返回值异常。原因PL/SQL 里赋值用了而不是:或者输出参数没有正确声明。原始代码里total_timesabsence_times就是典型错误。解决编译后用select * from user_errors where nameGETMESSAGE查看详细错误。调用时注意输入参数用字符串输出参数用绑定变量接收。4.5 触发器在应用层看不到输出现象在 SQL*Plus 里测试触发器能看到提示但在 Java/JDBC 程序里调用同样的 insert 却没有任何输出。原因dbms_output 是 SQL*Plus 的客户端特性JDBC 默认不启用 serveroutput输出被丢弃。解决把预警信息写入独立的预警表应用层查询该表获取通知。或者用dbms_output.enable在 JDBC 里手动启用但这种方式不够可靠不推荐在生产环境使用。5. 进阶技巧用分析函数和分页查询把考勤统计做得更细原始设计里的统计只做到了 count(*)但实际考勤管理需要更细的维度——比如按课程统计每个学生的出勤率、按班级排名缺勤次数、按时间段筛选考勤记录。这些用 Oracle 的分析函数可以一条 SQL 搞定。先看一个按课程统计出勤率的查询-- 统计每个学生在每门课程中的出勤率 select stu_number, course_no, count(*) as total_records, sum(case when stu_status 出勤 then 1 else 0 end) as attend_count, round(sum(case when stu_status 出勤 then 1 else 0 end) / count(*) * 100, 2) as attend_rate from kaoqin_record group by stu_number, course_no order by attend_rate asc;这个查询用 case when 做条件计数比多次子查询效率高。attend_rate 保留两位小数方便直接展示。如果要按缺勤次数排名用 rank() 分析函数-- 按缺勤次数对班级学生排名 select stu_number, course_no, absence_count, rank() over (partition by course_no order by absence_count desc) as rk from ( select stu_number, course_no, count(*) as absence_count from kaoqin_record where stu_status 缺勤 group by stu_number, course_no ) where rk 10;内层子查询先算出每个学生每门课的缺勤次数外层用 rank() 按课程分组排名最后筛选前 10 名。这种写法在做缺勤预警名单时特别实用。Oracle 分页查询也是课程设计里常被问到的点。12c 之前用 rownum12c 之后可以用 offset fetch-- Oracle 12c 分页查询考勤记录 select kaoqin_id, stu_number, course_no, sk_time, stu_status from kaoqin_record order by sk_time desc offset 20 rows fetch next 10 rows only;如果是 11g 环境得用嵌套 rownum-- Oracle 11g 分页查询 select * from ( select a.*, rownum rn from ( select kaoqin_id, stu_number, course_no, sk_time, stu_status from kaoqin_record order by sk_time desc ) a where rownum 30 ) where rn 20;这里有个细节内层rownum 30必须先于外层rn 20执行否则分页结果会错乱。这个坑我在早期项目里踩过不止一次排序和分页的嵌套层级一定要理清楚。最后说一个验证方法建完所有表和对象后用select object_name, object_type, status from user_objects where status INVALID检查有没有编译失败的对象。如果有 INVALID 的存储过程或触发器用show errors或查 user_errors 定位问题。从那以后我每次做完数据库设计都会先跑一遍这个检查再插入测试数据验证约束和触发器最后才交给应用层对接。希望帮到你。本文还有配套的精品资源点击获取