查询班级的学生信息
CREATE OR REPLACE PROCEDURE getstuclass(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR
select class.cid, class.cname from student,grade,class,teacher ,courses where student.sid=grade.sid and teacher.tid=courses.coteacher and courses.coid=grade.coid and class.cid=student.sclass and teacher.tid=n;
END getstuclass;
查询班级信息
CREATE OR REPLACE PROCEDURE getclassinfo(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2,m in varchar2)
IS
BEGIN
OPEN test_CURSOR FOR
select class.cname,student.sname,grade.score from student,grade,class,teacher,courses where student.sid=grade.sid and teacher.tid=courses.coteacher and courses.coid=grade.coid and class.cid=student.sclass and teacher.tid=n and class.cid=m order by grade.score desc;
END getclassinfo;
查询不及格学生
CREATE OR REPLACE PROCEDURE getstunopass(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR
select student.sname, grade.score from student,grade,class,teacher ,courses where student.sid=grade.sid and teacher.tid=courses.coteacher and courses.coid=grade.coid and class.cid=student.sclass and teacher.tid=n and grade.score<60;
END getstunopass;
查询教师信息
CREATE OR REPLACE PROCEDURE gettea(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR SELECT * FROM teacher where tid=n;
END gettea;
查询所教科目的平均成绩最高成绩以及最低成绩
CREATE OR REPLACE PROCEDURE getangrade(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR
select courses.coname, avg(grade.score),max(grade.score),min(grade.score) from grade,teacher,courses where teacher.tid=courses.coteacher and courses.coid=grade.coid and teacher.tid=n;
END getangrade;
查询学生成绩及信息
CREATE OR REPLACE PROCEDURE getstu(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR SELECT student.sid,student.sname,grade.coid,courses.coname,courses.coteacher,teacher.tname,grade.score FROM courses,student,grade,teacher where student.sid=grade.sid and grade.coid=courses.coid and courses.coteacher=teacher.tid and tid =n;
END getstu;
查询学生平均成绩
CREATE OR REPLACE PROCEDURE getstuavg(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR
select class.cname, avg(grade.score) from student,grade,class,teacher ,courses where student.sid=grade.sid and teacher.tid=courses.coteacher and courses.coid=grade.coid and class.cid=student.sclass and teacher.tid=n group by class.cname;
END getstuavg;
查询最高分
CREATE OR REPLACE PROCEDURE getstucountbest(test_CURSOR out TEST_PACKAGE.Test_CURSOR,n in varchar2) IS
BEGIN
OPEN test_CURSOR FOR
select class.cname, avg(grade.score) from student,grade,class,teacher ,courses where student.sid=grade.sid and teacher.tid=courses.coteacher and courses.coid=grade.coid and class.cid=student.sclass and teacher.tid=n and grade.score>90 group by class.cname;
END getstucountbest;
修改成绩
CREATE OR REPLACE PROCEDURE setstuinfo(stuid in varchar2,corsesid in varchar2,stuscore in number) IS
BEGIN
update grade set score=stuscore where sid=stuid and coid=corsesid;
END setstuinfo;
修改教师信息
CREATE OR REPLACE PROCEDURE setteainfo(teaid in varchar2,teaname in varchar2,tealocation in varchar2,teacode in varchar2) IS
BEGIN
update teacher set tname=teaname,tdegree=tealocation,code=teacode where tid=teaid;
END setteainfo;
------解决方案--------------------
TEST_PACKAGE.Test_CURSOR
一个自定义的存储过程返回类型，游标类型：
如：
CREATE OR REPLACE PACKAGE return_tableList AS
TYPE list_cursor IS REF CURSOR;
END return_tableList;