データ更新ツール

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

ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
データ型に応じてランダム値をセットするupdate文作成(主キーカラムは除く)
作成したupdate文を実行


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

declare sql_text varchar(4000);
declare tab_name varchar(30);
declare pcolname varchar(30);
declare maxval varchar(100);
declare colnames varchar(4000);
declare date_flg int;
declare exec_flg int;

-- カーソル定義
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 date_flg = 0;
  set exec_flg = 0;

  set sql_text := concat('update ',tab_name,' set ');


  -- decimal型の場合、numeric_precision - numeric_scale に格納可能な整数部分の桁数が入る
  -- int型の場合、最大値2147483647
  -- bigint型の場合、最大値9223372036854775807
  -- char,varchar型の場合、character_maximum_lengthに格納可能な文字長が入る

  begin
    declare vdone2 int;
    declare v_column_name varchar(64);
    declare v_data_type longtext;
    declare v_character_maximum_length bigint;
    declare v_numeric_precision bigint unsigned;
    declare v_numeric_scale bigint unsigned;

    declare cur2 cursor for select t1.column_name,t1.data_type,t1.character_maximum_length,t1.numeric_precision,t1.numeric_scale
                            from information_schema.columns t1
                            where t1.table_schema = database()
                            and t1.table_name = tab_name
-- 主キーカラムは除外
                            and not exists (
                                select 1
                                from information_schema.statistics
                                where table_schema = database()
                                and table_name = t1.table_name
                                and index_name = 'PRIMARY'
                                and column_name = t1.column_name
                            )
                            order by t1.ordinal_position;
    declare continue handler for sqlstate '02000' set vdone2 = 1;

    set vdone2 = 0;
    open cur2;
    fetch cur2 into v_column_name,v_data_type,v_character_maximum_length,v_numeric_precision,v_numeric_scale;
    while vdone2 != 1 do

      set exec_flg = 1;
      case
        when v_data_type = 'int' then
          set sql_text := concat(sql_text,v_column_name,' = ','floor(rand() * 2147483647),');
        when v_data_type = 'bigint' then
          set sql_text := concat(sql_text,v_column_name,' = ','floor(rand() * 9223372036854775807),');
        when v_data_type = 'decimal' then
          set sql_text := concat(sql_text,v_column_name,' = ','floor(rand() * power(10,(', cast((v_numeric_precision - v_numeric_scale) as char) ,') ) ),');

        when v_data_type in ('char','varchar') then
          set sql_text := concat(sql_text,v_column_name,' = ','substring(md5(rand() ), 1, ',cast(v_character_maximum_length as char),'),');

        when v_data_type in ('date','datetime','timestamp') then
          set sql_text := concat(sql_text,v_column_name,' = ','date_add(''2000-01-01'', interval floor(7600 * rand() ) day),');

        else  set sql_text := concat(sql_text,v_column_name,' = ','NULL,');
      end case;

      fetch cur2 into v_column_name,v_data_type,v_character_maximum_length,v_numeric_precision,v_numeric_scale;
    end while;
    close cur2;
  end;

  set sql_text := substring(sql_text,1, length(sql_text) -1);
  set sql_text := concat(sql_text,' limit ',cast(param1 as char) );

  if exec_flg = 1 then
    select sql_text;
    set @sql_text := sql_text;
    prepare stmt from @sql_text;
    execute stmt;
    deallocate prepare stmt;
  end if;

  fetch cur1 into tab_name;
end while;
close cur1;

end
//
delimiter ;

 


-- テスト用SQL


drop table tab51;
create table tab51(col1 int,col2 varchar(10) );
alter table tab51 add constraint tab51pk primary key(col1,col2);
call proc_ins(10,'tab51');
select * from tab51;
call proc_upd(10,'tab51');

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

select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;
select * from tab15;

select * from tab21;
select * from tab22;
select * from tab23;

select * from tab31;
select * from tab41;

(19c)

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


ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
データ型に応じてランダム値をセットするupdate文作成(主キーカラムは除く)
作成したupdate文を実行


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

