(8.0.22)
drop procedure proc1;
delimiter //
create procedure proc1(in param1 int, in param2 int, in param3 int)
begin
declare tabnum int default param1;
declare colmin int default param2;
declare colmax int default param3;
declare colnum int default 1;
declare indnum int default 1;
-- データ型比率
declare int_w int default 40;
declare varchar_w int default 40;
declare datetime_w int default 20;
declare tab_name varchar(30);
declare sql_text varchar(1000);
declare wk int;
declare col_type varchar(10);
declare char_len varchar(10);
declare ind_col varchar(1000);
declare sumi_col1 int;
declare sumi_col2 int;
-- テーブル名はtab1,tab2,,,とする
-- カラム名はcol1,col2,,,とする
-- 主キー制約名はtab1pk,tab2pk,,,とする
-- インデックス名はind1_1,ind1_2,,ind2_1,ind2_2,とする
-- 主キーはcol1とする
-- インデックス数は最大3とする
-- インデックスカラム数は作成順に1,2,3とする
-- テーブル件数ループ
set @i := 1;
while @i <= tabnum do
-- テーブル名生成
set tab_name := concat('tab',cast(@i as char));
-- カラム件数決定
set colnum := floor(rand() * (colmax - colmin +1) ) + colmin;
set sql_text := concat('create table ',tab_name,' (');
-- カラム件数ループ
set @j := 1;
while @j <= colnum do
-- データ型決定(int,varchar,datetime)
set wk := floor(rand() * (int_w + varchar_w + datetime_w) ) + 1;
if wk <= int_w then
set col_type := 'int';
elseif wk <= int_w + varchar_w then
set col_type := 'varchar';
else
set col_type := 'datetime';
end if;
-- カラム長決定(varcharの場合、1～100)
if col_type = 'varchar' then
set char_len := concat('(' , cast( (floor(rand() * 100 ) + 1) as char) , ')');
else
set char_len :='';
end if;
set sql_text := concat(sql_text,'col', cast(@j as char),' ' ,col_type,char_len);
if @j = 1 then
set sql_text := concat(sql_text,' not null ');
end if;
if @j != colnum then
set sql_text := concat(sql_text,',');
end if;
set @j := @j+1;
end while;
-- drop table文作成、実行
set @sql_text := concat('drop table if exists ',tab_name);
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
-- create table 文作成、実行
set sql_text := concat(sql_text,' )');
set @sql_text := sql_text;
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
-- alter table文作成、実行
set @sql_text := concat('alter table ',tab_name,' add constraint ',tab_name,'pk primary key(col1)');
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
-- インデックス作成件数決定
set indnum := floor(rand() * 3 ) + 1;
if indnum > colnum - 1 then
set indnum := colnum - 1;
end if;
-- インデックス件数ループ
set @k := 1;
while @k <= indnum do
set sumi_col1 := 0;
set sumi_col2 := 0;
set ind_col :='';
-- インデックスカラム件数ループ
set @n := 1;
while @n <= @k do
set wk := 0;
while wk = 0 or (wk = sumi_col1 or wk = sumi_col2) do
-- 2以上、カラム数以下のカラムを既存使用カラム以外が取得できるまで繰り返す
set wk := floor(rand() * (colnum -2 +1) ) + 2;
end while;
if @n = 1 then
set sumi_col1 := wk;
elseif @n = 2 then
set sumi_col2 := wk;
end if;
if @n < @k then
set ind_col := concat(ind_col,'col',cast(wk as char),',');
else
set ind_col := concat(ind_col,'col',cast(wk as char));
end if;
set @n := @n+1;
end while;
-- create index文作成、実行
set sql_text :=concat('create index ind',cast(@i as char),'_',cast(@k as char),' on ',tab_name,' (',ind_col,') ');
set @sql_text := sql_text;
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
set @k := @k+1;
end while;
set @i := @i+1;
end while;
end
//
delimiter ;
call proc1(1,1,1);
call proc1(1,10,15);
call proc1(10,10,30);
show tables from test;
select table_name,group_concat(column_name order by ordinal_position separator ',') column_name
from information_schema.columns
where table_schema='test'
group by table_name
order by table_name;
select table_name,index_name,group_concat(column_name order by seq_in_index separator ',') column_name
from information_schema.statistics
where table_schema = 'test'
group by table_name,index_name
order by 1
;
select concat('drop table ',table_name,';')
from information_schema.tables
where table_schema='test'
order by table_name
;
(19c)
set serveroutput on
create or replace procedure proc1(param1 in number, param2 in number, param3 in number)
authid current_user
as
tabnum integer := param1;
colmin integer := param2;
colmax integer := param3;
colnum integer :=1;
indnum integer :=1;
-- データ型比率
int_w integer := 40;
varchar2_w integer := 40;
timestamp_w integer := 20;
tab_name varchar2(30);
sql_text varchar2(1000);
type array_t is table of varchar2(1000) index by pls_integer;
arr array_t;
arr2 array_t;
wk int;
col_type varchar2(10);
char_len varchar2(10);
ind_col varchar2(1000);
no_tab_exists_err exception;
pragma exception_init(no_tab_exists_err,-942);
begin
-- テーブル名はtab1,tab2,,,とする
-- カラム名はcol1,col2,,,とする
-- 主キー制約名はtab1pk,tab2pk,,,とする
-- インデックス名はind1_1,ind1_2,,ind2_1,ind2_2,とする
-- 主キーはcol1とする
-- インデックス数は最大3とする
-- インデックスカラム数は作成順に1,2,3とする
-- テーブル件数ループ
for i in 1..tabnum loop
-- テーブル名生成
tab_name := 'tab'||to_char(i);
-- カラム件数決定
colnum := floor(dbms_random.value(colmin, colmax+1));
-- カラム内容配列初期化
arr.delete;
-- カラム件数ループ
for j in 1..colnum loop
-- データ型決定(int,varchar2,timestamp)
wk := floor(dbms_random.value(1, int_w + varchar2_w + timestamp_w +1));
if wk <= int_w then
col_type := 'int';
elsif wk <= int_w + varchar2_w then
col_type := 'varchar2';
else
col_type := 'timestamp';
end if;
-- カラム長決定(varchar2の場合、1～100)
if col_type = 'varchar2' then
char_len := '(' || to_char( floor(dbms_random.value(1, 100 )) ) || ')';
else
char_len :='';
end if;
-- カラム内容を配列に格納
arr(j) := 'col'|| to_char(j) || ' ' || col_type || char_len ;
if j = 1 then
arr(j) := arr(j) || ' not null ';
end if;
if j != colnum then
arr(j) := arr(j) || ',';
end if;
end loop;
-- drop table文作成、実行
sql_text :='drop table ' || tab_name || ' purge';
begin
execute immediate sql_text;
exception
when no_tab_exists_err then
null;
when others then
raise;
end;
-- create table 文作成、実行
sql_text :='create table ' || tab_name || ' (';
for j in 1..colnum loop
sql_text := sql_text || CHR(10) || arr(j);
end loop;
sql_text := sql_text || CHR(10) || ')';
execute immediate sql_text;
-- alter table文作成、実行
sql_text :='alter table ' || tab_name || ' add constraint ' || tab_name || 'pk primary key(col1)';
execute immediate sql_text;
-- インデックス作成件数決定
indnum := floor(dbms_random.value(1, 3+1));
if indnum > colnum - 1 then
indnum := colnum - 1;
end if;
-- インデックス件数ループ
for k in 1..indnum loop
-- インデックスカラム候補番号配列初期化
arr2.delete;
for m in 1..colnum loop
arr2(m) := m;
end loop;
arr2.delete(1);
ind_col :='';
-- インデックスカラム件数ループ
for n in 1..k loop
wk := floor(dbms_random.value(0, colnum - n + 1));
wk := arr2.next(wk);
arr2.delete(wk);
if n < k then
ind_col := ind_col || 'col'||to_char(wk) || ',';
else
ind_col := ind_col || 'col'||to_char(wk);
end if;
end loop;
-- create index文作成、実行
sql_text :='create index ind' || to_char(i) || '_' || to_char(k) || ' on ' || tab_name || ' (' || ind_col || ') ';
execute immediate sql_text;
end loop;
end loop;
end;
/
show error;
exec proc1(1,1,1);
exec proc1(1,10,15);
exec proc1(10,10,30);
select table_name
from user_tables
order by table_name;
select table_name,listagg(column_name,',') within group ( order by column_id )
from user_tab_columns
group by table_name
order by table_name;
select table_name,index_name,listagg(column_name,',') within group ( order by column_position )
from user_ind_columns
group by table_name,index_name
order by table_name,index_name;
select 'drop table ' || table_name || ' purge;'
from user_tables
order by table_name;
※procedure内で動的SQLを実行する場合、create table権限の直接付与が必要。
DBAロールがあっても「ORA-01031: 権限が不足しています」となる。
もしくは実行者権限で動作させる。
(13)
create or replace procedure proc1(param1 in int, param2 in int, param3 in int)
language plpgsql
as $$
declare
tabnum integer := param1;
colmin integer := param2;
colmax integer := param3;
colnum integer :=1;
indnum integer :=1;
-- データ型比率
int_w integer := 40;
varchar_w integer := 40;
timestamp_w integer := 20;
tab_name varchar(30);
sql_text varchar(1000);
element varchar(1000);
arr varchar[];
arr2 int[];
wk int;
col_type varchar(10);
char_len varchar(10);
ind_col varchar(1000);
begin
-- テーブル名はtab1,tab2,,,とする
-- カラム名はcol1,col2,,,とする
-- 主キー制約名はtab1pk,tab2pk,,,とする
-- インデックス名はind1_1,ind1_2,,ind2_1,ind2_2,とする
-- 主キーはcol1とする
-- インデックス数は最大3とする
-- インデックスカラム数は作成順に1,2,3とする
-- テーブル件数ループ
for i in 1..tabnum loop
-- テーブル名生成
tab_name := 'tab'||i::varchar;
-- カラム件数決定
colnum := floor(random() *(colmax - colmin +1) ) + colmin;
-- カラム内容配列初期化
arr = array[]::varchar[];
-- カラム件数ループ
for j in 1..colnum loop
-- データ型決定(int,varchar,timestamp)
wk := floor(random() * (int_w + varchar_w + timestamp_w ) ) + 1;
if wk <= int_w then
col_type := 'int';
elseif wk <= int_w + varchar_w then
col_type := 'varchar';
else
col_type := 'timestamp';
end if;
-- カラム長決定(varcharの場合、1～100)
if col_type = 'varchar' then
char_len := '(' || (floor(random() * 100)+1)::varchar || ')';
else
char_len :='';
end if;
-- カラム内容を配列に格納
if j = 1 and j != colnum then
element := 'col'|| (j)::varchar || ' ' || col_type || char_len || ' not null, ';
elseif j = 1 and j = colnum then
element := 'col'|| (j)::varchar || ' ' || col_type || char_len || ' not null ';
elseif j != 1 and j != colnum then
element := 'col'|| (j)::varchar || ' ' || col_type || char_len || ',';
else
element := 'col'|| (j)::varchar || ' ' || col_type || char_len;
end if;
arr := array_append(arr, element);
end loop;
-- drop table文作成、実行
sql_text :='drop table if exists ' || tab_name;
execute sql_text;
-- create table 文作成、実行
sql_text :='create table ' || tab_name || ' (';
for j in 1..array_length(arr,1) loop
sql_text := sql_text || CHR(10) || arr[j];
end loop;
sql_text := sql_text || CHR(10) || ')';
execute sql_text;
-- alter table文作成、実行
sql_text :='alter table ' || tab_name || ' add constraint ' || tab_name || 'pk primary key(col1)';
execute sql_text;
-- インデックス作成件数決定
indnum := floor(random() * 3)+1;
if indnum > colnum - 1 then
indnum := colnum - 1;
end if;
-- インデックス件数ループ
for k in 1..indnum loop
-- インデックスカラム候補番号配列初期化
arr2 = array[]::int[];
for m in 2..colnum loop
arr2 := array_append(arr2, m);
end loop;
ind_col :='';
-- インデックスカラム件数ループ
for n in 1..k loop
wk := floor(random() * (colnum - n ) )+1;
wk := arr2[wk];
arr2 := array_remove(arr2, wk);
if n < k then
ind_col := ind_col || 'col'|| (wk)::varchar || ',';
else
ind_col := ind_col || 'col'|| (wk)::varchar ;
end if;
end loop;
-- create index文作成、実行
sql_text :='create index ind' || (i)::varchar || '_' || (k)::varchar || ' on ' || tab_name || ' (' || ind_col || ') ';
execute sql_text;
end loop;
end loop;
end;
$$;
call proc1(1,1,1);
call proc1(1,10,15);
call proc1(10,10,30);
select tablename
from pg_tables
where schemaname = 'public'
order by tablename;
select table_schema,table_name, string_agg(column_name,',' order by ordinal_position )
from information_schema.columns
where table_catalog='test'
and table_schema = 'public'
group by table_schema,table_name
order by table_schema,table_name
;
select tablename,indexname
,replace(substring(indexdef from (position('(' in indexdef))+1 for (position(')' in indexdef) - position('(' in indexdef)-1)),' ','')
from pg_indexes
where schemaname = 'public'
order by tablename,indexname
;
select 'drop table ' || schemaname || '.' || relname || ';'
from pg_stat_user_tables
order by relname;
(2019)
create or alter procedure proc1(@param1 int, @param2 int, @param3 int)
as
begin
set nocount on;
declare @tabnum integer = @param1;
declare @colmin integer = @param2;
declare @colmax integer = @param3;
declare @colnum integer =1;
declare @indnum integer =1;
-- データ型比率
declare @int_w integer = 40;
declare @varchar_w integer = 40;
declare @datetime2_w integer = 20;
declare @tab_name varchar(30);
declare @sql_text varchar(1000);
declare @element varchar(1000);
declare @arr table(id int, val varchar(100));
declare @arr2 table(val int);
declare @wk int;
declare @col_type varchar(10);
declare @char_len varchar(10);
declare @ind_col varchar(1000);
declare @i integer;
declare @j integer;
declare @k integer;
declare @m integer;
declare @n integer;
declare @p integer;
-- テーブル名はtab1,tab2,,,とする
-- カラム名はcol1,col2,,,とする
-- 主キー制約名はtab1pk,tab2pk,,,とする
-- インデックス名はind1_1,ind1_2,,ind2_1,ind2_2,とする
-- 主キーはcol1とする
-- インデックス数は最大3とする
-- インデックスカラム数は作成順に1,2,3とする
-- テーブル件数ループ
set @i = 1;
while (@i <= @tabnum)
begin
-- テーブル名生成
set @tab_name = 'tab'+cast(@i as varchar);
-- カラム件数決定
set @colnum = floor(rand() * (@colmax - @colmin +1)) + @colmin;
-- カラム内容配列初期化
delete from @arr;
-- カラム件数ループ
set @j = 1;
while (@j <= @colnum)
begin
-- データ型決定(int,varchar,datetime2)
set @wk = floor(rand() * (@int_w + @varchar_w + @datetime2_w +1));
if @wk <= @int_w
begin
set @col_type = 'int';
end
else if @wk <= @int_w + @varchar_w
begin
set @col_type = 'varchar';
end
else
begin
set @col_type = 'datetime2';
end;
-- カラム長決定(varcharの場合、1～100)
if @col_type = 'varchar'
begin
set @char_len = '(' + cast( (floor(rand() * 100)+1 ) as varchar) + ')';
end
else
begin
set @char_len ='';
end
-- カラム内容を配列に格納
set @element = 'col'+ cast(@j as varchar) + ' ' + @col_type + @char_len ;
if @j = 1
begin
set @element = @element + ' not null ';
end
if @j != @colnum
begin
set @element = @element + ',';
end
insert into @arr values(@j,@element);
set @j = @j + 1;
end
-- drop table文作成、実行
set @sql_text = 'drop table if exists ' + @tab_name ;
execute (@sql_text)
-- create table 文作成、実行
set @sql_text ='create table ' + @tab_name + ' (';
set @j = 1;
while (@j <= @colnum)
begin
set @sql_text = @sql_text + CHAR(10) + (select val from @arr where id = @j);
set @j = @j + 1;
end
set @sql_text = @sql_text + CHAR(10) + ')';
execute (@sql_text)
-- alter table文作成、実行
set @sql_text ='alter table ' + @tab_name + ' add constraint ' + @tab_name + 'pk primary key(col1)';
execute (@sql_text)
-- インデックス作成件数決定
set @indnum = floor(rand() * 3)+1;
if @indnum > @colnum - 1
begin
set @indnum = @colnum - 1;
end
-- インデックス件数ループ
set @k = 1;
while (@k <= @indnum)
begin
-- インデックスカラム候補番号配列初期化
delete from @arr2;
set @m = 2;
while (@m <= @colnum)
begin
insert into @arr2 values(@m);
set @m = @m + 1;
end
set @ind_col ='';
-- インデックスカラム件数ループ
set @n = 1;
while (@n <= @k)
begin
set @wk = floor(rand() * (@colnum - @n) ) +1;
set @p = @wk
declare cur1 cursor for
select val from @arr2 order by val;
open cur1;
fetch next from cur1 into @wk;
while (@p > 1)
begin
fetch next from cur1 into @wk;
set @p = @p - 1;
end
close cur1;
deallocate cur1;
delete from @arr2 where val = @wk;
if @n < @k
begin
set @ind_col = @ind_col + 'col' + cast(@wk as varchar) + ',';
end
else
begin
set @ind_col = @ind_col + 'col' + cast(@wk as varchar);
end
set @n = @n + 1;
end
-- create index文作成、実行
set @sql_text ='create index ind' + cast(@i as varchar) + '_' + cast(@k as varchar) + ' on ' + @tab_name + ' (' + @ind_col + ') ';
execute (@sql_text)
set @k = @k + 1;
end
set @i = @i + 1;
end
end
go
exec proc1 1, 10, 15;
exec proc1 1, 1, 1;
exec proc1 10, 10, 30;
select name
from sys.tables
order by name
;
select object_name(object_id) object_name,string_agg( name ,',') within group ( order by column_id ) table_column
from sys.columns
where object_name(object_id) like 'tab%'
group by object_name(object_id)
order by object_name(object_id)
;
select
A.name index_name
,string_agg( col_name(B.object_id,B.column_id) ,',') within group ( order by B.key_ordinal) index_column
from sys.indexes A
inner join sys.index_columns B
on A.object_id = B.object_id
and A.index_id = B.index_id
where A.name like 'ind%'
group by A.name
order by A.name
;
select 'drop table ' + name + ';'
from sys.tables
order by name
;
