CREATE DEFINER=`tusa`@`%` PROCEDURE `CURSOR_DEMO_TRANSACTION`(countryName VARCHAR(53))
BEGIN
DECLARE i_ID INT;
DECLARE s_name VARCHAR(255);
DECLARE s_district VARCHAR(255);
DECLARE s_code VARCHAR(53);
DECLARE s_DELETE_ID VARCHAR(2000);
DECLARE l_done INT DEFAULT 0;
DECLARE txn_error INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT ID, city.Name, District,country.name FROM city,country
where city.CountryCode = country.code and country.name=countryName order by CountryCode,District;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET txn_error=1;
DROP TABLE IF EXISTS city_table;
CREATE TABLE city_table (id INT, name VARCHAR(255), district varchar(255), CountryName char(53), transactionId INT)
ENGINE=InnoDB;
START TRANSACTION;
SET @transactionId = FLOOR(1+ (RAND() * 50000000));
OPEN c1;
SET s_DELETE_ID='';
city_loop: LOOP
SET s_code = '';
FETCH c1 into i_ID,s_name, s_district, s_code;
IF s_code LIKE countryName THEN
INSERT INTO city_table (id, name, district,CountryName, transactionId) VALUES ( i_ID,s_name, s_district,s_code,@transactionId);
SET s_DELETE_ID = CONCAT_WS(',', s_DELETE_ID, i_ID);
END IF;
IF l_done=1 THEN
LEAVE city_loop;
END IF;
END LOOP city_loop;
CLOSE c1;
Select * from city_table;
SAVEPOINT delete_tmp_recset;
DELETE from city_table where transactionId = @transactionId;
IF txn_error=1 THEN
ROLLBACK TO delete_tmp_recset;
SELECT "Table not purge";
END IF;
COMMIT;
END $$