CREATE PROCEDURE `mydb`.`static_all` ()
BEGIN
declare not_found int default 0;
declare tem int;
declare f_datatablename varchar(100);
declare cur_0 cursor for select DATATABLENAME from db_dataset;
declare continue handler for not found set not_found=1;
open cur_0;
fetch cur_0 into f_datatablename;
set @a=f_datatablename;
set @stmt:=concat("select count(*) as tem from information_schema.TABLES WHERE TABLE_NAME='",@a,"'");
prepare stmt from @stmt;
execute stmt;
drop prepare stmt;
while(not_found=0) and (tem=1) do
set @stmt1:=concat("insert into sys_stadatasave(DATATYPEID,storageID,datanum,productTotalSize)
select distinct ",@a,",SaveNodeID,count(SaveNodeID),round(sum(FileSize),3) group by storageID from ",@a);
prepare stmt1 from @stmt1;
execute stmt1;
drop prepare stmt1;
fetch cur_0 into f_datatablename;
end while;
close cur_0;
END
最后,sys_stadatasave并没有更新,哪儿出错了呢,dbdataset里存的是表的名字,在数据库中如果查到该表,就把它的统计信息取出来放到sys_stadatasave中
最后出来的结果只有一行,dem 1 MySQL 数据库 存储过程 游标
BEGIN
declare not_found int default 0;
declare tem int;
declare f_datatablename varchar(100);
declare cur_0 cursor for select DATATABLENAME from db_dataset;
declare continue handler for not found set not_found=1;
open cur_0;
fetch cur_0 into f_datatablename;
set @a=f_datatablename;
set @stmt:=concat("select count(*) as tem from information_schema.TABLES WHERE TABLE_NAME='",@a,"'");
prepare stmt from @stmt;
execute stmt;
drop prepare stmt;
while(not_found=0) and (tem=1) do
set @stmt1:=concat("insert into sys_stadatasave(DATATYPEID,storageID,datanum,productTotalSize)
select distinct ",@a,",SaveNodeID,count(SaveNodeID),round(sum(FileSize),3) group by storageID from ",@a);
prepare stmt1 from @stmt1;
execute stmt1;
drop prepare stmt1;
fetch cur_0 into f_datatablename;
end while;
close cur_0;
END
最后,sys_stadatasave并没有更新,哪儿出错了呢,dbdataset里存的是表的名字,在数据库中如果查到该表,就把它的统计信息取出来放到sys_stadatasave中
最后出来的结果只有一行,dem 1 MySQL 数据库 存储过程 游标
解决方案 »
- [提问]建了一个数据库,插入项目的时候出了问题。大牛们帮忙看看。
- Mysql存储过程的执行顺序问题
- psql如何改数据库的字符集?
- 求助mysql-5.1.6-alpha-win32简化安装的问题
- auto_increment
- 整了一天了,越来越晕。event定时每周日23点45分执行一个任务到底怎么做啊
- mysql查询后更新前10条数据问题。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
- 数据量有3496221,查询count的时候用了3.8s 。该如何优化呢?
- *************我需要Motorola J2ME SDK(模拟器加开发包)一份,谁能给我,送PHP+MYSQL空间50M和论坛积分
- mysql 执行selcet 1卡死问题
- 在VS2010中如何使用mysql语句同时执行两条sql语句?
- MySQL的Show命令后面可以跟查询条件吗?
第1个游标没有 循环?