mysql cursor： fetch ... into var1[,var2,...] 失效

[thomasLand]

问题描述:
计算中国农历的步骤中关键的一步是计算二十四节气。
下述代码是计算二十四节气中的一段，在测试时，结果不合预期。
调试发现，使用游标绑定数据失效。

代码如下:
USE test;
-- VSOPD 周期项参数
DROP TABLE IF EXISTS `vsop87_periodic_terms`;
CREATE TABLE IF NOT EXISTS `vsop87_periodic_terms`
(
`planet` VARCHAR(10) COMMENT '星球',
`series` VARCHAR(2) COMMENT '系列标识',
`number` INT COMMENT '内码',
`A` DOUBLE COMMENT '轨道参数A',
`B` DOUBLE COMMENT '轨道参数B',
`C` DOUBLE COMMENT '轨道参数C'
);
ALTER TABLE `vsop87_periodic_terms` ADD CONSTRAINT PK_VSOP87_PERIODIC_TERMS PRIMARY KEY (`planet`,`series`,`number`);

-- 初始化L0数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','L0',1,175347046,0,0),
('EARTH','L0',2,3341656,4.6692568,6283.0758500),
('EARTH','L0',3,34894,4.62610,12566.15170),
('EARTH','L0',4,3497,2.7441,5753.3849),
('EARTH','L0',5,3418,2.8289,3.5231),
('EARTH','L0',6,3136,3.6277,77713.7715),
('EARTH','L0',7,2676,4.4181,7860.4194),
('EARTH','L0',8,2343,6.1352,3930.2097),
('EARTH','L0',9,1324,0.7425,11506.7698),
('EARTH','L0',10,1273,2.0371,529.6910),
('EARTH','L0',11,1199,1.1096,1577.3435),
('EARTH','L0',12,990,5.233,5884.927),
('EARTH','L0',13,902,2.045,26.298),
('EARTH','L0',14,857,3.508,398.149),
('EARTH','L0',15,780,1.179,5223.694),
('EARTH','L0',16,753,2.533,5507.533),
('EARTH','L0',17,505,4.583,18849.228),
('EARTH','L0',18,492,4.205,775.523),
('EARTH','L0',19,357,2.920,0.067),
('EARTH','L0',20,317,5.849,11790.629),
('EARTH','L0',21,284,1.899,796.298),
('EARTH','L0',22,271,0.315,10977.079),
('EARTH','L0',23,243,0.345,5486.778),
('EARTH','L0',24,206,4.806,2544.314),
('EARTH','L0',25,205,1.869,5573.143),
('EARTH','L0',26,202,2.458,6069.777),
('EARTH','L0',27,156,0.833,213.299),
('EARTH','L0',28,132,3.411,2942.463),
('EARTH','L0',29,126,1.083,20.775),
('EARTH','L0',30,115,0.645,0.980),
('EARTH','L0',31,103,0.636,4694.003),
('EARTH','L0',32,102,0.976,15720.839),
('EARTH','L0',33,102,4.267,7.114),
('EARTH','L0',34,99,6.21,2146.17),
('EARTH','L0',35,98,0.68,155.42),
('EARTH','L0',36,86,5.98,161000.69),
('EARTH','L0',37,85,1.30,6275.96),
('EARTH','L0',38,85,3.67,71430.70),
('EARTH','L0',39,80,1.81,17260.15),
('EARTH','L0',40,79,3.04,12036.46),
('EARTH','L0',41,75,1.76,5088.63),
('EARTH','L0',42,74,3.50,3154.69),
('EARTH','L0',43,74,4.68,801.82),
('EARTH','L0',44,70,0.83,9437.76),
('EARTH','L0',45,62,3.98,8827.39),
('EARTH','L0',46,61,1.82,7084.90),
('EARTH','L0',47,57,2.78,6286.60),
('EARTH','L0',48,56,4.39,14143.50),
('EARTH','L0',49,56,3.47,6279.55),
('EARTH','L0',50,52,0.19,12139.55),
('EARTH','L0',51,52,1.33,1748.02),
('EARTH','L0',52,51,0.28,5856.48),
('EARTH','L0',53,49,0.49,1194.45),
('EARTH','L0',54,41,5.37,8429.24),
('EARTH','L0',55,41,2.40,19651.05),
('EARTH','L0',56,39,6.17,10447.39),
('EARTH','L0',57,37,6.04,10213.29),
('EARTH','L0',58,37,2.57,1059.38),
('EARTH','L0',59,36,1.71,2352.87),
('EARTH','L0',60,36,1.78,6812.77),
('EARTH','L0',61,33,0.59,17789.85),
('EARTH','L0',62,30,0.44,83996.85),
('EARTH','L0',63,30,2.74,1349.87),
('EARTH','L0',64,25,3.16,4690.48);

