PL/SQL Program that uses a Cursor to Rank

Question

I'm trying to use the SQL statement in here and put it into a cursor. Unfortunately, I am unable to use the Rank() function for this particular assignment.

CREATE or REPLACE PROCEDURE RankGPA
IS
  vSnum   student.snum%type;
  vSname  student.sname%type;
  vGPA    student.GPA%type;
  vMajor  student.major%type;
  CURSOR rankGPA_cursor IS
    SELECT s1.snum, s1.sname, s1.gpa, count( s2.gpa ) + 1 as rank, s1.major
    FROM students s1
    LEFT JOIN students s2
    ON s1.GPA < s2.GPA
    GROUP BY s1.snum, s1.sname, s1.gpa, s1.Major
    ORDER BY 4;
BEGIN 
  OPEN rankGPA_cursor;
  FETCH rankGPA_cursor INTO vSnum, vSname, vGPA, vMajor;
END;

I'm trying to get an output like this:

Rank SNUM  SNAME  GPA  MAJOR
**** ****  *****  ***  *****
1    101   Bob    4     ENGR
2    102   Cari   3.5   ENGL

Answer 1

The phrasing in your comment is a bit confusing, but it sounds like they want you to have a local variable to act as the 'rank' by counting the rows as they are fetched, something like:

DECLARE
  counter pls_integer := 0;
  CURSOR rankGPA_cursor IS
    SELECT s.snum, s.sname, s.gpa, s.major
    FROM students s
    ORDER BY s.gpa DESC;
BEGIN 
  FOR rankGPA_row IN rankGPA_cursor LOOP
    counter := counter + 1;
    dbms_output.put_line(counter
      ||' '|| rankGPA_row.snum
      ||' '|| rankGPA_row.sname
      ||' '|| rankGPA_row.gpa
      ||' '|| rankGPA_row.major
    );
  END LOOP;
END;
/

Each time the loop the counter variable is increased. Because the cursor query is ordered by GPA descending the first row fetched has the highest GPA and the counter is 1 at that point.

Using dbms_output for this is bad practise as in the real world you won't know if the client has that enabled, and you have to do some work to format the printed results neatly. But then this is contrived anyway.

You also need to consider how you want to rank things if two students have the same GPA, which seems likely. This will currently rank tied students arbitrarily. You could change the order by clause to add, say, secondary sorting by name; but students with tied scores will still get different ranks. It's certainly possible to give them the same rank even with this method, by keeping track of the last-seen GPA and only incrementing the rank/counter if the new row's value is different form the previous one. I don't know what you're expected to produce though.

You can use any style of cursor for this; explicit or implicit, and with the cursor-for-loop or with explicit open-fetch in a loop-close if you prefer.