Oracle代写:CS6024 Dynamic SQL

Introduction

这次需要代写的SQL作业可以二选一,将所给代码补充完整即可。

Option 1 (for regular credit): Given the following package, including the procedure, as named:

1
2
3
4
5
6
7
8
9
CREATEORREPLACEPACKAGE project_pkg
AS
TYPE namePriceRecType ISRECORD(
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:

1
2
3
4
5
6
7
8
9
CREATEORREPLACEPACKAGE project_pkg
AS
TYPE namePriceRecType ISRECORD(
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:

1
2
3
4
5
6
7
8
9
10
CREATEORREPLACEPACKAGE project_pkg
AS
TYPE namePriceRecType ISRECORD(
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:

1
2
3
4
5
6
7
8
9
CREATEORREPLACEPACKAGE project_pkg
AS
TYPE namePriceRecType ISRECORD(
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:

  1. Do the assignment as a normal procedure
  2. Do the assignment as a function (10% extra credit)
  3. Do the assignment as a dynamic SQL procedure (10% extra credit)
  4. Do the assignment as a dynamic SQL function (20% extra credit)

Please note - your chosen option must work for credit.