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

一. 引言

要求：

Oracle系统案例——学生信息管理系统

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

背景：

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

需求：

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

功能描述：

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

详细信息描述如下：

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

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

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

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

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

各实体之间关系如下：

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

二. 概念模型设计阶段

分析业务实体： 4个实体：教师、班级、学生和课程。
分析业务实体之间的关系、清除业务实体的数据冗余：从题目分析学生和课程、教师和课程、班级和学生之间存在实体关联：学生-课程（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;--建表空间llfcreate tablespace llf datafile d:llf.dbfsize 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、学生选课表stcreate 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 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语句都有对应的游标。

游标分两类：

隐式游标：处理单行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_classbefore insert or update or delete on classdeclare  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_beforebefore insert or update or delete on classfor each rowdeclare  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 classset 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;

测试：

begin  report_pack.student_of_class(CC101);end;