mysql存储过程和游标

CREATE DEFINER&＃61;&＃96;root&＃96;&＃64;&＃96;localhost&＃96; PROCEDURE &＃96;NewProc&＃96;()
BEGIN#Routine body goes here...DECLARE itemId varchar(64); -- idDECLARE AA varchar(64); -- 省DECLARE BB varchar(64); -- 市DECLARE CC varchar(64); -- 区DECLARE new_province varchar(64); -- 省DECLARE new_city varchar(64); -- 市DECLARE new_area varchar(64); -- 区-- 遍历数据结束标志DECLARE done INT DEFAULT FALSE;-- 游标DECLARE cur_account CURSOR FOR select id,province,city,area from startup_project_copy;-- 将结束标志绑定到游标DECLARE CONTINUE HANDLER FOR NOT FOUND SET done &＃61; TRUE;-- 打开游标OPEN cur_account; -- 遍历read_loop: LOOP-- 取值 取多个字段FETCH NEXT from cur_account INTO itemId,AA,BB,CC;IF done THENLEAVE read_loop;END IF;SET new_province &＃61; (SELECT Impcode FROM sys_area WHERE AreaID &＃61; AA);SET new_city &＃61; (SELECT Impcode FROM sys_area WHERE AreaID &＃61; BB);SET new_area &＃61; (SELECT Impcode FROM sys_area WHERE AreaID &＃61; CC);
-- 你自己想做的操作-- INSERT INTO test_city VALUE(new_province, new_city, new_area, itemId);-- 执行更新UPDATE startup_project_copy SET province &＃61; new_province, city&＃61; CONCAT(new_city,&＃39;000000&＃39;), area &＃61; CONCAT(new_area,&＃39;000000&＃39;) WHERE id &＃61; itemId;
-- SET &＃64;UPDATE &＃61; CONCAT(&＃39;UPDATE startup_project_copy SET province &＃61;&＃39;, new_province,&＃39;, city&＃61;&＃39;, new_city, &＃39;, area &＃61;&＃39;, new_area, &＃39; WHERE id &＃61; &＃39;,id);
-- PREPARE stm FROM &＃64;UPDATE;
-- EXECUTE stm;
-- DEALLOCATE PREPARE stm;COMMIT;-- 提交END LOOP;CLOSE cur_account;END