CREATE OR REPLACE FUNCTION F_SALE_BY_ZIP_TIME (P_ZIPCODE CUSTOMERS.ZIP%TYPE, P_MONTH NUMBER, P_YEAR NUMBER) RETURN NUMBER IS
V_SALE NUMBER;
BEGIN
SELECT SUM(OI.QUANTITY * B.RETAIL)
INTO V_SALE
FROM BOOKS B JOIN Orderitems OI ON B.ISBN = OI.ISBN JOIN
ORDERS O ON OI.ORDER# = O.ORDER# JOIN
Customers C ON C.CUSTOMER# = O.CUSTOMER#
WHERE C.ZIP=P_ZIPCODE AND EXTRACT(MONTH FROM O.ORDERDATE) = P_MONTH AND EXTRACT(YEAR FROM O.ORDERDATE)= P_YEAR;
RETURN V_SALE;
END;
CREATE OR REPLACE FUNCTION F_MONTH(P_DATE DATE) RETURN NUMBER IS
V_MONTH NUMBER;
BEGIN
SELECT EXTRACT(MONTH FROM P_DATE) INTO V_MONTH FROM DUAL;
RETURN V_MONTH;
END;
CREATE OR REPLACE FUNCTION F_YEAR(P_DATE DATE) RETURN NUMBER IS
V_YEAR NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM P_DATE) INTO V_YEAR FROM DUAL;
RETURN V_YEAR;
END;
CREATE OR REPLACE PROCEDURE P_APPEND_DATA IS
BEGIN
MERGE INTO ZIP Z
USING (SELECT DISTINCT ZIP, STATE FROM Customers) SUB
ON (Z.ZIP_CODE = SUB.ZIP)
WHEN NOT MATCHED THEN
INSERT(ZIP_CODE, STATE) VALUES (SUB.ZIP, SUB.STATE);
-- WHEM MATCHED THEN
MERGE INTO TIME T
USING (SELECT EXTRACT(MONTH FROM ORDERDATE) MONTH, EXTRACT(YEAR FROM ORDERDATE) YEAR FROM ORDERS) SUB
ON (T.YEAR = SUB.YEAR AND T.MONTH = SUB.MONTH)
WHEN NOT MATCHED THEN
INSERT(MONTH, YEAR) VALUES (SUB.YEAR, SUB.MONTH);
MERGE INTO SALE S
USING (
SELECT DISTINCT F_SALE_BY_ZIP_TIME(Z.ZIP_CODE, T.MONTH, T.YEAR) AMOUNT, T.ID_TIME, Z.ID_ZIP FROM ZIP Z, TIME T, CUSTOMERS C, ORDERS O
WHERE c.customer# = o.customer# AND C.ZIP=z.zip_code AND EXTRACT(MONTH FROM ORDERDATE) = T.MONTH AND EXTRACT(YEAR FROM ORDERDATE) = T.YEAR
) SUB
ON (S.ID_TIME = SUB.ID_TIME AND S.ID_ZIP = SUB.ID_ZIP)
WHEN NOT MATCHED THEN
INSERT(ID_TIME, ID_ZIP, AMOUNT) VALUES (SUB.ID_TIME, SUB.ID_ZIP, SUB.AMOUNT);
END;
EXECUTE P_APPEND_DATA;
CREATE OR REPLACE PACKAGE PACKAGE_DATA_MIGRATION AS
FUNCTION F_SALE_BY_ZIP_TIME (P_ZIPCODE CUSTOMERS.ZIP%TYPE, P_MONTH NUMBER, P_YEAR NUMBER) RETURN NUMBER;
PROCEDURE P_APPEND_DATA;
END;
CREATE OR REPLACE PACKAGE BODY PACKAGE_DATA_MIGRATION AS
FUNCTION F_SALE_BY_ZIP_TIME (P_ZIPCODE CUSTOMERS.ZIP%TYPE, P_MONTH NUMBER, P_YEAR NUMBER) RETURN NUMBER IS
V_SALE NUMBER;
BEGIN
SELECT SUM(OI.QUANTITY * B.RETAIL)
INTO V_SALE
FROM BOOKS B JOIN Orderitems OI ON B.ISBN = OI.ISBN JOIN
ORDERS O ON OI.ORDER# = O.ORDER# JOIN
Customers C ON C.CUSTOMER# = O.CUSTOMER#
WHERE C.ZIP=P_ZIPCODE AND EXTRACT(MONTH FROM O.ORDERDATE) = P_MONTH AND EXTRACT(YEAR FROM O.ORDERDATE)= P_YEAR;
RETURN V_SALE;
END F_SALE_BY_ZIP_TIME;
PROCEDURE P_APPEND_DATA IS
BEGIN
MERGE INTO ZIP Z
USING (SELECT DISTINCT ZIP, STATE FROM Customers) SUB
ON (Z.ZIP_CODE = SUB.ZIP)
WHEN NOT MATCHED THEN
INSERT(ZIP_CODE, STATE) VALUES (SUB.ZIP, SUB.STATE);
-- WHEM MATCHED THEN
MERGE INTO TIME T
USING (SELECT EXTRACT(MONTH FROM ORDERDATE) MONTH, EXTRACT(YEAR FROM ORDERDATE) YEAR FROM ORDERS) SUB
ON (T.YEAR = SUB.YEAR AND T.MONTH = SUB.MONTH)
WHEN NOT MATCHED THEN
INSERT(MONTH, YEAR) VALUES (SUB.YEAR, SUB.MONTH);
MERGE INTO SALE S
USING (
SELECT DISTINCT F_SALE_BY_ZIP_TIME(Z.ZIP_CODE, T.MONTH, T.YEAR) AMOUNT, T.ID_TIME, Z.ID_ZIP FROM ZIP Z, TIME T, CUSTOMERS C, ORDERS O
WHERE c.customer# = o.customer# AND C.ZIP=z.zip_code AND EXTRACT(MONTH FROM ORDERDATE) = T.MONTH AND EXTRACT(YEAR FROM ORDERDATE) = T.YEAR
) SUB
ON (S.ID_TIME = SUB.ID_TIME AND S.ID_ZIP = SUB.ID_ZIP)
WHEN NOT MATCHED THEN
INSERT(ID_TIME, ID_ZIP, AMOUNT) VALUES (SUB.ID_TIME, SUB.ID_ZIP, SUB.AMOUNT);
END P_APPEND_DATA;
END PACKAGE_DATA_MIGRATION;
execute PACKAGE_DATA_MIGRATION.P_APPEND_DATA;
CREATE OR REPLACE TRIGGER T_CONTROL_RETAIL
BEFORE INSERT OR UPDATE OF REATIL ON BOOKS
FOR EACH ROW
BEGIN
IF :NEW.RETAIL > 2* :NEW.COST THEN
:NEW.RETAIL := :NEW.COST*2;
END IF;
END;