Untitled
unknown
sql
20 days ago
3.6 kB
3
Indexable
-- Payments Table (Simulation) CREATE OR REPLACE PROCEDURE simulate_dummy_payments AS -- Variables v_batch_size INT := 1000; -- Collections for bulk insert TYPE t_payment_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_booking_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_amounts IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER; TYPE t_payment_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE t_payment_methods IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; TYPE t_payment_statuses IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; l_payment_ids t_payment_ids; l_booking_ids t_booking_ids; l_amounts t_amounts; l_payment_dates t_payment_dates; l_payment_methods t_payment_methods; l_payment_statuses t_payment_statuses; -- Preloaded BookingIDs booking_ids t_booking_ids; -- Array for payment methods and statuses TYPE method_array IS TABLE OF VARCHAR2(100); TYPE status_array IS TABLE OF VARCHAR2(50); payment_methods method_array := method_array('Credit Card', 'Debit Card', 'PayPal', 'Bank Transfer', 'Cash'); payment_statuses status_array := status_array('Paid', 'Pending', 'Failed', 'Refunded'); -- 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; -- Check if data exists IF booking_ids.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No BookingIDs found in the Bookings table.'); RETURN; END IF; -- Loop to simulate 1,200,000 Payments FOR batch IN 1..1200 LOOP -- Generate v_batch_size records FOR i IN 1..v_batch_size LOOP -- Generate random data l_payment_ids(i) := i; -- Simulated PaymentID l_booking_ids(i) := booking_ids(TRUNC(DBMS_RANDOM.VALUE(1, booking_ids.COUNT + 1))); l_amounts(i) := TRUNC(DBMS_RANDOM.VALUE(50, 5000), 2); -- Random amount between 50 and 5000 l_payment_dates(i) := TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)); -- Random date within the last year l_payment_methods(i) := payment_methods(TRUNC(DBMS_RANDOM.VALUE(1, payment_methods.COUNT + 1))); l_payment_statuses(i) := payment_statuses(TRUNC(DBMS_RANDOM.VALUE(1, payment_statuses.COUNT + 1))); 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_payment_ids.FIRST..l_payment_ids.LAST INSERT INTO Payments ( PaymentID, BookingID, AmountPaid, PaymentDate, PaymentMethod, PaymentStatus ) VALUES ( l_payment_ids(i), l_booking_ids(i), l_amounts(i), l_payment_dates(i), l_payment_methods(i), l_payment_statuses(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_payments(); END; /
Editor is loading...
Leave a Comment