创建一个带输入参数的存储过程,输入分数参数,执行存储过程得到平均分大于该分 数
的学生统计成绩信息(包括学号,姓名,平均分,课程门数字段)表结构:
StudName
StudNo
StudSex
CourseID
StudScore先谢了!
的学生统计成绩信息(包括学号,姓名,平均分,课程门数字段)表结构:
StudName
StudNo
StudSex
CourseID
StudScore先谢了!
调试欢乐多
is
begin
for i in (select studname,studno,avg(studscore) a,count(courseid) b
from t group by studname,studno having avg(studscore) > v_avg)
dbms_output.put_line(i.studname||' : '||i.studno||' : '||i.a||' : '||i.b);
end pro;
from tab
group by studno,studname
having avg(studscore)>'参数'
as
begin
for cur1 in (select * from (select t.*,avg(studscore)over(partition by studno)ag from score t)
where ag>p_score)
loop
dbms_output.put_line(cur1.studname||' '||cur1.studno||' '||cur1.studsex||' '||cur1.courseid||' '||cur1.studscore);
end loop;
end proc;
#1首尾加
loop
end loop;写的太快没测试
1 create or replace procedure pro(v_avg in number)
2 is
3 begin
4 for i in (select studname,studno,avg(studscore) a,count(courseid) b
5 from t group by studname,studno having avg(studscore) > v_avg)
6 dbms_output.put_line(i.studname||' : '||i.studno||' : '||i.a||' : '||i.b);
7* end pro;
8 /警告: 创建的过程带有编译错误。
出现上面的错误了
is
begin
for i in (select studname,studno,avg(studscore) a,count(courseid) b
from t group by studname,studno having avg(studscore) > v_avg)
loop
dbms_output.put_line(i.studname||' : '||i.studno||' : '||i.a||' : '||i.b);
end loop;
end pro;
is
begin
for i in (select studname,studno,avg(studscore) a,count(courseid) b
from t group by studname,studno having avg(studscore) > v_avg) loop
dbms_output.put_line(i.studname||' : '||i.studno||' : '||i.a||' : '||i.b);
end loop;
end pro;
/
1 create or replace procedure pro(v_avg in number)
2 is
3 begin
4 for i in (select studname,studno,avg(studscore) a,count(courseid) b
5 from t group by studname,studno having avg(studscore) > v_avg)
6 loop
7 dbms_output.put_line(i.studname||' : '||i.studno||' : '||i.a||' : '||i.b);
8 end loop;
9* end pro;
SQL> /警告: 创建的过程带有编译错误。
把from后面的t换成你的表名在编译后执行时打开set serveroutput on