pcolname varchar2(30);
maxval varchar2(100);
sql_text2 varchar2(4000);

int_flg int;
char_flg int;
date_flg int;
timestamp_flg int;
exec_flg int;

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;

cursor cur2(vparam1 in varchar2) is select T0.column_name,T0.data_type,T0.data_length,T0.data_precision,T0.data_scale
                                    from user_tab_columns T0 where T0.table_name = vparam1 
-- 主キーカラムは除外
                                    and not exists ( select 1
                                    from user_constraints T1
                                    inner join user_ind_columns T2
                                    on T1.index_name = T2.index_name
                                    where T1.constraint_type = 'P'
                                    and T1.table_name = T0.table_name
                                    and T2.column_name = T0.column_name
                                    )
                                    order by T0.column_id;

 

begin
  for c1 in cur1 loop
    dbms_output.put_line(c1.table_name);
    int_flg := 0;
    char_flg := 0;
    date_flg := 0;
    timestamp_flg := 0;
    exec_flg := 0;
    sql_text :='update ' || c1.table_name || ' set ';

    -- number型の場合、data_precision - data_scale に格納可能な整数部分の桁数が入る
    -- char,varchar2型の場合、data_lengthに格納可能なバイト長が入る
    -- nchar,nvarchar2型の場合、data_lengthに格納可能なバイト長が入る。これを1/2した値が最大文字長となる。

    for c2 in cur2(c1.table_name) loop

      -- 全カラムが主キーの場合エラーとなるので、c2ループに入った場合だけSQLを実行する
      exec_flg := 1;

      case
        when c2.data_type = 'NUMBER' then
          sql_text := sql_text || c2.column_name || ' = ' || 'floor(dbms_random.value(1, power(10,'|| to_char(nvl(c2.data_precision,9) - nvl(c2.data_scale,0) ) ||') ) ),';
        when c2.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then
          sql_text := sql_text || c2.column_name || ' = ' || 'substr(standard_hash(dbms_random.value(), ''MD5''),1,'|| 
          case when c2.data_type in ('NCHAR','NVARCHAR2') then to_char(nvl(c2.data_length/2,1) ) else to_char(nvl(c2.data_length,1) ) end 
          ||'),';
        when c2.data_type = 'DATE' or  c2.data_type like 'TIMESTAMP%' then
          sql_text := sql_text || c2.column_name || ' = ' ||  'to_date(''20000101'',''yyyymmdd'') + floor(dbms_random.value(0, 7600) ),';
        else  sql_text := sql_text || c2.column_name || ' = ' ||  'NULL,';
      end case;

    end loop;

    sql_text := substr(sql_text,1, length(sql_text) -1) || ' where rownum <= '||to_char(param1) ;
    
    if exec_flg = 1 then
      dbms_output.put_line(sql_text);
      begin
        execute immediate sql_text;
      exception
        when others then
          raise;
      end;
    end if;

    commit;
  end loop;
end;
/
show error;

 

-- テスト用SQL

drop table tab51 purge;
create table tab51(col1 int,col2 varchar2(10) );
alter table tab51 add constraint tab51pk primary key(col1,col2);
exec proc_ins(10,'TAB51');
select * from tab51;
exec proc_upd(10,'TAB51');


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

select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;

select * from tab21;
select * from tab22;
select * from tab23;

select * from tab31;
select * from tab41;

 

 

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


ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
データ型に応じてランダム値をセットするupdate文作成(主キーカラムは除く)
作成したupdate文を実行

 

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

sql_text varchar(4000);

pcolname varchar(30);
maxval varchar(100);
sql_text2 varchar(4000);

int_flg int;
char_flg int;
date_flg int;
timestamp_flg int;
exec_flg int;

cur1 cursor for select tablename from pg_tables
                where schemaname = 'public'
                and tablename = coalesce(param2,tablename)
                order by tablename;
cur2 cursor(vparam1 varchar) for select T0.column_name,T0.data_type,T0.character_maximum_length,T0.numeric_precision,T0.numeric_scale
                             from information_schema.columns T0
                             where T0.table_schema = 'public'
                             and T0.table_name = vparam1