-- 初始化L1数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','L1',1,628331966747,0,0),
('EARTH','L1',2,206059,2.678235,6283.075850),
('EARTH','L1',3,4303,2.6351,12566.1517),
('EARTH','L1',4,425,1.590,3.523),
('EARTH','L1',5,119,5.796,26.298),
('EARTH','L1',6,109,2.966,1577.344),
('EARTH','L1',7,93,2.59,18849.23),
('EARTH','L1',8,72,1.14,529.69),
('EARTH','L1',9,68,1.87,398.15),
('EARTH','L1',10,67,4.41,5507.55),
('EARTH','L1',11,59,2.89,5223.69),
('EARTH','L1',12,56,2.17,155.42),
('EARTH','L1',13,45,0.40,796.30),
('EARTH','L1',14,36,0.47,775.52),
('EARTH','L1',15,29,2.65,7.11),
('EARTH','L1',16,21,5.34,0.98),
('EARTH','L1',17,19,1.85,5486.78),
('EARTH','L1',18,19,4.97,213.30),
('EARTH','L1',19,17,2.99,6275.96),
('EARTH','L1',20,16,0.03,2544.31),
('EARTH','L1',21,16,1.43,2146.17),
('EARTH','L1',22,15,1.21,10977.08),
('EARTH','L1',23,12,2.83,1748.02),
('EARTH','L1',24,12,3.26,5088.63),
('EARTH','L1',25,12,5.27,1194.45),
('EARTH','L1',26,12,2.08,4694.00),
('EARTH','L1',27,11,0.77,553.57),
('EARTH','L1',28,10,1.30,6286.60),
('EARTH','L1',29,10,4.24,1349.87),
('EARTH','L1',30,9,2.70,242.73),
('EARTH','L1',31,9,5.64,951.72),
('EARTH','L1',32,8,5.30,2352.87),
('EARTH','L1',33,6,2.65,9437.76),
('EARTH','L1',34,6,4.67,4690.48);

-- 初始化L2数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','L2',1,52919,0,0),
('EARTH','L2',2,8720,1.0721,6283.0758),
('EARTH','L2',3,309,0.867,12566.152),
('EARTH','L2',4,27,0.05,3.52),
('EARTH','L2',5,16,5.19,26.30),
('EARTH','L2',6,16,3.68,155.42),
('EARTH','L2',7,10,0.76,18849.23),
('EARTH','L2',8,9,2.06,77713.77),
('EARTH','L2',9,7,0.83,775.52),
('EARTH','L2',10,5,4.66,1577.34),
('EARTH','L2',11,4,1.03,7.11),
('EARTH','L2',12,4,3.44,5573.14),
('EARTH','L2',13,3,5.14,796.30),
('EARTH','L2',14,3,6.05,5507.55),
('EARTH','L2',15,3,1.19,242.73),
('EARTH','L2',16,3,6.12,529.69),
('EARTH','L2',17,3,0.31,398.15),
('EARTH','L2',18,3,2.28,553.57),
('EARTH','L2',19,2,4.38,5223.69),
('EARTH','L2',20,2,3.75,0.98);

-- 初始化L3数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','L3',1,289,5.844,6283.076),
('EARTH','L3',2,35,0,0),
('EARTH','L3',3,17,5.49,12566.15),
('EARTH','L3',4,3,5.20,155.42),
('EARTH','L3',5,1,4.72,3.52),
('EARTH','L3',6,1,5.30,18849.23),
('EARTH','L3',7,1,5.97,242.73);

-- 初始化L4数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','L4',1,114,3.142,0),
('EARTH','L4',2,8,4.13,6283.08),
('EARTH','L4',3,1,3.84,12566.15);

-- 初始化L5数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','L5',1,1,3.14,0);

-- 初始化B0数据
INSERT INTO `vsop87_periodic_terms`(`planet`,`series`,`number`,`A`,`B`,`C`) VALUES
('EARTH','B0',1,280,3.199,84334.662),
('EARTH','B0',2,102,5.422,5507.553),
('EARTH','B0',3,80,3.88,5223.69),
('EARTH','B0',4,44,3.70,2352.87),
('EARTH','B0',5,32,4.00,1577.34);

-- 根据格里历计算儒略日
DROP FUNCTION IF EXISTS `calculateJulianDay`//
CREATE FUNCTION `calculateJulianDay`(argDate TIMESTAMP) RETURNS INT
BEGIN
DECLARE year INT DEFAULT 0;
DECLARE month INT DEFAULT 0;
DECLARE day INT DEFAULT 0;
DECLARE B INT DEFAULT 0;
DECLARE dd DOUBLE DEFAULT 0.0;
SET year = year(argDate);
SET month = month(argDate);
SET day = dayofmonth(argDate);
IF month <= 2 THEN
SET month = month + 12;
SET year = year - 1;
END IF ;
SET B = FLOOR(year / 100) ;
SET B = 2 - B + FLOOR(year/400) ;
-- 本日12:00之后才是儒略日的开始(过一秒钟)
SET dd = day + 0.5000115740;
RETURN FLOOR(FLOOR(365.25 * (year + 4716)+0.01) + FLOOR(30.60001 *(month+1)) + dd + B -1524.5);

