/*按照成绩,分课程的名次*/create or replace procedure pro_training ascursor c_training is select count(*) count,course from training group by course;begin for c1 in c_training loop declare cursor c_grade is select grade, badge from training a where a.course = c1.course order by grade desc; v_num number(2):=1; begin for c2 in c_grade loop update training set order1 = v_num where training.badge = c2.badge; v_num:= c_grade%rowcount+1; end loop; end; end loop; end pro_training; / 执行: execute pro_trining commit /--得到结果,但是相同分数的名次不一样
2、re竟然是char型,排序易出错。
for c1 in c_training loop
declare
cursor c_grade is select grade, badge from training a where a.course = c1.course order by grade desc;
v_num number(2):=1;
begin
for c2 in c_grade loop
update training set order1 = v_num where training.badge = c2.badge;
v_num:= c_grade%rowcount+1;
end loop;
end;
end loop;
end pro_training;
/
执行:
execute pro_trining
commit
/--得到结果,但是相同分数的名次不一样