[大数据]Oracle系统案例——学生信息管理系统

本篇文章由于是完成系统后写的总结，故很多运行结果显示的是系统完成后的最终结果，与相应的SQL执行结果会有出入。

一. 引言

要求：

完成一个简单的应用系统设计开发。

背景：

本次实践中实现学生信息管理系统的数据库结构的创建。

需求：

实现学生信息管理系统中教学模块的数据库设计。

功能描述：

教学模块包括学生基本信息管理、课程信息管理、班级信息管理、选课信息管理、教师基本信息管理、教师授课信息管理。

详细信息描述如下：

学生基本信息包括：学号、姓名、年龄、性别、入学日期、家庭住址。

教师基本信息包括：教师号、姓名、年龄、担当的课程号。

课程基本信息包括：课程号、课程名、课程学分。

选课（选教师）信息包括：学号、教师号、成绩。（由于每门课可能有多个授课老师，所以在选课时需要同时指定此门课程的授课老师，故在此存储的是老师的教师号）

班级信息包括：班级号、系、年级、班级。

各实体之间关系如下：

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

二. 概念模型设计阶段

1. 分析业务实体：

   4个实体：教师、班级、学生和课程。

2. 分析业务实体之间的关系、清除业务实体的数据冗余：

   从题目分析学生和课程、教师和课程、班级和学生之间存在实体关联：学生-课程（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 '学生选课表';

六. 添加约束

1. class.grade约束check

   alter table class
   add CONSTRAINT ck_class_grade check(grade in('大一','大二','大三','大四'));
   

2. 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:女';
   

3. course.score的check约束

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

4. 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);
   

5. 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);
   

七. 录入起始数据

1. 向班级表添加数据

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

   

2. 向学生表添加数据

   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');
   

   

3. 向课程信息表添加数据

   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);
   

   

4. 向教师信息表添加数据

   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');
   

   

5. 向选课信息表添加数据

   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);
   

   

八. 创建视图

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

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

   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;
   

   

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

   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;
   

   

3. 选课信息查询页面：提供学生号、学生姓名、所选课程编号、课程名称

   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;
   

   

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

   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;
   

   

5. 班级课程查询页面：提供系、年级、班级及当前班所覆盖的课程名称

   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;
   

   

九. 创建数据统计页面

1. 班级人数统计页面

   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);
   

   

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

   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);
   

   

3. 学生成绩列表页面

   这里会使用到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 t  on 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 into   row_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 into   row_id,info;
  dbms_output.put_line('删除：'||row_id||':'||info);
  exception
    when others then
      dbms_output.put_line('出现某种异常');
end;

十一. 游标的使用

游标：

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

游标分两类：

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

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

1. 定义游标

   cursor cursor_name[…]select_statement;

2. 打开游标

   open cursor_name[…];

3. 提取游标数据

   fetch cursor_name into {};

4. 对游标指针指向的数据进行处理

5. 继续处理，直到没有记录可以处理为止

6. 关闭游标

   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;

十二. 嵌套表

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

   create or replace type family_type is table of varchar2(20);
   

2. 重新调整学生表，增加一列family_type类型的新的属性列

   alter table student add(
     family family_type
   )nested table family store as family_table;
   

3. 为嵌套表添加数据

   insert into student 
   values('95006','昭君',19,0,to_date('2006-09-01','yyyy-mm-dd'),'上海','CC102',family_type('父亲','母亲','姐姐'));
   

4. 需要检索嵌套表列的数据时，也需要定义嵌套表变量接收数据

   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;
   

5. 更新嵌套表列数据

   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操作），会发生一些事件，从而自动执行相应程序。
触发器分类：

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

2. 行触发器。

语句触发器：

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

案例：

--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 char(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.putline('--------------------------------------------');
    --取得当前班级的学生信息
    open student_cursor(p_clno);
    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
     ||',入学日期:'||tochar(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;

测试：

begin
  report_pack.student_of_class('cc101');
end;