Stored Procedure/User Defined Functions Question. Please use Stored Procedure or User Defined FUnctions to solve the following problems.

/*

1) Develop a PL/SQL program that passes database name as parameter to a stored procedure and displays the following: List of all tables (name the store procedure: ALL_TABLES_selectlist) (12.5 points)

*/

/*

2) Develop a PL/SQL program that passes database name and table name as parameters to a stored procedure and displays the following: List of all columns of the table (name the store procedure: ALL_TAB_COLUMNS_selectlist) (12.5 points)

*/

/*

3)

A. The SUB_TOTAL column in ORDERS table are null. Your task is to develop a stored procedure or user defined function that will receive order number as parameter and calculates the SUB_TOTAL column of the ORDERS table using the following formula: SUM (QTY * UNIT_PRICE)

Procedure Name: UDP_SUB_TOTAL or Function Name: UDF_SUB_TOTAL) (12.5 points)

*/

/*

B. The TAX_AMOUNT column in ORDERS table are null. Your task is to develop a stored procedure or user defined function that will receive order number as parameter and calculates the TAX_AMOUNT column of the ORDERS table using the following formula:

IF TAX_STATUS = Y THEN (SUB_TOTAL * 0.085)

IF TAX_STATUS = N THEN 0

Procedure Name: UDP_TAXAMT_TOTAL or Function Name: UDF_ TAXAMT_TOTAL (12.5 points)

*/

/*

C. The SHIPPING_TOTAL column in ORDERS table are null. Your task is to develop a stored procedure or user defined that will receive order number as parameter and calculates the SHIPPING_TOTAL column of the ORDERS table using the following formula: (SUB_TOTAL * 0.10)

Procedure Name: UDP_SHIPPING_TOTAL or Function Name: UDF_ SHIPPING_TOTAL (12.5 points)

*/

**************************************************************************************************************************

Please use the database from here

--alter session set "_oracle_script"=true;

--CREATE USER salesdb IDENTIFIED BY salesdb DEFAULT TABLESPACE users;

--GRANT ALL PRIVILEGES TO salesdb;

SET SERVEROUTPUT ON

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE ORDERLINE CASCADE CONSTRAINTS';

EXECUTE IMMEDIATE 'DROP TABLE ORDERS CASCADE CONSTRAINTS';

EXECUTE IMMEDIATE 'DROP TABLE PRODUCT CASCADE CONSTRAINTS';

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('');

END;

/

CREATE TABLE ORDERS(

ORDER_NO NUMBER(38, 0) PRIMARY KEY ,

ORDER_DATE DATE NULL,

SHIP_DATE DATE NULL,

TAX_STATUS CHAR(1) NULL,

SUB_TOTAL NUMBER(18, 0) NULL,

TAX_AMT NUMBER(18, 0) NULL,

SHIPPING_CHARGE NUMBER(18, 0) NULL,

TOTAL_AMT NUMBER(18, 0) NULL,

TOTAL_QTY NUMBER(18, 0) NULL,

CUSTOMER_NO NUMBER(38, 0) NULL

) ;

/

CREATE TABLE PRODUCT(

PRODUCT_NO NUMBER(18, 0) PRIMARY KEY,

PRODUCT_DESCRIPTION VARCHAR2(50) NULL,

UNIT_PRICE NUMBER(18, 0) NULL,

QOH NUMBER(38, 0) NULL

) ;

CREATE TABLE ORDERLINE(

ORDERLINE_NO NUMBER(38, 0) PRIMARY KEY,

PRODUCT_NO NUMBER(18, 0) NULL,

QTY NUMBER(38, 0) NULL,

ORDER_NO NUMBER(38, 0) NULL,

FOREIGN KEY(PRODUCT_NO) REFERENCES PRODUCT(PRODUCT_NO),

FOREIGN KEY(ORDER_NO) REFERENCES ORDERS(ORDER_NO)

);

