Create the package body, which will define a cursor to select the product name and unit price from the Products table in order by descending unit price. After opening the cursor, fetch each item from the cursor into the table (which was passed as a parameter) until you either run out of items or reach a maximum of 10. Properly end the procedure.
Create an anonymous script that declares the table variable needed to be able to retrieve the table that the procedure has built. Obtain the record count of the table. Loop from the beginning for this record count and display the sequence number, name, and price of the values in the retrieved table. Display “End of data in table” at the end of all retrieved values.
Your output should look like this:
In routine, Rec Count: 10 #:1 Cote de Blaye 263.5 #:2 Thuringer Rostbratwurst 123.79 #:3 Mishi Kobe Niku 97 #:4 Sir Rodney's Marmalade 81 #:5 Carnarvon Tigers 62.5 #:6 Raclette Courdavault 55 #:7 Manjimup Dried Apples 53 #:8 Tarte au sucre 49.3 #:9 Ipoh Coffee 46 #:10 Rossle Sauerkraut 45.6 End of data in table
Create the package body, in which the function will do the same as the above procedure, except that being a function, it will obtain the “maximum number of results to return” as a parameter (top) and will return the same kind of table. If the calling script passed 10 to this function, the displayed result would be the same as option 1. However, if the calling script were to pass 5, the displayed result would be
In routine, Rec Count: 10 #:1 Cote de Blaye 263.5 #:2 Thuringer Rostbratwurst 123.79 #:3 Mishi Kobe Niku 97 #:4 Sir Rodney's Marmalade 81 #:5 Carnarvon Tigers 62.5 End of data in table
Thus, with this function, the user (calling script) can elect the maximum number of results to be returned. Be sure to capture several sets of output, each showing different results.
Option 3p (for an additional 10% extra credit): Choose the following package for a procedure, as named:
or Option 3f (also an additional 10% extra credit): the following package for a function, as named:
TYPE namePriceRecType ISRECORD(
TYPE tblNamePriceType IS TABLE OF namePriceRecType
INDEX BY BINARY_INTEGER;
FUNCTION Rank_Selected(top NUMBER, dir IN VARCHAR2) RETURN tblNamePriceType;
Use dynamic SQL which will obtain multiple rows in a query, allow the user to supply an order (ASC or a null value for ascending, DESC for descending), in addition to the maximum limit as described above.
Again, with this routine, the user (calling script) can select not only the maximum number of results returned, but also the sort order. Be sure to capture several sets of output, each showing illustrating combinations of results.
Save your anonymous script source as Project1.sql, Project2.sql, or Project3.sql. Be sure to use the same procedure or function name as given in the assignment.
Submit your package header, package body, and anonymous script along with screen capture(s) of your output. Place into a compressed folder named as
e.g., for a student named Ima Skolar doing option 3 as a function, it would be
Note: the above instructions give you one of four possible options:
- Do the assignment as a normal procedure
- Do the assignment as a function (10% extra credit)
- Do the assignment as a dynamic SQL procedure (10% extra credit)
- Do the assignment as a dynamic SQL function (20% extra credit)
Please note - your chosen option must work for credit.