sql语句

drop PROCEDURE c

CREATE PROCEDURE c()
BEGIN
DECLARE mobile VARCHAR(255);
DECLARE emergencyContactMobile VARCHAR(255);
DECLARE realName VARCHAR(255);
DECLARE identity VARCHAR(255);
DECLARE DoctorName VARCHAR(255);
DECLARE createdate VARCHAR(255);
DECLARE userLabelIds VARCHAR(255);
DECLARE userLabelName1 VARCHAR(255);
DECLARE userLabelName2 VARCHAR(255);
DECLARE userLabelName3 VARCHAR(255);
DECLARE userLabelName4 VARCHAR(255);
DECLARE userLabelName VARCHAR(255);
DECLARE dones INT;
DECLARE cur_user_id CURSOR FOR
SELECT
SUBSTRING(payload->"$.mobile",2,CHA{R}_{L}ENGTH(payload->"$.mobile")-2) AS ‘mobile’,
SUBSTRING(payload->"$.emergencyContactMobile",2,CHA{R}_{L}ENGTH(payload->"$.emergencyContactMobile")-2)AS ‘emergencyContactMobile’ ,
SUBSTRING(payload->"$.realName",2,CHA{R}_{L}ENGTH(payload->"$.realName")-2) AS ‘realName’ ,
SUBSTRING( payload->"$.identity",2,CHA{R}_{L}ENGTH(payload->"$.identity")-2) AS ‘identity’
,(SELECT real_name FROM blade_user WHERE id=SUBSTRING(hr.payload->"$.responsibleDoctorId",2,CHA{R}_{L}ENGTH(hr.payload->"$.responsibleDoctorId")-2))AS ‘DoctorName’,
(SELECT create_time FROM hr_user_info WHERE id =hr.user_id ) AS ‘createdate’,
(SELECT user_label_ids FROM hr_user_info WHERE id =hr.user_id ) ‘userLabelIds’
FROM hr_f_personal_basic_information hr WHERE payload->"$.mobile"="无"ANDpayload->"$.emergencyContactMobile"=“无”;
#游标中的内容执行完后将done设置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dones=1;
#打开游标
OPEN cur_user_id;
#执行循环
posLoop:LOOP
#判断是否结束循环
IF dones=1 THEN
LEAVE posLoop;
END IF;
#取游标中的值
FETCH cur_user_id INTO mobile,emergencyContactMobile,realName,identity,DoctorName,createdate,userLabelIds;
#截取人群标签id进行循环搜索然后拼接起来 (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(userLabelIds,’,’, 1), ‘,’, -1)) 通过逗号分割
SELECT short_name into userLabelName1 FROM hr_lable_info WHERE id =(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(userLabelIds,’,’, 1), ‘,’, -1));
SELECT short_name into userLabelName2 FROM hr_lable_info WHERE id =(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(userLabelIds,’,’, 2), ‘,’, -1));
SELECT short_name into userLabelName3 FROM hr_lable_info WHERE id =(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(userLabelIds,’,’, 3), ‘,’, -1));
SELECT short_name into userLabelName4 FROM hr_lable_info WHERE id =(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(userLabelIds,’,’, 4), ‘,’, -1));
#select user_ids,mobil;
if userLabelName4 <=> userLabelName3 then
set userLabelName4="";
end if;
IF userLabelName3 <=> userLabelName2 then
set userLabelName3="";
end if;
IF userLabelName2 <=> userLabelName1 then
set userLabelName2="";
end if;
set userLabelName = CONCAT(userLabelName1,userLabelName2,userLabelName3,userLabelName4);
select userLabelName,mobile,emergencyContactMobile,realName,identity,DoctorName,createdate;
insert into new_table values(mobile,emergencyContactMobile,realName,identity,DoctorName,createdate,userLabelName);
END LOOP posLoop;
#释放游标
CLOSE cur_user_id;
END
SELECT * FROM new_table
call c();