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

–应用报表

–1、输入班级号，打印输出当前班级信息及当前班级中的学生信息
–创建报表打印的PL/SQL块如下：
–分析：
–一、根据要求，需要动态输入班级号，可使用带参数的游标；
–二、班级号是主键，可以用select into 语句直接获取班级信息;
–三、学生有多个，需要单独处理每个学生，可用游标；
–四、会存在班号输入错误，引入异常处理
–Oracle数据库中，所有的查询语句select的使用，
–系统都会产生相应的游标
–所有的游标分两类：隐式游标，显式游标需要通过游标类型变量的定义

declare
/*声明带参数的游标*/
cursor student_cursor(p_clno class.clno%type)
is
select * from student where clno=p_clno;
/*学生记录类型*/
student_record student%rowtype;
/*班级记录类型*/
class_record class%rowtype;
/*班级编号*/
v_clno class.clno%type;
/*班级人数*/
v_count number(2);
v_sex varchar2(2);
--报表块主体部分
begin
v_clno:=upper('&p_clno');
/*取得班级信息*/
select * into class_record from class where clno=v_clno;
/*取得班级人数*/
select count(*) into v_count from student 
where clno=v_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(v_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||',入学日期:'
  ||to_char(student_record.entrance,'yyyy-mm-dd'));
end loop;
  close student_cursor;
exception
  when no_data_found then
   dbms_output.put_line('指定的班级号不存在！');
end;

2、根据业务要求，需定义一个报表打印，要求输出所有课程的信息
(课程编号，课程名称，课程学分)；
并打印输出针对当前课程按照授课教师分组，所教学生的人数
以及学生的平均得分；
（教师号，教师姓名，学生人数，学生平均得分）
分析：

1. 需要输出所有课程的信息，涉及多条数据处理，需要引入游标
2. 再按照课程分组，输出每个教师所教学生的人数及平均得分时，
   需首先确定课程，分析一、中的数据**
3. 创建视图：教师号，教师姓名，学生人数，学生平均得分

解决方案：
1、创建视图，按课程和教师分组，统计每个教师所教学生人数、
其所负责课程名称及所教学生在此课程所得成绩的平均值。

create or replace view vw_course_teacher
as
select c.cno,t.tno,t.name tname,
count(*) stu_count,trunc(avg(st.grade),2) score_avg
from teacher t join st st on t.tno=st.tno
join course c on t.cno=c.cno
group by(c.cno,t.tno,t.name);

2、创建报表打印的PL/SQL如下：

declare
  /*课程游标*/
  cursor course_cursor is select cno,name,score from course;
 /*基于vw_course_teacher声明带参数的游标*/
  cursor vw_c_t_cursor(p_cno course.cno%type) is
  select * from vw_course_teacher
  where cno=p_cno;
  /*课程记录类型*/
  course_record course%rowtype;
  /*vw_course_teacher记录类型*/
  vw_c_t_record vw_course_teacher%rowtype;
  /*统计选择当前课程的人数*/
  v_stu_count int;
  /*统计选择当前课程的学生的平均分*/
  v_score_avg number(4,2);
begin
  open course_cursor;
  loop
    /*取得课程记录*/
    fetch course_cursor into course_record;
    exit when course_cursor%notfound;
    /*打印课程信息*/
    dbms_output.put_line('课程号:'||course_record.cno);
    dbms_output.put_line('课程名:'||course_record.name);
    dbms_output.put_line('总学分:'||course_record.score);
    dbms_output.put_line('');
    /*根据当前课程编号，打开基于vw_course_teacher的游标*/
    open vw_c_t_cursor(p_cno=>course_record.cno);
    v_stu_count:=0;
    v_score_avg:=0;
    /*打印报表表头*/
    dbms_output.put_line(LPAD('教师号',10,' ')
    ||LPAD('教师姓名',12,' ')
    ||LPAD('学生人数',12,' ')||LPAD('平均分',10,' '));
    loop
      fetch vw_c_t_cursor into vw_c_t_record;
      exit when vw_c_t_cursor%notfound;
      v_stu_count:=v_stu_count+vw_c_t_record.stu_count;
      v_score_avg:=v_score_avg+
      vw_c_t_record.score_avg*vw_c_t_record.stu_count;
      dbms_output.put_line(LPAD(vw_c_t_record.tno,10,' ')
      ||LPAD(vw_c_t_record.tname,12,' ')
      ||LPAD(vw_c_t_record.stu_count,12,' ')
      ||LPAD(vw_c_t_record.score_avg,10,' '));
    end loop;
    dbms_output.put_line(LPAD(RPAD(' ',22,'-'),44,' '));
    dbms_output.put_line('总计'||LPAD(v_stu_count,30,' ')
    ||LPAD(v_score_avg/v_stu_count,10,' '));
    close vw_c_t_cursor;
    dbms_output.put_line('');
    dbms_output.put_line(LPAD('- ',44,'- '));
  end loop;
  close course_cursor;
end;

异常处理
1、异常简介：在编写PL/SQL块时，应该捕捉并处理各种可能出现的异常。
如果不捕捉和处理异常，Oracle会将错误传递到调用环境，
整个程序运行自动终止，并不提示任何错误信息；
如果捕捉并处理异常，那么Oracle会在PL/SQL块内解决运行错误。
在PL/SQL，一般将异常处理部分放在PL/SQL程序体的后半部，其语法结构为：

exception
  when exception1 then
    <对于exception1的处理语句>
  when exception2 then
    <对于exception2的处理语句>
  when others then
    <对于其他异常的处理语句>
end;

Others不是必需的，但Others必须放在最后。
对两数相除可能出现的错误进行处理

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('除数不能为零！');
  when others then
     dbms_output.put_line('出现未知错误！');
