Lab 4: Stored Procedures
Started: Thursday, 4/14; Due: Thursday 4/21, by the beginning of class
In this lab, you will create a stored procedure named RANK_BEERS, written in PL/SQL, for adding ranks to a table of beers. You will be using the Beers table that I showed in class on Tuesday, which is a slight modification of the one Max used two years ago. It has a ranks column which can hold an integer, but is currently null. Your task will be to wrote a procedure which does this ranking using one of the rank definitions given below.
- The examples we worked through in class on Tuesday, including the table definition and initial data for the Beers table, are available at Stored Procedures. I'd recommend that you try out one or two of the procedures given there to see how to define procedures in SQL Developer.
- The Oracle documentation has a link titles PL/SQL User's Guide and Reference.
- Ullman has a web page describing Oracle PL/SQL at Oracle PL/SQL summary.
- Two of the Oracle SQL books in the monitor room have a chapter devoted to PL/SQL.
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.
Alternative definitions of "rank"
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.
What to turn in
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 on the initial data. Keep in mind that I will test it on other data as well.
This extra-credit will require yo to learn about triggers and how they are implemented in Oracle. 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?)