问题描述:
计算中国农历的步骤中关键的一步是计算二十四节气。
下述代码是计算二十四节气中的一段,在测试时,结果不合预期。
调试发现,使用游标绑定数据失效。
代码如下:
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 语句绑定数据失效。以上,求指导,求科普。CursorMySQL函数
计算中国农历的步骤中关键的一步是计算二十四节气。
下述代码是计算二十四节气中的一段,在测试时,结果不合预期。
调试发现,使用游标绑定数据失效。
代码如下:
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 语句绑定数据失效。以上,求指导,求科普。CursorMySQL函数
解决方案 »
- mysql switch case语句探讨
- mysql 存储过程求解
- mysql group by的问题
- 求一个sql语句
- data too lang for column的问题,我想骂街了。。该死的mysql。。。
- 谁能给个mysql创建序列sequence 和触发器的例子啊
- 如何从mysql 把 .sql文件 执行呀
- 无法连接MYSQL服务
- Win 2000 Server下的MYSQL数据库该如何做备份,能使他顺利恢复到LINUX下的MYSQL数据库中?
- 同一个事件代码,我分别在2个数据库中创建的,为什么执行时间不一样?
- Mysql里text类型的修改问题,一个对高手来说简单的问题
- 请教用过db4free的大哥。。。。。。
SET t = calculateJulianMillennium(argDate); set done:=0;
-- 加上试试,是不是done被上一个函数设置了。
OPEN cur;
REPEAT
FETCH cur INTO a,b,c
重构 `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)