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

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

1、学生信息查询页面：

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

create or replace view vw_class_studentasselect cl.dept,cl.grade,cl.branch,s.sno,s.name,s.age,s.sex,s.entrancefrom class cl,student swhere cl.clno=s.clnowith read only;

create or replace view vw_class_studentasselect cl.dept,cl.grade,cl.branch,s.sno,s.name,s.age,s.sex,s.entrancefrom class cl join student s on cl.clno=s.clnowith read only;

2、授课信息查询页面;

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

create or replace view vw_teacher_courseasselect t.tno,t.name tname,t.age,c.cno,c.name cnamefrom teacher t join course c on t.cno=c.cnowith read only; 

3、选课信息查询页面：

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

create or replace view vw_student_courseasselect distinct s.sno,s.name sname,c.cno,c.name cnamefrom student s join st st on s.sno=st.snojoin teacher t on st.tno=t.tnojoin course c on t.cno=c.cnowith read only;

4、任课教师查询页面：

–提供系、年级、班级及在当前班担当授课任务的教师姓名

create or replace view vw_class_teacheras select distinct cl.dept,cl.grade,cl.branch,t.namefrom class cljoin student s on cl.clno=s.clnojoin st st on s.sno=st.snojoin teacher t on st.tno=t.tnowith read only;

5、班级课程查询页面：

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

create or replace view vw_class_courseasselect distinct cl.dept,cl.grade,cl.branch,c.namefrom class cljoin student s on cl.clno=s.clnojoin st st on s.sno=st.snojoin teacher t on st.tno=t.tnojoin course c on t.cno=c.cnowith read only;

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

1、班级人数统计页面：
–按班级分组，统计每个班级学生人数；
–分析上述页面的要求，创建相应的统计视图

create or replace view vw_class_countasselect cl.dept 系,cl.grade 年级,cl.branch 班级,count(*) 学生人数from class cl,student swhere cl.clno=s.clnogroup by (cl.dept,cl.grade,cl.branch);

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

create or replace view vw_student_countasselect cl.dept 系,cl.grade 年级,cl.branch 班级,s.name 学生名,count(*) 课程数,sum(st.grade) 总学分from class cl join student s on cl.clno=s.clnojoin st st on s.sno=st.snogroup by (cl.dept,cl.grade,cl.branch,s.name);

3、创建选课统计页面需要的视图

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

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

create or replace view vw_student_gradeasselect 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.snojoin teacher t on st.tno=t.tnojoin course c on t.cno=c.cnowhere 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;beginselect name,family into v_name,family_tablefrom student where sno=&sno;dbms_output.put_line('学生'||v_name||'的亲属有：');for i in 1..family_table.countloopdbms_output.put_line(family_table(i)||'');end loop;dbms_output.new_line();exception    when no_data_found then     dbms_output.put_line('指定学生号不存在！');end;