mysql cursor fetch无法取出值的问题

现遇到mysql中使用游标时在fetch取出记录的主键id值始终为0(如下存储过程中已被注释的代码– select id),且独立执行cursor定义语句是有结果的。经过试验得出如下结论:
cursor定义时如果取出列中包含主键id,必须为表定义别名,否则fetch出值为0;非主键列未发现此问题。特此记录以备忘~
PS:Server version: 5.6.24-log MySQL Community Server (GPL)

DROP PROCEDURE IF EXISTS `pro_update_plan_step_id`;
DELIMITER ;;
CREATE PROCEDURE `pro_update_plan_step_id`(in in_plan_id varchar(36), in in_update_userid varchar(50), out o_count int)
BEGIN
  DECLARE count INT DEFAULT 1;
  DECLARE finished BOOLEAN DEFAULT FALSE;
  DECLARE id INT DEFAULT 0;
  DECLARE cur CURSOR FOR 
        SELECT s.id FROM at_test_plan_step s
    WHERE s.plan_id = in_plan_id AND s.is_valid = 1 
    ORDER BY s.step_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

  OPEN cur;
  curloop: LOOP
    FETCH cur INTO id;
    -- select id;
    IF finished THEN
      SET finished = FALSE;
      LEAVE curloop;
    END IF;
    UPDATE at_test_plan_step s SET s.step_id = count, 
            s.update_userid = in_update_userid, s.update_time = now() 
      WHERE s.id = id;
    SET count = count + 1; 
  END LOOP curloop;
  CLOSE cur;
  SET o_count = count;
END
;;
DELIMITER ;

mysql cursorfetch ... into var1[,var2,...] 失效

07-25

