在存储过程中有如下语句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`)
)
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`)
)
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
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');
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)
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
在MYSQL中运行,是否有值
有的,而且用*就没问题
DECLARE cur CURSOR FOR SELECT * organ WHERE PRE_ORGAN_ID = 2;
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是否有值
没有,改为SELECT * 有