游标结果集有20条记录,但游标到第7条就跳出,但如截图所示是有结果集的,且数据与之前数据类似,请各位大神帮忙看一下是什么问题,谢谢!DECLARE done INT DEFAULT FALSE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
declare fxjsgspz_cur CURSOR for select IFNULL(t.xh,''),t.fxbbdm,t.fxzdmc,IFNULL(t.fxzdmcms,''), IFNULL(t.fxzdjsgsh,''),IFNULL(t.fxzdjsgshsm,''),IFNULL(t.fjdfxzdmc,'') from ddss_fx_fxjsgspz t where t.fxbbdm = fxbbdm and t.fxbbdm = fxbbdm and t.yxqq <= SYSDATE() and t.yxqz >= SYSDATE() and t.yxbz = 'Y' order by t.fxzdjsgsh; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; create temporary table if not exists tmpTable ( fxzdmc varchar(50), bnnd varchar(10), bnshz double(16,2), snnd varchar(10), snshz double(16,2), fjdfxzdmc varchar(50) ); OPEN fxjsgspz_cur; seq_loop:loop FETCH NEXT FROM fxjsgspz_cur INTO _xh,_fxbbdm,_fxzdmc,_fxzdmcms,_fxzdjsgsh,_fxzdjsgshsm,_fjdfxzdmc; IF done THEN LEAVE seq_loop; END IF;
SET @insertTmpTableSql := CONCAT("INSERT INTO tmpTable(fxzdmc,bnnd,bnshz,snnd,snshz,fjdfxzdmc) VALUES ( '",_fxzdmc,"','",bnnd,"',null,'",snnd,"',null,'",_fjdfxzdmc,"')"); PREPARE insertTmpTableSTMT FROM @insertTmpTableSql; EXECUTE insertTmpTableSTMT; DEALLOCATE PREPARE insertTmpTableSTMT; IF (_fxzdjsgsh is NULL OR _fxzdjsgsh = '') THEN select t.sjybmc, t.sjybzdmc into _sjybmc, _sjybzdmc from ddss_fx_fxzdsjypz t where t.fxbbdm = _fxbbdm and t.fxzdmc = _fxzdmc and t.yxbz = 'Y'; SET _nd = bnnd; SET @selectysjsql := CONCAT("SELECT ",_sjybzdmc," into @_sjyshz from "," ( SELECT ",_sjybzdmc," FROM ddss_cj_cjzb a, ",_sjybmc," b WHERE a.uuid = b.cjzbuuid AND a.xmid = '", xmid ,"' AND a.bbrqz like '", _nd ,"%' AND a.yxbz = 'Y' AND b.yxbz = 'Y' ORDER BY a.bbrqz DESC) c LIMIT 0,1"); PREPARE selectysjSTMT FROM @selectysjsql; EXECUTE selectysjSTMT; DEALLOCATE PREPARE selectysjSTMT; SET _sjyshz = @_sjyshz; UPDATE tmpTable t set t.bnshz = _sjyshz WHERE t.fxzdmc = _fxzdmc;
SET _nd = snnd; SET @selectysjsql := CONCAT("SELECT ",_sjybzdmc," into @_sjyshz from "," ( SELECT ",_sjybzdmc," FROM ddss_cj_cjzb a, ",_sjybmc," b WHERE a.uuid = b.cjzbuuid AND a.xmid = '", xmid ,"' AND a.bbrqz like '", _nd ,"%' AND a.yxbz = 'Y' AND b.yxbz = 'Y' ORDER BY a.bbrqz DESC) c LIMIT 0,1"); PREPARE selectysjSTMT FROM @selectysjsql; EXECUTE selectysjSTMT; DEALLOCATE PREPARE selectysjSTMT; SET _sjyshz = @_sjyshz; UPDATE tmpTable t set t.snshz = _sjyshz WHERE t.fxzdmc = _fxzdmc;
ELSE
SET @bnjsgs = _fxzdjsgsh; SET @snjsgs = _fxzdjsgsh; SET @bnbj1wz = 0; SET @bnbj2wz = 0; SET @snbj1wz = 0; SET @snbj2wz = 0; SET @bnshz = 0; SET @snshz = 0; set @bngsdy = null; set @bngsdymc = NULL; SET @sngsdy = NULL; SET @sngsdymc = NULL; SET @_whileflag = TRUE;
WHILE @_whileflag DO SELECT INSTR(@bnjsgs,'[') INTO @bnbj1wz; SELECT INSTR(@bnjsgs,']') INTO @bnbj2wz; SELECT SUBSTR(@bnjsgs,@bnbj1wz,@bnbj2wz-@bnbj1wz+1) INTO @bngsdy; SELECT SUBSTR(@bnjsgs,@bnbj1wz+1,@bnbj2wz-@bnbj1wz-1) INTO @bngsdymc; SELECT INSTR(@snjsgs,'[') INTO @snbj1wz; SELECT INSTR(@snjsgs,']') INTO @snbj2wz; SELECT SUBSTR(@snjsgs,@snbj1wz,@snbj2wz-@snbj1wz+1) INTO @sngsdy; SELECT SUBSTR(@snjsgs,@snbj1wz+1,@snbj2wz-@snbj1wz-1) INTO @sngsdymc;
SET @selectysj2sql := CONCAT("SELECT t.bnshz, t.snshz INTO @bnshz, @snshz FROM tmpTable t WHERE t.fxzdmc = '",@bngsdymc,"'"); PREPARE selectysj2STMT FROM @selectysj2sql; EXECUTE selectysj2STMT; DEALLOCATE PREPARE selectysj2STMT;
SET @bnjsgs = REPLACE(@bnjsgs,CONCAT(@bngsdy),@bnshz); SET @snjsgs = REPLACE(@snjsgs,CONCAT(@sngsdy),@snshz);
SELECT INSTR(@bnjsgs,'[') INTO @bnbj1wz; SELECT INSTR(@bnjsgs,']') INTO @bnbj2wz;
SELECT INSTR(@snjsgs,'[') INTO @snbj1wz; SELECT INSTR(@snjsgs,']') INTO @snbj2wz;
IF @bnbj1wz = 0 && @bnbj2wz = 0 && @snbj1wz = 0 && @snbj2wz = 0 THEN set @_whileflag = FALSE; end IF; END WHILE; IF INSTR(@bnjsgs,'/0') > 0 THEN SET @bnjsgs = '0'; end IF; IF INSTR(@snjsgs,'/0') > 0 THEN SET @snjsgs = '0'; end IF;
SET @selectysj3sql := CONCAT("SELECT ",@bnjsgs,",",@snjsgs," INTO @bnjsgs,@snjsgs"); PREPARE selectysj3STMT FROM @selectysj3sql; EXECUTE selectysj3STMT; DEALLOCATE PREPARE selectysj3STMT; SET @updateysj4sql := CONCAT("UPDATE tmpTable t SET t.bnshz = ",@bnjsgs,", ","t.snshz = ",@snjsgs," WHERE t.fxzdmc = '",_fxzdmc,"'"); PREPARE updateysj4STMT FROM @updateysj4sql; EXECUTE updateysj4STMT; DEALLOCATE PREPARE updateysj4STMT;
END IF; end loop; CLOSE fxjsgspz_cur; SELECT * FROM tmpTable t;END
代码如下(比较乱,凑合看吧):CREATE DEFINER = 'root'@'%'
PROCEDURE test(IN xmid VARCHAR(32), IN fxbbdm VARCHAR(50), IN bnnd VARCHAR(50), IN snnd VARCHAR(50))
BEGIN DECLARE _xh VARCHAR(32);
DECLARE _fxbbdm VARCHAR(32);
DECLARE _fxzdmc VARCHAR(50);
DECLARE _fxzdmcms VARCHAR(150);
DECLARE _fxzdjsgsh VARCHAR(150);
DECLARE _fxzdjsgshsm VARCHAR(150);
DECLARE _fjdfxzdmc VARCHAR(50);
DECLARE _sjybmc VARCHAR(50);
DECLARE _sjybzdmc VARCHAR(50);
DECLARE _sjyshz VARCHAR(50);
DECLARE _nd varchar(50);
DECLARE done INT DEFAULT FALSE;
declare fxjsgspz_cur CURSOR for select IFNULL(t.xh,''),t.fxbbdm,t.fxzdmc,IFNULL(t.fxzdmcms,''),
IFNULL(t.fxzdjsgsh,''),IFNULL(t.fxzdjsgshsm,''),IFNULL(t.fjdfxzdmc,'') from ddss_fx_fxjsgspz t
where t.fxbbdm = fxbbdm
and t.fxbbdm = fxbbdm
and t.yxqq <= SYSDATE()
and t.yxqz >= SYSDATE()
and t.yxbz = 'Y'
order by t.fxzdjsgsh; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; create temporary table if not exists tmpTable
(
fxzdmc varchar(50),
bnnd varchar(10),
bnshz double(16,2),
snnd varchar(10),
snshz double(16,2),
fjdfxzdmc varchar(50)
); OPEN fxjsgspz_cur; seq_loop:loop FETCH NEXT FROM fxjsgspz_cur INTO _xh,_fxbbdm,_fxzdmc,_fxzdmcms,_fxzdjsgsh,_fxzdjsgshsm,_fjdfxzdmc; IF done THEN
LEAVE seq_loop;
END IF;
SET @insertTmpTableSql := CONCAT("INSERT INTO tmpTable(fxzdmc,bnnd,bnshz,snnd,snshz,fjdfxzdmc) VALUES ( '",_fxzdmc,"','",bnnd,"',null,'",snnd,"',null,'",_fjdfxzdmc,"')");
PREPARE insertTmpTableSTMT FROM @insertTmpTableSql;
EXECUTE insertTmpTableSTMT;
DEALLOCATE PREPARE insertTmpTableSTMT;
IF (_fxzdjsgsh is NULL OR _fxzdjsgsh = '') THEN
select t.sjybmc, t.sjybzdmc into _sjybmc, _sjybzdmc from ddss_fx_fxzdsjypz t
where t.fxbbdm = _fxbbdm
and t.fxzdmc = _fxzdmc
and t.yxbz = 'Y'; SET _nd = bnnd;
SET @selectysjsql := CONCAT("SELECT ",_sjybzdmc," into @_sjyshz from "," (
SELECT ",_sjybzdmc," FROM ddss_cj_cjzb a, ",_sjybmc," b
WHERE a.uuid = b.cjzbuuid
AND a.xmid = '", xmid ,"'
AND a.bbrqz like '", _nd ,"%'
AND a.yxbz = 'Y'
AND b.yxbz = 'Y'
ORDER BY a.bbrqz DESC) c
LIMIT 0,1");
PREPARE selectysjSTMT FROM @selectysjsql;
EXECUTE selectysjSTMT;
DEALLOCATE PREPARE selectysjSTMT;
SET _sjyshz = @_sjyshz;
UPDATE tmpTable t set t.bnshz = _sjyshz WHERE t.fxzdmc = _fxzdmc;
SET _nd = snnd;
SET @selectysjsql := CONCAT("SELECT ",_sjybzdmc," into @_sjyshz from "," (
SELECT ",_sjybzdmc," FROM ddss_cj_cjzb a, ",_sjybmc," b
WHERE a.uuid = b.cjzbuuid
AND a.xmid = '", xmid ,"'
AND a.bbrqz like '", _nd ,"%'
AND a.yxbz = 'Y'
AND b.yxbz = 'Y'
ORDER BY a.bbrqz DESC) c
LIMIT 0,1");
PREPARE selectysjSTMT FROM @selectysjsql;
EXECUTE selectysjSTMT;
DEALLOCATE PREPARE selectysjSTMT;
SET _sjyshz = @_sjyshz;
UPDATE tmpTable t set t.snshz = _sjyshz WHERE t.fxzdmc = _fxzdmc;
ELSE
SET @bnjsgs = _fxzdjsgsh;
SET @snjsgs = _fxzdjsgsh;
SET @bnbj1wz = 0;
SET @bnbj2wz = 0;
SET @snbj1wz = 0;
SET @snbj2wz = 0;
SET @bnshz = 0;
SET @snshz = 0;
set @bngsdy = null;
set @bngsdymc = NULL;
SET @sngsdy = NULL;
SET @sngsdymc = NULL;
SET @_whileflag = TRUE;
WHILE @_whileflag DO SELECT INSTR(@bnjsgs,'[') INTO @bnbj1wz;
SELECT INSTR(@bnjsgs,']') INTO @bnbj2wz;
SELECT SUBSTR(@bnjsgs,@bnbj1wz,@bnbj2wz-@bnbj1wz+1) INTO @bngsdy;
SELECT SUBSTR(@bnjsgs,@bnbj1wz+1,@bnbj2wz-@bnbj1wz-1) INTO @bngsdymc; SELECT INSTR(@snjsgs,'[') INTO @snbj1wz;
SELECT INSTR(@snjsgs,']') INTO @snbj2wz;
SELECT SUBSTR(@snjsgs,@snbj1wz,@snbj2wz-@snbj1wz+1) INTO @sngsdy;
SELECT SUBSTR(@snjsgs,@snbj1wz+1,@snbj2wz-@snbj1wz-1) INTO @sngsdymc;
SET @selectysj2sql := CONCAT("SELECT t.bnshz, t.snshz INTO @bnshz, @snshz FROM tmpTable t WHERE t.fxzdmc = '",@bngsdymc,"'");
PREPARE selectysj2STMT FROM @selectysj2sql;
EXECUTE selectysj2STMT;
DEALLOCATE PREPARE selectysj2STMT;
SET @bnjsgs = REPLACE(@bnjsgs,CONCAT(@bngsdy),@bnshz);
SET @snjsgs = REPLACE(@snjsgs,CONCAT(@sngsdy),@snshz);
SELECT INSTR(@bnjsgs,'[') INTO @bnbj1wz;
SELECT INSTR(@bnjsgs,']') INTO @bnbj2wz;
SELECT INSTR(@snjsgs,'[') INTO @snbj1wz;
SELECT INSTR(@snjsgs,']') INTO @snbj2wz;
IF @bnbj1wz = 0 && @bnbj2wz = 0 && @snbj1wz = 0 && @snbj2wz = 0 THEN
set @_whileflag = FALSE;
end IF;
END WHILE; IF INSTR(@bnjsgs,'/0') > 0 THEN
SET @bnjsgs = '0';
end IF;
IF INSTR(@snjsgs,'/0') > 0 THEN
SET @snjsgs = '0';
end IF;
SET @selectysj3sql := CONCAT("SELECT ",@bnjsgs,",",@snjsgs," INTO @bnjsgs,@snjsgs");
PREPARE selectysj3STMT FROM @selectysj3sql;
EXECUTE selectysj3STMT;
DEALLOCATE PREPARE selectysj3STMT; SET @updateysj4sql := CONCAT("UPDATE tmpTable t SET t.bnshz = ",@bnjsgs,", ","t.snshz = ",@snjsgs," WHERE t.fxzdmc = '",_fxzdmc,"'");
PREPARE updateysj4STMT FROM @updateysj4sql;
EXECUTE updateysj4STMT;
DEALLOCATE PREPARE updateysj4STMT;
END IF; end loop; CLOSE fxjsgspz_cur; SELECT * FROM tmpTable t;END
按照图片前6条循环没问题,第7条跳出。