Introduction
这次需要代写的SQL作业可以二选一，将所给代码补充完整即可。
Option 1 (for regular credit): Given the following package, including the
procedure, as named:
CREATE OR REPLACE PACKAGE project_pkg
AS
TYPE namePriceRecType IS RECORD(
pname Products.Product_Name%TYPE,
pprice Products.Unit_Price%TYPE);
TYPE tblNamePriceType IS TABLE
INDEX BY BINARY_INTEGER;
PROCEDURE Top_Ten (tblNamePrice IN OUT tblNamePriceType);
END project_pkg;
—|—
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
Option 2 (for 10% extra credit): Given the following package, including
the function, as named:
CREATE OR REPLACE PACKAGE project_pkg
AS
TYPE namePriceRecType IS RECORD(
pname Products.Product_Name%TYPE,
pprice Products.Unit_Price%TYPE);
TYPE tblNamePriceType IS TABLE OF namePriceRecType
INDEX BY BINARY_INTEGER;
FUNCTION Top_Selected (top NUMBER) RETURN tblNamePriceType;
END project_pkg;
—|—
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:
CREATE OR REPLACE PACKAGE project_pkg
AS
TYPE namePriceRecType IS RECORD(
pname Products.Product_Name%TYPE,
pprice Products.Unit_Price%TYPE);
TYPE tblNamePriceType IS TABLE OF namePriceRecType
INDEX BY BINARY_INTEGER;
PROCEDURE Rank_Selected(top NUMBER, dir IN VARCHAR2,
tblNamePrice IN OUT tblNamePriceType);
END project_pkg;
—|—
or Option 3f (also an additional 10% extra credit): the following package for
a function, as named:
CREATE OR REPLACE PACKAGE project_pkg
AS
TYPE namePriceRecType IS RECORD(
pname Products.Product_Name%TYPE,
pprice Products.Unit_Price%TYPE);
TYPE tblNamePriceType IS TABLE OF namePriceRecType
INDEX BY BINARY_INTEGER;
FUNCTION Rank_Selected(top NUMBER, dir IN VARCHAR2) RETURN tblNamePriceType;
END project_pkg;
—|—
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.
IMPORTANT
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
YourLastNameFirstNameProjectOption#.zip
e.g., for a student named Ima Skolar doing option 3 as a function, it would be
SkolarImaProjectOption3f.zip
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.
