Untitled

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;