我写了个存储过程,里面有2个游标,但是第二个循环只执行了一次,要怎么弄才行啊?
大体代码: DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_CompanyID; IF NOT done THEN
。
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET done = 0;
OPEN cur2;
REPEAT
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER;
IF NOT done THEN
。。
END IF;
UNTIL done END REPEAT;
CLOSE cur2;
大体代码: DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_CompanyID; IF NOT done THEN
。
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET done = 0;
OPEN cur2;
REPEAT
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER;
IF NOT done THEN
。。
END IF;
UNTIL done END REPEAT;
CLOSE cur2;
SET done = 0;
原来的处理是想从动态的表里把数据读到cur1,然后进行处理。
后来发现好像Mysql不支持动态游标,就先把数据都INSERT到一个TEMP表里,然后用cur2来读取。假如把程序改成下面的样子cur2就能够循环多次,数据能够登录,只是会出错。(Duplicate entry '00007' for key 'PRIMARY' 错误) DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; OPEN cur1;
REPEAT
FETCH cur1 INTO v_CompanyID; IF NOT done THEN
。
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
OPEN cur2;
REPEAT
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER;
IF NOT done1 THEN
。。
END IF;
UNTIL done1 END REPEAT;
CLOSE cur2;
DECLARE done INT DEFAULT 0;
DECLARE v_CompanyID varchar(5);
DECLARE v_UNITED_ID varchar(8);
DECLARE v_MAIL_HASH_CODE varchar(32);
DECLARE v_OTHER varchar(32); DECLARE cur1 CURSOR FOR SELECT COMPANY_ID FROM COMPANY_MST;
DECLARE cur2 CURSOR FOR SELECT UNITED_ID, COMPANY_CODE, MAIL_HASH_CODE, OTHER FROM REPEAT_TEMP; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; DELETE FROM REPEAT_TEMP;
DELETE FROM REPEAT_TABLE; OPEN cur1;
REPEAT
FETCH cur1 INTO v_CompanyID; IF NOT done THEN SET @sqlExec=CONCAT('INSERT INTO REPEAT_TEMP SELECT UNITED_ID, \'', v_CompanyID, '\', MAIL_HASH_CODE, CONCAT(USER_BIRTH_DATE, USER_SEX_CODE, USER_ZIP, USER_PREFECTURE_CODE) AS OTHER FROM COMPANY_',v_CompanyID); PREPARE stmt1 FROM @sqlExec;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; END IF;
UNTIL done END REPEAT;
CLOSE cur1; SET done = 0; OPEN cur2;
REPEAT
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER;
IF NOT done THEN
.......
END IF;
UNTIL done END REPEAT;
CLOSE cur2;
END $$
select count(*) from REPEAT_TEMP;SET done = 0;
里面有14条记录。我感觉是DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;这个语句的问题,它参照了cur1的结果,所以执行一次以后就跳出了。
代码:(cur2部分)
SELECT COUNT(*) INTO v_Count FROM REPEAT_TEMP;
OPEN cur2;
Cur2Loop:Loop
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER;
IF v_Index = v_Count THEN
LEAVE Cur2Loop;
END IF;
SET v_Index = v_Index + 1;
END LOOP;
CLOSE cur2;
我也怀疑,
SET @SQLSTATE='0000';
这样试试
DROP TABLE IF EXISTS `panelmixnew`.`panel_company_mst`;
CREATE TABLE `panelmixnew`.`panel_company_mst` (
`PANEL_COMPANY_ID` varchar(5) NOT NULL,
`COMPANY_NAME` varchar(255) DEFAULT NULL,
`ACTIVE_RATE_UPDATE_DATE` datetime DEFAULT NULL,
`MAIN_CHARGER_NAME` varchar(32) DEFAULT NULL,
`MAIN_CHARGER_TEL` varchar(13) DEFAULT NULL,
`MAIN_CHARGER_MAIL` varchar(255) DEFAULT NULL,
`SYS_CHARGER_NAME` varchar(32) DEFAULT NULL,
`SYS_CHARGER_TEL` varchar(13) DEFAULT NULL,
`SYS_CHARGER_MAIL` varchar(255) DEFAULT NULL,
`REC_DATE` datetime DEFAULT NULL,
`REC_USR_ID` varchar(5) DEFAULT NULL,
`UPDATE_DATE` datetime DEFAULT NULL,
`UPDATE_USR_ID` varchar(5) DEFAULT NULL,
PRIMARY KEY (`PANEL_COMPANY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `panelmixnew`.`panel_001`;
CREATE TABLE `panelmixnew`.`panel_001` (
`UNITED_ID` varchar(8) NOT NULL,
`MAIL_HASH_CODE` varchar(32) NOT NULL,
`COMPANY_CODE` varchar(8) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`USER_BIRTH_DATE` date NOT NULL,
`USER_SEX_CODE` varchar(1) NOT NULL,
`USER_MARRIAGE_CODE` varchar(1) DEFAULT NULL,
`USER_ZIP` varchar(7) DEFAULT NULL,
`USER_PREFECTURE_CODE` varchar(2) DEFAULT NULL,
`USER_REC_DATE` datetime NOT NULL,
`USER_UPDATE_DATE` datetime NOT NULL,
`USER_UPDATE_ID` varchar(32) NOT NULL,
`USER_FIRST_REC_DATE` datetime NOT NULL,
`USER_FIRST_REC_ID` varchar(32) NOT NULL,
`USER_ABOLITION_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`UNITED_ID`),
KEY `Index_2` (`MAIL_HASH_CODE`,`USER_ABOLITION_DATE`),
KEY `Index_3` (`USER_BIRTH_DATE`,`USER_SEX_CODE`,`USER_ZIP`,`USER_PREFECTURE_CODE`,`USER_ABOLITION_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `panelmixnew`.`repeat_table`;
CREATE TABLE `panelmixnew`.`repeat_table` (
`UNITED_ID` varchar(8) NOT NULL,
`COMPANY_CODE` varchar(5) NOT NULL,
`REPEATID` int(11) DEFAULT NULL,
`MAIL_HASH_CODE` varchar(32) NOT NULL,
`OTHER` varchar(32) DEFAULT NULL,
PRIMARY KEY (`UNITED_ID`,`COMPANY_CODE`),
KEY `Index_3` (`OTHER`),
KEY `Index_4` (`REPEATID`),
KEY `Index_2` (`MAIL_HASH_CODE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `panelmixnew`.`repeat_temp`;
CREATE TABLE `panelmixnew`.`repeat_temp` (
`UNITED_ID` varchar(8) NOT NULL,
`COMPANY_CODE` varchar(5) NOT NULL,
`MAIL_HASH_CODE` varchar(32) NOT NULL,
`OTHER` varchar(32) DEFAULT NULL,
PRIMARY KEY (`UNITED_ID`,`COMPANY_CODE`),
KEY `Index_3` (`OTHER`),
KEY `Index_2` (`MAIL_HASH_CODE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
代码:
DELIMITER $$DROP PROCEDURE IF EXISTS `panelmixnew`.`Repeat` $$
CREATE PROCEDURE `panelmixnew`.`Repeat` ()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE done1 INT DEFAULT 0;
DECLARE v_CompanyID varchar(5);
DECLARE v_UNITED_ID varchar(8);
DECLARE v_MAIL_HASH_CODE varchar(32);
DECLARE v_OTHER varchar(32);
DECLARE v_MailRepeatID INT;
DECLARE v_OtherRepeatID INT;
DECLARE v_RepeatID INT DEFAULT 0;
DECLARE cur2_loop_Count INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT PANEL_COMPANY_ID FROM PANEL_COMPANY_MST;
DECLARE cur2 CURSOR FOR SELECT UNITED_ID, COMPANY_CODE, MAIL_HASH_CODE, OTHER FROM REPEAT_TEMP; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; DELETE FROM REPEAT_TEMP;
DELETE FROM REPEAT_TABLE; OPEN cur1;
REPEAT
FETCH cur1 INTO v_CompanyID; IF NOT done THEN SET @sqlExec=CONCAT('INSERT INTO REPEAT_TEMP SELECT UNITED_ID, \'', v_CompanyID, '\', MAIL_HASH_CODE, CONCAT(USER_BIRTH_DATE, USER_SEX_CODE, USER_ZIP, USER_PREFECTURE_CODE) AS OTHER FROM PANEL_',v_CompanyID); PREPARE stmt1 FROM @sqlExec;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; END IF;
UNTIL done END REPEAT;
CLOSE cur1;
OPEN cur2;
REPEAT
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER; IF NOT done THEN SET v_MailRepeatID = null;
SET v_OtherRepeatID = null; SELECT REPEATID INTO v_MailRepeatID FROM REPEAT_TABLE WHERE MAIL_HASH_CODE = v_MAIL_HASH_CODE LIMIT 1; IF v_OTHER IS NOT null THEN
SELECT REPEATID INTO v_OtherRepeatID FROM REPEAT_TABLE WHERE OTHER = v_OTHER LIMIT 1;
END IF; IF v_MailRepeatID IS NOT null AND v_OtherRepeatID IS NOT null THEN
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_MailRepeatID, v_MAIL_HASH_CODE, v_OTHER);
UPDATE REPEAT_TABLE SET REPEATID = v_MailRepeatID WHERE REPEATID = v_OTHER; ELSEIF v_MailRepeatID IS NOT null THEN
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_MailRepeatID, v_MAIL_HASH_CODE, v_OTHER); ELSEIF v_OtherRepeatID IS NOT null THEN
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_OtherRepeatID, v_MAIL_HASH_CODE, v_OTHER); ELSE
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_RepeatID, v_MAIL_HASH_CODE, v_OTHER);
SET v_RepeatID = v_RepeatID + 1; END IF;
END IF; SET cur2_loop_Count = cur2_loop_Count + 1;
UNTIL done END REPEAT;
CLOSE cur2;SELECT cur2_loop_Count;END $$DELIMITER ;
CREATE TABLE `panel_001` (
`UNITED_ID` varchar(8) NOT NULL,
`MAIL_HASH_CODE` varchar(32) NOT NULL,
`COMPANY_CODE` varchar(8) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`USER_BIRTH_DATE` date NOT NULL,
`USER_SEX_CODE` varchar(1) NOT NULL,
`USER_MARRIAGE_CODE` varchar(1) DEFAULT NULL,
`USER_ZIP` varchar(7) DEFAULT NULL,
`USER_PREFECTURE_CODE` varchar(2) DEFAULT NULL,
`USER_REC_DATE` datetime NOT NULL,
`USER_UPDATE_DATE` datetime NOT NULL,
`USER_UPDATE_ID` varchar(32) NOT NULL,
`USER_FIRST_REC_DATE` datetime NOT NULL,
`USER_FIRST_REC_ID` varchar(32) NOT NULL,
`USER_ABOLITION_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`UNITED_ID`),
KEY `Index_2` (`MAIL_HASH_CODE`,`USER_ABOLITION_DATE`),
KEY `Index_3` (`USER_BIRTH_DATE`,`USER_SEX_CODE`,`USER_ZIP`,`USER_PREFECTURE_CODE`,`USER_ABOLITION_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;--
-- Dumping data for table `panel_001`
--/*!40000 ALTER TABLE `panel_001` DISABLE KEYS */;
INSERT INTO `panel_001` (`UNITED_ID`,`MAIL_HASH_CODE`,`COMPANY_CODE`,`USER_ID`,`USER_BIRTH_DATE`,`USER_SEX_CODE`,`USER_MARRIAGE_CODE`,`USER_ZIP`,`USER_PREFECTURE_CODE`,`USER_REC_DATE`,`USER_UPDATE_DATE`,`USER_UPDATE_ID`,`USER_FIRST_REC_DATE`,`USER_FIRST_REC_ID`,`USER_ABOLITION_DATE`) VALUES
('00000001','dd39946cd70125f4871a20a9ebb1d62e','001','A001','1979-01-01','1','1',NULL,'12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000002','0ace9102af82de8923bcff6af6ec7205','001','A002','1980-01-01','1','1','2100099','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000003','e3fcf795d355abfc78bc6e8ee66d90dc','001','A003','1981-01-01','2','1','2100099','23','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000004','e4bd0574108056362eb0e9aa19c3acb6','001','A004','1982-01-01','1','1','2100099','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000005','e3fcf795d355abfc78bc6e8ee66d90dc','001','A005','1983-01-01','1','2','2100011','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000006','f89edce12a45cf3851067a898073fdfb','001','A006','1983-01-01','1','1','2100011','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000007','fd2a55a053f3b139c40bff24f14ac1b1','001','A007','1985-01-01','2','2','2100000','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL);
/*!40000 ALTER TABLE `panel_001` ENABLE KEYS */;
--
-- Definition of table `panel_002`
--DROP TABLE IF EXISTS `panel_002`;
CREATE TABLE `panel_002` (
`UNITED_ID` varchar(8) NOT NULL,
`MAIL_HASH_CODE` varchar(32) NOT NULL,
`COMPANY_CODE` varchar(8) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`USER_BIRTH_DATE` date NOT NULL,
`USER_SEX_CODE` varchar(1) NOT NULL,
`USER_MARRIAGE_CODE` varchar(1) DEFAULT NULL,
`USER_ZIP` varchar(7) DEFAULT NULL,
`USER_PREFECTURE_CODE` varchar(2) DEFAULT NULL,
`USER_REC_DATE` datetime NOT NULL,
`USER_UPDATE_DATE` datetime NOT NULL,
`USER_UPDATE_ID` varchar(32) NOT NULL,
`USER_FIRST_REC_DATE` datetime NOT NULL,
`USER_FIRST_REC_ID` varchar(32) NOT NULL,
`USER_ABOLITION_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`UNITED_ID`),
KEY `Index_2` (`MAIL_HASH_CODE`,`USER_ABOLITION_DATE`),
KEY `Index_3` (`USER_BIRTH_DATE`,`USER_SEX_CODE`,`USER_ZIP`,`USER_PREFECTURE_CODE`,`USER_ABOLITION_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;--
-- Dumping data for table `panel_002`
--/*!40000 ALTER TABLE `panel_002` DISABLE KEYS */;
INSERT INTO `panel_002` (`UNITED_ID`,`MAIL_HASH_CODE`,`COMPANY_CODE`,`USER_ID`,`USER_BIRTH_DATE`,`USER_SEX_CODE`,`USER_MARRIAGE_CODE`,`USER_ZIP`,`USER_PREFECTURE_CODE`,`USER_REC_DATE`,`USER_UPDATE_DATE`,`USER_UPDATE_ID`,`USER_FIRST_REC_DATE`,`USER_FIRST_REC_ID`,`USER_ABOLITION_DATE`) VALUES
('00000001','dd39946cd70125f4871a20a9ebb1d62e','002','A001','1979-01-01','1','1','2100099','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000002','0ace9102af82de8923bcff6af6ec7205','002','A002','1980-01-01','1','1','2100099','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000003','e3fcf795d355abfc78bc6e8ee66d90dc','002','A003','1981-01-01','2','1','2100099','23','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000004','e4bd0574108056362eb0e9aa19c3acb6','002','A004','1982-01-01','1','1','2100099','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000005','169e0815b951b4375ee2c1cd1dce5601','002','A005','1987-01-01','1','2','2100011','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000006','f89edce12a45cf3851067a898073fdfb','002','A006','1984-01-01','1','1','2100099','2','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL),
('00000007','fd2a55a053f3b139c40bff24f14ac1b1','002','A007','1985-01-01','2','2','2100000','12','2008-01-01 00:00:00','2009-12-21 00:00:00','001','2009-12-21 00:00:00','001',NULL);
/*!40000 ALTER TABLE `panel_002` ENABLE KEYS */;
--
-- Definition of table `panel_company_mst`
--DROP TABLE IF EXISTS `panel_company_mst`;
CREATE TABLE `panel_company_mst` (
`PANEL_COMPANY_ID` varchar(5) NOT NULL,
`COMPANY_NAME` varchar(255) DEFAULT NULL,
`ACTIVE_RATE_UPDATE_DATE` datetime DEFAULT NULL,
`MAIN_CHARGER_NAME` varchar(32) DEFAULT NULL,
`MAIN_CHARGER_TEL` varchar(13) DEFAULT NULL,
`MAIN_CHARGER_MAIL` varchar(255) DEFAULT NULL,
`SYS_CHARGER_NAME` varchar(32) DEFAULT NULL,
`SYS_CHARGER_TEL` varchar(13) DEFAULT NULL,
`SYS_CHARGER_MAIL` varchar(255) DEFAULT NULL,
`REC_DATE` datetime DEFAULT NULL,
`REC_USR_ID` varchar(5) DEFAULT NULL,
`UPDATE_DATE` datetime DEFAULT NULL,
`UPDATE_USR_ID` varchar(5) DEFAULT NULL,
PRIMARY KEY (`PANEL_COMPANY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;--
-- Dumping data for table `panel_company_mst`
--/*!40000 ALTER TABLE `panel_company_mst` DISABLE KEYS */;
INSERT INTO `panel_company_mst` (`PANEL_COMPANY_ID`,`COMPANY_NAME`,`ACTIVE_RATE_UPDATE_DATE`,`MAIN_CHARGER_NAME`,`MAIN_CHARGER_TEL`,`MAIN_CHARGER_MAIL`,`SYS_CHARGER_NAME`,`SYS_CHARGER_TEL`,`SYS_CHARGER_MAIL`,`REC_DATE`,`REC_USR_ID`,`UPDATE_DATE`,`UPDATE_USR_ID`) VALUES
('001','A社',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('002','B社',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `panel_company_mst` ENABLE KEYS */;
SELECT REPEATID INTO v_MailRepeatID FROM REPEAT_TABLE WHERE MAIL_HASH_CODE = v_MAIL_HASH_CODE LIMIT 1; IF v_OTHER IS NOT null THEN
SELECT REPEATID INTO v_OtherRepeatID FROM REPEAT_TABLE WHERE OTHER = v_OTHER LIMIT 1;
END IF;如果把它们删了就能循环,但是这样的检索和CURSOR也没关系啊等待解答
CREATE PROCEDURE `Repeat` ()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE done1 INT DEFAULT 0;
DECLARE v_CompanyID varchar(5);
DECLARE v_UNITED_ID varchar(8);
DECLARE v_MAIL_HASH_CODE varchar(32);
DECLARE v_OTHER varchar(32);
DECLARE v_MailRepeatID INT;
DECLARE v_OtherRepeatID INT;
DECLARE v_RepeatID INT DEFAULT 0;declare done_x INT; -- ACMAIN // add a new variable for keep the status of done
DECLARE cur2_loop_Count INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT PANEL_COMPANY_ID FROM PANEL_COMPANY_MST;
DECLARE cur2 CURSOR FOR SELECT UNITED_ID, COMPANY_CODE, MAIL_HASH_CODE, OTHER FROM REPEAT_TEMP;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;DELETE FROM REPEAT_TEMP;
DELETE FROM REPEAT_TABLE;OPEN cur1;
REPEAT
FETCH cur1 INTO v_CompanyID; IF NOT done THEN SET @sqlExec=CONCAT('INSERT INTO REPEAT_TEMP SELECT UNITED_ID, \'', v_CompanyID, '\', MAIL_HASH_CODE, CONCAT(USER_BIRTH_DATE, USER_SEX_CODE, USER_ZIP, USER_PREFECTURE_CODE) AS OTHER FROM PANEL_',v_CompanyID); PREPARE stmt1 FROM @sqlExec;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; END IF;
UNTIL done END REPEAT;
CLOSE cur1;set done = 0; -- add by ACMAIN
OPEN cur2;
REPEAT
FETCH cur2 INTO v_UNITED_ID, v_CompanyID, v_MAIL_HASH_CODE, v_OTHER; IF NOT done THEN SET v_MailRepeatID = null;
SET v_OtherRepeatID = null; SELECT REPEATID INTO v_MailRepeatID FROM REPEAT_TABLE WHERE MAIL_HASH_CODE = v_MAIL_HASH_CODE LIMIT 1;
-- the done had been flaged by this SELECT ... LIMIT 1 !!! //ACMAIN
IF v_OTHER IS NOT null THEN
SELECT REPEATID INTO v_OtherRepeatID FROM REPEAT_TABLE WHERE OTHER = v_OTHER LIMIT 1;
END IF; IF v_MailRepeatID IS NOT null AND v_OtherRepeatID IS NOT null THEN
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_MailRepeatID, v_MAIL_HASH_CODE, v_OTHER);
UPDATE REPEAT_TABLE SET REPEATID = v_MailRepeatID WHERE REPEATID = v_OTHER; ELSEIF v_MailRepeatID IS NOT null THEN
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_MailRepeatID, v_MAIL_HASH_CODE, v_OTHER); ELSEIF v_OtherRepeatID IS NOT null THEN
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_OtherRepeatID, v_MAIL_HASH_CODE, v_OTHER); ELSE
INSERT INTO REPEAT_TABLE (UNITED_ID, COMPANY_CODE, REPEATID, MAIL_HASH_CODE, OTHER) VALUE(v_UNITED_ID, v_CompanyID, v_RepeatID, v_MAIL_HASH_CODE, v_OTHER);
SET v_RepeatID = v_RepeatID + 1; END IF; set done=0; -- add by ACMAIN, as the done had been flaged by select ... limit 1! END IF;
SET cur2_loop_Count = cur2_loop_Count + 1;
UNTIL done END REPEAT;
CLOSE cur2;SELECT cur2_loop_Count;END $$DELIMITER ;call `Repeat`()
+-----------------+
| cur2_loop_Count |
+-----------------+
| 15 |
+-----------------+
1 row in set (0.45 sec)Query OK, 0 rows affected (0.45 sec)mysql>
SELECT REPEATID INTO v_MailRepeatID FROM REPEAT_TABLE WHERE MAIL_HASH_CODE = v_MAIL_HASH_CODE LIMIT 1;
检查一下是否有记录,如果没有,DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
这句条件成立
DECLARE cur2 CURSOR FOR SELECT UNITED_ID, COMPANY_CODE, MAIL_HASH_CODE, OTHER FROM REPEAT_TEMP; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;这个错误处理机制,确实只能作用于第一个游标的。
两个游标时,怎么处理呐?