存储过程取不到值

[baishengjie]

用mysql写了个存储过程，写了张临时表，然后想用游标循环这个表中的某个字段，定义游标和写入临时表的数据都没有问题，但用游标循环的时候就报错了，错误是
1329 - No data - zero rows fetched , selected, or processed

以下是代码，请帮忙解决一下，谢谢

begin

	DECLARE mysql  VARCHAR(1000) CHARSET utf8;
	DECLARE s_column VARCHAR(1000);
	DECLARE e_column VARCHAR(1000);
	DECLARE cash_total decimal(12,2);
 	DECLARE estateId int;
	DECLARE cash_max decimal(12,2);

	DECLARE cur CURSOR FOR SELECT DISTINCT(ESTATE_ID)
		FROM tmp_cash_record;

	/*  Create temporary table */
	DROP TABLE IF EXISTS tmp_cash_record;
	CREATE TEMPORARY TABLE tmp_cash_record(
					`ESTATE_ID` int NOT NULL,
					`ESTATE_NAME` varchar(50) collate utf8_unicode_ci NOT NULL,
  					`CUSTOMER_NAME` varchar(50) collate utf8_unicode_ci NOT NULL,
  					`CUSTOMER_ADDRESS` varchar(50) collate utf8_unicode_ci NOT NULL,
  					`CUSTOMER_TEL` varchar(50) collate utf8_unicode_ci NOT NULL

					) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

	/*  Create temporary table */
	DROP TABLE IF EXISTS tmp_cash_record_estate;
	CREATE TEMPORARY TABLE tmp_cash_record_estate(
					`ESTATE_ID` int NOT NULL,
					`ESTATE_NAME` varchar(50) collate utf8_unicode_ci NOT NULL,
  					`CASH_TOTAL` decimal(12,2) NOT NULL default '0.00',
  					`TOTAL` decimal(12,2) NOT NULL default '0.00',
  					`CASH_TOTAL_RATE` decimal(12,2) NOT NULL default '0.00',
  					`CUSTOMER_TOTAL`  int NOT NULL,
					`CASH_MAX`  decimal(12,2) NOT NULL default '0.00'
					) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




	SET s_column = "ESTATE_ID, ESTATE_NAME, YF_MONEY,CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_TEL";
	SET mysql = CONCAT('insert into tmp_cash_record(',s_column,')' );
	SET mysql = CONCAT(mysql ,"select s.ESTATE_ID, s.ESTATE_NAME, r.YF_MONEY, c.CUSTOMER_NAME, c.CUSTOMER_ADDRESS, c.CUSTOMER_TEL" );
	SET mysql = CONCAT(mysql, " FROM cash_record_", regionCode, " AS r, sys_estate_2_0_", regionCode, " AS s, cash_record_customer_", regionCode, " AS c");
	SET mysql = CONCAT(mysql, " WHERE r.id = c.CASH_ID AND s.ESTATE_ID = c.ESTATE_ID AND r.CASH_DATETIME BETWEEN '",beginDate," 00:00:00' and '",endDate," 23:59:59' " );

	SET @sql = mysql;
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;


	SET mysql = CONCAT('insert into tmp_cash_record(',s_column,')' );
	SET mysql = CONCAT(mysql ,"select s.ESTATE_ID, s.ESTATE_NAME, r.YF_MONEY, c.CUSTOMER_NAME, c.CUSTOMER_ADDRESS, c.CUSTOMER_TEL" );
	SET mysql = CONCAT(mysql, " FROM cash_record_history_", regionCode, " AS r, sys_estate_2_0_", regionCode, " AS s, cash_record_customer_", regionCode, " AS c");
	SET mysql = CONCAT(mysql, " WHERE r.id = c.CASH_ID AND s.ESTATE_ID = c.ESTATE_ID AND r.CASH_DATETIME BETWEEN '",beginDate," 00:00:00' and '",endDate," 23:59:59' " );

	SET @sql = mysql;
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	SELECT ifnull(sum(YF_MONEY) , 0)INTO cash_total  FROM tmp_cash_record;
	SET e_column = "ESTATE_ID, ESTATE_NAME, CASH_TOTAL,TOTAL, CASH_TOTAL_RATE, CUSTOMER_TOTAL,CASH_MAX";

	SET mysql = CONCAT('insert into tmp_cash_record_estate(',e_column,')' );
	SET mysql = concat( "SELECT ESTATE_ID, ESTATE_NAME,SUM(YF_MONEY) , ", cash_total, " AS 'CASH_TOTAL', ROUND(SUM(YF_MONEY) *100/",cash_total,", 2) , COUNT(DISTINCT CUSTOMER_TEL),0.00");
	SET mysql = concat(mysql, " from tmp_cash_record group by ESTATE_NAME ORDER BY CASH_TOTAL DESC LIMIT ", pageCount, " , ", pageSize, ";");

	SET @sql = mysql;
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	OPEN cur;
         FETCH cur INTO estateId;

      WHILE ( estateId is not null) DO

	 SELECT MAX(CASH)  FROM(SELECT sum(t.YF_MONEY) as 'CASH' FROM tmp_cash_record AS t GROUP BY t.CUSTOMER_TEL) AS NUM INTO cash_max;
	 SET mysql = CONCAT("UPDATE  tmp_cash_record_estate SET CASH_MAX = ",cash_max," WHERE ESTATE_ID =", estateId);
	SET @sql = mysql;
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;


        FETCH cur INTO estateId;
      END WHILE;
    CLOSE cur;



	/*SELECT COALESCE(COUNT(distinct ESTATE_NAME),0) INTO totalCount  FROM tmp_cash_record;*/

end

[ACMAIN_CHM]

没有看到你定义DECLARE HANDLER FOR SQLSTATE

参考一下帮助手册中的游标使用的例子。

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

[xmx2009]

handle for

[WWWWA]

分步测试，游标这定义有点问题，少了
DECLARE CONTINUE HANDLER FOR
检查一下

[zuoxingyu]

DECLARE cur CURSOR FOR SELECT DISTINCT(ESTATE_ID)
FROM tmp_cash_record;


把这个移到
OPEN cur;

上面看看。

[zuoxingyu]

# DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

缺了这个