[SOLVED] PL/SQL Stored Procedure to Populate Fact Table

PL/SQL Stored Procedure to Populate Fact Table

I need to populate this fact table using a PL/SQL stored procedure:

CREATE TABLE SALES_FACTS
(saleDay        DATE,
vehicleCode         INT,
planID          INT,
dealerID        INT,
vehiclesSold        INT,
grossSalesAmt   NUMBER(10),
CONSTRAINT SALE_DAY_FK FOREIGN KEY (saleDay) REFERENCES TIMES(saleDay) ON DELETE CASCADE,
CONSTRAINT VEHICLE_CODE_FK FOREIGN KEY (vehicleCode) REFERENCES VEHICLES(vehicleCode) ON DELETE CASCADE,
CONSTRAINT PLAN_ID_FK FOREIGN KEY (planID) REFERENCES FINANCING_PLANS(planID) ON DELETE CASCADE,
CONSTRAINT DEALER_FK FOREIGN KEY (dealerID) REFERENCES DEALERSHIPS(dealerID) ON DELETE CASCADE,
CONSTRAINT SALES_FACTS_PK PRIMARY KEY (saleDay, vehicleCode, planID, dealerID));

I have been asked to do this by using four nested cursor loops to get every possible combination of the dimension tables' primary keys, along with the total vehicles sold and gross sales amount for each combination.

Also, if the values for vehiclesSold and grossSalesAmount are zero, then a row SHOULD NOT be inserted into the SALES_FACTS table.

Only rows for combinations of the four foreign key columns where there were some vehicles sold should be inserted.

I have created the following code that I hoped would accomplish this:

CURSOR factData IS

SELECT vehicleVin,saleDate,sf.planID,sp.dealerID
    COUNT (*) AS vehiclesSold
    SUM (s.grossSalePrice) AS grossSalesAmount
FROM SALES s, SALES_FINANCINGS sf, SALESPERSONS sp
WHERE s.saleID = sf.saleID
AND s.salespersonID = sp.salespersonID
GROUP BY vehicleVIN, saleDate, sf.planID, sp.dealerID
HAVING COUNT(*) > 0;
BEGIN
FOR record IN factData
LOOP
    INSERT INTO SALES_FACTS (saleDay,vehicleCode,planID,dealerID,vehiclesSold, grossSalesAmount
    VALUES (record.saleDate,record.vehicleVin,record.planID,record.dealerID,record.vehiclesSold,record.grossSalesAmount);
END LOOP;
END;
/

However the code executes fine, but I do not get any results when I run a

SELECT COUNT(*) FROM SALES_FACTS;

I have created an SQL Fiddle link here http://sqlfiddle.com/#!4/9708d6/1 since the code for the tables and table population was too much to post on this question. Keep in mind that I only INSERTed about 2-3 rows of data for each table to keep the code somewhat short, however the data that has been inserted should suffice to get this working.

Please let me know where I'm going wrong and what the best way to fix it is! Thanks in advance!

Solution

This Ended up doing the trick. Thanks for all of the help to those who commented.

DECLARE
CURSOR sales_data
IS
 SELECT vehicleVIN, saleDate, SF.planID, SP.dealerID, 
 COUNT(*) AS vehiclesSold, SUM(S.grossSalePrice) AS grossSalesAmt
 FROM SALES S, SALES_FINANCINGS SF, SALESPERSONS SP, VEHICLES V
 WHERE S.saleID = SF.saleID AND S.vehicleVIN = V.vehicleCode AND S.salespersonID = SP.salespersonID
 GROUP BY vehicleVIN, saleDate, SF.planID, SP.dealerID
 HAVING COUNT(*) > 0;

BEGIN
FOR record IN sales_data
LOOP
    INSERT INTO SALES_FACTS (saleDay,vehicleCode,planID,dealerID,vehiclesSold, grossSalesAmt)
    VALUES (record.saleDate,record.vehicleVIN,record.planID,record.dealerID,record.vehiclesSold,record.grossSalesAmt);
END LOOP;
END;
/