Oracle实验七 包

Oracle实验七 包

第1关：包1

一、创建包pks1，包头中定义一个存放学号的变量（如vsno）和一个静态游标，查询学号变量对应的学生的选课信息，列出学号，姓名，课程名，成绩，并按成绩降序排列。

1、在包中创建过程ps1，传入系，将该系平均分最高的学生的学号赋予包头中的学号变量（vsno）（这里假设每个系平均分最高的学生只有一个），若找不到平均分最高的学生，则判断系是否存在，若系不存在，则给学号变量赋值‘-1’，若系存在（该系的学生没有选课），则给学号变量赋值‘-2’。
2、在包中创建函数fs1，传入学号，返回该学生获得的总学分。（必须要成绩及格才能获得对应课程学分）
要求：
1、不能在包中使用dbms_output.put_line打印数据。
2、通过匿名块多次调用pks1包中的ps1，传入‘CS’，‘Es’，‘IOS’系，然后判断pks1.vsno的值，若不是‘-1’或‘-2’，则遍历包头中的游标，将查询到的学生选课信息打印出来（学号，姓名，课程名分别占10个长度），并且调用包中的函数fs1，传入该学号，获取该生获得的总学分，打印格式如下（1）所示。若pks1.vsno的值是‘-1’，则打印格式如下（2）所示。若pks1.vsno的值是‘-2’，则打印格式如下（3）所示。
输出格式说明：
(1)若找到了平均分最高的学生（冒号为中文状态下的冒号）：
学号 姓名 课程名 成绩
==================================（打印35个等号）
006 张三 数据库 99
006 张三 C语言 98
006 张三 数据结构 55
==================================（打印35个等号）
006（pks1.vsno的值）号同学获得的总学分为：8（函数返回的学分）

(2)系不存在（pks1.vsno的值为-1）：
（传入的系）系不存在。
例如：CS系不存在。

(3)若系存在，但该系的学生没有选课（pks1.vsno的值为-2）
（传入的系）系的同学没有选课记录。
例如：CS系的同学没有选课记录。

--***********1、完成包头的创建****************
create or replace package pks1 is
vsno Student.sno%type;
cursor c1 is select Student.sno,sname,cname,grade from Student,Course,SC 
where Student.sno=SC.sno and SC.cno=Course.cno and SC.sno=vsno order by grade desc;
procedure ps1(vsdept Student.sdept%type);
function fs1(vsno Student.sno%type) return int;
end pks1;
--***********End*******************************************
/
--***********2、完成包体的创建*****************
create or replace package body pks1 is
procedure ps1(vsdept Student.sdept%type)
is
cnt int;
cnt1 int;
begin
select count(*) into cnt from Student where sdept=vsdept;
if (cnt=0) then
vsno:='-1';
else 
begin
    select count(*) into cnt1 from SC where sno in(select sno from Student where sdept=vsdept);
    if(cnt1=0) then
    vsno:='-2'; 
    else
    select sno into vsno from SC where sno in(select sno from Student where sdept=vsdept) group by sno 
    having avg(grade)>=all(select avg(grade) from SC where sno in(select sno from Student where sdept=vsdept) group by sno);
    end if;
    end;
end if;
end ps1;
function fs1(vsno Student.sno%type) return int
is
sumccredit int;
begin
select sum(ccredit) into sumccredit from Course,SC where SC.cno=Course.cno
and sno=vsno and grade>=60;
return sumccredit;
end fs1;
end pks1;
--***********End******