END //

-- 计算儒略千年数
DROP FUNCTION IF EXISTS `calculateJulianMillennium`//
CREATE FUNCTION `calculateJulianMillennium`( argDate TIMESTAMP) RETURNS DOUBLE
BEGIN
DECLARE t DOUBLE DEFAULT 0;
SET t = (calculateJulianDay(argDate) - 2451545.0)/365250;
RETURN t;
END //
SELECT calculateJulianMillennium(STR_TO_DATE('2000-01-15','%Y-%m-%d'))//
SELECT calculateJulianMillennium(STR_TO_DATE('2005-05-31','%Y-%m-%d'))//

-- 周期项系数表求和
DROP FUNCTION IF EXISTS `calculatePeriodicTerm`//
CREATE FUNCTION `calculatePeriodicTerm`(argDate TIMESTAMP,p VARCHAR(32),s VARCHAR(32)) RETURNS DOUBLE
BEGIN
DECLARE a DOUBLE DEFAULT 0.0;
DECLARE b DOUBLE DEFAULT 0.0;
DECLARE c DOUBLE DEFAULT 0.0;
DECLARE done INT DEFAULT 0;
DECLARE val DOUBLE DEFAULT 0.0;
DECLARE t DOUBLE DEFAULT 0;
DECLARE cur CURSOR FOR SELECT A,B,C FROM vsop87_periodic_terms WHERE (planet= p) AND (series= s);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
SET t = calculateJulianMillennium(argDate);
OPEN cur;
REPEAT
FETCH cur INTO a,b,c;
IF NOT done THEN
-- A * cos(B+ct)
SET val = val+ (a * COS(b + c * t));
END IF;
UNTIL done END REPEAT;
CLOSE cur;
RETURN val;
END //
-- 测试数据
SELECT calculatePeriodicTerm(STR_TO_DATE('2013-03-20','%Y-%m-%d'),'EARTH','L0')//
SELECT calculatePeriodicTerm(STR_TO_DATE('2013-09-23','%Y-%m-%d'),'EARTH','L0')//

经调试，是Fetch 语句绑定数据失效。

以上，求指导，求科普。

[ACMAIN_CHM]

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ; SET t = calculateJulianMillennium(argDate); set done:=0; -- 加上试试，是不是done被上一个函数设置了。 OPEN cur; REPEAT FETCH cur INTO a,b,c

[thomasLand]

[quote=引用 1 楼 ACMAIN_CHM 的回复:] DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ; SET t = calculateJulianMillennium(argDate); set done:=0; -- 加上试试，是不是done被上一个函数设置了。 先谢谢指导，已按照指导重新生成了下函数。 首先 1）测试的结果不变 2）FETCH cur INTO a,b,c; 任然赋值信息不正确。a,b,c 始终为0.0。 3）为了方便，上述代码时最小的可运行集。不会有其他的函数来重置变量。

[rucypli]

在怀疑的地方select出来值慢慢调试

[thomasLand]

引用 1 楼 ACMAIN_CHM 的回复:

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ; SET t = calculateJulianMillennium(argDate); set done:=0; -- 加上试试，是不是done被上一个函数设置了。 OPEN cur; REPEAT FETCH cur INTO a,b,c

引用 3 楼 rucypli 的回复:

在怀疑的地方select出来值慢慢调试

已调试完成,做出了如下更改： 重构 `vsop87_periodic_terms` 的A、B、C字段为ptA、ptB、ptC SELECT A,B,C FROM vsop87_periodic_terms WHERE (planet= p) AND (series= s); 改正为 SELECT ptA,ptB,ptC FROM vsop87_periodic_terms WHERE (planet= p) AND (series= s); 个人认为可能的原因是在函数calculatePeriodicTerm中已定义了a、b、c的变量。字段名又定义为了A、B、C。有存在冲突的可能性。话说、mysql不是区分大小写吗？ 这下给自己长了个记性。 测试结果如下: mysql> SELECT calculatePeriodicTerm(STR_TO_DATE('2013-03-20','%Y-%m-%d'),'EARTH', +--------------------------------------------------------------------------+ | calculatePeriodicTerm(STR_TO_DATE('2013-03-20','%Y-%m-%d'),'EARTH','L0') | +--------------------------------------------------------------------------+ | 178586317.61148137 | +--------------------------------------------------------------------------+ mysql> SELECT calculatePeriodicTerm(STR_TO_DATE('2013-09-23','%Y-%m-%d'),'EARTH','L0')// +--------------------------------------------------------------------------+ | calculatePeriodicTerm(STR_TO_DATE('2013-09-23','%Y-%m-%d'),'EARTH','L0') | +--------------------------------------------------------------------------+ | 172067555.46968985 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec)