Untitled

-- TransportBookings Table (Simulation)
CREATE OR REPLACE PROCEDURE simulate_dummy_transport_bookings AS
    -- Variables
    v_batch_size INT := 1000;

    -- Collections for bulk insert
    TYPE t_transport_booking_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_booking_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_transport_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_departure_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    TYPE t_return_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;

    l_transport_booking_ids t_transport_booking_ids;
    l_booking_ids t_booking_ids;
    l_transport_ids t_transport_ids;
    l_departure_dates t_departure_dates;
    l_return_dates t_return_dates;

    -- Preloaded BookingIDs and TransportIDs
    booking_ids t_booking_ids;
    transport_ids t_transport_ids;

    -- Timing variables
    start_time NUMBER;
    end_time NUMBER;
    elapsed_time NUMBER;

BEGIN
    -- Record the start time
    start_time := DBMS_UTILITY.GET_TIME;

    -- Preload all BookingIDs into memory
    SELECT BookingID BULK COLLECT INTO booking_ids FROM Bookings;

    -- Preload all TransportIDs into memory
    SELECT TransportationID BULK COLLECT INTO transport_ids FROM Transportation;

    -- Check if data exists
    IF booking_ids.COUNT = 0 OR transport_ids.COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No BookingIDs or TransportIDs found in the respective tables.');
        RETURN;
    END IF;

    -- Loop to create 1,200,000 TransportBookings
    FOR batch IN 1..1200 LOOP
        -- Generate v_batch_size records
        FOR i IN 1..v_batch_size LOOP
            -- Generate random data
            l_transport_booking_ids(i) := i; -- Simulated TransportBookingID
            l_booking_ids(i) := booking_ids(TRUNC(DBMS_RANDOM.VALUE(1, booking_ids.COUNT + 1)));
            l_transport_ids(i) := transport_ids(TRUNC(DBMS_RANDOM.VALUE(1, transport_ids.COUNT + 1)));
            l_departure_dates(i) := TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)); -- Random departure date within the last year
            l_return_dates(i) := l_departure_dates(i) + TRUNC(DBMS_RANDOM.VALUE(1, 15)); -- Random return date within 1-15 days after departure
        END LOOP;

        -- Simulate progress report
        DBMS_OUTPUT.PUT_LINE('Simulated batch ' || batch || ' of 1200 (' || (batch * v_batch_size) || ' records)');

        -- Commented out actual bulk insert
        /*
        FORALL i IN l_transport_booking_ids.FIRST..l_transport_booking_ids.LAST
            INSERT INTO TransportBookings (
                TransportBookingID,
                BookingID,
                TransportID,
                DepartureDate,
                ReturnDate
            ) VALUES (
                l_transport_booking_ids(i),
                l_booking_ids(i),
                l_transport_ids(i),
                l_departure_dates(i),
                l_return_dates(i)
            );

        -- Commit each batch
        COMMIT;
        */
    END LOOP;

    -- Record the end time
    end_time := DBMS_UTILITY.GET_TIME;

    -- Calculate elapsed time in seconds
    elapsed_time := (end_time - start_time) / 100;
    DBMS_OUTPUT.PUT_LINE('Execution Time (Simulation): ' || elapsed_time || ' seconds');
END;
/

-- Execute the procedure
BEGIN
    simulate_dummy_transport_bookings();
END;
/