GBase数据库存储过程——批量删除多个数据表的数据

偶尔需要清空一下数据库，重装成本太高。

--清空历史存储过程
DROP Procedure `dap_model`.`delete_datas` ;
 
--创建存储过程
DELIMITER //
CREATE DEFINER="dba"@"%" PROCEDURE "delete_datas"()
begin
    declare v_tableName   varchar(100);
    declare v_sql           varchar(5000);
    declare done1 int default 0;
    declare tablename_cursor cursor for
    select distinct TABLE_NAME from information_schema.tables  where TABLE_NAME like 'A_%' or TABLE_NAME like 'B_%';
 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1= 1;
     
    open tablename_cursor ;
       fetch tablename_cursor into v_tableName;
       while done1 = 0 do
           set v_sql = 'TRUNCATE TABLE ' || v_tableName;
           call executeSQL(v_sql);
           fetch tablename_cursor into v_tableName;
       end while;
    close tablename_cursor;     
end //
 
--调用存储过程
call delete_datas()

 

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