使用PL/SQL存储过程（四嵌套游标）填充SALES_FACTS事实表需求

Overview #

Let's build a PL/SQL stored procedure that exactly meets your requirements: we'll use nested cursors to iterate through every possible primary key combination of your four dimension tables, calculate the corresponding sales metrics, and only insert records into SALES_FACTS when there's actual sales activity (no rows where both vehiclesSold and grossSalesAmount are zero).

Complete Procedure Code #

CREATE OR REPLACE PROCEDURE populate_sales_facts
IS
    -- Define cursors for each dimension table (replace with your actual table/column names)
    CURSOR c_date_dim IS
        SELECT date_id FROM date_dim;
    CURSOR c_location_dim IS
        SELECT location_id FROM location_dim;
    CURSOR c_vehicle_type_dim IS
        SELECT vehicle_type_id FROM vehicle_type_dim;
    CURSOR c_dealer_dim IS
        SELECT dealer_id FROM dealer_dim;
    
    -- Variables to store cursor values
    v_date_id DATE_DIM.date_id%TYPE;
    v_location_id LOCATION_DIM.location_id%TYPE;
    v_vehicle_type_id VEHICLE_TYPE_DIM.vehicle_type_id%TYPE;
    v_dealer_id DEALER_DIM.dealer_id%TYPE;
    
    -- Variables for sales calculations
    v_vehicles_sold NUMBER;
    v_gross_sales_amount NUMBER;
BEGIN
    -- Outer loop: iterate through all date dimension keys
    OPEN c_date_dim;
    LOOP
        FETCH c_date_dim INTO v_date_id;
        EXIT WHEN c_date_dim%NOTFOUND;
        
        -- Second loop: iterate through all location dimension keys
        OPEN c_location_dim;
        LOOP
            FETCH c_location_dim INTO v_location_id;
            EXIT WHEN c_location_dim%NOTFOUND;
            
            -- Third loop: iterate through all vehicle type dimension keys
            OPEN c_vehicle_type_dim;
            LOOP
                FETCH c_vehicle_type_dim INTO v_vehicle_type_id;
                EXIT WHEN c_vehicle_type_dim%NOTFOUND;
                
                -- Innermost loop: iterate through all dealer dimension keys
                OPEN c_dealer_dim;
                LOOP
                    FETCH c_dealer_dim INTO v_dealer_id;
                    EXIT WHEN c_dealer_dim%NOTFOUND;
                    
                    -- Calculate sales metrics for the current dimension combination
                    SELECT 
                        COUNT(s.sale_record_id) AS vehicles_sold,
                        NVL(SUM(s.sale_total), 0) AS gross_sales
                    INTO 
                        v_vehicles_sold,
                        v_gross_sales_amount
                    FROM 
                        raw_sales_data s
                    WHERE 
                        s.date_id = v_date_id
                        AND s.location_id = v_location_id
                        AND s.vehicle_type_id = v_vehicle_type_id
                        AND s.dealer_id = v_dealer_id;
                    
                    -- Only insert if there's actual sales activity
                    IF v_vehicles_sold > 0 OR v_gross_sales_amount > 0 THEN
                        INSERT INTO sales_facts (
                            date_id,
                            location_id,
                            vehicle_type_id,
                            dealer_id,
                            vehicles_sold,
                            gross_sales_amount
                        ) VALUES (
                            v_date_id,
                            v_location_id,
                            v_vehicle_type_id,
                            v_dealer_id,
                            v_vehicles_sold,
                            v_gross_sales_amount
                        );
                    END IF;
                    
                END LOOP;
                CLOSE c_dealer_dim;
                
            END LOOP;
            CLOSE c_vehicle_type_dim;
            
        END LOOP;
        CLOSE c_location_dim;
        
    END LOOP;
    CLOSE c_date_dim;
    
    -- Commit all valid inserts
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback on any error and re-throw for debugging
        ROLLBACK;
        RAISE;
END populate_sales_facts;
/

Key Breakdown & Customization Tips #

• Cursor Setup: Replace the dimension table names (date_dim, location_dim, etc.) and primary key columns with your actual schema objects. The raw sales table (raw_sales_data) should be your source transaction table that links to all four dimensions.
• Metric Calculation: COUNT(s.sale_record_id) counts individual vehicle sales, while NVL(SUM(s.sale_total), 0) ensures we get a 0 instead of NULL when there are no sales for a combination.
• Insert Guard Clause: The IF condition skips inserting rows where both metrics are zero, exactly as you requested.
• Error Handling: The exception block ensures we don't leave partial transactions in place if something goes wrong.

Performance Optimization Note #

If your dimension tables are large, nested cursors can be slow. For better efficiency, consider a set-based approach instead of row-by-row processing:

INSERT INTO sales_facts (date_id, location_id, vehicle_type_id, dealer_id, vehicles_sold, gross_sales_amount)
SELECT 
    d.date_id,
    l.location_id,
    vt.vehicle_type_id,
    dl.dealer_id,
    COUNT(s.sale_record_id) AS vehicles_sold,
    NVL(SUM(s.sale_total), 0) AS gross_sales_amount
FROM 
    date_dim d
    CROSS JOIN location_dim l
    CROSS JOIN vehicle_type_dim vt
    CROSS JOIN dealer_dim dl
    LEFT JOIN raw_sales_data s 
        ON s.date_id = d.date_id
        AND s.location_id = l.location_id
        AND s.vehicle_type_id = vt.vehicle_type_id
        AND s.dealer_id = dl.dealer_id
GROUP BY 
    d.date_id, l.location_id, vt.vehicle_type_id, dl.dealer_id
HAVING 
    COUNT(s.sale_record_id) > 0 OR NVL(SUM(s.sale_total), 0) > 0;

This uses Oracle's query optimizer to handle the cross joins and aggregation in a more efficient, bulk-processing way.

内容的提问来源于stack exchange，提问作者StevenC