-- 主キーがある場合、主キーカラムは除外
and  not exists (
                               select 1
                               from pg_indexes T1
                               inner join information_schema.table_constraints T2
                               on T1.schemaname = T2.table_schema
                               and T1.tablename = T2.table_name
                               and T2.constraint_type = 'PRIMARY KEY'
                               and T1.indexname = T2.constraint_name
                               where T1.schemaname = 'public'
                               and T1.tablename = vparam1
                               and (','||replace(substring(T1.indexdef from (position('(' in T1.indexdef) )+1 for (position(')' in T1.indexdef) - position('(' in T1.indexdef)-1) ),' ','') ||',') like '%,'||T0.column_name||',%' 
                 )
                             order by T0.ordinal_position;


begin
  for c1 in cur1 loop
    raise notice 'c1.tablename=%', c1.tablename;
    int_flg := 0;
    char_flg := 0;
    date_flg := 0;
    timestamp_flg := 0;
    exec_flg := 0;
    sql_text :='update ' || c1.tablename || ' set ';


    -- numeric型の場合、numeric_precision - numeric_scale に格納可能な整数部分の桁数が入る
    -- integer型の場合、最大値2147483647
    -- bigint型の場合、最大値9223372036854775807
    -- char,varchar型の場合、character_maximum_lengthに格納可能な文字長が入る

    for c2 in cur2(c1.tablename) loop
      exec_flg := 1;
        case
          when c2.data_type = 'integer' then
            sql_text := sql_text || c2.column_name || ' = ' || 'floor(random() * 2147483647),';
          when c2.data_type = 'bigint' then
            sql_text := sql_text || c2.column_name || ' = ' || 'floor(random() * 9223372036854775807),';
          when c2.data_type = 'numeric' then
            sql_text := sql_text || c2.column_name || ' = ' || 'floor(random() * power(10, ' || (c2.numeric_precision - c2.numeric_scale)::varchar || ') ),';
          when c2.data_type like 'character%' then
            sql_text := sql_text || c2.column_name || ' = ' || 'substr(md5(random()::text), 1, ' || c2.character_maximum_length::varchar || '),';
          when c2.data_type = 'date' or  c2.data_type like 'timestamp%' then
            sql_text := sql_text || c2.column_name || ' = ' || '''2000-01-01''::date + CAST( floor(7600 * random() ) || ''days'' AS interval),';
          else  sql_text := sql_text || c2.column_name || ' = ' || 'NULL,';
        end case;
      
    end loop;

    sql_text := substr(sql_text,1, length(sql_text) -1);
    sql_text := sql_text || ' where (ctid) in (select ctid from ' || c1.tablename || ' limit ' || param1::varchar || ')';

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

end;
$$;

 

-- テスト用SQL


drop table tab51;
create table tab51(col1 int,col2 varchar(10) );
alter table tab51 add constraint tab51pk primary key(col1,col2);
call proc_ins(10,'tab51');
select * from tab51;
call proc_upd(10,'tab51');

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

select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;

select * from tab21;
select * from tab22;
select * from tab23;

select * from tab31;
select * from tab41;

 

 

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


ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
データ型に応じてランダム値をセットするupdate文作成(主キーカラムは除く)
作成したupdate文を実行

 


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

declare @sql_text nvarchar(4000);

declare @pcolname varchar(30);
declare @sql_text2 nvarchar(4000);
declare @params nvarchar(4000);

declare @tab_name varchar(30);
declare @colnames varchar(4000);
declare @i bigint;

declare @int_flg int;
declare @char_flg int;
declare @date_flg int;
declare @datetime2_flg int;
declare @exec_flg int;

declare @maxval_int bigint;
declare @maxval_char varchar(100);
declare @maxval_date date;
declare @maxval_datetime2 datetime2;

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 @int_flg = 0;
    set @char_flg = 0;
    set @date_flg = 0;
    set @datetime2_flg = 0;
    set @exec_flg = 0;

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

    -- decimal,numeric型の場合、presicion - scale に格納可能な整数部分の桁数が入る
    -- int型の場合、最大値2147483647
    -- bigint型の場合、最大値9223372036854775807
    -- char,varchar型の場合、max_lengthに格納可能なバイト長が入る(ただし、maxの場合は-1)
    -- nchar,nvarchar型の場合、max_lengthに格納可能なバイト長が入る。これを1/2した値が最大文字長となる。(ただし、maxの場合は-1)

    begin
      declare @colname varchar(100);
      declare @system_type_id int;
      declare @max_length int;
      declare @precision int;
      declare @scale int;
      declare @max_length2 int;

      declare cur2 cursor for select t1.name,t1.system_type_id,t1.max_length,t1.precision,t1.scale
                              from sys.columns t1
                              where object_name(t1.object_id)  = @tab_name
                              and not exists (
                                select 1
                                from sys.indexes t2
                                inner join sys.index_columns t3
                                on t2.object_id = t3.object_id 
                                and t2.index_id = t3.index_id  
                                where t2.object_id = t1.object_id
                                and t2.is_primary_key = 1
                                and col_name(t3.object_id,t3.column_id) = t1.name
                                )
                              order by t1.column_id


      open cur2;
      fetch next from cur2 into @colname,@system_type_id,@max_length,@precision,@scale;
      while @@fetch_status = 0
        begin
        -- int-> 56   bigint-> 127 numeric-> 108 decimal-> 106
        -- char-> 175 varchar-> 167  nchar-> 239 nvarchar-> 231
        -- date-> 40  datetime2-> 42

          set @exec_flg = 1;

          if   @system_type_id = 56 
            set @sql_text = @sql_text + @colname +' = ' + 'floor(rand( cast(substring(cast(newid() as varbinary),1,2) as int) ) * 2147483647),';
          else if   @system_type_id = 127 
            set @sql_text = @sql_text + @colname +' = ' + 'floor(rand( cast(substring(cast(newid() as varbinary),1,2) as int) ) * 9223372036854775807),';
          else if   @system_type_id in (108,106) 
            set @sql_text = @sql_text + @colname +' = ' + 'floor(rand( cast(substring(cast(newid() as varbinary),1,2) as int) ) * power(cast(10 as bigint),cast(' + cast((@precision - @scale) as varchar) + ' as bigint) ) ),';
          else if   @system_type_id in (175,167,239,231) 
            begin
              if  @max_length = -1
                set @max_length2 = 1000;
              else if @system_type_id in (239,231) 
                set @max_length2 = @max_length/2;
              else  set @max_length2 = @max_length;

              set @sql_text = @sql_text + @colname +' = ' + 'substring(master.dbo.fn_varbintohexstr(HASHBYTES(''MD5'',cast(rand( cast(substring(cast(newid() as varbinary),1,2) as int) ) as varchar) ) ),3,' + cast(@max_length2 as varchar) + '),';
            end;
          else if   @system_type_id in (40,42) 
            set @sql_text = @sql_text + @colname +' = ' + 'dateadd(day,floor(7600 * rand( cast(substring(cast(newid() as varbinary),1,2) as int) ) ),''2000-01-01''),';
          else  set @sql_text = @sql_text+ @colname +' = ' + 'NULL,';

      fetch next from cur2 into @colname,@system_type_id,@max_length,@precision,@scale;

      end
      close cur2;
      deallocate cur2;
    end;

    set @sql_text = substring(@sql_text,1,len(@sql_text)-1);

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

end
go

 

 

-- テスト用SQL

drop table tab51;
create table tab51(col1 int not null,col2 varchar(10) not null);
alter table tab51 add constraint tab51pk primary key(col1,col2);
exec proc_ins 10, 'tab51';
select * from tab51;
exec proc_upd 10, 'tab51';

exec proc_upd;
exec proc_upd 10;
exec proc_upd 10, null;
exec proc_upd 10, 'tab11';


select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;

select * from tab21;
select * from tab22;
select * from tab23;

select * from tab31;
select * from tab41;