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

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