MySql 存储过程使用游标循环插入数据很慢
pwtan 2016-11-27 05:55:05 请高手指教,以下是存储过程代码:
CREATE PROCEDURE `PRO_BP_ANALYSIS_PROVIDER_RECRUIT`(IN P_TYPE VARCHAR(1))
BEGIN
DECLARE strGUID VARCHAR(32);
DECLARE strFK_ORG_GUID VARCHAR(32);
DECLARE strFK_PRODUCT_GUID VARCHAR(32);
DECLARE strFK_PROVIDER_GUID VARCHAR(32);
DECLARE strRECRUIT1_NO VARCHAR(32);
DECLARE strRECRUIT2_NO VARCHAR(32);
DECLARE strRECRUIT3_NO VARCHAR(32);
DECLARE strRECRUIT4_NO VARCHAR(32);
DECLARE strREAL_RECRUIT VARCHAR(32);
DECLARE strREAL_RECRUIT_TEMP VARCHAR(32);
DECLARE strRECRUIT_GUID VARCHAR(32);
DECLARE strRECRUIT_CODE VARCHAR(32);
DECLARE strExecSql VARCHAR(4000); -- 执行文本
DECLARE ii INT(11);
DECLARE DONE INT DEFAULT 0; -- 是否达到记录的末尾控制变量
-- 游标
DECLARE CUR_1 CURSOR FOR
SELECT GUID,FK_ORG_GUID,FK_PRODUCT_GUID,FK_PROVIDER_GUID,
RECRUIT1_NO,RECRUIT2_NO,RECRUIT3_NO,RECRUIT4_NO,REAL_RECRUIT
FROM T_BP_PROVIDER_RECRUIT; -- 该表只有5398条记录
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
-- 创建临时表
-- DROP TABLE IF EXISTS T_BP_PROVIDER_RECRUIT_TEMP;
CREATE TEMPORARY TABLE IF NOT EXISTS T_BP_PROVIDER_RECRUIT_TEMP -- 不存在则创建临时表
(
GUID VARCHAR(32),
FK_ORG_GUID VARCHAR(32),
FK_PRODUCT_GUID VARCHAR(32),
FK_PROVIDER_GUID VARCHAR(32),
RECRUIT1_NO VARCHAR(32),
RECRUIT2_NO VARCHAR(32),
RECRUIT3_NO VARCHAR(32),
RECRUIT4_NO VARCHAR(32),
REAL_RECRUIT VARCHAR(32)
);
TRUNCATE TABLE T_BP_PROVIDER_RECRUIT_TEMP; -- 使用前先清空临时表
SET ii = 0;
-- 打开游标
OPEN CUR_1;
-- 获取第一条记录
FETCH CUR_1 INTO strGUID,strFK_ORG_GUID,strFK_PRODUCT_GUID,strFK_PROVIDER_GUID,
strRECRUIT1_NO,strRECRUIT2_NO,strRECRUIT3_NO,strRECRUIT4_NO,strREAL_RECRUIT;
WHILE DONE <> 1 DO
SET ii = ii + 1;
SELECT FUN_ISNULL(GUID,'') INTO strRECRUIT_GUID FROM T_BP_RECRUIT_INFO WHERE RECRUIT_NO = strREAL_RECRUIT;
SET DONE = 0;
SELECT a.GUID,a.PARENT_GUID,a.RECRUIT_GUID,a.DIC_RECRUITTYPE_CODE,b.RECRUIT_NO,b.RECRUIT_NAME
INTO @GUID,@PARENT_GUID,@RECRUIT_GUID,@DIC_RECRUITTYPE_CODE,@RECRUIT_NO,@RECRUIT_NAME
FROM T_BP_RECRUIT_RELATION_SETTING a
LEFT OUTER JOIN T_BP_RECRUIT_INFO b on a.RECRUIT_GUID=b.GUID
WHERE a.RECRUIT_GUID=strRECRUIT_GUID
ORDER BY a.DIC_RECRUITTYPE_CODE DESC
LIMIT 0,1;
SET DONE = 0;
SET @sqlText = CONCAT('SELECT ''',@RECRUIT_NO,''' into @RECRUIT',@DIC_RECRUITTYPE_CODE,'_NO');
PREPARE strExecSql from @sqlText;
EXECUTE strExecSql;
DEALLOCATE PREPARE strExecSql;
SET DONE = 0;
WHILE @DIC_RECRUITTYPE_CODE > 1 DO
SET @DIC_RECRUITTYPE_CODE = @DIC_RECRUITTYPE_CODE - 1;
IF (@DIC_RECRUITTYPE_CODE = 1) THEN
SELECT GUID INTO @RECRUIT1_NO FROM T_BP_RECRUIT_RELATION_SETTING WHERE GUID = @PARENT_GUID;
SET DONE = 0;
ELSE
SET @sqlText = CONCAT('SELECT a.PARENT_GUID,b.RECRUIT_NO INTO @PARENT_GUID,@RECRUIT',@DIC_RECRUITTYPE_CODE,'_NO
FROM T_BP_RECRUIT_RELATION_SETTING a
LEFT OUTER JOIN T_BP_RECRUIT_INFO b on a.RECRUIT_GUID=b.GUID
WHERE a.GUID=''',@PARENT_GUID,'''');
PREPARE strExecSql from @sqlText;
EXECUTE strExecSql;
DEALLOCATE PREPARE strExecSql;
SET DONE = 0;
END IF;
END WHILE;
-- 注释insrt into 用时4.226s
-- 使用insrt into 用时296.646s
INSERT INTO T_BP_PROVIDER_RECRUIT_TEMP(GUID,FK_ORG_GUID,FK_PRODUCT_GUID,FK_PROVIDER_GUID,RECRUIT1_NO,RECRUIT2_NO,RECRUIT3_NO,RECRUIT4_NO,REAL_RECRUIT)
VALUES(strGUID,strFK_ORG_GUID,strFK_PRODUCT_GUID,strFK_PROVIDER_GUID,@RECRUIT1_NO,@RECRUIT2_NO,@RECRUIT3_NO,@RECRUIT4_NO,strREAL_RECRUIT);
SET strREAL_RECRUIT_TEMP = strREAL_RECRUIT;
SET @sqlText = '';
SET strExecSql= '';
SET @RECRUIT1_NO = '';
SET @RECRUIT2_NO = '';
SET @RECRUIT3_NO = '';
SET @RECRUIT4_NO = '';
SET @GUID = '';
SET @PARENT_GUID = '';
SET @RECRUIT_GUID = '';
SET @DIC_RECRUITTYPE_CODE = '';
SET @RECRUIT_NO = '';
SET @RECRUIT_NAME = '';
-- 取下一条记录
FETCH CUR_1 INTO strGUID,strFK_ORG_GUID,strFK_PRODUCT_GUID,strFK_PROVIDER_GUID,
strRECRUIT1_NO,strRECRUIT2_NO,strRECRUIT3_NO,strRECRUIT4_NO,strREAL_RECRUIT;
END WHILE;
CLOSE CUR_1;
SELECT * FROM T_BP_PROVIDER_RECRUIT_TEMP;
END