Pl/SQL过程将最高GPA排名到最低 [英] Pl/SQL procedure to rank highest GPA to lowest

问题描述

所以我试图编写一个过程，该过程将使用最高的GPA = 1排名最高，依此类推.我被困的地方是

so I am trying to write a procedure that will rank the highest GPA= 1 and so on using a cursor. Where i am stuck is

declare
 snum students.snum%type;
 sname students.sname%type;
 GPA students.GPA%type;
begin 
 for Rec in 
   (select snum,sname,GPA 
      from students
      order by GPA desc)loop
  dbms_output.put_line(rec);
  end loop;
end;

我被卡在循环中(现在它只是一个占位符).我不确定设置每个GPA等级的正确方法.

I get stuck here in the loop(right now its just a place holder). I am not sure the correct way to set each GPA's rank.

推荐答案

oracle中有RANK函数. 在此处查看更多信息.

There is RANK function in oracle. See more here.

示例:

declare
 v_snum students.snum%type;    --Avoid to use "variable name" as same as "column name"
 v_sname students.sname%type;
 v_GPA students.GPA%type;
begin 
 for rec in 
   (select snum,
           sname, 
           GPA, 
           RANK() OVER (ORDER BY GPA desc) as srank
      from students)
 loop
  dbms_output.put_line(rec.snum  || '|' || rec.sname || '|' || rec.GPA || '|' || rec.srank);
 end loop;
end;

这篇关于Pl/SQL过程将最高GPA排名到最低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！