DELIMITER $$
CREATE PROCEDURE deal_peixunshuju()
BEGIN
— 定义变量
DECLARE id_var INT(11);
DECLARE username_var VARCHAR(100);
DECLARE auth_key_var VARCHAR(32);
DECLARE password_hash_var VARCHAR(200);
DECLARE password_cn_var VARCHAR(100);
DECLARE phone_var VARCHAR(50);
DECLARE state_var TINYINT(3);
DECLARE created_at_var INT(11);
DECLARE status_var TINYINT(3);
DECLARE updated_at_var INT(11);
DECLARE weixin_user_id_var INT(11);
DECLARE cardid_var VARCHAR(50);
DECLARE id_tmp INT(11);
DECLARE truename_var VARCHAR(50);
DECLARE born_time_var INT(11);
DECLARE sex_var TINYINT(3);
DECLARE region_code_city_var INT(11);
DECLARE region_name_city_var VARCHAR(50);
DECLARE region_code_district_var VARCHAR(50);
DECLARE region_name_district_var VARCHAR(50);
— DECLARE h_province_var VARCHAR(50);
— DECLARE h_city_var VARCHAR(50);
— DECLARE h_district_var VARCHAR(50);
DECLARE school_name_var VARCHAR(50);
DECLARE time_start_var VARCHAR(50);
DECLARE time_end_var VARCHAR(50);
DECLARE major_var VARCHAR(50);
DECLARE education_var VARCHAR(50);
DECLARE resume_id_tmp INT(50);
DECLARE stop_flag TINYINT;
— 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT `id`,`phone`,`state`,UNIX_TIMESTAMP(`created_at`),`status`,UNIX_TIMESTAMP(`updated_at`),`fcardid` FROM `ljl_user`;
— DECLARE done_two INT DEFAULT 0;
DECLARE cur_two CURSOR FOR SELECT `school_name`,UNIX_TIMESTAMP(`time_start`),UNIX_TIMESTAMP(`time_end`),`major`,`education` FROM `ljl_course` where user_id = id_var;
— 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET stop_flag=1;
— 为变量赋值
— SET _nowTime = NOW();
SET stop_flag = 0;
OPEN cur; — 打开游标
FETCH cur INTO id_var,phone_var,state_var,created_at_var,status_var,updated_at_var,cardid_var;
SELECT `truename`,UNIX_TIMESTAMP(`born_time`),`sex`,`region_code_city`,`region_name_city`,`region_code_district`,`region_name_district` into truename_var,born_time_var,sex_var,region_code_city_var,region_name_city_var,region_code_district_var,region_name_district_var from `ljl_userinfo` where user_id = id_var order by id desc limit 1;
WHILE stop_flag<>1 DO — 若游标有下一条记录，循环
INSERT INTO wo_user(`id`,`username`,`auth_key`,`password_hash`,`password_cn`,`phone`,`state`,`created_at`,`status`,`updated_at`,`weixin_user_id`, `temp_id` ) VALUES(null,phone_var,”,”,”,phone_var,state_var,created_at_var,status_var,updated_at_var,”,id_var);
SELECT id into id_tmp from wo_user where temp_id = id_var;
INSERT INTO wo_resume(`id`,`user_id`,`truename`,`phone`,`born_time`,`sex`,`h_code`,`h_province`,`h_city`,`h_district`,`id_card` ) VALUES(null,id_tmp,truename_var,phone_var,born_time_var,sex_var,region_code_city_var,region_name_city_var,region_code_district_var,region_name_district_var,cardid_var);
SELECT id into resume_id_tmp from wo_resume where user_id = id_tmp;
— done_two = 0;
— DECLARE cur_two CURSOR FOR SELECT `ree_id`,`school_name`,UNIX_TIMESTAMP(`time_start`),UNIX_TIMESTAMP(`time_end`),`major`,`education` FROM `ljl_course` where user_id = id_var;
— 异常处理
— declare CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done_two=1;
OPEN cur_two;
FETCH cur_two into school_name_var,time_start_var,time_end_var,major_var,education_var;
— 循环插入数据
while stop_flag <>1 DO
INSERT INTO `wo_resume_education_experience` (`ree_id`, `school_name`, `time_start`, `time_end`, `major`, `education_old`,`resume_id`,`user_id`) VALUES (null, school_name_var,time_start_var,time_end_var, major_var,education_var,resume_id_tmp,id_tmp);
UPDATE wo_resume SET pc_name_old=major_var WHERE id=resume_id_tmp;
FETCH cur_two into school_name_var,time_start_var,time_end_var,major_var,education_var;
END WHILE;
— 关闭游标
CLOSE cur_two;
SET stop_flag = 0;
FETCH cur INTO id_var,phone_var,state_var,created_at_var,status_var,updated_at_var,cardid_var;
SELECT `truename`,UNIX_TIMESTAMP(`born_time`),`sex`,`region_code_city`,`region_name_city`,`region_code_district`,`region_name_district` into truename_var,born_time_var,sex_var,region_code_city_var,region_name_city_var,region_code_district_var,region_name_district_var from `ljl_userinfo` where user_id=id_var order by id desc limit 1;
END WHILE;
CLOSE cur; — 关闭游标
END $$
DELIMITER ;