Mysql 存储过程中 游标使用遇到问题了 CREATE FUNCTION func_get_split_string( f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8 BEGIN declare result varchar(255) default ''; set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); return result; END
CREATE PROCEDURE split_data() BEGIN DECLARE tid VARCHAR(32); DECLARE address_name VARCHAR(200); DECLARE province_name VARCHAR(200); DECLARE city_name VARCHAR(200); DECLARE zone_name VARCHAR(200);
DECLARE cur CURSOR FOR SELECT id,address FROM tn_company; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur; address_lb:LOOP FETCH cur INTO tid,address_name; SELECT func_get_split_string(address_name,' ',2) INTO province_name; SELECT func_get_split_string(address_name,' ',3) INTO city_name; SELECT func_get_split_string(address_name,' ',4) INTO zone_name;
SELECT idcode INTO province_id FROM tl_district WHERE name = province_name and LENGTH(idcode)=2; SELECT idcode INTO city_id FROM tl_district WHERE name = city_name AND LENGTH(idcode)=4 and parentId=province_id; SELECT idcode INTO zone_id FROM tl_district WHERE name like zone_name AND LENGTH(idcode)=6 and parentid=city_id LIMIT 1;
UPDATE tn_company SET address_province = province_name, address_city = city_name, address_zone = zone_name, provinceId = province_id, cityId = city_id, zoneId = zone_id WHERE id = tid;
IF done = 1 THEN LEAVE address_lb; END IF; END LOOP address_lb;