关于MySQL游标

[jagin]

在存储过程中有如下语句

DECLARE cur_organ CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = organID;

在循环中把游标FETCH到变量中时没有数据，然后若使用如下语句就能获取到数据

DECLARE cur_organ CURSOR FOR SELECT * FROM organ WHERE PRE_ORGAN_ID = organID;

有试过其他表，没有问题，百思不得其解，求解。

表结构

CREATE TABLE `organ` (
  `ORGAN_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '机构ID号',
  `ORGAN_NAME` varchar(64) NOT NULL COMMENT '机构名称',
  `PRE_ORGAN_ID` int(11) DEFAULT NULL COMMENT '上级机构ID号',
  PRIMARY KEY (`ORGAN_ID`),
  UNIQUE KEY `ORGAN_NAME` (`ORGAN_NAME`)
) 

[rucypli]

贴你的存储过程

[jagin]

我把获得的变量insert到一张调试表去，没有数据

存储过程

GET_AMOUNT:BEGIN
	DECLARE organID INT DEFAULT -1;

	SELECT ORGAN_ID INTO organID FROM organ WHERE ORGAN_NAME = organName;

	IF organID = -1 THEN
		LEAVE GET_AMOUNT;
	END IF;

GET_DATA:BEGIN
	DECLARE record INT DEFAULT 0;
	DECLARE done INT DEFAULT 0;
	DECLARE organ_id INT DEFAULT 0;
	DECLARE organ_name VARCHAR(64) DEFAULT '';
	DECLARE settle_Lv1 INT DEFAULT 0;
	DECLARE settle_Lv2 INT DEFAULT 0;
	DECLARE settle_Lv3 INT DEFAULT 0;
	DECLARE settle_Lv4 INT DEFAULT 0;
	DECLARE settle_Lv5 INT DEFAULT 0;
	DECLARE settle_Lv6 INT DEFAULT 0;
	DECLARE sell_Lv1 INT DEFAULT 0;
	DECLARE sell_Lv2 INT DEFAULT 0;
	DECLARE sell_Lv3 INT DEFAULT 0;
	DECLARE sell_Lv4 INT DEFAULT 0;
	DECLARE sell_Lv5 INT DEFAULT 0;
	DECLARE sell_Lv6 INT DEFAULT 0;
	DECLARE temp INT DEFAULT 0;

	/*定义游标，获取该机构下子机构ID*/
	DECLARE cur_organ CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = organID;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	/*判断是否有子机构*/
	SELECT COUNT(ORGAN_ID) INTO record FROM organ WHERE PRE_ORGAN_ID = organID;

	IF record = 0 THEN
		LEAVE GET_DATA;
	END IF;

	/*创建临时表*/
	CREATE TEMPORARY TABLE IF NOT EXISTS temp_amount(DISPALY_NAME VARCHAR(32), LV1_SETTLE INT, LV1_SELL INT, LV2_SETTLE INT, LV2_SELL INT, LV3_SETTLE INT, LV3_SELL INT, LV4_SETTLE INT, LV4_SELL INT, LV5_SETTLE INT, LV5_SELL INT, LV6_SETTLE INT, LV6_SELL INT);

	/*打开游标*/
	OPEN cur_organ;

	REPEAT
		/*游标数据存入变量*/
		FETCH cur_organ INTO organ_id, organ_name;

		IF NOT done THEN

			/*获取数据，结算类：AMOUNT_CLASS = 1，销售类：AMOUNT_CLASS = 2*/
			SELECT IFNULL(SUM(AMOUNT_LV1), 0), IFNULL(SUM(AMOUNT_LV2), 0), IFNULL(SUM(AMOUNT_LV3), 0), IFNULL(SUM(AMOUNT_LV4), 0), IFNULL(SUM(AMOUNT_LV5), 0), IFNULL(SUM(AMOUNT_LV6), 0) INTO settle_Lv1, settle_Lv2, settle_Lv3, settle_Lv4, settle_Lv5, settle_Lv6 FROM v_amount_organ WHERE ORGAN_ID = organ_name AND AMOUNT_CLASS = 1 AND (AMOUNT_DATE BETWEEN beginDate AND endDate);
			SELECT IFNULL(SUM(AMOUNT_LV1), 0), IFNULL(SUM(AMOUNT_LV2), 0), IFNULL(SUM(AMOUNT_LV3), 0), IFNULL(SUM(AMOUNT_LV4), 0), IFNULL(SUM(AMOUNT_LV5), 0), IFNULL(SUM(AMOUNT_LV6), 0) INTO sell_Lv1, sell_Lv2, sell_Lv3, sell_Lv4, sell_Lv5, sell_Lv6 FROM v_amount_organ WHERE ORGAN_ID= organ_name AND AMOUNT_CLASS = 2 AND (AMOUNT_DATE BETWEEN beginDate AND endDate);

			CALL Proc_Debug(CONCAT(organ_id,',', organ_name,',', temp, ',',settle_Lv1, ',',settle_Lv2, ',',settle_Lv3, ',',settle_Lv4, ',',settle_Lv5, ',',settle_Lv6));
			/*插入数据到临时表*/
			INSERT INTO temp_amount VALUES(organ_name, IFNULL(settle_Lv1, 0), IFNULL(sell_Lv1, 0), IFNULL(settle_Lv2, 0), IFNULL(sell_Lv2, 0), IFNULL(settle_Lv3, 0), IFNULL(sell_Lv3, 0), IFNULL(settle_Lv4, 0), IFNULL(sell_Lv4, 0), IFNULL(settle_Lv5, 0), IFNULL(sell_Lv5, 0), IFNULL(settle_Lv6, 0), IFNULL(sell_Lv6, 0));
		END IF;

	UNTIL done END REPEAT;

	/*总计数据*/
	SELECT IFNULL(SUM(AMOUNT_LV1), 0), IFNULL(SUM(AMOUNT_LV2), 0), IFNULL(SUM(AMOUNT_LV3), 0), IFNULL(SUM(AMOUNT_LV4), 0), IFNULL(SUM(AMOUNT_LV5), 0), IFNULL(SUM(AMOUNT_LV6), 0) INTO settle_Lv1, settle_Lv2, settle_Lv3, settle_Lv4, settle_Lv5, settle_Lv6 FROM v_amount_organ WHERE PRE_ORGAN_ID = organID AND AMOUNT_CLASS = 1 AND (AMOUNT_DATE BETWEEN beginDate AND endDate);
	SELECT IFNULL(SUM(AMOUNT_LV1), 0), IFNULL(SUM(AMOUNT_LV2), 0), IFNULL(SUM(AMOUNT_LV3), 0), IFNULL(SUM(AMOUNT_LV4), 0), IFNULL(SUM(AMOUNT_LV5), 0), IFNULL(SUM(AMOUNT_LV6), 0) INTO sell_Lv1, sell_Lv2, sell_Lv3, sell_Lv4, sell_Lv5, sell_Lv6 FROM v_amount_organ WHERE PRE_ORGAN_ID = organID AND AMOUNT_CLASS = 2 AND (AMOUNT_DATE BETWEEN beginDate AND endDate);
	INSERT INTO temp_amount VALUES('总计', settle_Lv1, sell_Lv1, settle_Lv2, sell_Lv2, settle_Lv3, sell_Lv3, settle_Lv4, sell_Lv4, settle_Lv5, sell_Lv5, settle_Lv6, sell_Lv6);

	/*关闭游标*/
	CLOSE cur_organ;

	/*获取数据*/
	SELECT * FROM temp_amount;

	/*删除数据*/
	DELETE FROM temp_amount;
END;

END

[wwwwb]

[Quote=引用 1 楼 wwwwa 的回复:]
插入记录的SQL语句？
[/Quote]
数据

[jagin]

[Quote=引用 5 楼 wwwwb 的回复:]

引用 1 楼 wwwwa 的回复:
插入记录的SQL语句？

数据
[/Quote]

INSERT INTO `organ` VALUES ('1', '广西自治区', '0');
INSERT INTO `organ` VALUES ('2', '桂林市', '1');
INSERT INTO `organ` VALUES ('3', '柳州市', '1');
INSERT INTO `organ` VALUES ('4', '南宁市', '1');
INSERT INTO `organ` VALUES ('5', '阳朔县', '2');
INSERT INTO `organ` VALUES ('6', '网点', '3');
INSERT INTO `organ` VALUES ('7', '网点2', '2');
INSERT INTO `organ` VALUES ('9', '网点1', '3');
INSERT INTO `organ` VALUES ('12', '网点3', '2');
INSERT INTO `organ` VALUES ('13', '网点4', '2');

[wwwwb]

用你的数据，自己编制了1个SP，可以得到结果
DELIMITER $$

DROP PROCEDURE IF EXISTS `tg`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `tg`(organID INT(10))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a1 CHAR(20);
DECLARE a2 CHAR(20);
DECLARE cur_organ CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = organID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur_organ;
FETCH cur_organ INTO a1,a2;
WHILE done=0 DO
SELECT a1,a2;
FETCH cur_organ INTO a1,a2;
END WHILE;

END$$

DELIMITER ;
CALL tg(1)

[jagin]

刚又写了个测试的，还是一样的情况，SELECT * 才有数据，否则数据就是空的

BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE organ_id INT DEFAULT 0;
	DECLARE id INT DEFAULT 0;
	DECLARE organ_name VARCHAR(64) DEFAULT '';

	DECLARE cur CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = 2;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN cur;

	REPEAT
		FETCH cur INTO organ_id, organ_name;

		IF NOT done THEN
			CALL Proc_Debug(CONCAT(organ_id, organ_name));
		END IF;

	UNTIL done END REPEAT;

	CLOSE cur;
END

[wwwwb]

DECLARE cur CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = 2;
在MYSQL中运行，是否有值

[jagin]

[Quote=引用 9 楼 wwwwb 的回复:]

DECLARE cur CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = 2;
在MYSQL中运行，是否有值
[/Quote]

有的，而且用*就没问题
DECLARE cur CURSOR FOR SELECT * organ WHERE PRE_ORGAN_ID = 2;

[wwwwb]

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE organ_id INT DEFAULT 0;
DECLARE id INT DEFAULT 0;
DECLARE organ_name VARCHAR(64) DEFAULT '';

DECLARE cur CURSOR FOR SELECT ORGAN_ID, ORGAN_NAME FROM organ WHERE PRE_ORGAN_ID = 2;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur;

REPEAT
FETCH cur INTO organ_id, organ_name;

IF NOT done THEN
select organ_id, organ_name;
END IF;

UNTIL done END REPEAT;

CLOSE cur;
END

是否有值

[jagin]

[Quote=引用 11 楼 wwwwb 的回复:]

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE organ_id INT DEFAULT 0;
DECLARE id INT DEFAULT 0;
DECLARE organ_name VARCHAR(64) DEFAULT '';

DECLARE cur CURSOR FOR SELECT ORGAN_I……
[/Quote]

没有，改为SELECT * 有

[jagin]

OK了，

DECLARE cur CURSOR FOR SELECT organ.ORGAN_ID, organ.ORGAN_NAME FROM organ ...

要加上表名，

[WWWWA]

插入记录的SQL语句？

[ACMAIN_CHM]

你是如何得出， "在循环中把游标FETCH到变量中时没有数据" ，贴出你的存储过程。