In this lab, you will create a stored procedure named RANK_BEERS, written in PL/SQL, for adding ranks to a table of beers. I've modified slightly the BEERS table we used in earlier demos; beyond bringing it up to date, I added a RANK column, which can hold an integer but is currently NULL in each row. You should start by making your own copy of this table as follows:
create table beers as select * from max.beers;
Your procedure will use a cursor to iterate through the rows of the table in order of decreasing rating. You must obtain any information you need by fetching it from one row at a time (the cursor's row) while looping, not by doing any other querying of the table. In the loop body, you will also update the cursor's row to include the rank you have calculated. This requires that when you declare the cursor, you include FOR UPDATE at the end of the declaration. Suppose you declare the cursor as BEERS_CURSOR. Then in the loop body, you would use an UPDATE statement that specifies the row to update using WHERE CURRENT OF BEERS_CURSOR, rather than the sort of WHERE clause we saw in Chapter 6.
Although you are definitely computing ranks and putting them into the table, there are three possible alternatives for what constitutes a rank. You are welcome to choose any of these three, but you will only get full credit for option 3; option 2 will earn at most 95% and option 1 will earn at most 80%.
You could give the first beer encountered by the loop the rank 1, the second one rank 2, etc. Because some beers have equal ratings, even though the loop works in order of decreasing ratings, its order is not completely specified. For example, my table contains two beers with the top rating (100). Either of these might be assigned the rank of 1 and the other the rank of 2.
You could give equal ranks to all tied beers. For example, both of my top-rated beers would get rank 1, and the beers with the next highest rating (99) would all be ranked as 3, because there are two higher-rated beers.
You could give equal ranks to all tied beers and moreover base the rankings on individual bottles rather than merely beer varieties. (That is, you would take the quantities into account.) The two beers rated 100 would still be ranked as 1. But because I've got two bottles of one of them (and one of the other), the various beers rated 99 would now be down at rank 4, because there are 3 higher-rated bottles.
You should turn in the PL/SQL code of your procedure. You should also turn in testing results, namely, the result of the query
select * from beers order by rank;
performed after running your procedure
You can create a trigger that runs after any insert or delete on BEERS as well as after any update of the QUANTITY or RATING column. The body of the trigger should consist of a call to your RANK_BEERS procedure. You should be able to verify that this automatically keeps the ranks up to date when the table is modified. (Think about why it is important to limit the update triggering to specific columns. What would happen if the trigger ran after all updates to the table?)