oracle中创建一个存储过程输入班级和科目，就可输出该科目的各分数段人数

Question

有表 学生号 学生名 班级 a成绩 b成绩 c成绩
输入班级和科目，就可以输出该科目90以上，80~90等个分数段的人数
求教啊 急急急

Answer 1

select 成绩表.班级，成绩表.学科, 等级表.level ,count(成绩表.学生号)
from 成绩表,
(select 90 start,100 end,1 level from dual
union all
select 80 start,90 end,2 level from dual
union all
select 70 start,80 end,3 level from dual
union all
select 0 start,70 end,4 level from dual
) 等级表
where 成绩表.成绩>=等级表.start
and 成绩表.成绩<等级表.end
and 成绩表.班级=输入条件
and 成绩表.学科=输入条件
group by 成绩表.班级，成绩表.学科, 等级表.level

Answer 2

传入的两个参数分别为班级替换下面where条件中的班级传值，科目在执行sql前判断，如果传入科目a，那么执行下面这段sql，如果传入科目b，那么执行sql中将grade_a替换为grade_b即可：
SELECT CASE WHEN GRADE_A >=90 THEN COUNT(1) ELSE 0 END 科目a90以上人数,
CASE WHEN GRADE_A >=80 AND grade_a >90 THEN COUNT(1) ELSE 0 END 科目a8090分人数, CASE WHEN GRADE_A >=70 AND grade_a >80 THEN COUNT(1) ELSE 0 END 科目a7080分人数, CASE WHEN GRADE_A >=60 AND grade_a >70 THEN COUNT(1) ELSE 0 END 科目a6070分人数, CASE WHEN GRADE_A <60 THEN COUNT(1) ELSE 0 END 科目a60以下人数 from b WHERE CLASS = 'BANJI1' GROUP BY grade_a;本回答被提问者和网友采纳

Answer 3

create table stu_score(id varchar2(10) primary key,stu_no varchar2(10),stu_name varchar2(20),stu_grade varchar2(10),score_a number(3),score_b number(3),score_c number(3));
create or replace procedure stu_score_pro(stu_grade in varchar2,score_a in number,ret out varchar2)as v_ret_80 varchar2(100); v_ret_90 varchar2(100); v_ret varchar2(100); begin select sum(nvl(decode(score_a,1,score_a,2,score_b,score_c),0)) into v_ret_90 from stu_score where stu_grade=stu_grade and decode(score_a,1,score_a,2,score_b,score_c)>=90; select sum(nvl(decode(score_a,1,score_a,2,score_b,score_c),0)) into v_ret_80 from stu_score where stu_grade=stu_grade and decode(score_a,1,score_a,2,score_b,score_c)>=80 and decode(score_a,1,score_a,2,score_b,score_c)<90; --其他分数段 ret := '90:'||v_ret_90||' 80-90'||v_ret_80; exception when others then ret := 'error';end;
具体的表和字段看你的情况