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 province_id VARCHAR(200);
DECLARE city_id VARCHAR(200);
DECLARE zone_id VARCHAR(200);
DECLARE done INT DEFAULT 0;
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;
CLOSE cur;
commit;
END
就是将tn_company 表中的字段address 根据空格 分出来 省市区 3个字段，再和tl_district（全国省市区及Id）表中做对
比，去其idcode，存入tn_company表中对应的字段里，不知道为什么，运行没有错误，就是一个字段也插不进去，而且我是70w条数据，几秒就运行完了， 不知道是不是哪写的不对？ 高人上座，求指教！
MYSQL要转到MYSQL版块的，知道MSSQL的路过。
探讨
引用:
MYSQL要转到MYSQL版块的，知道MSSQL的路过。
姐姐，俺不会转呀，第一次发帖，见谅！ 有问题，求指导！
管理菜单，移动，找MYSQL版块。要不我给你移过去？
MY SQL 也有存储过程的啊？
