delimiter //
CREATE PROCEDURE sp_power()
begin
declare l_id bigint(20);
declare l_power smallint(6);
declare no_more_device int default 0;
declare power_csr CURSOR FOR
SELECT dev_id,power_stat FROM device;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_device=1;
SET no_more_device = 0;
create table if not exists stat_power(dev_id bigint(20),dev_power smallint(6));
truncate table stat_power;
open power_csr;
dev_loop:repeat
fetch power_csr into l_id,l_power;
if no_more_device then
leave dev_loop;
end if;
if l_power < 11 then
insert into stat_power values(l_id,l_power);
end if;
until no_more_device
end repeat dev_loop;
close power_csr;
set no_more_device = 0;
end //
delimiter ;
以上是我的存储过程,但每次调用时stat_power表并没有清空,而是在原来的表里增加了这次执行结果的内容
请问应该怎么解决?
select * from stat_power;
什么结果
建议给出你的测试用例,想办法让别人可以在自己的机器上模拟你的问题。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
在数据没有任何改动的情况下调用两次sp_power后的结果:
dev_id dev_power
7 0
9 0
10 0
11 0
7 0
9 0
10 0
11 0
但是单独在命令行工具里执行truncate table stat_power;却可以清空
dev_id power_stat
1 319
3 415
4 415
5 255
7 0
8 415
9 0
10 0
11 0
CREATE TABLE `device` ( `dev_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT , `power_stat` SMALLINT(6) UNSIGNED NULL , PRIMARY KEY (`dev_id`) );
INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (1, 319);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (3, 415);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (4, 415);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (5, 255);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (7, 0);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (8, 415);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (9, 0);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (10, 0);INSERT INTO `device` (`dev_id`, `power_stat`) VALUES (11, 0);
mysql> delimiter //
mysql> CREATE PROCEDURE sp_power()
-> begin
-> declare l_id bigint(20);
-> declare l_power smallint(6);
-> declare no_more_device int default 0;
->
-> declare power_csr CURSOR FOR
-> SELECT dev_id,power_stat FROM device;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_device=1;
-> SET no_more_device = 0;
->
-> create table if not exists stat_power(dev_id bigint(20),dev_power sma
llint(6));
-> truncate table stat_power;
->
-> open power_csr;
-> dev_loop:repeat
-> fetch power_csr into l_id,l_power;
-> if no_more_device then
-> leave dev_loop;
-> end if;
->
-> if l_power < 11 then
-> insert into stat_power values(l_id,l_power);
-> end if;
-> until no_more_device
-> end repeat dev_loop;
-> close power_csr;
-> set no_more_device = 0;
-> end //
Query OK, 0 rows affected (0.18 sec)mysql> delimiter ;
mysql> call sp_power();
Query OK, 1 row affected (0.12 sec)mysql> select * from stat_power;
+--------+-----------+
| dev_id | dev_power |
+--------+-----------+
| 7 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
+--------+-----------+
4 rows in set (0.00 sec)mysql> call sp_power();
Query OK, 1 row affected, 1 warning (0.03 sec)mysql> select * from stat_power;
+--------+-----------+
| dev_id | dev_power |
+--------+-----------+
| 7 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
+--------+-----------+
4 rows in set (0.00 sec)mysql>
mysql> call sp_power();
Query OK, 1 row affected (0.21 sec)mysql> call sp_power();
Query OK, 1 row affected, 1 warning (0.09 sec)mysql> select * from stat_power;
+--------+-----------+
| dev_id | dev_power |
+--------+-----------+
| 7 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 7 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
+--------+-----------+
8 rows in set (0.00 sec)
我的结果是这样额…
应该没有,数据库装了也没多久,做这个project才开始用的,也没有改过配置什么的
难道之前跟用workbench有关系?而且现在用workbench执行call sp_power();会发生错误:call sp_power() Error Code: 1192. Can't execute the given command because you have active locked tables or an active transaction但是至少现在我要实现的功能没有问题了,谢谢