mysql存储过程使用总结

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