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 $$
 

Latest conferences

We have 76 guests and no members online

oracle_ace