Oracle数据库|Oracle系统案例——学生信息管理系统数据库|database|oracle

文章目录

一. 引言
二. 概念模型设计阶段
三. 创建school用户
四. 建表
五. 追加表的注释
六. 添加约束
七. 录入起始数据
八. 创建视图
九. 创建数据统计页面
十. 通过使用%rowtype定义记录类型
十一. 游标的使用
十二. 嵌套表
十三. 例外的举例
十四. 触发器
十五. 使用包

本篇文章由于是完成系统后写的总结，故很多运行结果显示的是系统完成后的最终结果，与相应的SQL执行结果会有出入。
一. 引言要求：
完成一个简单的应用系统设计开发。
背景：
本次实践中实现学生信息管理系统的数据库结构的创建。
需求：
实现学生信息管理系统中教学模块的数据库设计。
功能描述：
教学模块包括学生基本信息管理、课程信息管理、班级信息管理、选课信息管理、教师基本信息管理、教师授课信息管理。
详细信息描述如下：
学生基本信息包括：学号、姓名、年龄、性别、入学日期、家庭住址。
教师基本信息包括：教师号、姓名、年龄、担当的课程号。
课程基本信息包括：课程号、课程名、课程学分。
选课（选教师）信息包括：学号、教师号、成绩。（由于每门课可能有多个授课老师，所以在选课时需要同时指定此门课程的授课老师，故在此存储的是老师的教师号）
班级信息包括：班级号、系、年级、班级。
各实体之间关系如下：

一个学生只能属于一个班级，一个班级有多个学生；
一个学生可以选择多门课程，一门课程可以被多个学生选择；
一个老师只能教授一门课，一门课程可以由多个老师教。

二. 概念模型设计阶段

分析业务实体：
4个实体：教师、班级、学生和课程。
【Oracle数据库|Oracle系统案例——学生信息管理系统】分析业务实体之间的关系、清除业务实体的数据冗余：
从题目分析学生和课程、教师和课程、班级和学生之间存在实体关联：学生-课程（m:n），课程-教师（1:n），班级-学生（1:n）。学生选课可以用教师编号代替课程编号，教师编号可唯一确定课程信息。

三. 创建school用户

grant dba to hr; conn hr/hr; --创建school用户 create user school identified by school; --为school用户授权dba; grant dba to school; --连接school用户 conn school/school; --建表空间llf create tablespace llf datafile 'd:llf.dbf'size 20m uniform size 128k;

四. 建表

--1、class表 create table class( clno varchar(5) primary key, dept varchar2(40) not null, grade varchar(8) not null, branch varchar2(20) not null )tablespace llf; --2、student表 create table student( sno varchar2(5) primary key, name varchar2(12) not null, age number(2) not null, sex char(1) not null, entrance date, address varchar2(100), clno varchar2(5), constraint fk_clno foreign key(clno) references class(clno) )tablespace llf; --3、course表 create table course( cno varchar2(5) primary key, name varchar2(60) not null, score number(2) not null )tablespace llf; --4、teacher 表 create table teacher( tno varchar2(5) primary key, name varchar2(12) not null, age number(2), cno varchar2(5) not null )tablespace llf; --5、学生选课表st create table st( sno varchar2(5) not null, tno varchar2(5) not null, grade number(2) default 0 )tablespace llf;

五. 追加表的注释

comment on table class is '班级信息表'; comment on table student is '学生信息表'; comment on table course is '课程信息表'; comment on table teacher is '教师信息表'; comment on table st is '学生选课表';

六. 添加约束

class.grade约束check

alter table class add CONSTRAINT ck_class_grade check(grade in('大一','大二','大三','大四'));
student.age/student.sex约束

alter table student add constraint ck_student_age check(age>=10 and age<41); alter table student add constraint ck_student_sex check(sex in ('1','0')); comment on column student.sex is'性别,1:男,0:女';
course.score的check约束

alter table course add constraint ck_course_score check(score>0 and score<10);
teacher表约束

alter table teacher add constraint fk_teacher_cno foreign key(cno) references course(cno); alter table teacher add constraint ck_teacher_age check(age>20 and age<66);
st表的约束

