CODEWITHSUNDEEP
mysqlparametersprocedurepurgeroutines
Michael Bernardo
Michael Bernardo

Reputation: 31

How to create a stored procedure that iterates through a table and pass the id to another stored procedure?

I have an enormous orders table that I need to purge having certain city_id's. I created a stored procedure that accepts 1 parameter and deletes 10,000 records at a time and it works well. The city_id's come from a table using this query:

mysql> SELECT city_id from cities where state_name in ('NY', 'TX');

The issue is that I have to manually run dozens of stored procedures one-by-one for each city_id in the result set like this:

mysql> CALL purge_orders_by_city(12);
mysql> CALL purge_orders_by_city(16);
mysql> CALL purge_orders_by_city(28);
mysql> CALL purge_orders_by_city(39);
etc...

How do I create a stored procedure that will iterate through the cities table, and pass the city_id to the purge_orders_by_city() stored procedure?

I already tried the regular "DELETE from orders where city_id in (1, 2, 3, 4...) order by id limit 10000" but execution time for each 10K batch takes gets longer as it executes. For example, the first few batches only took 15 seconds each. By the 300,000th batch, it's already taking 30 seconds. Limiting the batch to one city_id using "where city_id = 1" is way faster even if I had to run it 1 city_id at a time.

Upvotes: 0

Views: 47

Answers (2)

Michael Bernardo
Michael Bernardo

Reputation: 31

The correct solution was the one provided by Mohamed Tadwala but with some minor corrections. I couldn't format the code in the comments so I posted a new answer to make it more readable. This is the code as it appears within the PHPMyAdmin edit routine form:

BEGIN
DECLARE done, v_city_id INT DEFAULT FALSE;
DECLARE cur_city_id CURSOR FOR SELECT city_id from cities where state_name in ('NY', 'TX');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_city_id ;
    read_loop: LOOP
        FETCH cur_city_id INTO v_city_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        CALL purge_orders_by_city(v_city_id);
    END LOOP;
    CLOSE cur_city_id; 
END

Upvotes: 0

Mohamed Tadwala
Mohamed Tadwala

Reputation: 11

You have to create another stored procedure that will run a loop of the purge_orders_by_city procedure.

something like this 
    
-- existing varaibles
DECLARE cur_city_id  CURSOR FOR SELECT city_id from cities where state_name in ('NY', 'TX');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


OPEN cur_city_id ;
    read_loop: LOOP
        FETCH cur_city_id INTO v_city_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        CALL purge_orders_by_city(v_city_id);

    END LOOP;

    CLOSE cur_city_id

Upvotes: 1

Related Questions