SET DEFINE OFF;

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (100 , '1-Apr-15', '5-Apr-15', 'Y', NULL, NULL, NULL, NULL,NULL, 1023 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (110 , '2-Apr-15', '6-Apr-15', 'Y', NULL, NULL, NULL, NULL, NULL,1060 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1005 , '3-Apr-15', '7-Apr-15', 'Y', NULL, NULL, NULL, NULL,NULL, 1072 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1006 , '4-Apr-15', '8-Apr-15', 'Y', NULL, NULL, NULL, NULL, NULL,1076 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1007 , '5-Apr-15', '9-Apr-15', 'N', NULL, NULL, NULL, NULL,NULL, 1000 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1008 , '6-Apr-15', '10-Apr-15', 'N', NULL, NULL, NULL, NULL,NULL, 1064 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1023 , '7-Apr-15', '11-Apr-15', 'N', NULL, NULL, NULL, NULL, NULL,1068 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1025 , '8-Apr-15', '12-Apr-15', 'Y', NULL, NULL, NULL, NULL,NULL, 1064 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1026 , '9-Apr-15', '13-Apr-15', 'Y', NULL, NULL, NULL, NULL, NULL,1060 );

INSERT INTO ORDERS (ORDER_NO, ORDER_DATE, SHIP_DATE, TAX_STATUS, SUB_TOTAL, TAX_AMT, SHIPPING_CHARGE, TOTAL_AMT,TOTAL_QTY, CUSTOMER_NO) VALUES (1027 , '15-Apr-15', '19-Apr-15', 'N', NULL, NULL, NULL, NULL, NULL,1068 );

/

INSERT INTO PRODUCT (PRODUCT_NO, PRODUCT_DESCRIPTION, UNIT_PRICE, QOH) VALUES (110 , 'Xbox 2 controller', 150 , 2000 );

INSERT INTO PRODUCT (PRODUCT_NO, PRODUCT_DESCRIPTION, UNIT_PRICE, QOH) VALUES (120 , 'Xbox', 100, 20000 );

INSERT INTO PRODUCT (PRODUCT_NO, PRODUCT_DESCRIPTION, UNIT_PRICE, QOH) VALUES (130 , 'Xbox controller', 49, 1000 );

INSERT INTO PRODUCT (PRODUCT_NO, PRODUCT_DESCRIPTION, UNIT_PRICE, QOH) VALUES (140 , 'Xbox Memeory Card', 20 , 500 );

/

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1005 , 140 , 1 , 100 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (100 , 110 , 10 , 100 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1004 , 140 , 25 , 110 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1006 , 130 , 15 , 110 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1007 , 120 , 2 , 110 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1024 , 110 , 2 , 1005 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1025 , 120 , 6 , 1005 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1026 , 140 , 5 , 1006 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1027 , 130 , 1 , 1007 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1028 , 130 , 2 , 1008 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1041 , 110 , 3 , 1023 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1043 , 110 , 7 , 1025 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1044 , 130 , 1 , 1025 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1045 , 140 , 2 , 1026 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1046 , 120 , 2 , 1026 );

INSERT INTO ORDERLINE (ORDERLINE_NO, PRODUCT_NO, QTY, ORDER_NO) VALUES (1048 , 130 , 2 , 1027 );

COMMIT;

/*

D. The TOTAL_AMOUNT column in ORDERS table are null. Your task is to develop a stored procedure or user defined that will receive order number as parameter and calculates the TOTAL_AMOUNT of the ORDERS table using the following formula: SUB_TOTAL + TAX_AMOUNT + SHIPPING_TOTAL

Procedure Name: UDP_TOTAL_AMOUNT Function Name: UDF_TOTAL_AMOUNT (12.5 points)

*/

/*

E. The TOTAL_QTY column in ORDERS table are null. Your task is to develop a stored procedure or user defined that will receive order number as parameter and calculates the TOTAL_QTY column of the ORDERS table using the following formula: (SUM (QTY))

Procedure Name: UDP_TOTAL_QTY or Function Name: UDF_ TOTAL_QTY (12.5 points)

*/

/*

4) Develop an anonymous procedure that calculates correctly the sub-totals, shipping charge, tax amount, and total amounts and total qty of every order in orders table. (12.5 points)