[*用Oracle来创建学生信息管理系统(续1)*]

–在学生信息管理系统-教学模块的开发过程中， –需要实现以下数据查询页面（视图）：

1、学生信息查询页面：

–提供系、年级、班级、学号、学生姓名、年龄、性别、入学日期；

create or replace view vw_class_student
as
select cl.dept,cl.grade,cl.branch,s.sno,s.name,s.age,s.sex,s.entrance
from class cl,student s
where cl.clno=s.clno
with read only;

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

2、授课信息查询页面;

–提供教师号、姓名、年龄、及其所任课程编号和名称；

create or replace view vw_teacher_course
as
select t.tno,t.name tname,t.age,c.cno,c.name cname
from teacher t join course c on t.cno=c.cno
with read only; 

3、选课信息查询页面：

–提供学号、学生姓名、所选课程编号、课程名称；

create or replace view vw_student_course
as
select distinct s.sno,s.name sname,c.cno,c.name cname
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 distinct cl.dept,cl.grade,cl.branch,t.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
with read only;

5、班级课程查询页面：

–提供系，年级，班级及当前班所覆盖的课程名称

create or replace view vw_class_course
as
select distinct 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,student s
where 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、创建选课统计页面需要的视图

create or replace view view_course_count
as
select c.name 课程名,c.score 学分,count(*) 选修人数,
       count(distinct t.tno) 教师数,avg(st.grade) 平均分
from course c join teacher t on c.cno=t.cno
join st st on t.tno=st.tno
group by(c.name,c.score);

4、创建学生成绩列表页面需要的视图

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定义行记录类型
–通过使用%rowtype定义班级表（class）的记录类型，在PL/SQL中实现
–对班级表的数据添加、删除、修改操作。

declare
/*使用%rowtype声明class的记录类型变量*/
class_record class%rowtype;
row_id rowid;
info varchar2(60);
begin
  class_record.clno:='1410';
  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);
end; 

declare
/*使用%rowtype声明class的记录类型变量*/
class_record class%rowtype;
row_id rowid;
info varchar2(60);
begin
  class_record.clno:=&no;
  class_record.dept:='软件工程';
  class_record.grade:='大四';
  class_record.branch:='一班';
  /*基于记录类型变量数据删除*/
  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 no_data_found then
   dbms_output.put_line('出现某种异常');
end; 

–嵌套表列

create or replace type family_type is table of varchar2(8);
create or replace type family_t_type is table of varchar2(10);

–重新调整教师表

alter table teacher add(
family_t family_t_type
)nested table family_t store as family_t_table;

–为嵌套表添加数据

insert into teacher values('T8107','王进',40,'CN001',
family_t_type('父亲','母亲','丈夫','儿子'));

–重新调整学生表（student）

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

–为嵌套表添加数据

insert into student values(
'1362042301','杨燕',15,'0','14-5月-2016','山东','1423',
family_type('父亲','母亲','弟弟'));
insert into class values('1625','软件工程','大四','五班');
insert into student values(
'1362042420','贾波',21,'2','14-3月-2014','山西','1424',
family_type('父亲','母亲','姐姐'));

–有关事务
–设置保存点 savepoint
–提交事务 commit
–回滚 rollback to
–savepoint a; rollback to a;

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;