end;

2、异常处理
Oracle中的异常包括预定义异常、非预定义异常和自定义异常三种类型
预定义异常

declare
  emp_record emp%rowtype;
begin
  select * into emp_record from emp where sal=&p_sal;
  dbms_output.put_line('雇员姓名：'||emp_record.ename||',工资：'||emp_record.sal);
  exception
    when no_data_found then
      dbms_output.put_line('不存在该工资的雇员！');
    when too_many_rows then
      dbms_output.put_line('该工资的雇员有多个！');
end;

由于经常对学生信息表进行操作，为方便管理和提高代码执行速度，
需要将学生信息表的增、删、改、查封装到包内使用子过程实现，
以方便应用程序JAVA调用。

分析

1. 使用包可以使程序设计模块化，而且可以提高程序的执行效率。
2. 对于学生信息增加操作，可使用记录类型作为输入参数的过程实现。
3. 对于查询操作，需要根据学生号查询学生信息，并能返回所有学生信息
4. 对于修改操作，需要能够根据指定的条件，动态构造修改语句，在Oracle中
   可以使用EXECUTE IMMEDIATE过程动态地执行本地SQL。

参考的解决方案
1、定义包规范如下：

create or replace package student_pack
is
  /*统计学生人数*/
  v_count int;
  /*增加学生信息*/
  procedure add_student(student_record student%rowtype);
  /*根据指定条件修改学生信息*/
  procedure update_student(p_modifiers varchar2,p_condition varchar2);
  /*根据学生号删除学生信息*/
  procedure del_student(p_sno student.sno%type);
  /*根据学生号查询学生信息*/
  function get_student(p_sno student.sno%type)
  return student%rowtype;
  /*定义基于记录类型的嵌套表*/
  type student_table_type is table of student%rowtype;
  /*获得所有学生的信息*/
  function get_student return student_table_type;
end student_pack;

2、对于student_pack包的包体代码如下：

create or replace package body student_pack
is
/*check_student是包体的私有子程序*/
 function check_student(p_sno student.sno%type)
 return boolean
 is
 v_count int;
 begin
   select count(*) into v_count from student where sno=p_sno;
   if v_count>0 then
      return true;
   else
      return false;
   end if;
 end check_student;
 /*实现add_student过程*/
 procedure add_student(student_record student%rowtype)
 is
 begin
   if check_student(student_record.sno)=false then
      insert into student
      values(student_record.sno,student_record.name,
      student_record.age,student_record.sex,
      student_record.entrance,student_record.address,
      student_record.clno,student_record.family);
      dbms_output.put_line('添加成功！');
   else
      dbms_output.put_line('添加失败：学生编号冲突！');
   end if;
   exception
     when others then
      dbms_output.put_line('添加错误：'||SQLCODE||'----'||SQLERRM);
 end add_student;
 /*实现update_student过程*/
 procedure update_student(p_modifiers varchar2,p_condition varchar2)
 is
 begin
 execute immediate 'update student'||'set'
 ||p_modifiers||'where'||p_condition;
 exception
   when others then
     dbms_output.put_line('修改出错：'||SQLCODE||'----'||SQLERRM);
 end;
 /*实现del_student过程*/
 procedure del_student(p_sno student.sno%type)
 is
 begin
   if check_student(p_sno)=true then
     delete from student where sno=p_sno;
     dbms_output.put_line('删除成功！');
   else
     dbms_output.put_line('删除失败：指定的学生不存在！');
   end if;
 exception
   when others then
     dbms_output.put_line('删除出错：'||SQLCODE||'----'||SQLERRM);
 end del_student;
 /*实现get_student函数*/
 function get_student(p_sno student.sno%type)
 return student%rowtype
 is
 student_record student%rowtype;
 no_result exception;
 begin
   if check_student(p_sno)=true then
     select * into student_record from student where sno=p_sno;
     return student_record;
   else 
     raise no_result;
   end if;
  exception
   when no_result then 
     raise_application_error(-20099,'查询的学生不存在');
   when others then
     /*输出错误编码和消息*/
     --dbms_output.put_line(SQLCODE||'----'||SQLERRM);
     raise_application_error(-20100,'查询出错');
 end get_student;
 /*实现get_student函数*/
 function get_student
 return student_table_type
 is
 student_table student_table_type;
 begin
   select * bulk collect into student_table from student;
   return student_table;
 end get_student;
 /*获取学生总人数*/
begin
 select count(*) into v_count from student;
end student_pack;
--用于测试student_pack功能的PL/SQL语句如下：
/*添加过程测试*/
declare
  student_record student%rowtype;
begin
student_record.sno:=&add_sno;
student_record.name:=&add_name;
student_record.age:=&add_age;
student_record.sex:=&add_sex;
student_record.entrance:=&add_entr;
student_record.address:=&add_addr;
student_record.clno:=&add_clno;
student_record.family:=&add_fami;
student_pack.add_student(student_record);
end;
/*删除过程测试*/
declare
student_record student%rowtype;
begin
student_record.sno:=&del_sno;
student_pack.del_student(student_record.sno);
end;

–包的调用

call student_pack.get_student('1462105'); 

–过程调用

 call zxx_package2.sal_pro1('CLERK',0.01);

–函数的调用

var vsal number;
call zxx_package2.avg_sal(10) into:vsal
select zxx_package2.avg_sal(10) from dual;

call student_pack.check_student('1462119');
declare
begin
 student_pack.get_student('1462119');
end;

var v_ck boolean;
call student_pack.check_student('1462140819') into:v_ck;
select student_pack.check_student('1462140819')  from dual;