--st的外键约束 alter table st add constraint fk_st_sno foreign key(sno) references student(sno); alter table st add constraint fk_st_tno foreign key(tno) references teacher(tno); --st的check约束 alter table st add constraint ck_st_grade check(grade >= 0 and grade <= 9);

七. 录入起始数据

向班级表添加数据

insert into class values ('CC101','计算机','大一','一班'); insert into class values ('CC102','计算机','大一','二班');

文章图片
向学生表添加数据

insert into student values('95001','张飞',23，1，to_date('2006-9-1','yyyy-mm-dd'),'青岛','CC101'); insert into student values('95002','赵云',21，1，to_date('2006-9-1','yyyy-mm-dd'),'青岛','CC101'); insert into student values('95003','关羽',22，1，to_date('2006-9-1','yyyy-mm-dd'),'天津','CC101'); insert into student values('95004','貂蝉',20，0，to_date('2006-9-1','yyyy-mm-dd'),'北京','CC102'); insert into student values('95005','小乔',19，0，to_date('2006-9-1','yyyy-mm-dd'),'上海','CC102');

文章图片
向课程信息表添加数据

insert into course values('CN001','数据库原理',6); insert into course values('CN002','数据结构',2); insert into course values('CN003','编译原理',2); insert into course values('CN004','程序设计',2); insert into course values('CN005','高等数学',3);

文章图片
向教师信息表添加数据

insert into teacher values('T8101','刘华',34,'CN001'); insert into teacher values('T8103','王刚',28,'CN002'); insert into teacher values('T8104','张雪',33,'CN003'); insert into teacher values('T8105','赵顺',32,'CN004'); insert into teacher values('T8106','周刚',32,'CN005');

文章图片
向选课信息表添加数据

insert into st values('95001','T8101',0); insert into st values('95001','T8102',0); insert into st values('95001','T8104',0); insert into st values('95002','T8101',0); insert into st values('95002','T8103',0); insert into st values('95002','T8104',0); insert into st values('95002','T8106',0); insert into st values('95003','T8102',0); insert into st values('95003','T8104',0); insert into st values('95003','T8105',0);

文章图片

八. 创建视图在学生信息管理系统——教学模块开发过程中，需要实现如下页面（创建视图）：

学生信息查询页面：系，年级，班级，学生号，学生姓名，年龄，性别，入学日期

create or replace view vw_class_student as select c.dept, c.branch,s.sno,s.age,s.sex,s.entrance from class c join student s on c.clno=s.clno with read only;

文章图片
授课信息页面查询：提供教师号、教师姓名、年龄、所担任课程的编号和名称

create or replace view vw_teacher_course as select t.tno 教师号,t.name 教师姓名,t.age 教师年龄,c.cno 课程号,c.name 课程名称 from teacher t join course c on t.cno=c.cno with read only;

文章图片
选课信息查询页面：提供学生号、学生姓名、所选课程编号、课程名称

create or replace view vw_student_course as select s.sno 学生号,s.name 学生姓名,c.cno 课程号,c.name 课程名称 from student s join st st on s.sno=st.sno join teacher t on st.tno=t.tno join course c on t.cno=c.cno with read only;

文章图片
任教信息查询页面：提供系、年级、班级及在当前班担任授课任务的教师姓名

create or replace view vw_class_teacher as select c.dept 系,c.grade 年级,c.branch 班级,t.name 教师姓名 from class c join student s on c.clno=s.clno join st st on s.sno=st.sno join teacher t on st.tno=t.tno with read only;

文章图片
班级课程查询页面：提供系、年级、班级及当前班所覆盖的课程名称

create or replace view vw_class_course as select cl.dept 系,cl.grade 年级,cl.branch 班级,c.name 课程名称 from class cl join student s on cl.clno=s.clno join st st on s.sno=st.sno join teacher t on st.tno=t.tno join course c on t.cno=c.cno with read only;

文章图片

九. 创建数据统计页面

班级人数统计页面

create or replace view vw_class_count as select cl.dept 系,cl.grade 年级,cl.branch 班级,count(*) 学生人数 from class cl join student s on cl.clno=s.clno group by(cl.dept,cl.grade,cl.branch);

文章图片
学生成绩统计页面：按班级、学生分组，统计每个学生的成绩信息总和及选修的课程数

