Convert into Oracle Packages

alter session set "_oracle_script"=true;

CREATE USER sales IDENTIFIED BY sales DEFAULT TABLESPACE users;

--

GRANT ALL PRIVILEGES TO sales;

BEGIN

EXECUTE IMMEDIATE 'drop table ORDERLINE cascade constraint';

EXECUTE IMMEDIATE 'drop table ORDERS cascade constraint';

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('');

END;

/

CREATE TABLE ORDERLINE(

OrderItemID NUMBER(9) NOT NULL,

OrderID NUMBER(9) NOT NULL,

ProductID NUMBER(9) NOT NULL,

UnitPrice NUMBER(9,3) NULL,

Quantity NUMBER(9,3) NULL,

PRIMARY KEY (OrderItemID )) ;

/

CREATE TABLE ORDERS(

OrderID NUMBER(9) NOT NULL,

CustomerID VARCHAR2(5) NULL,

EmployeeID NUMBER(9) NULL,

OrderDate DATE NULL,

TotalQty NUMBER(9) NULL,

TotalCost NUMBER(9) NULL,

PRIMARY KEY (OrderID )) ;

/

--INSERT ORDERS

INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, TotalQty, TotalCost) VALUES (10248, 'VINET', 5, to_date('2017-06-01', 'yyyy/mm/dd') , NULL, NULL);

INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, TotalQty, TotalCost) VALUES (10249, 'TOMSP', 6, to_date('2017-06-01', 'yyyy/mm/dd'), NULL, NULL);

INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, TotalQty, TotalCost) VALUES (10250, 'HANAR', 4, to_date('2017-06-01', 'yyyy/mm/dd'), NULL, NULL);

INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, TotalQty, TotalCost) VALUES (10251, 'VICTE', 3, to_date('2017-06-01', 'yyyy/mm/dd'), NULL, NULL);

--INSERT ORDERLINE

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (1,10248, 11, 14.0000, 12);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (2,10248, 42, 9.8000, 10);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (3,10248, 72, 34.8000, 5);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (4,10249, 14, 18.6000, 9);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (5,10249, 51, 42.4000, 40);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (6,10250, 41, 7.7000, 10);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (7,10250, 51, 42.4000, 35);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (8,10250, 65, 16.8000, 15);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (10,10251, 22, 16.8000, 6);

INSERT INTO ORDERLINE (OrderItemID,OrderID, ProductID, UnitPrice, Quantity) VALUES (11,10251, 57, 15.6000, 15);

ALTER TABLE ORDERLINE ADD FOREIGN KEY(ORDERID)

REFERENCES ORDERS (ORDERID);

/

CREATE OR REPLACE PROCEDURE UDP_TOTAL_COST_UPDATE(

P_ORDERID IN NUMBER)

IS

VAR_TOTAL_COST NUMBER;

BEGIN

SELECT SUM(NVL(T.QUANTITY,0) * NVL(T.UNITPRICE,0)) AS TOTAL_COST

INTO VAR_TOTAL_COST

FROM ORDERLINE T

WHERE T.ORDERID = P_ORDERID ;

UPDATE ORDERS SET TotalCost = VAR_TOTAL_COST WHERE ORDERID = P_ORDERID;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

/

CREATE OR REPLACE PROCEDURE UDP_TOT_QUANTITY_UPDATE(

P_ORDERID IN NUMBER)

IS

VAR_TOT_QUANTITY NUMBER;

BEGIN

SELECT SUM(T.QUANTITY ) AS VAR_TOT_QUANTITY

INTO VAR_TOT_QUANTITY

FROM ORDERLINE T

WHERE T.ORDERID = P_ORDERID ;

UPDATE ORDERS SET TotalQTY = VAR_TOT_QUANTITY WHERE ORDERID = P_ORDERID;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

/

SELECT * FROM ORDERS;

UPDATE ORDERS

SET TotalQty = NULL ,

TotalCost = NULL

/

SELECT * FROM ORDERS;

BEGIN

FOR rec IN

(SELECT ORDERID FROM ORDERS

)

LOOP

UDP_TOTAL_COST_UPDATE(rec.ORDERID);

UDP_TOT_QUANTITY_UPDATE(rec.ORDERID);

END LOOP;

END;

/

SELECT * FROM ORDERS;

CREATE OR REPLACE FUNCTION UDF_TOTAL_COST(

P_ORDERID IN NUMBER )

RETURN NUMBER

IS

VAR_TOTAL_COST NUMBER;

BEGIN

SELECT SUM(NVL(T.QUANTITY,0) * NVL(T.UNITPRICE,0)) AS TOTAL_COST

INTO VAR_TOTAL_COST

FROM ORDERLINE T

WHERE T.ORDERID = P_ORDERID ;

RETURN VAR_TOTAL_COST;

END;

/

CREATE OR REPLACE FUNCTION UDF_TOTAL_QUANTITY(

P_ORDERID IN NUMBER )

RETURN NUMBER

IS

V_TOTAL_QUANTITY NUMBER;

BEGIN

SELECT SUM(NVL(T.QUANTITY,0) ) AS TOTAL_QUANTITY

INTO V_TOTAL_QUANTITY

FROM ORDERLINE T

WHERE T.ORDERID = P_ORDERID ;

RETURN V_TOTAL_QUANTITY;

END;

/

UPDATE ORDERS

SET TotalQty = NULL ,

TotalCost = NULL

/

SELECT * FROM ORDERS;

DECLARE

V_TOTAL_COST NUMBER;

V_TOTAL_QUANTITY NUMBER;

BEGIN

FOR rec IN

(SELECT ORDERID FROM ORDERS

)

LOOP

SELECT UDF_TOTAL_COST(rec.ORDERID) ,

UDF_TOTAL_QUANTITY(rec.ORDERID)

INTO V_TOTAL_COST,

V_TOTAL_QUANTITY

FROM DUAL;

UPDATE ORDERS

SET TotalQty = V_TOTAL_QUANTITY ,

TotalCost = V_TOTAL_COST

WHERE ORDERID = rec.ORDERID;

END LOOP;

END;

/

SELECT * FROM ORDERS;