DELIMITER $$
DROP PROCEDURE IF EXISTS `world`.`CURSOR_DEMO`$$
CREATE DEFINER=`tusa`@`%` PROCEDURE  `world`.`CURSOR_DEMO`(countryName VARCHAR(53))
BEGIN
    DECLARE i_ID INT;
    DECLARE s_name VARCHAR(255);
    DECLARE s_district VARCHAR(255);
    DECLARE s_code VARCHAR(53);
    DECLARE l_done          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;
    DROP TABLE IF EXISTS city_table;
    CREATE TABLE city_table (id INT, name VARCHAR(255), district varchar(255), CountryName char(53))
      ENGINE=memory;
    OPEN c1;
  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) VALUES ( i_ID,s_name, s_district,s_code);
    END IF;
    IF l_done=1 THEN
       LEAVE city_loop;
    END IF;
  END LOOP city_loop;
 
END$$
DELIMITER ;

Latest conferences

We have 123 guests and no members online

oracle_ace