问题描述:rn 计算中国农历的步骤中关键的一步是计算二十四节气。rn 下述代码是计算二十四节气中的一段,在测试时,结果不合预期。rn 调试发现,使用游标绑定数据失效。rn rn代码如下:rnUSE test;rn-- VSOPD 周期项参数rnDROP TABLE IF EXISTS `vsop87_periodic_terms`;rnCREATE TABLE IF NOT EXISTS `vsop87_periodic_terms`rn(rn `planet` VARCHAR(10) COMMENT '星球',rn `series` VARCHAR(2) COMMENT '系列标识',rn `number` INT COMMENT '内码',rn `A` DOUBLE COMMENT '轨道参数A',rn `B` DOUBLE COMMENT '轨道参数B',rn `C` DOUBLE COMMENT '轨道参数C'rn);rnALTER TABLE `vsop87_periodic_terms` ADD CONSTRAINT PK_VSOP87_PERIODIC_TERMS PRIMARY KEY (`planet`,`series`,`number`);rnrn-- 初始化L0数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','L0',1,175347046,0,0),rn('EARTH','L0',2,3341656,4.6692568,6283.0758500),rn('EARTH','L0',3,34894,4.62610,12566.15170),rn('EARTH','L0',4,3497,2.7441,5753.3849),rn('EARTH','L0',5,3418,2.8289,3.5231),rn('EARTH','L0',6,3136,3.6277,77713.7715),rn('EARTH','L0',7,2676,4.4181,7860.4194),rn('EARTH','L0',8,2343,6.1352,3930.2097),rn('EARTH','L0',9,1324,0.7425,11506.7698),rn('EARTH','L0',10,1273,2.0371,529.6910),rn('EARTH','L0',11,1199,1.1096,1577.3435),rn('EARTH','L0',12,990,5.233,5884.927),rn('EARTH','L0',13,902,2.045,26.298),rn('EARTH','L0',14,857,3.508,398.149),rn('EARTH','L0',15,780,1.179,5223.694),rn('EARTH','L0',16,753,2.533,5507.533),rn('EARTH','L0',17,505,4.583,18849.228),rn('EARTH','L0',18,492,4.205,775.523),rn('EARTH','L0',19,357,2.920,0.067),rn('EARTH','L0',20,317,5.849,11790.629),rn('EARTH','L0',21,284,1.899,796.298),rn('EARTH','L0',22,271,0.315,10977.079),rn('EARTH','L0',23,243,0.345,5486.778),rn('EARTH','L0',24,206,4.806,2544.314),rn('EARTH','L0',25,205,1.869,5573.143),rn('EARTH','L0',26,202,2.458,6069.777),rn('EARTH','L0',27,156,0.833,213.299),rn('EARTH','L0',28,132,3.411,2942.463),rn('EARTH','L0',29,126,1.083,20.775),rn('EARTH','L0',30,115,0.645,0.980),rn('EARTH','L0',31,103,0.636,4694.003),rn('EARTH','L0',32,102,0.976,15720.839),rn('EARTH','L0',33,102,4.267,7.114),rn('EARTH','L0',34,99,6.21,2146.17),rn('EARTH','L0',35,98,0.68,155.42),rn('EARTH','L0',36,86,5.98,161000.69),rn('EARTH','L0',37,85,1.30,6275.96),rn('EARTH','L0',38,85,3.67,71430.70),rn('EARTH','L0',39,80,1.81,17260.15),rn('EARTH','L0',40,79,3.04,12036.46),rn('EARTH','L0',41,75,1.76,5088.63),rn('EARTH','L0',42,74,3.50,3154.69),rn('EARTH','L0',43,74,4.68,801.82),rn('EARTH','L0',44,70,0.83,9437.76),rn('EARTH','L0',45,62,3.98,8827.39),rn('EARTH','L0',46,61,1.82,7084.90),rn('EARTH','L0',47,57,2.78,6286.60),rn('EARTH','L0',48,56,4.39,14143.50),rn('EARTH','L0',49,56,3.47,6279.55),rn('EARTH','L0',50,52,0.19,12139.55),rn('EARTH','L0',51,52,1.33,1748.02),rn('EARTH','L0',52,51,0.28,5856.48),rn('EARTH','L0',53,49,0.49,1194.45),rn('EARTH','L0',54,41,5.37,8429.24),rn('EARTH','L0',55,41,2.40,19651.05),rn('EARTH','L0',56,39,6.17,10447.39),rn('EARTH','L0',57,37,6.04,10213.29),rn('EARTH','L0',58,37,2.57,1059.38),rn('EARTH','L0',59,36,1.71,2352.87),rn('EARTH','L0',60,36,1.78,6812.77),rn('EARTH','L0',61,33,0.59,17789.85),rn('EARTH','L0',62,30,0.44,83996.85),rn('EARTH','L0',63,30,2.74,1349.87),rn('EARTH','L0',64,25,3.16,4690.48);rnrn-- 初始化L1数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','L1',1,628331966747,0,0),rn('EARTH','L1',2,206059,2.678235,6283.075850),rn('EARTH','L1',3,4303,2.6351,12566.1517),rn('EARTH','L1',4,425,1.590,3.523),rn('EARTH','L1',5,119,5.796,26.298),rn('EARTH','L1',6,109,2.966,1577.344),rn('EARTH','L1',7,93,2.59,18849.23),rn('EARTH','L1',8,72,1.14,529.69),rn('EARTH','L1',9,68,1.87,398.15),rn('EARTH','L1',10,67,4.41,5507.55),rn('EARTH','L1',11,59,2.89,5223.69),rn('EARTH','L1',12,56,2.17,155.42),rn('EARTH','L1',13,45,0.40,796.30),rn('EARTH','L1',14,36,0.47,775.52),rn('EARTH','L1',15,29,2.65,7.11),rn('EARTH','L1',16,21,5.34,0.98),rn('EARTH','L1',17,19,1.85,5486.78),rn('EARTH','L1',18,19,4.97,213.30),rn('EARTH','L1',19,17,2.99,6275.96),rn('EARTH','L1',20,16,0.03,2544.31),rn('EARTH','L1',21,16,1.43,2146.17), rn('EARTH','L1',22,15,1.21,10977.08),rn('EARTH','L1',23,12,2.83,1748.02),rn('EARTH','L1',24,12,3.26,5088.63),rn('EARTH','L1',25,12,5.27,1194.45),rn('EARTH','L1',26,12,2.08,4694.00),rn('EARTH','L1',27,11,0.77,553.57),rn('EARTH','L1',28,10,1.30,6286.60),rn('EARTH','L1',29,10,4.24,1349.87),rn('EARTH','L1',30,9,2.70,242.73),rn('EARTH','L1',31,9,5.64,951.72),rn('EARTH','L1',32,8,5.30,2352.87),rn('EARTH','L1',33,6,2.65,9437.76),rn('EARTH','L1',34,6,4.67,4690.48);rnrn-- 初始化L2数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','L2',1,52919,0,0),rn('EARTH','L2',2,8720,1.0721,6283.0758),rn('EARTH','L2',3,309,0.867,12566.152),rn('EARTH','L2',4,27,0.05,3.52),rn('EARTH','L2',5,16,5.19,26.30),rn('EARTH','L2',6,16,3.68,155.42),rn('EARTH','L2',7,10,0.76,18849.23),rn('EARTH','L2',8,9,2.06,77713.77),rn('EARTH','L2',9,7,0.83,775.52),rn('EARTH','L2',10,5,4.66,1577.34),rn('EARTH','L2',11,4,1.03,7.11),rn('EARTH','L2',12,4,3.44,5573.14),rn('EARTH','L2',13,3,5.14,796.30),rn('EARTH','L2',14,3,6.05,5507.55),rn('EARTH','L2',15,3,1.19,242.73),rn('EARTH','L2',16,3,6.12,529.69),rn('EARTH','L2',17,3,0.31,398.15),rn('EARTH','L2',18,3,2.28,553.57),rn('EARTH','L2',19,2,4.38,5223.69),rn('EARTH','L2',20,2,3.75,0.98);rnrn-- 初始化L3数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','L3',1,289,5.844,6283.076),rn('EARTH','L3',2,35,0,0),rn('EARTH','L3',3,17,5.49,12566.15),rn('EARTH','L3',4,3,5.20,155.42),rn('EARTH','L3',5,1,4.72,3.52),rn('EARTH','L3',6,1,5.30,18849.23),rn('EARTH','L3',7,1,5.97,242.73);rnrn-- 初始化L4数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','L4',1,114,3.142,0),rn('EARTH','L4',2,8,4.13,6283.08),rn('EARTH','L4',3,1,3.84,12566.15);rnrn-- 初始化L5数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','L5',1,1,3.14,0);rnrn-- 初始化B0数据rnINSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES rn('EARTH','B0',1,280,3.199,84334.662),rn('EARTH','B0',2,102,5.422,5507.553),rn('EARTH','B0',3,80,3.88,5223.69),rn('EARTH','B0',4,44,3.70,2352.87),rn('EARTH','B0',5,32,4.00,1577.34);rnrn-- 根据格里历计算儒略日rnDROP FUNCTION IF EXISTS `calculateJulianDay`//rnCREATE FUNCTION `calculateJulianDay`(argDate TIMESTAMP) RETURNS INTrn BEGINrn DECLARE year INT DEFAULT 0;rn DECLARE month INT DEFAULT 0;rn DECLARE day INT DEFAULT 0;rn DECLARE B INT DEFAULT 0;rn DECLARE dd DOUBLE DEFAULT 0.0;rn SET year = year(argDate);rn SET month = month(argDate);rn SET day = dayofmonth(argDate);rn IF month <= 2 THENrn SET month = month + 12;rn SET year = year - 1;rn END IF ;rn SET B = FLOOR(year / 100) ;rn SET B = 2 - B + FLOOR(year/400) ;rn -- 本日12:00之后才是儒略日的开始(过一秒钟)rn SET dd = day + 0.5000115740; rn RETURN FLOOR(FLOOR(365.25 * (year + 4716)+0.01) + FLOOR(30.60001 *(month+1)) + dd + B -1524.5);rn rn END //rnrn-- 计算儒略千年数rnDROP FUNCTION IF EXISTS `calculateJulianMillennium`//rnCREATE FUNCTION `calculateJulianMillennium`( argDate TIMESTAMP) RETURNS DOUBLErn BEGINrn DECLARE t DOUBLE DEFAULT 0;rn SET t = (calculateJulianDay(argDate) - 2451545.0)/365250;rn RETURN t;rn END //rnSELECT calculateJulianMillennium(STR_TO_DATE('2000-01-15','%Y-%m-%d'))//rnSELECT calculateJulianMillennium(STR_TO_DATE('2005-05-31','%Y-%m-%d'))//rnrn-- 周期项系数表求和rnDROP FUNCTION IF EXISTS `calculatePeriodicTerm`//rnCREATE FUNCTION `calculatePeriodicTerm`(argDate TIMESTAMP,p VARCHAR(32),s VARCHAR(32)) RETURNS DOUBLErn BEGINrn DECLARE a DOUBLE DEFAULT 0.0;rn DECLARE b DOUBLE DEFAULT 0.0;rn DECLARE c DOUBLE DEFAULT 0.0;rn DECLARE done INT DEFAULT 0;rn DECLARE val DOUBLE DEFAULT 0.0;rn DECLARE t DOUBLE DEFAULT 0;rn DECLARE cur CURSOR FOR SELECT A,B,C FROM vsop87_periodic_terms WHERE (planet= p) AND (series= s);rn DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;rn SET t = calculateJulianMillennium(argDate); rn OPEN cur;rn REPEATrn [color=#FF0000]FETCH cur INTO a,b,c;[/color]rn IF NOT done THENrn -- A * cos(B+ct)rn SET val = val+ (a * COS(b + c * t));rn END IF;rn UNTIL done END REPEAT;rn CLOSE cur;rn RETURN val;rn END //rn-- 测试数据rnSELECT calculatePeriodicTerm(STR_TO_DATE('2013-03-20','%Y-%m-%d'),'EARTH','L0')//rnSELECT calculatePeriodicTerm(STR_TO_DATE('2013-09-23','%Y-%m-%d'),'EARTH','L0')//rnrn经调试,是Fetch 语句绑定数据失效。rnrn以上,求指导,求科普。

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试