GBase数据库存储过程——批量查询多个数据表的磁盘占用情况

--清理历史表，可选
DROP TABLE IF EXISTS `dap_model`.`data_statics`;
CREATE TABLE `dba`.`data_statics` (
  `TABLE_NAME` varchar(100) NOT NULL,
  `DATA_LINES` integer NOT NULL,
  `TABLE_STORAGE_SIZE` varchar(100) NOT NULL
);

--清理历史存储过程，可选
DROP Procedure `dba`.`get_data_statistics` ;
  
--创建存储过程
DELIMITER //
CREATE DEFINER="dba"@"%" PROCEDURE "get_data_statistics"()
begin
    declare v_tableName   varchar(100);
    declare v_sql           varchar(5000);
    declare v_dataLines     integer;
    declare v_tableStorageSize varchar(100);
    declare done1           int default 0;
    declare tablename_cursor cursor for
    select distinct TABLE_NAME from information_schema.tables  where TABLE_NAME like '%NAME%' ;  --表名匹配
  
     
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1= 1;
     
    truncate table data_statics;
     
    open tablename_cursor ;
       fetch tablename_cursor into v_tableName;
       while done1 = 0 do
           SELECT TABLE_STORAGE_SIZE into @v_tableStorageSize FROM information_schema.CLUSTER_TABLES WHERE table_schema = 'dba' and table_name = v_tableName;
           set v_tableStorageSize = @v_tableStorageSize;
           set v_sql = concat('SELECT COUNT(1) into @v_dataLines FROM ',v_tableName);
           call executeSQL(v_sql);
           set v_dataLines=@v_dataLines;
            
           set v_sql = concat('INSERT INTO data_statics values(''',v_poTableName ,''',',v_dataLines,',',v_tableStorageSize,')');
           call executeSQL(v_sql);
           fetch tablename_cursor into v_poTableName;
       end while;
    close tablename_cursor;    
end //
  
--调用存储过程
call get_data_statistics()

 批量查询了GBase的系统表，将查询到的数据记录在临时表中。使用完了删掉临时表。

转载于:https://www.cnblogs.com/BrokenSwitch/p/8472049.html