oracle利用游标cursor进行成绩排名

ba9vojjp3j

create table tb_score
(
id number(10) not null,
sid varchar2(20) not null,
chinese number(6,2),
maths number(6,2),
english number(6,2),
rank number(10)
);　　insert into tb_score values(1,'s0001',100,89,99,null);
insert into tb_score values(2,'s0002',66,58,24,null);
insert into tb_score values(3,'s0003',99,70,33,null);
insert into tb_score values(4,'s0004',46,78,88,null);
insert into tb_score values(5,'s0005',88,89,99,null);
　　create or replace procedure proc_upd_rank
as
begin
--定义游标
declare cursor cur_rank is
select * from tb_score for update;
--定义总分数
totalScore number(10,2);
--定义名次
v_rank number(10);
begin
for r in cur_rank loop
--获取总分
totalScore:=r.maths+r.chinese+r.english;
--获取名次
select count(*) into v_rank from tb_score
where chinese+maths+english>totalScore;
v_rank:=v_rank+1;
update tb_score set rank =v_rank where current of cur_rank;
end loop;
end;
end;
　　
SQL> call proc_upd_rank();
　　Method called
　　SQL> select * from tb_score;
　　ID SID CHINESE MATHS ENGLISH RANK
----------- -------------------- -------- -------- -------- -----------
1 s0001 100.00 89.00 99.00 1
2 s0002 66.00 58.00 24.00 5
3 s0003 99.00 70.00 33.00 4
4 s0004 46.00 78.00 88.00 3
5 s0005 88.00 89.00 99.00 2