Oracle代写：CS6024-Dynamic-SQL

发表于 | 分类于
这次需要代写的SQL需求可以二选一，将所给代码补充完整即可。

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

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10
11
12
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
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

1
2
3
4
5
6
7
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
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:

1
2
3
4
5
6
7
8
9
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.