查询班级的学生信息
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;
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;
解决方案 »
- 求Oracle用SQL语句或命令行l删除 某个数据库实例
- oracle sql groupby后 无记录 怎么使count()=0
- 求教Oracle矩阵报表乱码问题
- 高分求助!sql文,向表中插入数据,如果主键(PK)冲突,则执行更新操作,这个sql怎么写啊!谢谢
- 问个触发器里面变异(变化)表的概念问题~~(新人求助贴,请大家帮忙)
- 在oem里有没有像创建表一样的可视化界面对创建好的表进行修改?就像sqlserver里的一样?
- 如何使用C#上传图片到ORACLE中,然后在需要的时候取出呢?谢谢(目前只有这么多分分勒,有了再加)
- 又要救命了!!永久改变日期格式问题!
- decode函数的问题,大家帮帮忙
- 如何查看当前用户是否有创建调试调用存储过程的权限?
- 创建临时表空间时指定tempfile和datafile有什么不同
- 求以下SQL分析
一个自定义的存储过程返回类型,游标类型:
如:
CREATE OR REPLACE PACKAGE return_tableList AS
TYPE list_cursor IS REF CURSOR;
END return_tableList;
type TEST_PACKAGE.Test_CURSOR is ref cursor;