用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
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
解决方案 »
- 数据库? 数据库引擎?
- 关于CASCADE CONSTRAINTS的问题
- 一个需求sql不太会写
- 如何在Mysql 中,跟踪正在运行的Sql语句?象在Sql Server中的事件探查器可以跟踪一样。
- 郁闷,MYSQL数据库中的表定期损坏
- postgresql如何改变数据库编码啊?
- 请大家帮帮忙:我用Apache+PHP+MySQL,在命令行提符下键入:mysqladmin -u root create mydb 竟然会出现如下错误,请问是怎么回事:mysqlad
- 数据库报错..求助...
- mysql存储过程中发生异常,怎么将异常的详细信息打印出来呢?
- 为什么删除不了这一行!!
- mysql 触发器 出错 请高手指点
- 求按分类查询取两条数据
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
DECLARE CONTINUE HANDLER FOR
检查一下
FROM tmp_cash_record;
把这个移到
OPEN cur;上面看看。