Untitled
unknown
sql
19 days ago
3.0 kB
4
Indexable
-- Promotions Table (Simulation) CREATE OR REPLACE PROCEDURE simulate_dummy_promotions AS -- Variables v_batch_size INT := 1000; -- Collections for bulk insert TYPE t_promotion_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_package_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_discount_percentages IS TABLE OF DECIMAL(5,2) INDEX BY BINARY_INTEGER; TYPE t_start_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE t_end_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER; l_promotion_ids t_promotion_ids; l_package_ids t_package_ids; l_discount_percentages t_discount_percentages; l_start_dates t_start_dates; l_end_dates t_end_dates; -- Preloaded PackageIDs package_ids t_package_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 PackageIDs into memory SELECT PackageID BULK COLLECT INTO package_ids FROM TravelPackages; -- Check if data exists IF package_ids.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No PackageIDs found in the TravelPackages table.'); RETURN; END IF; -- Simulate generating 1,000 Promotions FOR batch IN 1..1 LOOP -- Generate v_batch_size records FOR i IN 1..v_batch_size LOOP -- Generate random data l_promotion_ids(i) := i; -- Simulated PromotionID l_package_ids(i) := package_ids(TRUNC(DBMS_RANDOM.VALUE(1, package_ids.COUNT + 1))); l_discount_percentages(i) := TRUNC(DBMS_RANDOM.VALUE(5, 50), 2); -- Random discount between 5% and 50% l_start_dates(i) := TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 30)); -- Random start date within the last 30 days l_end_dates(i) := l_start_dates(i) + TRUNC(DBMS_RANDOM.VALUE(7, 30)); -- Random end date 7-30 days after start date END LOOP; -- Simulate progress report DBMS_OUTPUT.PUT_LINE('Simulated batch ' || batch || ' of 1 (' || (batch * v_batch_size) || ' records)'); -- Commented out actual bulk insert /* FORALL i IN l_promotion_ids.FIRST..l_promotion_ids.LAST INSERT INTO Promotions ( PromotionID, PackageID, DiscountPercentage, StartDate, EndDate ) VALUES ( l_promotion_ids(i), l_package_ids(i), l_discount_percentages(i), l_start_dates(i), l_end_dates(i) ); -- Commit the 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_promotions(); END; /
Editor is loading...
Leave a Comment