(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;
