Untitled
unknown
sql
21 days ago
3.4 kB
2
Indexable
-- Invoices Table Creation CREATE OR REPLACE PROCEDURE generate_dummy_invoices AS -- Variables v_batch_size INT := 1000; -- Collections for bulk insert TYPE t_invoice_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_booking_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_due_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE t_total_amounts IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER; TYPE t_paid_amounts IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER; TYPE t_balances IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER; TYPE t_statuses IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; l_invoice_ids t_invoice_ids; l_booking_ids t_booking_ids; l_due_dates t_due_dates; l_total_amounts t_total_amounts; l_paid_amounts t_paid_amounts; l_balances t_balances; l_statuses t_statuses; -- Preloaded BookingIDs booking_ids t_booking_ids; -- Array for statuses TYPE status_array IS TABLE OF VARCHAR2(50); statuses status_array := status_array('Paid', 'Pending', 'Overdue', 'Cancelled'); BEGIN -- 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 create 1,200,000 Invoices FOR batch IN 1..1200 LOOP -- Generate v_batch_size records FOR i IN 1..v_batch_size LOOP -- Generate random data l_invoice_ids(i) := invoice_id_seq.NEXTVAL; l_booking_ids(i) := booking_ids(TRUNC(DBMS_RANDOM.VALUE(1, booking_ids.COUNT + 1))); l_due_dates(i) := TRUNC(SYSDATE) + TRUNC(DBMS_RANDOM.VALUE(1, 90)); -- Random due date within the next 90 days l_total_amounts(i) := TRUNC(DBMS_RANDOM.VALUE(100, 5000), 2); -- Random total amount between 100 and 5000 l_paid_amounts(i) := TRUNC(DBMS_RANDOM.VALUE(0, l_total_amounts(i)), 2); -- Random paid amount less than or equal to total amount l_balances(i) := l_total_amounts(i) - l_paid_amounts(i); -- Calculate balance l_statuses(i) := statuses(TRUNC(DBMS_RANDOM.VALUE(1, statuses.COUNT + 1))); END LOOP; -- Bulk insert data FORALL i IN l_invoice_ids.FIRST..l_invoice_ids.LAST INSERT INTO Invoices ( InvoiceID, BookingID, DueDate, TotalAmount, PaidAmount, Balance, Statuss ) VALUES ( l_invoice_ids(i), l_booking_ids(i), l_due_dates(i), l_total_amounts(i), l_paid_amounts(i), l_balances(i), l_statuses(i) ); -- Commit each batch COMMIT; -- Progress report DBMS_OUTPUT.PUT_LINE('Completed batch ' || batch || ' of 1200 (' || (batch * v_batch_size) || ' records)'); END LOOP; DBMS_OUTPUT.PUT_LINE('Successfully generated 1,200,000 Invoices records.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); ROLLBACK; END generate_dummy_invoices; / -- Execute the procedure BEGIN generate_dummy_invoices(); END; /
Editor is loading...
Leave a Comment