mysql 复制表游标插入

BEGIN


DECLARE userId VARCHAR(22);
# 公司ID
DECLARE cid VARCHAR(22);
# 用户ID
DECLARE cname VARCHAR(64);
# 服务ID
DECLARE cdesc TEXT;
# 创建人
DECLARE clogo VARCHAR(255);
# 编辑人
DECLARE ccityId VARCHAR(64);
# 编辑时间
DECLARE caddress VARCHAR(255);
# 创建时间
DECLARE cscale VARCHAR(64);
DECLARE ctype VARCHAR(64);
DECLARE clink VARCHAR(64);
# 公司ID
DECLARE rmid VARCHAR(64);
DECLARE rmcode VARCHAR(64);
# 用户ID
DECLARE rmcid VARCHAR(64);
# 服务ID
DECLARE recnitName VARCHAR(64);
# 创建人
DECLARE rmccname VARCHAR(64);
# 编辑人
DECLARE rmaddress VARCHAR(255);
# 编辑时间
DECLARE rmserviceId VARCHAR(64);
# 创建时间
DECLARE rmdesc TEXT;
DECLARE rmedu smallint(6);
DECLARE rmexp smallint(6);
DECLARE rmjobtyp smallint(6);
DECLARE b int default 0;
DECLARE r int default 0;
DECLARE createNo datetime;
-- 账号编号
DECLARE UNUM VARCHAR(32);
# 创建游标
DECLARE rs CURSOR FOR SELECT qyid cid,qylink clink,qyname cname,qcityid ccityId,qaddress caddress,qlog clogo,qcale cscale,qtype ctype,qdesc cdesc,CREATED_NO createNo FROM q_company WHERE IS_COLLECT IS NULL;
# 这个用于处理游标到达最后一行的情况  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b=1;
# 打开游标
OPEN rs; 
# 获取游标当前指针的记录，读取一行数据并传给变量
fetch  rs into cid,clink,cname,ccityid,caddress,clogo,cscale,ctype,cdesc,createNo;
SELECT cid;
# 循环游标
while b <> 1 do
START TRANSACTION;
SELECT b;
SET userId=rand_string(22,5);
SELECT CONCAT('0',COUNT(*)+1) INTO UNUM FROM u_user u WHERE u.ACCOUNT_NAME LIKE '%@ibmall.cn';
# 插入到用户服务表里
INSERT INTO `u_company` (`ID`, `NAME`, `DESCRIPTION`,`LOGO`,`CITY_ID`,`ADDRESS`, `SCALE`, `TYPE`,`VERIFY_STATUS`, `CREATED_BY`, `MODIFIED_BY`, `CREATED_ON`, `MODIFIED_ON`) VALUES (cid, cname, cdesc, clogo, ccityId, caddress, cscale, ctype,1,null,null,NOW(),NOW());
# 插入到用户服务表里
INSERT INTO `u_user` (`ID`, `ACCOUNT_NAME`, `PASSWORD`,`SALT`,`REGISTER_TIME`,`CREATE_TYPE`,`COMPANY_ID`, `IS_EMAIL_VERIFY`,`CLIENT_NAME`,`BINDING_EMAIL`,`CREATED_ON`, `MODIFIED_ON`) VALUES (userId, CONCAT(UNUM,'@ibmall.cn'), 'f64fb77415ff5686d7e8dee82e8ebbf5', '76903465bac30553', createNo, 1, cid, 1,'collect',CONCAT(UNUM,'@ibmall.cn'),NOW(), NOW());
SELECT UNUM;
UPDATE q_company SET IS_COLLECT = 1 WHERE qyid = cid;
BEGIN
# 创建游标
DECLARE rm CURSOR FOR SELECT qr.ID rmid,qr.gwcode rmcode,qr.companyId rmcid,qr.recnitName recnitName,qr.cityId rmccname,qr.address rmaddress,qr.serviceId rmserviceId,qr.DESCRIPTION rmdesc,qr.EDUCATION rmedu,qr.EXPERIENCE rmexp,qr.JOB_TYPE rmjobtyp FROM q_recnitment qr where qr.companyId = cid AND IS_COLLECT IS NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET rmcode=1;
# 打开游标
OPEN rm; 
fetch rm into rmid,rmcode,rmcid,recnitName,rmccname,rmaddress,rmserviceId,rmdesc,rmedu,rmexp,rmjobtyp;
# 循环游标
while rmcode <> 1 do
START TRANSACTION;
UPDATE q_recnitment SET IS_COLLECT = 1 WHERE ID = rmid;
INSERT INTO `r_recruitment` (`ID`, `CODE`, `COMPANY_ID`,`NAME`,`CITY_ID`,`ADDRESS`,`SERVICE_ID`,`DESCRIPTION`, `EDUCATION`,`EXPERIENCE`,`STATUS`,`JOB_TYPE`, `EMAIL`,`RELEASE_TIME`,`CREATED_ON`,`MODIFIED_ON`) VALUES (rmid, rmcode,rmcid, recnitName, rmccname, rmaddress, rmserviceId, rmdesc,rmedu,rmexp,1,rmjobtyp,CONCAT(UNUM,'@ibmall.cn'),NOW(),NOW(),NOW());
COMMIT;
# 获取游标当前指针的记录，读取一行数据并传给变量
fetch rm into rmid,rmcode,rmcid,recnitName,rmccname,rmaddress,rmserviceId,rmdesc,rmedu,rmexp,rmjobtyp;
end while;
# 关闭游标  
CLOSE rm;
END;
# 读取下一行的数据  
COMMIT;
fetch  rs into cid,clink,cname,ccityid,caddress,clogo,cscale,ctype,cdesc,createNo;
end while;
# 关闭游标  
CLOSE rs;
END