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

–应用报表

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

declare/*声明带参数的游标*/cursor student_cursor(p_clno class.clno%type)isselect * 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);--报表块主体部分beginv_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);loopfetch 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_teacherasselect c.cno,t.tno,t.name tname,count(*) stu_count,trunc(avg(st.grade),2) score_avgfrom teacher t join st st on t.tno=st.tnojoin course c on t.cno=c.cnogroup 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;beginv_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_packis  /*统计学生人数*/  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_packis/*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;beginstudent_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;/*删除过程测试*/declarestudent_record student%rowtype;beginstudent_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:vsalselect zxx_package2.avg_sal(10) from dual;

call student_pack.check_student('1462119');declarebegin 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;