create PROCEDURE calculateusedsize(in dbname varchar(128))
? BEGIN???

??? declare recordcount int(12);
??? declare tn varchar(128);???
??? declare size int(12);???
??? declare usedsize int(12);
??? DECLARE done INT DEFAULT 0;??
??? DECLARE fetchtablecur CURSOR FOR SELECT qc.tablename , qc.size FROM system.columnsize qc;??
??
??? declare continue handler for SQLSTATE '02000' set done = 1;
?????

/*先删除再新建表，以后操作为insert*/
??? set @dropquotatableSQL= CONCAT('DROP TABLE IF EXISTS ',dbname,'.usedtable');
??? prepare s1 from @dropquotatableSQL;
??? execute s1 ;
??? deallocate prepare s1;
??? set @dropquotatableSQL=null;???

??? set @createquotatableSQL= CONCAT('CREATE TABLE ',dbname,'.usedtable (Id int(11) NOT NULL auto_increment,
???????? tablename varchar(128) NOT NULL ,
???????? usedsize int(32) NOT NULL,
???????? PRIMARY KEY? (Id)
???????? )');
??? prepare s1 from @createquotatableSQL;
??? execute s1 ;
??? deallocate prepare s1;
??? set @createquotatableSQL=null;
???????
?????



OPEN fetchtablecur;
?
REPEAT
??? FETCH fetchtablecur INTO tn, size;
???????
??? IF NOT done THEN???

?????? #在动态sql中，将查询结果赋值给变量
?????? set @v_sqlcounts = concat('select count(1) into @recordcount from ',dbname,'.',tn);?
?????? set @countsSelectSQL := @v_sqlcounts;
?????? prepare s1 from @countsSelectSQL;????????
?????? execute s1;?
?????? deallocate prepare s1;????????
?????? set @v_sqlcounts =null;
?????? set @countsSelectSQL =null;
?????? set recordcount = @recordcount;
?????? select recordcount*size into usedsize;
?????
?????? set @insertSQL= CONCAT('insert into ',dbname,'.usedtable(tablename,usedsize) values(\'',tn,'\',',usedsize,')');
?????? prepare s1 from @insertSQL;
?????? execute s1 ;
?????? deallocate prepare s1;
?????? set @insertSQL=null;??????

??? END IF;
UNTIL done END REPEAT;
?
? CLOSE fetchtablecur;


? END