create or replace view vw_student_count as select cl.dept 系,cl.grade 年级,cl.branch 班级,s.name 学生姓名,count(*) 课程数,sum(st.grade) 总成绩 from class cl join student s on cl.clno=s.clno join st st on s.sno=st.sno group by(cl.dept,cl.grade,cl.branch,s.name);

文章图片
学生成绩列表页面
这里会使用到decode函数
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下：

IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ...... ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF

创建页面：

create or replace view vw_student_grade as select s.name 姓名, sum(decode(c.name,'数据库原理',grade,null))数据库原理, sum(decode(c.name,'数据结构',grade,null))数据结构, sum(decode(c.name,'编译原理',grade,null))编译原理, sum(decode(c.name,'程序设计',grade,null))程序设计, sum(decode(c.name,'高等数学',grade,null))高等数学 from student s join st st on s.sno=st.sno join teacher ton st.tno=t.tno join course c on t.cno=c.cno where c.name in ('数据库原理','数据结构','编译原理','程序设计','高等数学') group by s.name;

文章图片

十. 通过使用%rowtype定义记录类型

declare --使用%rowtype声明class的记录类型变量 class_record class%rowtype; row_id rowid; info varchar2(60); begin class_record.clno:='CC103'; class_record.dept:='自动化'; class_record.grade:='大三'; class_record.branch:='二班'; --使用记录类型变量完成数据插入操作 insert into class values class_record returning rowid,clno||','||dept||','||grade||','||branch into row_id,info; dbms_output.put_line('插入:'||row_id||':'||info); --基于记录类型变量完成数据的整行修改 class_record.clno:='CC104'; update class set row=class_record where clno='CC103' returning rowid,clno||','||dept||','||grade||','||branch intorow_id,info; dbms_output.put_line('修改:'||row_id||':'||info); --基于记录类型变量进行数据删除 class_record.clno:='CC104'; delete from class where clno=class_record.clno returning rowid,clno||','||dept||','||grade||','||branch intorow_id,info; dbms_output.put_line('删除：'||row_id||':'||info); exception when others then dbms_output.put_line('出现某种异常'); end;

十一. 游标的使用游标：
Oracle为用户开设的一个数据缓冲区，存放SQL语句的执行结果。Oracle数据库中执行的每一个SQL语句都有对应的游标。
游标分两类：

隐式游标：处理单行select into 和DML语句；
显示游标：处理select语句返回的多行数据

显式游标的使用（处理）步骤：

定义游标
cursor cursor_name[…]select_statement;
打开游标
open cursor_name[…];
提取游标数据
fetch cursor_name into {};
对游标指针指向的数据进行处理
继续处理，直到没有记录可以处理为止
关闭游标
close cursor_name;

案例：

declare --声明游标 cursor class_cursor is select clno,dept from class; v_clno class.clno%type; v_dept class.dept%type; begin --打开游标 open class_cursor; --循环取值 loop --将当前行数据提取到变量中 fetch class_cursor into v_clno,v_dept; exit when class_cursor%notfound; dbms_output.put_line('班级号：'||v_clno||'系：'||v_dept); end loop; --关闭游标 close class_cursor; end;

十二. 嵌套表

创建嵌套表列时，需要首先创建嵌套表类型

create or replace type family_type is table of varchar2(20);
重新调整学生表，增加一列family_type类型的新的属性列

alter table student add( family family_type )nested table family store as family_table;
为嵌套表添加数据

insert into student values('95006','昭君',19,0,to_date('2006-09-01','yyyy-mm-dd'),'上海','CC102',family_type('父亲','母亲','姐姐'));
需要检索嵌套表列的数据时，也需要定义嵌套表变量接收数据

declare --声明family_type类型用于接受检索结果 family_table family_type; v_name student.name%type; begin select name,family into v_name,family_table from student where sno=&sno; dbms_output.put_line('学生'||v_name||'的亲属有：'); for i in 1..family_table.count loop dbms_output.put_line(family_table(i)||' '); end loop; dbms_output.new_line(); exception when no_data_found then dbms_output.put_line('你指定的学生号不存在！'); end;
更新嵌套表列数据

