データ削除ツール

• MySQL

(8.0.26)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名

ディクショナリからテーブル一覧を取得
作成したdelete文を実行

drop procedure proc_del;
delimiter //
create procedure proc_del(in param1 int,in param2 varchar(100) )
begin

declare sql_text varchar(4000);
declare tab_name varchar(30);

-- カーソル定義
declare vdone1 int;
declare cur1 cursor for select table_name from information_schema.tables
                        where table_schema=database()
                        and table_name = coalesce(param2,table_name)
                        order by table_name;

declare continue handler for sqlstate '02000' set vdone1 = 1;

set vdone1 = 0;
open cur1;
fetch cur1 into tab_name;
while vdone1 != 1 do
  select tab_name;
  set sql_text := concat('delete from ',tab_name,' limit ',cast(param1 as char) );

  select sql_text;
  set @sql_text := sql_text;
  prepare stmt from @sql_text;
  execute stmt;
  deallocate prepare stmt;

  fetch cur1 into tab_name;
end while;
close cur1;

end
//
delimiter ;

-- テスト用SQL

call proc_del(10,null);
call proc_del(10,'tab11');

select count(*) from tab11;
select count(*) from tab12;
select count(*) from tab13;
select count(*) from tab14;

select count(*) from tab21;
select count(*) from tab22;
select count(*) from tab23;

select count(*) from tab31;
select count(*) from tab41;

• Oracle

(19c)

仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名

ディクショナリからテーブル一覧を取得
作成したdelete文を実行

set serveroutput on 
create or replace procedure proc_del(param1 in number default 100,param2 in varchar2 default null)
authid current_user
as
sql_text varchar2(4000);

cursor cur1 is select table_name
               from user_tables
               where table_name not like 'SYS%'
               and table_name = nvl(param2,table_name)
               order by table_name;

begin
  for c1 in cur1 loop
    dbms_output.put_line(c1.table_name);
    sql_text :='delete from ' || c1.table_name || ' where rownum <= '||to_char(param1);

    dbms_output.put_line(sql_text);
    begin
      execute immediate sql_text;
    exception
      when others then
        raise;
    end;

    commit;
  end loop;
end;
/
show error;

-- テスト用SQL

exec proc_del();
exec proc_del(10);
exec proc_del(10,NULL);
exec proc_del(10,'TAB11');

select count(*) from tab11;
select count(*) from tab12;
select count(*) from tab13;
select count(*) from tab14;

select count(*) from tab21;
select count(*) from tab22;
select count(*) from tab23;

select count(*) from tab31;
select count(*) from tab41;

• PostgreSQL

(14)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名

ディクショナリからテーブル一覧を取得
作成したdelete文を実行

create or replace procedure proc_del(param1 in int default 100, param2 in varchar(100) default null)
language plpgsql
as $$
declare

sql_text varchar(4000);

cur1 cursor for select tablename from pg_tables
                where schemaname = 'public'
                and tablename = coalesce(param2,tablename)
                order by tablename;

begin
  for c1 in cur1 loop
    raise notice 'c1.tablename=%', c1.tablename;
    sql_text :='delete from ' || c1.tablename || ' where (ctid) in (select ctid from ' || c1.tablename || ' limit ' || param1::varchar || ')';

    raise notice 'sql_text=%', sql_text;
    begin
      execute sql_text;
    exception
      when others then
        raise;
    end;
   
    commit;
  end loop;

end;
$$;

-- テスト用SQL

call proc_del();
call proc_del(10);
call proc_del(10,null);
call proc_del(10,'tab11');

select count(*) from tab11;
select count(*) from tab12;
select count(*) from tab13;
select count(*) from tab14;

select count(*) from tab21;
select count(*) from tab22;
select count(*) from tab23;

select count(*) from tab31;
select count(*) from tab41;

• SQL Server

(2019)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名

ディクショナリからテーブル一覧を取得
作成したdelete文を実行

create or alter procedure proc_del(@param1 int = 100,@param2 varchar(100) = null)
as
begin
set nocount on;

declare @sql_text nvarchar(4000);
declare @tab_name varchar(30);

declare cur1 cursor for select name from sys.tables where name = coalesce(@param2,name) order by name;

open cur1;
fetch next from cur1 into @tab_name;
while @@fetch_status = 0
  begin
    print @tab_name;

    set @sql_text = 'delete top ('+cast(@param1 as varchar)+') from '+@tab_name;

    print @sql_text;
    begin try
      execute (@sql_text)
    end try
    begin catch
      throw;
    end catch;
      
    fetch next from cur1 into @tab_name;
  end
close cur1;
deallocate cur1;

end
go

-- テスト用SQL

exec proc_del;
exec proc_del 10;
exec proc_del 10, null;
exec proc_del 10, 'tab11';

select count(*) from tab11;
select count(*) from tab12;
select count(*) from tab13;
select count(*) from tab14;

select count(*) from tab21;
select count(*) from tab22;
select count(*) from tab23;

select count(*) from tab31;
select count(*) from tab41;