Untitled
unknown
sql
13 days ago
3.3 kB
4
Indexable
-- 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; /
Editor is loading...
Leave a Comment