declare family_table1 family_type:=family_type('父亲','母亲','哥哥','姐姐','弟弟'); family_table2 family_type; v_sno student.sno%type:=&sno; v_name student.name%type; begin --使用嵌套表变量更新嵌套表列 update student set family=family_table1 where sno=v_sno; --获得更新后的数据 select name,family into v_name,family_table2 from student where sno=v_sno; dbms_output.put_line('学生'||v_name||'的亲属有：'); for i in 1..family_table2.count loop dbms_output.put_line(family_table2(i)||' '); end loop; dbms_output.new_line(); exception when no_data_found then dbms_output.put_line('你指定的学生号不存在！'); end;

十三. 例外的举例

declare v_n1 int:=&n1; v_n2 int:=&n2; v_div int; begin v_div:=v_n1/v_n2; dbms_output.put_line(v_n1||'/'||v_n2||'='||v_div); exception when zero_divide then dbms_output.put_line('除数不能为零！'); end;

十四. 触发器触发器（trigger）：
当数据库发生操作时（DML操作），会发生一些事件，从而自动执行相应程序。
触发器分类：

语句触发器：在执行DML操作（insert、update、delete）时，激活该类触发器。
行触发器。

语句触发器：

create or replace trigger trg_class before insert or update or delete on class declare v_now varchar2(30); begin v_now:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'); case when INSERTING THEN DBMS_OUTPUT.PUT_LINE(v_now||'对class表进行了insert操作'); when updating then dbms_output.put_line(v_now||'对class表进行了update操作'); when deleting then dbms_output.put_line(v_now||'对class表进行了delete操作'); end case; end;

测试：

insert into class values('CC106','自动化','大二','二班'); delete from class where clno='CC106';

文章图片

行触发器：

create or replace trigger trg_class_before before insert or update or delete on class for each row declare v_now varchar2(30); begin v_now:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'); case when INSERTING THEN DBMS_OUTPUT.PUT_LINE('添加数据：'||:new.clno||','||:new.dept||','||:new.grade||','||:new.branch); when updating then dbms_output.put_line('修改前数据：'||:old.clno||','||:old.dept); dbms_output.put_line('修改后数据：'||:new.clno||','||:new.dept); end case; end;

测试：

insert into class values('CC106','自动化','大一','一班'); update class set clno='CC109',dept='化工' where clno='CC106';

文章图片

十五. 使用包鉴于系统要求，经常需要打印报表信息。为了方便维护，我们将打印报表的过程统一封装，并且放入包内统一维护。
分析：

包结构的功能，一般是根据模块功能来定义的，可以将对学生表的操作定义为一个包。
对于报表的打印，一般涉及到多行语句的处理，引入游标。
由于报表打印，一般不需要返回值，可以使用过程来封装。

案例：

--1、定义包规范 create or replace package report_pack is --根据输入的班级号，打印当前班级信息以及当前班的学生信息 procedure student_of_class(p_clno class.clno%type); end report_pack; --2、对于report_pack包的包体代码如下： create or replace package body report_pack is --实现过程student_of_class procedure student_of_class(p_clno class.clno%type)--过程 is cursor student_cursor is select * from student where clno=p_clno; --游标 student_record student%rowtype; --定义学生记录类型的变量 class_record class%rowtype; --定义班级记录类型 v_count number(2); v_sex varchar2(3); begin select * into class_record from class where clno = p_clno; --取得班级信息 select count(*) into v_count from student where clno=p_clno group by clno; --取得班级人数 dbms_output.put_line(class_record.dept||'系'|| class_record.grade||'，'||class_record.branch||'总共有：'||v_count||'人'); dbms_output.put_line('--------------------------------------------'); --取得当前班级的学生信息 open student_cursor; loop fetch student_cursor into student_record; exit when student_cursor%notfound; if student_record.sex='1' then v_sex:='男'; else v_sex:='女'; end if; dbms_output.put_line('学生号:'||student_record.sno||',姓名:'|| student_record.name||',年龄:'||student_record.age||',性别:'||v_sex ||',入学日期:'||to_char(student_record.entrance,'yyyy-mm-dd')); end loop; close student_cursor; exception when no_data_found then dbms_output.put_line('指定的班级号不存在！'); end student_of_class; end report_pack;

测试：