SQL文作成ツール - HTN20190109の日記

MySQL

(8.0.29)

select @@max_sp_recursion_depth;
set session max_sp_recursion_depth=255;
select @@max_sp_recursion_depth;

-- 配列初期化処理を切り出す
drop procedure proc_init_table;

delimiter //
create procedure proc_init_table()
begin

-- テーブル名配列
drop temporary table if exists tabnames;
create temporary table if not exists tabnames(id int primary key auto_increment
,table_name varchar(4000)
,t_name varchar(30)
,semianti_flg numeric
);

-- カラム配列
drop temporary table if exists colnames;
create temporary table if not exists colnames(id int primary key auto_increment
,table_name varchar(4000)
,column_name varchar(30)
,data_type varchar(30)
,character_maximum_length numeric
,numeric_scale numeric
,data_scale numeric
,ind_head_flg numeric
,t_name varchar(30)
,semianti_flg numeric
);

-- 結合テーブル組み合わせ配列
drop temporary table if exists tabcombi;
create temporary table if not exists tabcombi(id int primary key auto_increment
,table_no1 numeric
,table_no2 numeric
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,join_type varchar(30) -- innner join or left join
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

-- 結合条件配列
drop temporary table if exists joincols;
create temporary table if not exists joincols(id int primary key auto_increment
,tabcombi_id int
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

drop temporary table if exists joincols2;
create temporary table if not exists joincols2(id int primary key auto_increment
,tabcombi_id int
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

drop temporary table if exists joincols3;
create temporary table if not exists joincols3(id int primary key auto_increment
,tabcombi_id int
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

-- where条件配列(indexあり)

drop temporary table if exists whereindcols;
create temporary table if not exists whereindcols(id int primary key auto_increment
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- where条件
,t_name varchar(30)
,semianti_flg numeric
);

-- wherex条件配列(indexなし)

drop temporary table if exists wherenoindcols;
create temporary table if not exists wherenoindcols(id int primary key auto_increment
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- where条件
,t_name varchar(30)
,semianti_flg numeric
);

-- select句配列

drop temporary table if exists selectcols;
create temporary table if not exists selectcols(id int primary key auto_increment
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- カラム別名
,t_name varchar(30)
,semianti_flg numeric
,data_type varchar(30) -- 再帰 SQL用
,character_maximum_length numeric -- 再帰 SQL用
,numeric_scale numeric -- 再帰 SQL用
,data_scale numeric -- 再帰 SQL用
);

-- orderby句配列

drop temporary table if exists orderbycols;
create temporary table if not exists orderbycols(id int primary key auto_increment
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- ソート順
,t_name varchar(30)
,semianti_flg numeric
);

end
//
delimiter ;

drop procedure proc_makesql;
delimiter //
create procedure proc_makesql(
in p_tab_num int
, in p_outer_prb numeric
, in p_joincond_num numeric
, in p_whereind_num numeric
, in p_wherenoind_num numeric
, in p_select_num numeric
, in p_orderby_num numeric
, in p_desc_prb numeric
, in p_joinnoind_prb numeric
, in p_rami_prb numeric
, in p_groupby_prb numeric
, in p_orderby_prb numeric
, in p_semianti_prb numeric
, in p_semi_rto numeric
, in p_level numeric
, in p_level_max numeric
, out o_vsql varchar(4000)
, in p_recur_prb numeric
, in p_union_prb numeric
, in p_union_rto numeric
, in p_union_flg numeric
)
begin

-- パラメータ
declare C_TAB_NUM int default p_tab_num ; -- テーブル数
declare C_OUTER_PRB numeric default p_outer_prb ; -- 外部結合確率
declare C_JOINCOND_NUM numeric default p_joincond_num ; -- 結合条件数(結合毎)
declare C_WHEREIND_NUM numeric default p_whereind_num ; -- where条件数(インデックスあり)
declare C_WHERENOIND_NUM numeric default p_wherenoind_num ; -- where条件数(インデックスなし)
declare C_SELECT_NUM numeric default p_select_num ; -- select句カラム数
declare C_ORDERBY_NUM numeric default p_orderby_num ; -- order by句カラム数
declare C_DESC_PRB numeric default p_desc_prb ; -- desc確率
declare C_JOINNOIND_PRB numeric default p_joinnoind_prb ; -- インデックスなし結合確率
declare C_RAMI_PRB numeric default p_rami_prb ; -- 結合分岐確率
declare C_GROUPBY_PRB numeric default p_groupby_prb ; -- groupby発生確率
declare C_ORDERBY_PRB numeric default p_orderby_prb ; -- orderby発生確率
declare C_SEMIANTI_PRB numeric default p_semianti_prb ; -- セミアンチ結合発生確率(最大1個)
declare C_SEMI_RTO numeric default p_semi_rto ; -- セミ結合発生比率
declare C_LEVEL numeric default p_level ; -- 再帰階層レベル(0: 最上位の親, 1:子, 2:孫)
declare C_LEVEL_MAX numeric default p_level_max ; -- 再帰階層レベルの最大値
declare C_RECUR_PRB numeric default p_recur_prb ; -- 再帰 SQL発生確率
declare C_UNION_PRB numeric default p_union_prb ; -- union/union all SQL発生確率
declare C_UNION_RTO numeric default p_union_rto ; -- union発生比率
declare C_UNION_FLG numeric default p_union_flg ; -- unionSQL 実行フラグ (0: 通常のSQL, 1: unionSQL)

-- ワーク変数
declare wk1 numeric;
declare wk2 numeric;
declare wk3 numeric;
declare wk4 numeric;
declare wk5 numeric;
declare wk6 numeric;
declare wk7 numeric;
declare wk8 numeric;
declare wk9 numeric;
declare wk10 numeric;
declare wk11 numeric;
declare wk12 numeric;

declare wk51 int;

declare va1 varchar(4000);
declare va2 varchar(4000);
declare va3 varchar(4000);
declare va4 varchar(4000);
declare va5 varchar(4000);
declare va6 varchar(4000);
declare va7 varchar(4000);
declare va8 varchar(4000);
declare va9 varchar(4000);
declare va10 varchar(4000);
declare va11 varchar(4000);
declare va12 varchar(4000);
declare va13 varchar(4000);

-- フラグ
declare flg1 numeric default 0; -- 0: group byなし、 1: group byあり
declare flg2 numeric default 0; -- 0: order byなし、 1: order byあり
declare flg3 numeric default 0; -- 0: セミアンチなし、 1: セミあり、 2: アンチあり
declare flg4 numeric default 0; -- 0: 再帰なし、 1: 再帰あり

-- SQL文
declare vsql varchar(4000);
declare vsql_upper varchar(4000);

-- カーソル関連
declare done int;
declare done2 int;
declare done3 int;
declare cur10 cursor for select table_name from information_schema.tables
where table_schema='test'
and table_type = 'BASE TABLE'
order by rand() limit C_TAB_NUM;

declare cur20 cursor for select table_name
,column_name
,data_type
,coalesce(character_maximum_length,-1) character_maximum_length
,coalesce(numeric_precision,-1) numeric_precision
,coalesce(numeric_scale,-1) numeric_scale
from information_schema.columns
where table_schema = 'test'
and table_name in ( select table_name from tabnames )
order by table_name,column_name;

-- インラインビュー対応
declare cur21 cursor for select column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale
from grec where level = C_LEVEL+1;

declare cur30 cursor for select t1.col1
from ( select row_number() over(order by table_catalog) col1 from information_schema.columns limit wk51 ) t1
order by rand();

declare cur31 cursor for select id,table_no1 from tabcombi order by id;

declare cur32 cursor for select id,table_no1,table_no2 from tabcombi order by id;

declare cur40 cursor for select id,table_name1,table_name2,semianti_flg from tabcombi order by id;

declare cur41 cursor for select table_name ,column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale ,ind_head_flg ,t_name
from colnames where table_name = va1;

declare cur42 cursor for select table_name ,column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale ,ind_head_flg ,t_name
from colnames where table_name = va2;

declare cur43 cursor for select id from tabcombi order by id;

-- unionSQL対応
declare cur60 cursor for select id,column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale from grec2 order by id;

declare cur61 cursor for select table_name ,column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale ,t_name
from colnames
where semianti_flg = 0
order by rand();

declare cur80 cursor for select id,t_name,column_name,col3 from selectcols order by id;

declare cur81 cursor for select id,table_name1,table_name2,join_type,t_name1,t_name2 from tabcombi where semianti_flg = 0 order by id; -- セミアンチ結合のテーブルは対象外
declare cur82 cursor for select t_name1,t_name2,column_name1,column_name2,pri from joincols3 where tabcombi_id = wk1; -- 通常の処理とセミアンチ処理で共用
declare cur83 cursor for select t_name,column_name,col3 from whereindcols where semianti_flg = 0 order by id; -- セミアンチ結合のテーブルのカラムは対象外
declare cur84 cursor for select t_name,column_name,col3 from wherenoindcols where semianti_flg = 0 order by id; -- セミアンチ結合のテーブルのカラムは対象外

declare cur85 cursor for select id,t_name,column_name,col3 from orderbycols order by id;

-- セミアンチ結合対応
declare cur86 cursor for select id,table_name1,table_name2,join_type,t_name1,t_name2,semianti_flg from tabcombi where semianti_flg != 0 order by id;
declare cur87 cursor for select t_name,column_name,col3 from whereindcols where semianti_flg != 0 order by id;
declare cur88 cursor for select t_name,column_name,col3 from wherenoindcols where semianti_flg != 0 order by id;

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

call proc_init_table;

if C_LEVEL = 0 then
-- 作成SQLのカラム情報(再帰処理用)
drop temporary table if exists grec;
create temporary table if not exists grec( level numeric
,column_name varchar(30)
,data_type varchar(30)
,character_maximum_length numeric
,numeric_scale numeric
,data_scale numeric
);
-- 作成SQLのカラム情報(unionSQL用)
drop temporary table if exists grec2;
create temporary table if not exists grec2( id int primary key auto_increment
,column_name varchar(30)
,data_type varchar(30)
,character_maximum_length numeric
,numeric_scale numeric
,data_scale numeric
);
end if;

-- unionSQLの判定
set wk1 = ( select rand()*100 );
if wk1 < C_UNION_PRB and C_LEVEL <= C_LEVEL_MAX - 1 then -- unionSQLの場合
-- 上段SQL実行
call proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,0 -- C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,1 -- C_UNION_FLG
);

set vsql_upper = vsql;

-- 下段SQL実行
call proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,0 -- C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,1 -- C_UNION_FLG
);

set wk1 = ( select rand()*100 );
if wk1 < C_UNION_RTO then
set va1 = ' union ';
else
set va1 = ' union all ';
end if;

-- order by句処理
set wk1 = ( select rand()*100 );
if wk1 < C_ORDERBY_PRB then
set wk51 = ( select count(*) from grec2 );
set wk2 = 1;
set done = 0;
open cur30;
fetch cur30 into wk3;
while done != 1 do
if wk2 = 1 then
set va2 = concat(' order by ' , wk3);
else
set va2 = concat(va2, ',', wk3);
end if;
fetch cur30 into wk3;
set wk2 = wk2+1;
end while;
close cur30;
else
set va2 = '';
end if;

set o_vsql = concat(' ( ',vsql_upper,' ) ',va1,' ( ',vsql,' ) ',va2);

-- 後続の処理なし

else -- unionSQLでない start

-- 再帰有無の判定
set wk1 = ( select rand()*100 );

if wk1 < C_RECUR_PRB and C_LEVEL <= C_LEVEL_MAX - 1 then
set flg4 = 1;
end if;

if flg4 = 1 then

call proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,C_UNION_FLG
);

-- 配列初期化
call proc_init_table;

end if;

-- group by句とorder by句有無の判定
set wk1 = ( select rand()*100 );
set wk2 = ( select count(*) from grec2 );
set wk3 = ( select max(id) from grec2 );
set va1 = ( select data_type from grec2 where id = wk3 );
if wk1 < C_GROUPBY_PRB then
if C_UNION_FLG = 1 and wk2 > 0 then
if va1 = 'decimal' then
-- unionSQLの2回目以降は最後のカラムがdecimal型の場合のみgroup byを実施する
set flg1 = 1;
end if;
else
set flg1 = 1;
end if;
end if;

set wk1 = ( select rand()*100 );
if wk1 < C_ORDERBY_PRB then
set flg2 = 1;
end if;
-- セミアンチ有無の判定
set wk1 = ( select rand()*100);
if wk1 < C_SEMIANTI_PRB then
set wk2 = ( select rand()*100 );
if wk2 < C_SEMI_RTO then
set flg3 = 1;
else
set flg3 = 2;
end if;
end if;

-- ①テーブル名配列にテーブルを格納
-- テーブル数=3

set wk1 = 1;
set done = 0;
open cur10;
fetch cur10 into va1;
while done != 1 do
set va2 = concat('t',wk1,'_',C_LEVEL);
insert into tabnames(table_name,t_name,semianti_flg) values(va1,va2,0);
set wk1 = wk1+1;
fetch cur10 into va1;
end while;
close cur10;

-- ②テーブルのカラム名、データ型をカラム配列(2次元)に格納

set done = 0;
open cur20;
fetch cur20 into va1, va2, va3, wk1, wk2, wk3;
while done != 1 do
insert into colnames(table_name,column_name,data_type,character_maximum_length,numeric_scale,data_scale,ind_head_flg,semianti_flg)
values( va1, va2, va3, wk1, wk2, wk3, 0, 0 );

set wk4 = ( select count(*) from information_schema.statistics
where table_schema = 'test'
and table_name = va1
and column_name = va2
and seq_in_index = 1 );

if wk4 > 0 then
update colnames set ind_head_flg = 1 where table_name = va1
and column_name = va2;
end if;

update colnames t1 set t1.t_name = ( select t2.t_name from tabnames t2 where t2.table_name = t1.table_name );

fetch cur20 into va1, va2, va3, wk1, wk2, wk3;
end while;
close cur20;

-- 再帰ありの場合、子から受け取ったカラム情報をテーブル配列とカラム配列に追加
if flg4 = 1 then
set wk1 = ( select count(*) from tabnames)+1;

set va1 = concat(' ( ',vsql,' ) ');
set va2 = concat('t' , wk1 , '_' , C_LEVEL);
insert into tabnames(table_name,t_name,semianti_flg) values(va1,va2,0);

set done = 0;
open cur21;
fetch cur21 into va3, va4, wk2, wk3, wk4;
while done != 1 do
insert into colnames(table_name,column_name,data_type,character_maximum_length,numeric_scale,data_scale,ind_head_flg,t_name,semianti_flg)
values( va1, va3, va4, wk2, wk3, wk4, 0, va2, 0 );

fetch cur21 into va3, va4, wk2, wk3, wk4;
end while;
close cur21;
end if;

-- ③結合テーブル組み合わせ配列の作成

set wk1 = 1;
set wk51 = ( select count(*) from tabnames );

if wk51 > 1 then
set done = 0;
open cur30;
fetch cur30 into wk3;
while done != 1 do
if wk1 = 1 then
insert into tabcombi(table_no1,join_type) values(wk3,'I');
elseif wk1 < wk51 then
update tabcombi set table_no2 = wk3 where id = wk1-1;
insert into tabcombi(table_no1,join_type) values(wk3,'I');
else
update tabcombi set table_no2 = wk3 where id = wk1-1;
end if;

fetch cur30 into wk3;
set wk1 = wk1+1;
end while;
close cur30;
end if;

-- 結合分岐設定
set done = 0;
open cur31;
fetch cur31 into wk1,wk2;
while done != 1 do
set wk3 = ( select rand()*100 );
if wk1 > 1 and wk3 < C_RAMI_PRB then
update tabcombi set table_no1 = wk4 where id = wk1;
end if;
set wk4 = ( select table_no1 from tabcombi where id = wk1 );
fetch cur31 into wk1,wk2;
end while;
close cur31;

-- 結合タイプ、テーブル名のセット

set done = 0;
open cur32;
fetch cur32 into wk1,wk2,wk3;
while done != 1 do
update tabcombi set table_name1 = ( select table_name from tabnames where id = wk2 ) where id = wk1;
update tabcombi set table_name2 = ( select table_name from tabnames where id = wk3 ) where id = wk1;
update tabcombi set t_name1 = ( select t_name from tabnames where id = wk2 ) where id = wk1;
update tabcombi set t_name2 = ( select t_name from tabnames where id = wk3 ) where id = wk1;
update tabcombi set semianti_flg = 0 where id = wk1;

-- 結合タイプ設定。外部結合確率20%
set wk4 = ( select rand()*100 );
if wk4 < C_OUTER_PRB then
update tabcombi set join_type = 'O' where id = wk1;
end if;

-- セミアンチありの場合、最後の1件にフラグを立てる
-- さかのぼり、テーブル名配列とカラム配列もフラグを立てる
if wk1 > 1 and wk1 = (select count(*) from tabcombi) and flg3 != 0 then
update tabcombi set semianti_flg = flg3 where id = wk1;

update tabnames set semianti_flg = flg3 where table_name in ( select table_name2 from tabcombi where id = wk1 );
update colnames set semianti_flg = flg3 where table_name in ( select table_name2 from tabcombi where id = wk1 );
end if;

fetch cur32 into wk1,wk2,wk3;
end while;
close cur32;

-- ④結合条件配列の作成
-- 結合条件優先度
-- 20: データ型、データ長一致、インデックス先頭含む
-- 15: データ型、データ長一致
-- 10: データ型のみ一致、インデックス先頭含む
-- 5: データ型のみ一致
-- 0: データ型一致カラムなし

set done = 0;
open cur40;
fetch cur40 into wk11,va1,va2,wk12;
while done != 1 do

set wk10 = 1;

begin
declare continue handler for sqlstate '02000' set done2 = 1;
set done2 = 0;
open cur41;
fetch cur41 into va3,va4,va5,wk1,wk2,wk3,wk4,va9;
while done2 != 1 do

begin
declare continue handler for sqlstate '02000' set done3 = 1;
set done3 = 0;
open cur42;
fetch cur42 into va6,va7,va8,wk5,wk6,wk7,wk8,va10;
while done3 != 1 do

if va5 = va8 then
if wk1 = wk5 and wk2 = wk6 and wk3 = wk7 then
if wk4 = 1 or wk8 = 1 then
set wk9 = 20;
else
set wk9 = 15;
end if;
else
if wk4 = 1 or wk8 = 1 then
set wk9 = 10;
else
set wk9 = 5;
end if;
end if;

insert into joincols(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg) values(wk11,va3,va6,va4,va7,wk9,va9,va10,wk12);
set wk10 = wk10+1;
end if;

fetch cur42 into va6,va7,va8,wk5,wk6,wk7,wk8,va10;
end while;
close cur42;
end;

fetch cur41 into va3,va4,va5,wk1,wk2,wk3,wk4,va9;
end while;
close cur41;
end;

-- 結合条件がない場合、優先度0でレコード作成
if wk10 = 1 then
insert into joincols(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg) values(0,'','','','',0,'','',0);
end if;

fetch cur40 into wk11,va1,va2,wk12;
end while;
close cur40;

-- 結合条件配列のシャッフル
-- 結合条件配列の過剰分削除
-- テーブル組み合わせごとに条件数の上限=3

set done = 0;
open cur43;
fetch cur43 into wk1;
while done != 1 do
set wk2 = ( select rand()*100 );
if wk2 < C_JOINNOIND_PRB then
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 15 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 20 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 5 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 10 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 0 order by rand();
else
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 20 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 15 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 10 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 5 order by rand();
insert into joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols where tabcombi_id = wk1 and pri = 0 order by rand();

end if;

set wk3 = ( select min(id) from joincols2 );
set wk3 = wk3 + C_JOINCOND_NUM;

insert into joincols3(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from joincols2 where id <= wk3;

fetch cur43 into wk1;
end while;
close cur43;

-- ⑤where条件配列の作成
-- インデックス有とインデックスなし
-- where条件配列のシャッフル
-- where条件配列の過剰分削除
-- 条件数の上限=3

insert into whereindcols(table_name,column_name,col3,t_name,semianti_flg)
select table_name,column_name, case when data_type in ( 'int','decimal' ) then ' < 100 '
when data_type in ( 'char','varchar' ) then ' like ''%A%'' '
else ' IS NOT NULL '
end col3
, t_name
, semianti_flg
from colnames
where ind_head_flg = 1
order by rand();

insert into wherenoindcols(table_name,column_name,col3,t_name,semianti_flg)
select table_name,column_name, case when data_type in ( 'int','decimal' ) then ' < 100 '
when data_type in ( 'char','varchar' ) then ' like ''%A%'' '
else ' IS NOT NULL '
end col3
, t_name
, semianti_flg
from colnames
where ind_head_flg = 0
order by rand();

delete from whereindcols where id > C_WHEREIND_NUM;
delete from wherenoindcols where id > C_WHERENOIND_NUM;

-- ⑥select句配列の作成
-- セミアンチ結合のテーブルのカラムは対象外
-- select句配列のシャッフル
-- select句配列の過剰分削除＋カラム別名付与
-- select句カラム数上限=5

set wk1 = ( select count(*) from grec2);
if C_UNION_FLG = 1 and wk1 > 0 then -- unionSQLの2回目以降
-- group byの場合、カラム数-1
if flg1 = 1 then
set wk2 = wk1 - 1;
else
set wk2 = wk1;
end if;

set done = 0;
open cur60;
fetch cur60 into wk3,va1,va2,wk4,wk5,wk6;
while ( done != 1 and wk3 <= wk2 ) do

set wk11 = 1;
begin
declare continue handler for sqlstate '02000' set done2 = 1;
set done2 = 0;
open cur61;
fetch cur61 into va3,va4,va5,wk7,wk8,wk9,va6;
L1: while done2 != 1 do

if va2 = va5 then
-- 既に使用済みカラムかチェック
set wk10 = ( select count(*) from selectcols where table_name = va3 and column_name = va4 );
if wk10 = 0 then
set va7 = concat('col',wk3);
insert into selectcols (table_name ,column_name ,col3 ,t_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale)
values(va3 ,va4 ,va7 ,va6 ,va5 ,wk7 ,wk8 ,wk9);
set wk11 = wk11+1;
leave L1;
end if;
end if;

fetch cur61 into va3,va4,va5,wk7,wk8,wk9,va6;
end while L1;
close cur61;
end;

if wk11 = 1 then
-- 同じデータ型のカラムがない場合カラム名としてNULLをセット
set va7 = concat('col',wk3);
insert into selectcols (table_name ,column_name ,col3 ,t_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale)
values('' ,'NULL' ,va7 ,'' ,'' ,0 ,0 ,0);
end if;

fetch cur60 into wk3,va1,va2,wk4,wk5,wk6;
end while;
close cur60;

else
insert into selectcols(table_name,column_name,t_name,semianti_flg ,data_type,character_maximum_length,numeric_scale,data_scale)
select table_name,column_name,t_name,semianti_flg ,data_type,character_maximum_length,numeric_scale,data_scale
from colnames
where semianti_flg = 0
order by rand();
delete from selectcols where id > C_SELECT_NUM;
update selectcols set col3 = concat('col',id);
end if;

-- select句の内容を親へ渡すためにout配列に格納

insert into grec(level, column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale )
select C_LEVEL, col3 ,data_type ,character_maximum_length ,numeric_scale ,data_scale from selectcols;

-- ⑦orderby句配列の作成
-- orderby句カラム数上限=3
-- セミアンチ結合のテーブルのカラムは対象外
-- orderby句配列のシャッフル
-- orderby句配列の過剰分削除

-- order by句出力なしの場合も作成する
-- group by句出力ありの場合は、select句配列をコピーし、シャッフルと過剰分削除を行う
if flg1 = 1 then
insert into orderbycols(table_name,column_name,t_name,semianti_flg)
select table_name,column_name,t_name,semianti_flg
from selectcols
order by rand();
else
insert into orderbycols(table_name,column_name,t_name,semianti_flg)
select table_name,column_name,t_name,semianti_flg
from colnames
where semianti_flg = 0
order by rand();
end if;

delete from orderbycols where id > C_ORDERBY_NUM;
update orderbycols set col3 = case when rand()*100 < C_DESC_PRB then ' desc ' else ' asc ' end;

-- ⑧ SQL文出力
set vsql = 'select ';

set done = 0;
open cur80;
fetch cur80 into wk1,va1,va2,va3;
while done != 1 do
if va2 != 'NULL' then
set va4 = concat(va1,'.',va2,' ',va3);
else
set va4 = concat(va2,' ',va3);
end if;
if wk1 > 1 then
set va4 = concat(',',va4);
end if;
set vsql = concat(vsql,va4);
fetch cur80 into wk1,va1,va2,va3;
end while;
close cur80;

-- group by句ありの場合、count関数を出力
if flg1 = 1 then
set wk1 = ( select count(*) from selectcols );
set wk1 = wk1+1;
set va1 = concat('col',wk1);
if wk1 > 1 then
set vsql = concat(vsql,' ,count(*) ',va1);
else
set vsql = concat(vsql,' count(*) ',va1);
end if;
insert into grec(level, column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale )
values( C_LEVEL, va1 ,'decimal' ,-1 ,10 ,0);

end if;

set vsql = concat(vsql,' from ');

set wk1 = ( select count(*) from tabnames );
if wk1 > 1 then
set done = 0;
open cur81;
fetch cur81 into wk1,va1,va2,va3,va12,va13;
while done != 1 do
if va3 = 'I' then
set va4 = ' inner join ';
else
set va4 = ' left outer join ';
end if;

if wk1 = 1 then
set va5 = concat(va1,' ',va12,va4,va2,' ',va13);
else
set va5 = concat(va4,va2,' ',va13);
end if;
set vsql = concat(vsql,va5);

-- 結合条件セット
set wk2 = 0;

begin
declare continue handler for sqlstate '02000' set done2 = 1;
set done2 = 0;
open cur82;
fetch cur82 into va6,va7,va8,va9,wk3;
while done2 != 1 do
if wk3 != 0 then
set wk2 = wk2+1;
if wk2 = 1 then
set va10 = ' on ';
else
set va10 = ' and ';
end if;

set va11 = concat(va10,va6,'.',va8,' = ',va7,'.',va9);
set vsql = concat(vsql,va11);
end if;

fetch cur82 into va6,va7,va8,va9,wk3;
end while;
close cur82;
end;

-- 結合条件がない場合
if wk2 = 0 then
set va11 = ' on 1 = 1 ';
set vsql = concat(vsql,va11);
end if;
fetch cur81 into wk1,va1,va2,va3,va12,va13;
end while;
close cur81;
else
-- テーブル1個の場合
set va1 = ( select table_name from tabnames );
set va2 = ( select t_name from tabnames );
set vsql = concat(vsql,va1,' ',va2);
end if;

set vsql = concat(vsql,' where 1 = 1 ');

-- セミアンチ結合対応

set done = 0;
open cur86;
fetch cur86 into wk1,va1,va2,va3,va12,va13,wk4;
while done != 1 do
if wk4 = 1 then
set va4 = ' and exists ( select 1 from ';
else
set va4 = ' and not exists ( select 1 from ';
end if;

set va4 = concat(va4,' ',va2,' ',va13);
set va4 = concat(va4,' where 1 = 1 ');

-- 結合条件セット

begin
declare continue handler for sqlstate '02000' set done2 = 1;
set done2 = 0;
open cur82;
fetch cur82 into va6,va7,va8,va9,wk3;
while done2 != 1 do
if wk3 != 0 then -- ダミーレコードは除く
set va4 = concat(va4,' and ',va7,'.',va9,' = ',va6,'.',va8);
end if;

fetch cur82 into va6,va7,va8,va9,wk3;
end while;
close cur82;
end;
set vsql = concat(vsql,va4);

-- where条件セット
set done = 0;
open cur87;
fetch cur87 into va1,va2,va3;
while done != 1 do
set va4 = concat(' and ',va1,'.',va2,va3);
set vsql = concat(vsql,va4);
fetch cur87 into va1,va2,va3;
end while;
close cur87;

set done = 0;
open cur88;
fetch cur88 into va1,va2,va3;
while done != 1 do
set va4 = concat(' and ',va1,'.',va2,va3);
set vsql = concat(vsql,va4);
fetch cur88 into va1,va2,va3;
end while;
close cur88;

set vsql = concat(vsql,' ) ');

fetch cur86 into wk1,va1,va2,va3,va12,va13,wk4;
end while;
close cur86;

set done = 0;
open cur83;
fetch cur83 into va1,va2,va3;
while done != 1 do
set va4 = concat(' and ',va1,'.',va2,va3);
set vsql = concat(vsql,va4);
fetch cur83 into va1,va2,va3;
end while;
close cur83;

set done = 0;
open cur84;
fetch cur84 into va1,va2,va3;
while done != 1 do
set va4 = concat(' and ',va1,'.',va2,va3);
set vsql = concat(vsql,va4);
fetch cur84 into va1,va2,va3;
end while;
close cur84;

-- group by句ありの場合
if flg1 = 1 then
set done = 0;
open cur80;
fetch cur80 into wk1,va1,va2,va3;
while done != 1 do
if wk1 = 1 then
set va4 = ' group by ';
else
set va4 = ',';
end if;
if va2 != 'NULL' then
set va5 = concat(va4,va1,'.',va2);
else
set va5 = concat(va4,va2);
end if;
set vsql = concat(vsql,va5);
fetch cur80 into wk1,va1,va2,va3;
end while;
close cur80;
end if;

-- order by句ありの場合
if flg2 = 1 then
set done = 0;
open cur85;
fetch cur85 into wk1,va1,va2,va3;
while done != 1 do
if wk1 = 1 then
set va4 = ' order by ';
else
set va4 = ',';
end if;
set va5 = concat(va4,va1,'.',va2,va3);
set vsql = concat(vsql,va5);
fetch cur85 into wk1,va1,va2,va3;
end while;
close cur85;
end if;

set o_vsql = vsql;

set wk1 = (select count(*) from grec2);

if C_UNION_FLG = 1 and wk1 = 0 then
insert into grec2( column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale )
select column_name ,data_type ,character_maximum_length ,numeric_scale ,data_scale from grec;
end if;

end if; -- unionSQLでない end

end
//
delimiter ;

drop procedure main;

delimiter //
create procedure main()
begin

declare vsql varchar(4000);

declare loop_num numeric default 1; -- proc_makesql実行回数

declare p_tab_num numeric;
declare p_outer_prb numeric;
declare p_joincond_num numeric;
declare p_whereind_num numeric;
declare p_wherenoind_num numeric;

declare p_select_num numeric;
declare p_orderby_num numeric;
declare p_desc_prb numeric;
declare p_joinnoind_prb numeric;
declare p_rami_prb numeric;

declare p_groupby_prb numeric;
declare p_orderby_prb numeric;
declare p_semianti_prb numeric;
declare p_semi_rto numeric;
declare p_level_max numeric;

declare p_recur_prb numeric;
declare p_union_prb numeric;
declare p_union_rto numeric;

declare p_tab_num_l numeric default 1;
declare p_tab_num_u numeric default 5;

declare p_outer_prb_l numeric default 0;
declare p_outer_prb_u numeric default 100;

declare p_joincond_num_l numeric default 1;
declare p_joincond_num_u numeric default 5;

declare p_whereind_num_l numeric default 1;
declare p_whereind_num_u numeric default 5;

declare p_wherenoind_num_l numeric default 1;
declare p_wherenoind_num_u numeric default 5;

declare p_select_num_l numeric default 1;
declare p_select_num_u numeric default 10;

declare p_orderby_num_l numeric default 1;
declare p_orderby_num_u numeric default 5;

declare p_desc_prb_l numeric default 0;
declare p_desc_prb_u numeric default 100;

declare p_joinnoind_prb_l numeric default 0;
declare p_joinnoind_prb_u numeric default 100;

declare p_rami_prb_l numeric default 0;
declare p_rami_prb_u numeric default 100;

declare p_groupby_prb_l numeric default 0;
declare p_groupby_prb_u numeric default 100;

declare p_orderby_prb_l numeric default 0;
declare p_orderby_prb_u numeric default 100;

declare p_semianti_prb_l numeric default 0;
declare p_semianti_prb_u numeric default 100;

declare p_semi_rto_l numeric default 0;
declare p_semi_rto_u numeric default 100;

declare p_level_max_l numeric default 0;
declare p_level_max_u numeric default 2;

declare p_recur_prb_l numeric default 0;
declare p_recur_prb_u numeric default 100;

declare p_union_prb_l numeric default 0;
declare p_union_prb_u numeric default 100;

declare p_union_rto_l numeric default 0;
declare p_union_rto_u numeric default 100;

declare wk1 numeric default 1;

while wk1 <= loop_num do
set p_tab_num = ( select round( p_tab_num_l +( p_tab_num_u - p_tab_num_l)*rand() ) );
set p_outer_prb = (select p_outer_prb_l+( p_outer_prb_u - p_outer_prb_l)*rand() ) ;
set p_joincond_num = (select round( p_joincond_num_l +( p_joincond_num_u - p_joincond_num_l)*rand() ) ) ;
set p_whereind_num = (select round( p_whereind_num_u +( p_whereind_num_u - p_whereind_num_l)*rand() ) ) ;
set p_wherenoind_num = (select round( p_wherenoind_num_l +( p_wherenoind_num_u - p_wherenoind_num_l)*rand() ) ) ;

set p_select_num = (select round( p_select_num_l +( p_select_num_u - p_select_num_l)*rand() ) ) ;
set p_orderby_num = (select round( p_orderby_num_l +( p_orderby_num_u - p_orderby_num_l)*rand() ) ) ;
set p_desc_prb = (select p_desc_prb_l +( p_desc_prb_u - p_desc_prb_l)*rand() ) ;
set p_joinnoind_prb = (select p_joinnoind_prb_l +( p_joinnoind_prb_u - p_joinnoind_prb_l)*rand() ) ;
set p_rami_prb = (select p_rami_prb_l +( p_rami_prb_u - p_rami_prb_l)*rand() ) ;

set p_groupby_prb = (select p_groupby_prb_l +( p_groupby_prb_u - p_groupby_prb_l)*rand() ) ;
set p_orderby_prb = (select p_orderby_prb_l +( p_orderby_prb_u - p_orderby_prb_l)*rand() ) ;
set p_semianti_prb = (select p_semianti_prb_l +( p_semianti_prb_u - p_semianti_prb_l)*rand() ) ;
set p_semi_rto = (select p_semi_rto_l +( p_semi_rto_u - p_semi_rto_l)*rand() ) ;
set p_level_max = (select round( p_level_max_l +( p_level_max_u - p_level_max_l)*rand() ) ) ;

set p_recur_prb = (select p_recur_prb_l +( p_recur_prb_u - p_recur_prb_l)*rand() ) ;
set p_union_prb = (select p_union_prb_l +( p_union_prb_u - p_union_prb_l)*rand() ) ;
set p_union_rto = (select p_union_rto_l +( p_union_rto_u - p_union_rto_l)*rand() ) ;

call proc_makesql(
p_tab_num
,p_outer_prb
,p_joincond_num
,p_whereind_num
,p_wherenoind_num
,p_select_num
,p_orderby_num
,p_desc_prb
,p_joinnoind_prb
,p_rami_prb
,p_groupby_prb
,p_orderby_prb
,p_semianti_prb
,p_semi_rto
,0 -- p_level
,p_level_max
,vsql
,p_recur_prb
,p_union_prb
,p_union_rto
,0 -- p_union_flg
);

set vsql = concat(vsql,' ; ');
select vsql;

set wk1 = wk1+1;
end while;

end
//
delimiter ;

call main;

Oracle

(19c)

drop type gar;
drop type grec;

create or replace type grec as object(
column_name varchar2(30)
,data_type varchar2(30)
,data_length number
,data_precision number
,data_scale number
);
/

create or replace type gar as
table of grec;
/

set serveroutput on
create or replace procedure proc_makesql(
p_tab_num in number default 3
,p_outer_prb in number default 0.2
,p_joincond_num in number default 3
,p_whereind_num in number default 3
,p_wherenoind_num in number default 3
,p_select_num in number default 5
,p_orderby_num in number default 3
,p_desc_prb in number default 0.2
,p_joinnoind_prb in number default 0.1
,p_rami_prb in number default 0.5
,p_groupby_prb in number default 0.5
,p_orderby_prb in number default 0.5
,p_semianti_prb in number default 0.2
,p_semi_rto in number default 0.5
,p_level in number default 0
,p_level_max in number default 2
,o_vsql out varchar2
,o_vsqlcolname out gar
,p_recur_prb in number default 0.3
,p_union_prb in number default 0.3
,p_union_rto in number default 0.5
,p_union_flg in number default 0
,io_vsqlcolname in out gar
)
as

-- パラメータ
C_TAB_NUM number := p_tab_num; -- テーブル数
C_OUTER_PRB number := p_outer_prb; -- 外部結合確率
C_JOINCOND_NUM number := p_joincond_num; -- 結合条件数(結合毎)
C_WHEREIND_NUM number := p_whereind_num; -- where条件数(インデックスあり)
C_WHERENOIND_NUM number := p_wherenoind_num; -- where条件数(インデックスなし)
C_SELECT_NUM number := p_select_num; -- select句カラム数
C_ORDERBY_NUM number := p_orderby_num; -- order by句カラム数
C_DESC_PRB number := p_desc_prb; -- desc確率
C_JOINNOIND_PRB number := p_joinnoind_prb; -- インデックスなし結合確率
C_RAMI_PRB number := p_rami_prb; -- 結合分岐確率
C_GROUPBY_PRB number := p_groupby_prb; -- groupby発生確率
C_ORDERBY_PRB number := p_orderby_prb; -- orderby発生確率
C_SEMIANTI_PRB number := p_semianti_prb; -- セミアンチ結合発生確率(最大1個)
C_SEMI_RTO number := p_semi_rto; -- セミ結合発生比率

C_LEVEL number := p_level; -- 再帰階層レベル(0: 最上位の親, 1:子, 2:孫)
C_LEVEL_MAX number := p_level_max; -- 再帰階層レベルの最大値
C_RECUR_PRB number := p_recur_prb; -- 再帰 SQL発生確率

C_UNION_PRB number := p_union_prb; -- union/union all SQL発生確率
C_UNION_RTO number := p_union_rto; -- union発生比率
C_UNION_FLG number := p_union_flg; -- unionSQL 実行フラグ (0: 通常のSQL, 1: unionSQL)

-- テーブル名配列
type rec10 is record(
table_name varchar2(4000)
,t_name varchar2(30)
,semianti_flg number
);

type ar10 is table of rec10 index by binary_integer;
tabnames ar10 := ar10();

-- カラム配列(2次元配列)
type rec20 is record(
table_name varchar2(4000)
,column_name varchar2(30)
,data_type varchar2(30)
,data_length number
,data_precision number
,data_scale number
,ind_head_flg number
,t_name varchar2(30)
,semianti_flg number
);

type ar20 is table of rec20 index by binary_integer;
type ar21 is table of ar20 index by binary_integer;
colnames ar21 := ar21();

-- 結合テーブル組み合わせ配列
type rec30 is record(
table_no1 number
,table_no2 number
,table_name1 varchar2(4000)
,table_name2 varchar2(4000)
,join_type varchar2(30) -- innner join or left join
,t_name1 varchar2(30)
,t_name2 varchar2(30)
,semianti_flg number
);

type ar30 is table of rec30 index by binary_integer;
tabcombi ar30 := ar30();

-- 結合条件配列(2次元配列)
type rec40 is record(
table_name1 varchar2(4000)
,table_name2 varchar2(4000)
,column_name1 varchar2(30)
,column_name2 varchar2(30)
,pri number -- priority
,t_name1 varchar2(30)
,t_name2 varchar2(30)
,semianti_flg number
);

type ar40 is table of rec40 index by binary_integer;
type ar41 is table of ar40 index by binary_integer;
joincols ar41 := ar41();
joincols2 ar41 := ar41();

-- where条件配列(indexあり)、wherex条件配列(indexなし)、select句配列、orderby句配列

type rec50 is record(
table_name varchar2(4000)
,column_name varchar2(30)
,col3 varchar2(30) -- where条件 or カラム別名 or ソート順
,t_name varchar2(30)
,semianti_flg number
,data_type varchar2(30) -- select句配列で使用。再帰 SQL用
,data_length number -- select句配列で使用。再帰 SQL用
,data_precision number -- select句配列で使用。再帰 SQL用
,data_scale number -- select句配列で使用。再帰 SQL用
);

type ar50 is table of rec50 index by binary_integer;

whereindcols ar50 := ar50();
whereindcols2 ar50 := ar50();

wherenoindcols ar50 := ar50();
wherenoindcols2 ar50 := ar50();

selectcols ar50 := ar50();
selectcols2 ar50 := ar50();

orderbycols ar50 := ar50();
orderbycols2 ar50 := ar50();

-- ワーク変数
wk1 number;
wk2 number;
wk3 number;
wk4 number;
wk5 number;

va1 varchar2(4000);
va2 varchar2(4000);
va3 varchar2(4000);
va4 varchar2(4000);
va5 varchar2(4000);

-- フラグ
flg1 number :=0 ; -- 0: group byなし、 1: group byあり
flg2 number :=0 ; -- 0: order byなし、 1: order byあり
flg3 number :=0 ; -- 0: セミアンチなし、 1: セミあり、 2: アンチあり
flg4 number :=0 ; -- 0: 再帰なし、 1: 再帰あり

-- SQL文
vsql varchar2(4000);
vsql_upper varchar2(4000);

-- 作成SQLの文のカラム情報(OUT用)
vsqlcolname gar := gar();

-- サブプログラム① 結合条件配列のシャッフル(優先度考慮あり)
-- p1 -> 結合テーブル組み合わせ
-- p2 -> シャッフル前配列
-- p3 -> シャッフル後配列
procedure subp1(p1 in int,p2 in ar41,p3 out ar41)
as
begin
for i in 1..p1 loop
wk2 := p2(i).count;

-- 10%の確率でインデックスなしの結合条件を優先とする

select dbms_random.value(0,1) into wk3 from dual;
if wk3 < C_JOINNOIND_PRB then

wk1 := 1;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 15 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 20 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 5 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 10 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 0 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;

else

wk1 := 1;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 20 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 15 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 10 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 5 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if p2(i)(c1.level).pri = 0 then
p3(i)(wk1) := p2(i)(c1.level);
wk1 := wk1+1;
end if;
end loop;
end if;

end loop;
end;

-- サブプログラム② where条件配列、select句配列、orderby句配列のシャッフル(優先度考慮なし)
-- p1 -> シャッフル前配列
-- p2 -> シャッフル後配列
procedure subp2(p1 in ar50,p2 out ar50)
as
begin
wk1 := 1;
wk2 := p1.count;
if wk2 > 0 then
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
p2(wk1) := p1(c1.level);
wk1 := wk1+1;
end loop;
end if;
end;

begin

-- unionSQLの判定
select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_UNION_PRB and C_LEVEL <= C_LEVEL_MAX - 1 then
-- 上段SQL実行
proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,0 -- C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,vsqlcolname
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,1 -- C_UNION_FLG
,io_vsqlcolname
);

vsql_upper := vsql;

-- 下段SQL実行
proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,0 -- C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,vsqlcolname
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,1 -- C_UNION_FLG
,io_vsqlcolname
);

select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_UNION_RTO then
va1 := ' union ';
else
va1 := ' union all ';
end if;

-- order by句処理
select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_ORDERBY_PRB then
wk2 := vsqlcolname.count;
wk3 := 1;
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if wk3 = 1 then
va2 := ' order by ' || c1.level;
else
va2 := va2|| ',' || c1.level;
end if;
wk3 := wk3+1;
end loop;
else
va2 := '';
end if;

o_vsql := ' ( ' || vsql_upper || ' ) ' || va1 || ' ( ' || vsql || ' ) ' || va2;
o_vsqlcolname := vsqlcolname;
-- 後続の処理なし
return;

end if;

-- 再帰有無の判定
select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_RECUR_PRB and C_LEVEL <= C_LEVEL_MAX - 1 then
flg4 := 1;
end if;

if flg4 = 1 then

proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,vsqlcolname
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,C_UNION_FLG
,io_vsqlcolname
);
end if;

-- group by句とorder by句有無の判定
select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_GROUPBY_PRB then
if C_UNION_FLG = 1 and io_vsqlcolname.count > 0 then
if io_vsqlcolname(io_vsqlcolname.count).data_type = 'NUMBER' then
-- unionSQLの2回目以降は最後のカラムがNUMBER型の場合のみgroup byを実施する
flg1 := 1;
end if;
else
flg1 := 1;
end if;
end if;

select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_ORDERBY_PRB then
flg2 := 1;
end if;
-- セミアンチ有無の判定
select dbms_random.value(0,1) into wk1 from dual;
if wk1 < C_SEMIANTI_PRB then
select dbms_random.value(0,1) into wk2 from dual;
if wk2 < C_SEMI_RTO then
flg3 := 1;
else
flg3 := 2;
end if;
end if;

-- ①テーブル名配列にテーブルを格納
-- テーブル数=3
wk1 := 1;
for c1 in ( select table_name from user_tables order by dbms_random.random fetch first C_TAB_NUM rows only ) loop
tabnames(wk1).table_name := c1.table_name;
tabnames(wk1).t_name := 'T' || wk1 || '_' || C_LEVEL;
tabnames(wk1).semianti_flg := 0;
wk1 := wk1+1;
end loop;

-- dbms_output.put_line('---tabnames---' );
-- for i in 1..tabnames.count loop
-- dbms_output.put_line( tabnames(i).table_name || ','||
-- tabnames(i).t_name );
-- end loop;

-- ②テーブルのカラム名、データ型をカラム配列(2次元)に格納

for c1 in 1..tabnames.count loop
wk1 := 1;
for c2 in ( select table_name
,column_name
,data_type
,nvl(data_length,-1) data_length
,nvl(data_precision,-1) data_precision
,nvl(data_scale,-1) data_scale
from user_tab_cols where table_name = tabnames(c1).table_name ) loop
colnames(c1)(wk1).table_name := c2.table_name;
colnames(c1)(wk1).column_name := c2.column_name;
colnames(c1)(wk1).data_type := c2.data_type;
colnames(c1)(wk1).data_length := c2.data_length;
colnames(c1)(wk1).data_precision := c2.data_precision;
colnames(c1)(wk1).data_scale := c2.data_scale;
colnames(c1)(wk1).t_name := tabnames(c1).t_name;
colnames(c1)(wk1).semianti_flg := 0;

select count(*) into wk2 from user_ind_columns
where table_name = tabnames(c1).table_name
and column_name = c2.column_name
and column_position = 1;
colnames(c1)(wk1).ind_head_flg := wk2;

wk1 := wk1+1;
end loop;
end loop;

-- 再帰ありの場合、子から受け取ったカラム情報をテーブル配列とカラム配列に追加
if flg4 = 1 then
wk1 := tabnames.count+1;
tabnames(wk1).table_name := ' ( '||vsql||' ) ';
tabnames(wk1).t_name := 'T' || wk1 || '_' || C_LEVEL;
tabnames(wk1).semianti_flg := 0;

for i in 1..vsqlcolname.count loop
colnames(wk1)(i).table_name := tabnames(wk1).table_name ;
colnames(wk1)(i).column_name := vsqlcolname(i).column_name;
colnames(wk1)(i).data_type := vsqlcolname(i).data_type;
colnames(wk1)(i).data_length := vsqlcolname(i).data_length;
colnames(wk1)(i).data_precision := vsqlcolname(i).data_precision;
colnames(wk1)(i).data_scale := vsqlcolname(i).data_scale;
colnames(wk1)(i).ind_head_flg := 0;
colnames(wk1)(i).t_name := tabnames(wk1).t_name;
colnames(wk1)(i).semianti_flg := 0;
end loop;

end if;

-- ③結合テーブル組み合わせ配列の作成

wk1 := 1;
wk2 := tabnames.count;

if wk2 > 1 then
for c1 in (select level from dual connect by level <= wk2 order by dbms_random.random ) loop
if wk1 = 1 then
tabcombi(wk1).table_no1 := c1.level;
elsif wk1 < wk2 then
tabcombi(wk1-1).table_no2 := c1.level;
tabcombi(wk1).table_no1 := c1.level;
else
tabcombi(wk1-1).table_no2 := c1.level;
end if;

wk1 := wk1+1;
end loop;
end if;

-- 結合分岐設定
for i in 1..tabcombi.count loop
select dbms_random.value(0,1) into wk1 from dual;
if i > 1 and wk1 < C_RAMI_PRB then
tabcombi(i).table_no1 := wk2;
end if;
wk2 := tabcombi(i).table_no1;

end loop;

-- 結合タイプ、テーブル名のセット
for i in 1..tabcombi.count loop

tabcombi(i).table_name1 := tabnames(tabcombi(i).table_no1).table_name;
tabcombi(i).table_name2 := tabnames(tabcombi(i).table_no2).table_name;
tabcombi(i).t_name1 := tabnames(tabcombi(i).table_no1).t_name;
tabcombi(i).t_name2 := tabnames(tabcombi(i).table_no2).t_name;
tabcombi(i).semianti_flg := 0;

tabcombi(i).join_type := 'I';
-- 結合タイプ設定。外部結合確率20%
select dbms_random.value(0,1) into wk3 from dual;
if wk3 < C_OUTER_PRB then
tabcombi(i).join_type := 'O';
end if;

-- セミアンチありの場合、最後の1件にフラグを立てる
-- さかのぼり、テーブル名配列とカラム配列もフラグを立てる
if i > 1 and i = tabcombi.count and flg3 != 0 then
tabcombi(i).semianti_flg := flg3;
for j in 1..tabnames.count loop
if tabnames(j).table_name = tabcombi(i).table_name2 then
tabnames(j).semianti_flg := flg3;
for k in 1..colnames(j).count loop
colnames(j)(k).semianti_flg := flg3;
end loop;
end if;
end loop;
end if;
end loop;

-- dbms_output.put_line('---tabcombi---' );
-- for i in 1..tabcombi.count loop
-- dbms_output.put_line( tabcombi(i).table_no1 || ','||
-- tabcombi(i).table_no2 || ','||
-- tabcombi(i).table_name1 || ','||
-- tabcombi(i).table_name2 || ','||
-- tabcombi(i).join_type || ','||
-- tabcombi(i).t_name1 || ',' ||
-- tabcombi(i).t_name2 || ',' ||
-- tabcombi(i).semianti_flg );
-- end loop;

for i in 1..tabcombi.count loop

wk1 := 1;
wk2 := tabcombi(i).table_no1;
wk3 := tabcombi(i).table_no2;

for j in 1..colnames(wk2).count loop
for k in 1..colnames(wk3).count loop
if colnames(wk2)(j).data_type = colnames(wk3)(k).data_type then

if colnames(wk2)(j).data_length = colnames(wk3)(k).data_length
and colnames(wk2)(j).data_precision = colnames(wk3)(k).data_precision
and colnames(wk2)(j).data_scale = colnames(wk3)(k).data_scale then
if colnames(wk2)(j).ind_head_flg = 1 or colnames(wk3)(k).ind_head_flg = 1 then
wk4 := 20;
else
wk4 := 15;
end if;
else
if colnames(wk2)(j).ind_head_flg = 1 or colnames(wk3)(k).ind_head_flg = 1 then
wk4 := 10;
else
wk4 := 5;
end if;
end if;

joincols(i)(wk1).table_name1 := colnames(wk2)(j).table_name;
joincols(i)(wk1).table_name2 := colnames(wk3)(k).table_name;
joincols(i)(wk1).column_name1 := colnames(wk2)(j).column_name;
joincols(i)(wk1).column_name2 := colnames(wk3)(k).column_name;
joincols(i)(wk1).pri := wk4;
joincols(i)(wk1).t_name1 := colnames(wk2)(j).t_name;
joincols(i)(wk1).t_name2 := colnames(wk3)(k).t_name;
joincols(i)(wk1).semianti_flg := tabcombi(i).semianti_flg;

wk1 := wk1+1;
end if;
end loop;
end loop;

-- 結合条件がない場合、優先度0でレコード作成
if wk1 = 1 then
joincols(i)(wk1).table_name1 := '';
joincols(i)(wk1).table_name2 := '';
joincols(i)(wk1).column_name1 := '';
joincols(i)(wk1).column_name2 := '';
joincols(i)(wk1).pri := 0;
joincols(i)(wk1).t_name1 := '';
joincols(i)(wk1).t_name2 := '';
joincols(i)(wk1).semianti_flg := 0;
end if;
end loop;

-- 結合条件配列のシャッフル
subp1(tabcombi.count,joincols,joincols2);

-- 結合条件配列の過剰分削除
-- テーブル組み合わせごとに条件数の上限=3
for i in 1..tabcombi.count loop
for j in 1..joincols2(i).count loop
if j > C_JOINCOND_NUM then
joincols2(i).delete(j);
end if;
end loop;
end loop;

-- dbms_output.put_line('---joincols2 after delete---' );
-- for i in 1..tabcombi.count loop
-- for j in 1..joincols2(i).count loop
-- dbms_output.put_line( joincols2(i)(j).table_name1 || ','||
-- joincols2(i)(j).table_name2 || ','||
-- joincols2(i)(j).column_name1 || ','||
-- joincols2(i)(j).column_name2 || ','||
-- joincols2(i)(j).pri || ','||
-- joincols2(i)(j).t_name1 || ','||
-- joincols2(i)(j).t_name2 || ','||
-- joincols2(i)(j).semianti_flg );
-- end loop;
-- end loop;

-- ⑤where条件配列の作成
-- インデックス有とインデックスなし

wk1 := 1;
wk2 := 1;
for i in 1..tabnames.count loop
for j in 1..colnames(i).count loop

if colnames(i)(j).ind_head_flg = 1 then
whereindcols(wk1).table_name := colnames(i)(j).table_name;
whereindcols(wk1).column_name := colnames(i)(j).column_name;
whereindcols(wk1).t_name := colnames(i)(j).t_name;
whereindcols(wk1).semianti_flg := colnames(i)(j).semianti_flg;

if colnames(i)(j).data_type = 'NUMBER' then
va1 := ' < 100 ';
elsif colnames(i)(j).data_type in ('VARCHAR2','CHAR') then
va1 := ' like ''%A%'' ';
else
va1 := ' IS NOT NULL ';
end if;

whereindcols(wk1).col3 := va1;

wk1 := wk1+1;
else
wherenoindcols(wk2).table_name := colnames(i)(j).table_name;
wherenoindcols(wk2).column_name := colnames(i)(j).column_name;
wherenoindcols(wk2).t_name := colnames(i)(j).t_name;
wherenoindcols(wk2).semianti_flg := colnames(i)(j).semianti_flg;

if colnames(i)(j).data_type = 'NUMBER' then
va1 := ' < 100 ';
elsif colnames(i)(j).data_type in ('VARCHAR2','CHAR') then
va1 := ' like ''%A%'' ';
else
va1 := ' IS NOT NULL ';
end if;

wherenoindcols(wk2).col3 := va1;

wk2 := wk2+1;
end if;

end loop;
end loop;

-- where条件配列のシャッフル
subp2(whereindcols,whereindcols2);
subp2(wherenoindcols,wherenoindcols2);

-- where条件配列の過剰分削除
-- 条件数の上限=3
for i in 1..whereindcols2.count loop
if i > C_WHEREIND_NUM then
whereindcols2.delete(i);
end if;
end loop;

for i in 1..wherenoindcols2.count loop
if i > C_WHERENOIND_NUM then
wherenoindcols2.delete(i);
end if;
end loop;

-- dbms_output.put_line('---whereindcols2 after delete---' );
-- for i in 1..whereindcols2.count loop
-- dbms_output.put_line( whereindcols2(i).table_name || ','||
-- whereindcols2(i).column_name || ','||
-- whereindcols2(i).col3 || ','||
-- whereindcols2(i).t_name || ','||
-- whereindcols2(i).semianti_flg );
-- end loop;
--
-- dbms_output.put_line('---wherenoindcols2 after delete---' );
-- for i in 1..wherenoindcols2.count loop
-- dbms_output.put_line( wherenoindcols2(i).table_name || ','||
-- wherenoindcols2(i).column_name || ','||
-- wherenoindcols2(i).col3 || ','||
-- wherenoindcols2(i).t_name || ','||
-- wherenoindcols2(i).semianti_flg );
-- end loop;

-- ⑥select句配列の作成
-- セミアンチ結合のテーブルのカラムは対象外

if C_UNION_FLG = 1 and io_vsqlcolname.count > 0 then -- unionSQLの2回目以降
-- group byの場合、カラム数-1
if flg1 = 1 then
wk3 := io_vsqlcolname.count - 1;
else
wk3 := io_vsqlcolname.count;
end if;
for i in 1..wk3 loop
wk1 := 1;
<<J_LOOP>>
for j in 1..tabnames.count loop
if tabnames(j).semianti_flg = 0 then
for k in 1..colnames(j).count loop

if io_vsqlcolname(i).data_type = colnames(j)(k).data_type then

-- 既に使用済みカラムかチェック
wk2 := 1;
for m in 1..selectcols2.count loop
if colnames(j)(k).table_name = selectcols2(m).table_name
and colnames(j)(k).column_name = selectcols2(m).column_name then
wk2 := wk2+1;
exit;
end if;
end loop;

if wk2 = 1 then
selectcols2(i).table_name := colnames(j)(k).table_name;
selectcols2(i).column_name := colnames(j)(k).column_name;
selectcols2(i).col3 := 'col'||i;
selectcols2(i).t_name := colnames(j)(k).t_name;

selectcols2(i).data_type := colnames(j)(k).data_type;
selectcols2(i).data_length := colnames(j)(k).data_length;
selectcols2(i).data_precision := colnames(j)(k).data_precision;
selectcols2(i).data_scale := colnames(j)(k).data_scale;

wk1 := wk1+1;
exit J_LOOP when ( wk1 > 1 );
end if;
end if;
end loop;
end if;
end loop;

if wk1 = 1 then
-- 同じデータ型のカラムがない場合カラム名としてNULLをセット
selectcols2(i).table_name := '';
selectcols2(i).column_name := 'NULL';
selectcols2(i).col3 := 'col'||i;
selectcols2(i).t_name := '';

selectcols2(i).data_type := '';
selectcols2(i).data_length := 0;
selectcols2(i).data_precision := 0;
selectcols2(i).data_scale := 0;
end if;
end loop;

else

wk1 := 1;
for i in 1..tabnames.count loop
if tabnames(i).semianti_flg = 0 then
for j in 1..colnames(i).count loop
selectcols(wk1).table_name := colnames(i)(j).table_name;
selectcols(wk1).column_name := colnames(i)(j).column_name;
selectcols(wk1).t_name := colnames(i)(j).t_name;
selectcols(wk1).data_type := colnames(i)(j).data_type;
selectcols(wk1).data_length := colnames(i)(j).data_length;
selectcols(wk1).data_precision := colnames(i)(j).data_precision;
selectcols(wk1).data_scale := colnames(i)(j).data_scale;

wk1 := wk1+1;
end loop;
end if;
end loop;

-- select句配列のシャッフル
subp2(selectcols,selectcols2);

-- select句配列の過剰分削除＋カラム別名付与
-- select句カラム数上限=5
for i in 1..selectcols2.count loop

selectcols2(i).col3 := 'col'|| i;
if i > C_SELECT_NUM then
selectcols2.delete(i);
end if;
end loop;

end if;

-- select句の内容を親へ渡すためにout配列に格納
vsqlcolname.delete;
for i in 1..selectcols2.count loop
vsqlcolname.extend;
vsqlcolname(i) := grec( selectcols2(i).col3
,selectcols2(i).data_type
,selectcols2(i).data_length
,selectcols2(i).data_precision
,selectcols2(i).data_scale );
end loop;

-- dbms_output.put_line('---selectcols2 after delete---' );
-- for i in 1..selectcols2.count loop
-- dbms_output.put_line( selectcols2(i).table_name || ','||
-- selectcols2(i).column_name || ','||
-- selectcols2(i).col3 || ','||
-- selectcols2(i).t_name );
-- end loop;

-- ⑦orderby句配列の作成
-- orderby句カラム数上限=3
-- セミアンチ結合のテーブルのカラムは対象外

-- order by句出力なしの場合も作成する
-- group by句出力ありの場合は、select句配列をコピーし、シャッフルと過剰分削除を行う
if flg1 = 1 then
for i in 1..selectcols2.count loop
orderbycols(i).table_name := selectcols2(i).table_name ;
orderbycols(i).column_name := selectcols2(i).column_name ;
orderbycols(i).col3 := ' asc ';
orderbycols(i).t_name := selectcols2(i).t_name ;
-- ソート順設定。 desc確率20%
select dbms_random.value(0,1) into wk2 from dual;
if wk2 < C_DESC_PRB then
orderbycols(i).col3 := ' desc ';
end if;
end loop;
else
wk1 := 1;
for i in 1..tabnames.count loop
if tabnames(i).semianti_flg = 0 then
for j in 1..colnames(i).count loop

orderbycols(wk1).table_name := colnames(i)(j).table_name;
orderbycols(wk1).column_name := colnames(i)(j).column_name;
orderbycols(wk1).col3 := ' asc ';
orderbycols(wk1).t_name := colnames(i)(j).t_name;
-- ソート順設定。 desc確率20%
select dbms_random.value(0,1) into wk2 from dual;
if wk2 < C_DESC_PRB then
orderbycols(wk1).col3 := ' desc ';
end if;

wk1 := wk1+1;
end loop;
end if;
end loop;
end if;

-- orderby句配列のシャッフル
subp2(orderbycols,orderbycols2);

-- orderby句配列の過剰分削除
for i in 1..orderbycols2.count loop
if i > C_ORDERBY_NUM then
orderbycols2.delete(i);
end if;
end loop;

-- dbms_output.put_line('---orderbycols2 after delete---' );
-- for i in 1..orderbycols2.count loop
-- dbms_output.put_line( orderbycols2(i).table_name || ','||
-- orderbycols2(i).column_name || ','||
-- orderbycols2(i).col3 || ','||
-- orderbycols2(i).t_name );
-- end loop;

-- ⑧ SQL文出力
vsql := 'select ';

for i in 1..selectcols2.count loop
if selectcols2(i).column_name != 'NULL' then
va1 := selectcols2(i).t_name || '.' || selectcols2(i).column_name || ' ' || selectcols2(i).col3;
else
va1 := selectcols2(i).column_name || ' ' || selectcols2(i).col3;
end if;

if i > 1 then
va1 := ',' || va1;
end if;

vsql := vsql || va1;
end loop;

-- group by句ありの場合、count関数を出力
if flg1 = 1 then
wk1 := selectcols2.count;
wk1 := wk1+1;
va1 := 'col'||wk1;
if wk1 > 1 then
vsql := vsql || ' ,count(*) ' || va1;
else
vsql := vsql || ' count(*) ' || va1;
end if;
vsqlcolname.extend;
vsqlcolname(wk1) := grec( va1
, 'NUMBER'
, 22
, -1
, -1 );
end if;

vsql := vsql || ' from ';

if tabnames.count > 1 then
for i in 1..tabcombi.count loop
-- セミアンチ結合のテーブルは対象外
if tabcombi(i).semianti_flg = 0 then

if tabcombi(i).join_type = 'I' then
va1 := ' inner join ';
else
va1 := ' left outer join ';
end if;

if i = 1 then
va2 := tabcombi(i).table_name1 || ' ' || tabcombi(i).t_name1 || va1 || tabcombi(i).table_name2 || ' ' || tabcombi(i).t_name2;
else
va2 := va1 || tabcombi(i).table_name2 || ' ' || tabcombi(i).t_name2;
end if;

vsql := vsql || va2;

-- 結合条件セット
wk1 := 0;
for j in 1..joincols2(i).count loop
if joincols2(i)(j).pri != 0 then -- ダミーレコードは除く
wk1 := wk1+1;

if wk1 = 1 then
va3 := ' on ';
else
va3 := ' and ';
end if;

va4 := va3 || joincols2(i)(j).t_name1 || '.' || joincols2(i)(j).column_name1 || ' = ' ||
joincols2(i)(j).t_name2 || '.' || joincols2(i)(j).column_name2;
vsql := vsql || va4;
end if;
end loop;
-- 結合条件がない場合
if wk1 = 0 then
va4 := ' on 1 = 1 ';
vsql := vsql || va4;
end if;

end if;
end loop;
else
-- テーブル1個の場合
vsql := vsql || tabnames(1).table_name || ' ' || tabnames(1).t_name ;
end if;

vsql := vsql || ' where 1 = 1 ';

-- セミアンチ結合対応
for i in 1..tabcombi.count loop
if tabcombi(i).semianti_flg != 0 then

if tabcombi(i).semianti_flg = 1 then
va1 := ' and exists ( select 1 from ';
else
va1 := ' and not exists ( select 1 from ';
end if;

va1 := va1 || tabcombi(i).table_name2 || ' ' || tabcombi(i).t_name2;
va1 := va1 || ' where 1 = 1 ';

-- 結合条件セット
for j in 1..joincols2(i).count loop
if joincols2(i)(j).pri != 0 then -- ダミーレコードは除く
va1 := va1 || ' and ' || joincols2(i)(j).t_name2 || '.' || joincols2(i)(j).column_name2 || ' = ' ||
joincols2(i)(j).t_name1 || '.' || joincols2(i)(j).column_name1;
end if;
end loop;
vsql := vsql || va1 ;

-- where条件セット
for i in 1..whereindcols2.count loop
-- セミアンチ結合のテーブルのカラムのみ
if whereindcols2(i).semianti_flg != 0 then
va1 := ' and ' || whereindcols2(i).t_name || '.' || whereindcols2(i).column_name || whereindcols2(i).col3;
vsql := vsql || va1;
end if;
end loop;
for i in 1..wherenoindcols2.count loop
-- セミアンチ結合のテーブルのカラムのみ
if wherenoindcols2(i).semianti_flg != 0 then
va1 := ' and ' || wherenoindcols2(i).t_name || '.' || wherenoindcols2(i).column_name || wherenoindcols2(i).col3;
vsql := vsql || va1;
end if;
end loop;
vsql := vsql || ' ) ';

end if;
end loop;

for i in 1..whereindcols2.count loop
-- セミアンチ結合のテーブルのカラムは対象外
if whereindcols2(i).semianti_flg = 0 then
va1 := ' and ' || whereindcols2(i).t_name || '.' || whereindcols2(i).column_name || whereindcols2(i).col3;
vsql := vsql || va1;
end if;
end loop;
for i in 1..wherenoindcols2.count loop
-- セミアンチ結合のテーブルのカラムは対象外
if wherenoindcols2(i).semianti_flg = 0 then
va1 := ' and ' || wherenoindcols2(i).t_name || '.' || wherenoindcols2(i).column_name || wherenoindcols2(i).col3;
vsql := vsql || va1;
end if;
end loop;

-- group by句ありの場合
if flg1 = 1 then
for i in 1..selectcols2.count loop
if i = 1 then
va1 := ' group by ';
else
va1 := ',' ;
end if;
if selectcols2(i).column_name != 'NULL' then
va2 := va1 || selectcols2(i).t_name || '.' || selectcols2(i).column_name ;
else
va2 := va1 || selectcols2(i).column_name ;
end if;
vsql := vsql || va2;
end loop;
end if;

-- order by句ありの場合
if flg2 = 1 then
for i in 1..orderbycols2.count loop
if i = 1 then
va1 := ' order by ';
else
va1 := ',' ;
end if;
va2 := va1 || orderbycols2(i).t_name || '.' || orderbycols2(i).column_name || orderbycols2(i).col3;
vsql := vsql || va2;
end loop;
end if;

o_vsql := vsql;
o_vsqlcolname := vsqlcolname;

if C_UNION_FLG = 1 and io_vsqlcolname.count = 0 then
io_vsqlcolname := vsqlcolname;
end if;

-- vsql := vsql || ' ; ';
-- dbms_output.put_line( vsql );

end;
/
sho error;

create or replace procedure main
as
vsql varchar2(4000); -- out変数再帰処理時とunionSQLで使用する
vsqlcolname gar := gar(); -- out変数再帰処理時に使用する
vsqlcolname2 gar := gar(); -- in out変数 unionSQLのカラムデータ型連携用

loop_num number := 1; -- proc_makesql実行回数

p_tab_num number;
p_outer_prb number;
p_joincond_num number;
p_whereind_num number;
p_wherenoind_num number;

p_select_num number;
p_orderby_num number;
p_desc_prb number;
p_joinnoind_prb number;
p_rami_prb number;

p_groupby_prb number;
p_orderby_prb number;
p_semianti_prb number;
p_semi_rto number;
p_level_max number;

p_recur_prb number;
p_union_prb number;
p_union_rto number;

p_tab_num_l number := 1;
p_tab_num_u number := 5;

p_outer_prb_l number := 0;
p_outer_prb_u number := 1;

p_joincond_num_l number := 1;
p_joincond_num_u number := 5;

p_whereind_num_l number := 1;
p_whereind_num_u number := 5;

p_wherenoind_num_l number := 1;
p_wherenoind_num_u number := 5;

p_select_num_l number := 1;
p_select_num_u number := 10;

p_orderby_num_l number := 1;
p_orderby_num_u number := 5;

p_desc_prb_l number := 0;
p_desc_prb_u number := 1;

p_joinnoind_prb_l number := 0;
p_joinnoind_prb_u number := 1;

p_rami_prb_l number := 0;
p_rami_prb_u number := 1;

p_groupby_prb_l number := 0;
p_groupby_prb_u number := 1;

p_orderby_prb_l number := 0;
p_orderby_prb_u number := 1;

p_semianti_prb_l number := 0;
p_semianti_prb_u number := 1;

p_semi_rto_l number := 0;
p_semi_rto_u number := 1;

p_level_max_l number := 0;
p_level_max_u number := 2;

p_recur_prb_l number := 0;
p_recur_prb_u number := 1;

p_union_prb_l number := 0;
p_union_prb_u number := 1;

p_union_rto_l number := 0;
p_union_rto_u number := 1;

begin

for i in 1..loop_num loop
select round( dbms_random.value(p_tab_num_l,p_tab_num_u) ) into p_tab_num from dual;
select dbms_random.value(p_outer_prb_l,p_outer_prb_u) into p_outer_prb from dual;
select round( dbms_random.value(p_joincond_num_l,p_joincond_num_u) ) into p_joincond_num from dual;
select round( dbms_random.value(p_whereind_num_l,p_whereind_num_u) ) into p_whereind_num from dual;
select round( dbms_random.value(p_wherenoind_num_l,p_wherenoind_num_u) ) into p_wherenoind_num from dual;

select round( dbms_random.value(p_select_num_l,p_select_num_u) ) into p_select_num from dual;
select round( dbms_random.value(p_orderby_num_l,p_orderby_num_u) ) into p_orderby_num from dual;
select dbms_random.value(p_desc_prb_l,p_desc_prb_u) into p_desc_prb from dual;
select dbms_random.value(p_joinnoind_prb_l,p_joinnoind_prb_u) into p_joinnoind_prb from dual;
select dbms_random.value(p_rami_prb_l,p_rami_prb_u) into p_rami_prb from dual;

select dbms_random.value(p_groupby_prb_l,p_groupby_prb_u) into p_groupby_prb from dual;
select dbms_random.value(p_orderby_prb_l,p_orderby_prb_u) into p_orderby_prb from dual;
select dbms_random.value(p_semianti_prb_l,p_semianti_prb_u) into p_semianti_prb from dual;
select dbms_random.value(p_semi_rto_l,p_semi_rto_u) into p_semi_rto from dual;
select round( dbms_random.value(p_level_max_l,p_level_max_u) ) into p_level_max from dual;

select dbms_random.value(p_recur_prb_l,p_recur_prb_u) into p_recur_prb from dual;
select dbms_random.value(p_union_prb_l,p_union_prb_u) into p_union_prb from dual;
select dbms_random.value(p_union_rto_l,p_union_rto_u) into p_union_rto from dual;

proc_makesql(
p_tab_num
,p_outer_prb
,p_joincond_num
,p_whereind_num
,p_wherenoind_num
,p_select_num
,p_orderby_num
,p_desc_prb
,p_joinnoind_prb
,p_rami_prb
,p_groupby_prb
,p_orderby_prb
,p_semianti_prb
,p_semi_rto
,0 -- p_level
,p_level_max
,vsql
,vsqlcolname
,p_recur_prb
,p_union_prb
,p_union_rto
,0 -- p_union_flg
,vsqlcolname2
);

vsql := vsql || ' ; ';
dbms_output.put_line( vsql );
end loop;

end;
/
show error;

exec main;

PostgreSQL

(14)

drop type if exists grec CASCADE;
create type grec as(
column_name varchar(30)
,data_type varchar(30)
,character_maximum_length numeric
,numeric_precision numeric
,numeric_scale numeric
);

drop type if exists rec10 CASCADE;
create type rec10 as(
table_name varchar(4000)
,column_count numeric
,t_name varchar(30)
,semianti_flg numeric
);

drop type if exists rec20 CASCADE;
create type rec20 as(
table_name varchar(4000)
,column_name varchar(30)
,data_type varchar(30)
,character_maximum_length numeric
,numeric_precision numeric
,numeric_scale numeric
,ind_head_flg numeric
,t_name varchar(30)
,semianti_flg numeric
);

drop type if exists ar20 CASCADE;
create type ar20 as(
r20 rec20
);

drop type if exists rec30 CASCADE;
create type rec30 as(
table_no1 numeric
,table_no2 numeric
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,join_type varchar(30) -- innner join or left join
,cond_count numeric
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

drop type if exists rec40 CASCADE;
create type rec40 as(
table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

drop type if exists ar40 CASCADE;
create type ar40 as(
r40 rec40
);

drop type if exists rec50 CASCADE;
create type rec50 as(
table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- where条件 or カラム別名 or ソート順
,t_name varchar(30)
,semianti_flg numeric
,data_type varchar(30) -- select句配列で使用。再帰SQL用
,character_maximum_length numeric -- select句配列で使用。再帰SQL用
,numeric_precision numeric -- select句配列で使用。再帰SQL用
,numeric_scale numeric -- select句配列で使用。再帰SQL用
);

drop procedure proc_makesql;

create or replace procedure proc_makesql(
p_tab_num in numeric
,p_outer_prb in numeric
,p_joincond_num in numeric
,p_whereind_num in numeric
,p_wherenoind_num in numeric
,p_select_num in numeric
,p_orderby_num in numeric
,p_desc_prb in numeric
,p_joinnoind_prb in numeric
,p_rami_prb in numeric
,p_groupby_prb in numeric
,p_orderby_prb in numeric
,p_semianti_prb in numeric
,p_semi_rto in numeric
,p_level in numeric
,p_level_max in numeric
,o_vsql out varchar
,o_vsqlcolname out grec
,p_recur_prb in numeric
,p_union_prb in numeric
,p_union_rto in numeric
,p_union_flg in numeric
,io_vsqlcolname in out grec
)
language plpgsql
as $$
declare

-- パラメータ
C_TAB_NUM numeric := p_tab_num; -- テーブル数
C_OUTER_PRB numeric := p_outer_prb; -- 外部結合確率
C_JOINCOND_NUM numeric := p_joincond_num; -- 結合条件数(結合毎)
C_WHEREIND_NUM numeric := p_whereind_num; -- where条件数(インデックスあり)
C_WHERENOIND_NUM numeric := p_wherenoind_num; -- where条件数(インデックスなし)
C_SELECT_NUM numeric := p_select_num; -- select句カラム数
C_ORDERBY_NUM numeric := p_orderby_num; -- order by句カラム数
C_DESC_PRB numeric := p_desc_prb; -- desc確率
C_JOINNOIND_PRB numeric := p_joinnoind_prb; -- インデックスなし結合確率
C_RAMI_PRB numeric := p_rami_prb; -- 結合分岐確率
C_GROUPBY_PRB numeric := p_groupby_prb; -- groupby発生確率
C_ORDERBY_PRB numeric := p_orderby_prb; -- orderby発生確率
C_SEMIANTI_PRB numeric := p_semianti_prb; -- セミアンチ結合発生確率(最大1個)
C_SEMI_RTO numeric := p_semi_rto; -- セミ結合発生比率

C_LEVEL numeric := p_level; -- 再帰階層レベル(0: 最上位の親, 1:子, 2:孫)
C_LEVEL_MAX numeric := p_level_max; -- 再帰階層レベルの最大値
C_RECUR_PRB numeric := p_recur_prb; -- 再帰SQL発生確率

C_UNION_PRB numeric := p_union_prb; -- union/union all SQL発生確率
C_UNION_RTO numeric := p_union_rto; -- union発生比率
C_UNION_FLG numeric := p_union_flg; -- unionSQL 実行フラグ (0: 通常のSQL, 1: unionSQL)

-- テーブル名配列
tabnames rec10;

-- カラム配列(2次元配列)

colname ar20;
colnames ar20;

-- 結合テーブル組み合わせ配列
tabcombi rec30;

-- 結合条件配列(2次元配列)

joincol ar40;
joincols ar40;
joincols2 ar40; -- after shuffle
joincols3 ar40; -- after remove

-- where条件配列(indexあり)、wherex条件配列(indexなし)、select句配列、orderby句配列
whereindcols rec50;
whereindcols2 rec50;
whereindcols3 rec50;

wherenoindcols rec50;
wherenoindcols2 rec50;
wherenoindcols3 rec50;

selectcols rec50;
selectcols2 rec50;
selectcols3 rec50;

orderbycols rec50;
orderbycols2 rec50;
orderbycols3 rec50;

-- ワーク変数
r1 record;
r2 record;
r3 record;

wk1 numeric;
wk2 numeric;
wk3 numeric;
wk4 numeric;

va1 varchar(4000);
va2 varchar(4000);
va3 varchar(4000);
va4 varchar(4000);
va5 varchar(4000);

-- フラグ
flg1 numeric :=0; -- 0: group byなし、 1: group byあり
flg2 numeric :=0; -- 0: order byなし、 1: order byあり
flg3 numeric :=0; -- 0: セミアンチなし、 1: セミあり、 2: アンチあり
flg4 numeric :=0; -- 0: 再帰なし、 1: 再帰あり

-- SQL文
vsql varchar(4000);
vsql_upper varchar(4000);

-- 作成SQLの文のカラム情報(OUT用)
vsqlcolname grec;

begin

-- unionSQLの判定
select random() into strict wk1;
if wk1 < C_UNION_PRB and C_LEVEL <= C_LEVEL_MAX - 1 then

-- 上段SQL実行
call proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,0 -- C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,vsqlcolname
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,1 -- C_UNION_FLG
,io_vsqlcolname
);

vsql_upper := vsql;

-- 下段SQL実行
call proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,0 -- C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,vsqlcolname
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,1 -- C_UNION_FLG
,io_vsqlcolname
);

select random() into strict wk1;
if wk1 < C_UNION_RTO then
va1 := ' union ';
else
va1 := ' union all ';
end if;

-- order by句処理
select random() into strict wk1;
if wk1 < C_ORDERBY_PRB then
wk2 := array_upper(vsqlcolname, 1);
wk3 := 1;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if wk3 = 1 then
va2 := ' order by ' || r1.g;
else
va2 := va2|| ',' || r1.g;
end if;
wk3 := wk3+1;
end loop;
else
va2 := '';
end if;

o_vsql := ' ( ' || vsql_upper || ' ) ' || va1 || ' ( ' || vsql || ' ) ' || va2;
o_vsqlcolname := vsqlcolname;
-- 後続の処理なし
return;

end if;

-- 再帰有無の判定
select random() into strict wk1;
if wk1 < C_RECUR_PRB and C_LEVEL <= C_LEVEL_MAX - 1 then
flg4 := 1;
end if;

if flg4 = 1 then

call proc_makesql(
C_TAB_NUM
,C_OUTER_PRB
,C_JOINCOND_NUM
,C_WHEREIND_NUM
,C_WHERENOIND_NUM
,C_SELECT_NUM
,C_ORDERBY_NUM
,C_DESC_PRB
,C_JOINNOIND_PRB
,C_RAMI_PRB
,C_GROUPBY_PRB
,C_ORDERBY_PRB
,C_SEMIANTI_PRB
,C_SEMI_RTO
,C_LEVEL+1
,C_LEVEL_MAX
,vsql
,vsqlcolname
,C_RECUR_PRB
,C_UNION_PRB
,C_UNION_RTO
,C_UNION_FLG
,io_vsqlcolname
);

end if;

-- group by句とorder by句有無の判定
select random() into strict wk1;
if wk1 < C_GROUPBY_PRB then
if C_UNION_FLG = 1 and array_upper(io_vsqlcolname, 1) > 0 then
if io_vsqlcolname[array_upper(io_vsqlcolname, 1)].data_type = 'numeric' then
-- unionSQLの2回目以降は最後のカラムがNUMBER型の場合のみgroup byを実施する
flg1 := 1;
end if;
else
flg1 := 1;
end if;
end if;

select random() into strict wk1;
if wk1 < C_ORDERBY_PRB then
flg2 := 1;
end if;
-- セミアンチ有無の判定
select random() into strict wk1;
if wk1 < C_SEMIANTI_PRB then
select random() into strict wk2;
if wk2 < C_SEMI_RTO then
flg3 := 1;
else
flg3 := 2;
end if;
end if;

-- ①テーブル名配列にテーブルを格納
-- テーブル数=3
wk1 := 1;
for r1 in ( select tablename from pg_tables where schemaname = 'public' order by random() limit C_TAB_NUM ) loop
tabnames[wk1].table_name := r1.tablename;
tabnames[wk1].t_name := 'T' || wk1 || '_' || C_LEVEL;
tabnames[wk1].semianti_flg := 0;
wk1 := wk1+1;
end loop;

-- raise notice '---tabnames---';
-- for i in 1..array_upper(tabnames, 1) loop
-- raise notice 'tabnames[%]: %',i,tabnames[i].table_name;
-- raise notice 'tabnames[%]: %',i,tabnames[i].t_name;
-- end loop;

-- ②テーブルのカラム名、データ型をカラム配列(2次元)に格納
for c1 in 1..array_upper(tabnames, 1) loop
wk1 := 1;
colname := null;

for r1 in ( select table_name
,column_name
,data_type
,coalesce(character_maximum_length,-1) character_maximum_length
,coalesce(numeric_precision,-1) numeric_precision
,coalesce(numeric_scale,-1) numeric_scale
from information_schema.columns
where table_catalog = 'test'
and table_schema = 'public'
and table_name = tabnames[c1].table_name ) loop

colname.r20[wk1].table_name := r1.table_name;
colname.r20[wk1].column_name := r1.column_name;
colname.r20[wk1].data_type := r1.data_type;
colname.r20[wk1].character_maximum_length := r1.character_maximum_length;
colname.r20[wk1].numeric_precision := r1.numeric_precision;
colname.r20[wk1].numeric_scale := r1.numeric_scale;
colname.r20[wk1].t_name := tabnames[c1].t_name;
colname.r20[wk1].semianti_flg := 0;

select count(*) into strict wk2
from pg_indexes
where schemaname = 'public'
and tablename = tabnames[c1].table_name
and ( indexdef like '%('||r1.column_name||',%' or indexdef like '%('||r1.column_name||')%' )
;
if wk2 > 0 then
colname.r20[wk1].ind_head_flg := 1;
else
colname.r20[wk1].ind_head_flg := 0;
end if;
tabnames[c1].column_count := wk1;
wk1 := wk1+1;
end loop;
colnames[c1] := colname;
end loop;

-- 再帰ありの場合、子から受け取ったカラム情報をテーブル配列とカラム配列に追加
if flg4 = 1 then
wk1 := array_upper(tabnames, 1) +1;
tabnames[wk1].table_name := ' ( '||vsql||' ) ';
tabnames[wk1].t_name := 'T' || wk1 || '_' || C_LEVEL;
tabnames[wk1].semianti_flg := 0;

colname := null;
wk2 := 1;
for i in 1..array_upper(vsqlcolname, 1) loop
colname.r20[i].table_name := tabnames[wk1].table_name ;
colname.r20[i].column_name := vsqlcolname[i].column_name;
colname.r20[i].data_type := vsqlcolname[i].data_type;
colname.r20[i].character_maximum_length := vsqlcolname[i].character_maximum_length;
colname.r20[i].numeric_precision := vsqlcolname[i].numeric_precision;
colname.r20[i].numeric_scale := vsqlcolname[i].numeric_scale;
colname.r20[i].ind_head_flg := 0;
colname.r20[i].t_name := tabnames[wk1].t_name ;
colname.r20[i].semianti_flg := 0;

tabnames[wk1].column_count := wk2;
wk2 := wk2+1;
end loop;
colnames[wk1] := colname;
end if;

-- raise notice '---colnames---';
-- for i in 1..array_length(tabnames, 1) loop
-- for j in 1..tabnames[i].column_count loop
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].table_name;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].column_name;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].data_type;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].character_maximum_length;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].numeric_precision;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].numeric_scale;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].ind_head_flg;
-- raise notice 'colnames[%][%] = %', i,j, colnames[i].r20[j].t_name;
-- end loop;
-- end loop;

-- ③結合テーブル組み合わせ配列の作成

wk1 := 1;
wk2 := array_upper(tabnames, 1);

if wk2 > 1 then

for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop

if wk1 = 1 then
tabcombi[wk1].table_no1 := r1.g;
elsif wk1 < wk2 then
tabcombi[wk1-1].table_no2 := r1.g;
tabcombi[wk1].table_no1 := r1.g;
else
tabcombi[wk1-1].table_no2 := r1.g;
end if;

wk1 := wk1+1;
end loop;

-- 結合分岐設定
for i in 1..array_upper(tabcombi, 1) loop
select random() into strict wk1;
if i > 1 and wk1 < C_RAMI_PRB then
tabcombi[i].table_no1 := wk2;
end if;
wk2 := tabcombi[i].table_no1;
end loop;

-- 結合タイプ、テーブル名のセット
for i in 1..array_upper(tabcombi, 1) loop

tabcombi[i].table_name1 := tabnames[tabcombi[i].table_no1].table_name;
tabcombi[i].table_name2 := tabnames[tabcombi[i].table_no2].table_name;
tabcombi[i].t_name1 := tabnames[tabcombi[i].table_no1].t_name;
tabcombi[i].t_name2 := tabnames[tabcombi[i].table_no2].t_name;
tabcombi[i].semianti_flg := 0;

tabcombi[i].join_type := 'I';
-- 結合タイプ設定。外部結合確率20%
select random() into strict wk1;
if wk1 < C_OUTER_PRB then
tabcombi[i].join_type := 'O';
end if;

-- セミアンチありの場合、最後の1件にフラグを立てる
-- さかのぼり、テーブル名配列とカラム配列もフラグを立てる
if i > 1 and i = array_upper(tabcombi, 1) and flg3 != 0 then
tabcombi[i].semianti_flg := flg3;
for j in 1..array_upper(tabnames, 1) loop
if tabnames[j].table_name = tabcombi[i].table_name2 then
tabnames[j].semianti_flg := flg3;
for k in 1..tabnames[j].column_count loop
colnames[j].r20[k].semianti_flg := flg3;
end loop;
end if;
end loop;
end if;

end loop;

-- raise notice '---tabcombi---';
-- for i in 1..array_upper(tabcombi, 1) loop
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].table_no1;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].table_no2;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].table_name1;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].table_name2;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].join_type;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].t_name1;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].t_name2;
-- raise notice 'tabcombi[%] = %', i, tabcombi[i].semianti_flg;
-- end loop;

end if;

if array_upper(tabcombi, 1) > 0 then
for i in 1..array_upper(tabcombi, 1) loop

wk1 := 1;
wk2 := tabcombi[i].table_no1;
wk3 := tabcombi[i].table_no2;
joincol := null;

for j in 1..tabnames[wk2].column_count loop
for k in 1..tabnames[wk3].column_count loop

if colnames[wk2].r20[j].data_type = colnames[wk3].r20[k].data_type then


if colnames[wk2].r20[j].character_maximum_length = colnames[wk3].r20[k].character_maximum_length
and colnames[wk2].r20[j].numeric_precision = colnames[wk3].r20[k].numeric_precision
and colnames[wk2].r20[j].numeric_scale = colnames[wk3].r20[k].numeric_scale then

if colnames[wk2].r20[j].ind_head_flg = 1 or colnames[wk3].r20[k].ind_head_flg = 1 then
wk4 := 20;
else
wk4 := 15;
end if;

else

if colnames[wk2].r20[j].ind_head_flg = 1 or colnames[wk3].r20[k].ind_head_flg = 1 then
wk4 := 10;
else
wk4 := 5;
end if;

end if;

joincol.r40[wk1].table_name1 := colnames[wk2].r20[j].table_name;
joincol.r40[wk1].table_name2 := colnames[wk3].r20[k].table_name;
joincol.r40[wk1].column_name1 := colnames[wk2].r20[j].column_name;
joincol.r40[wk1].column_name2 := colnames[wk3].r20[k].column_name;
joincol.r40[wk1].pri := wk4;
joincol.r40[wk1].t_name1 := colnames[wk2].r20[j].t_name;
joincol.r40[wk1].t_name2 := colnames[wk3].r20[k].t_name;
joincol.r40[wk1].semianti_flg := tabcombi[i].semianti_flg;

tabcombi[i].cond_count := wk1;
joincols[i] := joincol;
wk1 := wk1+1;
end if;

end loop;
end loop;
-- 結合条件がない場合、優先度0でレコード作成
if wk1 = 1 then
joincol.r40[wk1].table_name1 := '';
joincol.r40[wk1].table_name2 := '';
joincol.r40[wk1].column_name1 := '';
joincol.r40[wk1].column_name2 := '';
joincol.r40[wk1].pri := 0;
joincol.r40[wk1].t_name1 := '';
joincol.r40[wk1].t_name2 := '';
joincol.r40[wk1].semianti_flg := 0;
tabcombi[i].cond_count := wk1;
joincols[i] := joincol;
end if;

end loop;

-- raise notice '---joincols---';
-- for i in 1..array_length(tabcombi, 1) loop
-- for j in 1..tabcombi[i].cond_count loop
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].table_name1;
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].table_name2;
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].column_name1;
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].column_name2;
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].pri;
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].t_name1;
-- raise notice 'joincols[%][%] = %', i,j, joincols[i].r40[j].t_name2;
-- end loop;
-- end loop;

-- 結合条件配列のシャッフル
for i in 1..array_length(tabcombi, 1) loop
wk2 := tabcombi[i].cond_count;

-- 10%の確率でインデックスなしの結合条件を優先とする

select random() into strict wk3;
if wk3 < C_JOINNOIND_PRB then

wk1 := 1;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 15 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 20 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 5 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 10 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 0 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;

else

wk1 := 1;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 20 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 15 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 10 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 5 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
if joincols[i].r40[r1.g].pri = 0 then
joincols2[i].r40[wk1] := joincols[i].r40[r1.g];
wk1 := wk1+1;
end if;
end loop;

end if;
end loop;

-- raise notice '---joincols2---';
-- for i in 1..array_length(tabcombi, 1) loop
-- for j in 1..tabcombi[i].cond_count loop
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].table_name1;
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].table_name2;
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].column_name1;
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].column_name2;
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].pri;
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].t_name1;
-- raise notice 'joincols2[%][%] = %', i,j, joincols2[i].r40[j].t_name2;
-- end loop;
-- end loop;

-- 結合条件配列の過剰分削除
-- テーブル組み合わせごとに条件数の上限=3
for i in 1..array_length(tabcombi, 1) loop
for j in 1..tabcombi[i].cond_count loop
if j <= C_JOINCOND_NUM then
joincols3[i].r40[j] := joincols2[i].r40[j];
tabcombi[i].cond_count := j; -- 結合毎の条件数を再設定
end if;
end loop;
end loop;

-- raise notice '---joincols3---';
-- for i in 1..array_length(tabcombi, 1) loop
-- for j in 1..tabcombi[i].cond_count loop
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].table_name1;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].table_name2;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].column_name1;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].column_name2;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].pri;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].t_name1;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].t_name2;
-- raise notice 'joincols3[%][%] = %', i,j, joincols3[i].r40[j].semianti_flg;
-- end loop;
-- end loop;

end if;

-- ⑤where条件配列の作成
-- インデックス有とインデックスなし

wk1 := 1;
wk2 := 1;
for i in 1..array_length(tabnames, 1) loop
for j in 1..tabnames[i].column_count loop

if colnames[i].r20[j].ind_head_flg = 1 then
whereindcols[wk1].table_name := colnames[i].r20[j].table_name;
whereindcols[wk1].column_name := colnames[i].r20[j].column_name;
whereindcols[wk1].t_name := colnames[i].r20[j].t_name;
whereindcols[wk1].semianti_flg := colnames[i].r20[j].semianti_flg;

if colnames[i].r20[j].data_type in ('integer','numeric') then
va1 := ' < 100 ';
elsif colnames[i].r20[j].data_type in ('character varying','character') then
va1 := ' like ''%A%'' ';
else
va1 := ' IS NOT NULL ';
end if;

whereindcols[wk1].col3 := va1;

wk1 := wk1+1;
else
wherenoindcols[wk2].table_name := colnames[i].r20[j].table_name;
wherenoindcols[wk2].column_name := colnames[i].r20[j].column_name;
wherenoindcols[wk2].t_name := colnames[i].r20[j].t_name;
wherenoindcols[wk2].semianti_flg := colnames[i].r20[j].semianti_flg;

-- where条件配列のシャッフル

wk1 := 1;
wk2 := array_length(whereindcols, 1);
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
whereindcols2[wk1] := whereindcols[r1.g];
wk1 := wk1+1;
end loop;

wk1 := 1;
wk2 := array_length(wherenoindcols, 1);
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
wherenoindcols2[wk1] := wherenoindcols[r1.g];
wk1 := wk1+1;
end loop;

-- where条件配列の過剰分削除
-- 条件数の上限=3
if array_length(whereindcols2, 1) > 0 then
for i in 1..array_length(whereindcols2, 1) loop
if i <= C_WHEREIND_NUM then
whereindcols3[i] := whereindcols2[i];
end if;
end loop;
end if;

if array_length(wherenoindcols2, 1) > 0 then
for i in 1..array_length(wherenoindcols2, 1) loop
if i <= C_WHERENOIND_NUM then
wherenoindcols3[i] := wherenoindcols2[i];
end if;
end loop;
end if;

-- raise notice '---whereindcols3---';
-- if array_upper(whereindcols3, 1) > 0 then
-- for i in 1..array_upper(whereindcols3, 1) loop
-- raise notice 'whereindcols3[%] = %', i, whereindcols3[i].table_name;
-- raise notice 'whereindcols3[%] = %', i, whereindcols3[i].column_name;
-- raise notice 'whereindcols3[%] = %', i, whereindcols3[i].col3;
-- raise notice 'whereindcols3[%] = %', i, whereindcols3[i].t_name;
-- raise notice 'whereindcols3[%] = %', i, whereindcols3[i].semianti_flg;
-- end loop;
-- end if;
--
-- raise notice '---wherenoindcols3---';
-- if array_upper(wherenoindcols3, 1) > 0 then
-- for i in 1..array_upper(wherenoindcols3, 1) loop
-- raise notice 'wherenoindcols3[%] = %', i, wherenoindcols3[i].table_name;
-- raise notice 'wherenoindcols3[%] = %', i, wherenoindcols3[i].column_name;
-- raise notice 'wherenoindcols3[%] = %', i, wherenoindcols3[i].col3;
-- raise notice 'wherenoindcols3[%] = %', i, wherenoindcols3[i].t_name;
-- raise notice 'wherenoindcols3[%] = %', i, wherenoindcols3[i].semianti_flg;
-- end loop;
-- end if;

-- ⑥select句配列の作成
-- セミアンチ結合のテーブルのカラムは対象外

if C_UNION_FLG = 1 and array_length(io_vsqlcolname, 1) > 0 then -- unionSQLの2回目以降
-- group byの場合、カラム数-1
if flg1 = 1 then
wk3 := array_length(io_vsqlcolname, 1) - 1;
else
wk3 := array_length(io_vsqlcolname, 1);
end if;

for i in 1..wk3 loop
wk1 := 1;
<<J_LOOP>>
for j in 1..array_length(tabnames, 1) loop
if tabnames[j].semianti_flg = 0 then
for k in 1..tabnames[j].column_count loop

if io_vsqlcolname[i].data_type = colnames[j].r20[k].data_type then

-- 既に使用済みカラムかチェック
wk2 := 1;
if array_length(selectcols3, 1) > 0 then
for m in 1..array_length(selectcols3, 1) loop
if colnames[j].r20[k].table_name = selectcols3[m].table_name
and colnames[j].r20[k].column_name = selectcols3[m].column_name then
wk2 := wk2+1;
exit;
end if;
end loop;
end if;

if wk2 = 1 then
selectcols3[i].table_name := colnames[j].r20[k].table_name;
selectcols3[i].column_name := colnames[j].r20[k].column_name;
selectcols3[i].col3 := 'col'||i;
selectcols3[i].t_name := colnames[j].r20[k].t_name;

selectcols3[i].data_type := colnames[j].r20[k].data_type;
selectcols3[i].character_maximum_length := colnames[j].r20[k].character_maximum_length;
selectcols3[i].numeric_precision := colnames[j].r20[k].numeric_precision;
selectcols3[i].numeric_scale := colnames[j].r20[k].numeric_scale;

wk1 := wk1+1;
exit J_LOOP when ( wk1 > 1 );
end if;
end if;
end loop;
end if;
end loop;

if wk1 = 1 then
-- 同じデータ型のカラムがない場合カラム名としてNULLをセット
selectcols3[i].table_name := '';
selectcols3[i].column_name := 'NULL';
selectcols3[i].col3 := 'col'||i;
selectcols3[i].t_name := '';

selectcols3[i].data_type := '';
selectcols3[i].character_maximum_length := 0;
selectcols3[i].numeric_precision := 0;
selectcols3[i].numeric_scale := 0;
end if;
end loop;

else
wk1 := 1;
for i in 1..array_length(tabnames, 1) loop
if tabnames[i].semianti_flg = 0 then
for j in 1..tabnames[i].column_count loop
selectcols[wk1].table_name := colnames[i].r20[j].table_name;
selectcols[wk1].column_name := colnames[i].r20[j].column_name;
selectcols[wk1].t_name := colnames[i].r20[j].t_name;
selectcols[wk1].data_type := colnames[i].r20[j].data_type;
selectcols[wk1].character_maximum_length := colnames[i].r20[j].character_maximum_length;
selectcols[wk1].numeric_precision := colnames[i].r20[j].numeric_precision;
selectcols[wk1].semianti_flg := colnames[i].r20[j].semianti_flg;

wk1 := wk1+1;
end loop;
end if;
end loop;

-- select句配列のシャッフル

wk1 := 1;
wk2 := array_length(selectcols, 1);
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
selectcols2[wk1] := selectcols[r1.g];
wk1 := wk1+1;
end loop;

-- select句配列の過剰分削除＋カラム別名付与
-- select句カラム数上限=5

for i in 1..array_length(selectcols2, 1) loop
if i <= C_SELECT_NUM then
selectcols3[i] := selectcols2[i];
selectcols3[i].col3 := 'col'||i;
end if;
end loop;
end if;

-- select句の内容を親へ渡すためにout配列に格納
for i in 1..array_length(selectcols3, 1) loop
vsqlcolname[i].column_name := selectcols3[i].col3;
vsqlcolname[i].data_type := selectcols3[i].data_type;
vsqlcolname[i].character_maximum_length := selectcols3[i].character_maximum_length;
vsqlcolname[i].numeric_precision := selectcols3[i].numeric_precision;
vsqlcolname[i].numeric_scale := selectcols3[i].numeric_scale;

end loop;

-- raise notice '---selectcols3---';
-- for i in 1..array_upper(selectcols3, 1) loop
-- raise notice 'selectcols3[%] = %', i, selectcols3[i].table_name;
-- raise notice 'selectcols3[%] = %', i, selectcols3[i].column_name;
-- raise notice 'selectcols3[%] = %', i, selectcols3[i].col3;
-- raise notice 'selectcols3[%] = %', i, selectcols3[i].t_name;
-- raise notice 'selectcols3[%] = %', i, selectcols3[i].data_type;
-- end loop;

-- ⑦orderby句配列の作成
-- orderby句カラム数上限=3
-- セミアンチ結合のテーブルのカラムは対象外

-- order by句出力なしの場合も作成する
-- group by句出力ありの場合は、select句配列をコピーし、シャッフルと過剰分削除を行う
if flg1 = 1 then
for i in 1..array_upper(selectcols3, 1) loop
orderbycols[i].table_name := selectcols3[i].table_name ;
orderbycols[i].column_name := selectcols3[i].column_name ;
orderbycols[i].col3 := ' asc ';
orderbycols[i].t_name := selectcols3[i].t_name ;

-- ソート順設定。 desc確率20%
select random() into strict wk2;
if wk2 < C_DESC_PRB then
orderbycols[i].col3 := ' desc ';
end if;
end loop;
else
wk1 := 1;
for i in 1..array_length(tabnames, 1) loop
if tabnames[i].semianti_flg = 0 then
for j in 1..tabnames[i].column_count loop
orderbycols[wk1].table_name := colnames[i].r20[j].table_name;
orderbycols[wk1].column_name := colnames[i].r20[j].column_name;
orderbycols[wk1].col3 := ' asc ';
orderbycols[wk1].t_name := colnames[i].r20[j].t_name;

-- ソート順設定。 desc確率20%
select random() into strict wk2;
if wk2 < C_DESC_PRB then
orderbycols[wk1].col3 := ' desc ';
end if;
wk1 := wk1+1;
end loop;
end if;
end loop;
end if;

-- orderby句配列のシャッフル
wk1 := 1;
wk2 := array_length(orderbycols, 1);
for r1 in ( select g from generate_series(1,wk2) g order by random() ) loop
orderbycols2[wk1] := orderbycols[r1.g];
wk1 := wk1+1;
end loop;

-- orderby句配列の過剰分削除
for i in 1..array_length(orderbycols2, 1) loop
if i <= C_ORDERBY_NUM then
orderbycols3[i] := orderbycols2[i];
end if;
end loop;

-- raise notice '---orderbycols3---';
-- for i in 1..array_upper(orderbycols3, 1) loop
-- raise notice 'orderbycols3[%] = %', i, orderbycols3[i].table_name;
-- raise notice 'orderbycols3[%] = %', i, orderbycols3[i].column_name;
-- raise notice 'orderbycols3[%] = %', i, orderbycols3[i].col3;
-- raise notice 'orderbycols3[%] = %', i, orderbycols3[i].t_name;
-- end loop;

-- ⑧ SQL文出力
vsql := 'select ';

for i in 1..array_upper(selectcols3, 1) loop
if selectcols3[i].column_name != 'NULL' then
va1 := selectcols3[i].t_name || '.' || selectcols3[i].column_name || ' ' || selectcols3[i].col3;
else
va1 := selectcols3[i].column_name || ' ' || selectcols3[i].col3;
end if;
if i > 1 then
va1 := ',' || va1;
end if;
vsql := vsql || va1;
end loop;

-- group by句ありの場合、count関数を出力
if flg1 = 1 then
wk1 := array_upper(selectcols3, 1);
wk1 := wk1+1;
va1 := 'col'||wk1;
if wk1 > 1 then
vsql := vsql || ' ,count(*) ' || va1;
else
vsql := vsql || ' count(*) ' || va1;
end if;
vsqlcolname[wk1].column_name := va1;
vsqlcolname[wk1].data_type := 'numeric';
vsqlcolname[wk1].character_maximum_length := -1;
vsqlcolname[wk1].numeric_precision := -1;
vsqlcolname[wk1].numeric_scale := -1;

end if;

vsql := vsql || ' from ';

if array_length(tabcombi, 1) > 0 then
for i in 1..array_length(tabcombi, 1) loop
-- セミアンチ結合のテーブルは対象外
if tabcombi[i].semianti_flg = 0 then

if tabcombi[i].join_type = 'I' then
va1 := ' inner join ';
else
va1 := ' left outer join ';
end if;

if i = 1 then
va2 := tabcombi[i].table_name1 || ' ' || tabcombi[i].t_name1 || va1 || tabcombi[i].table_name2 || ' ' || tabcombi[i].t_name2;
else
va2 := va1 || tabcombi[i].table_name2 || ' ' || tabcombi[i].t_name2;
end if;
vsql := vsql || va2;
-- 結合条件セット
wk1 := 0;
for j in 1..tabcombi[i].cond_count loop
if joincols3[i].r40[j].pri != 0 then -- ダミーレコードは除く
wk1 := wk1+1;
if wk1 = 1 then
va3 := ' on ';
else
va3 := ' and ';
end if;

va4 := va3 || joincols3[i].r40[j].t_name1 || '.' || joincols3[i].r40[j].column_name1 || ' = ' ||
joincols3[i].r40[j].t_name2 || '.' || joincols3[i].r40[j].column_name2;
vsql := vsql || va4;
end if;
end loop;
-- 結合条件がない場合
if wk1 = 0 then
va4 := ' on 1 = 1 ';
vsql := vsql || va4;
end if;

end if;
end loop;
else
-- テーブル1個の場合
vsql := vsql || tabnames[1].table_name || ' ' || tabnames[1].t_name ;
end if;

vsql := vsql || ' where 1 = 1 ';

-- セミアンチ結合対応
if array_length(tabcombi, 1) > 0 then
for i in 1..array_length(tabcombi, 1) loop
if tabcombi[i].semianti_flg != 0 then

if tabcombi[i].semianti_flg = 1 then
va1 := ' and exists ( select 1 from ';
else
va1 := ' and not exists ( select 1 from ';
end if;

va1 := va1 || tabcombi[i].table_name2 || ' ' || tabcombi[i].t_name2;
va1 := va1 || ' where 1 = 1 ';

-- 結合条件セット
for j in 1..tabcombi[i].cond_count loop
if joincols3[i].r40[j].pri != 0 then -- ダミーレコードは除く
va1 := va1 || ' and ' || joincols3[i].r40[j].t_name2 || '.' || joincols3[i].r40[j].column_name2 || ' = ' ||
joincols3[i].r40[j].t_name1 || '.' || joincols3[i].r40[j].column_name1;
end if;
end loop;
vsql := vsql || va1 ;

-- where条件セット
if array_upper(whereindcols3, 1) > 0 then
for i in 1..array_upper(whereindcols3, 1) loop
-- セミアンチ結合のテーブルのカラムのみ
if whereindcols3[i].semianti_flg != 0 then
va1 := ' and ' || whereindcols3[i].t_name || '.' || whereindcols3[i].column_name || whereindcols3[i].col3;
vsql := vsql || va1;
end if;
end loop;
end if;
if array_upper(wherenoindcols3, 1) > 0 then
for i in 1..array_upper(wherenoindcols3, 1) loop
-- セミアンチ結合のテーブルのカラムのみ
if wherenoindcols3[i].semianti_flg != 0 then
va1 := ' and ' || wherenoindcols3[i].t_name || '.' || wherenoindcols3[i].column_name || wherenoindcols3[i].col3;
vsql := vsql || va1;
end if;
end loop;
end if;
vsql := vsql || ' ) ';

end if;
end loop;
end if;

if array_upper(whereindcols3, 1) > 0 then
for i in 1..array_upper(whereindcols3, 1) loop
-- セミアンチ結合のテーブルのカラムは対象外
if whereindcols3[i].semianti_flg = 0 then
va1 := ' and ' || whereindcols3[i].t_name || '.' || whereindcols3[i].column_name || whereindcols3[i].col3;
vsql := vsql || va1;
end if;
end loop;
end if;
if array_upper(wherenoindcols3, 1) > 0 then
for i in 1..array_upper(wherenoindcols3, 1) loop
-- セミアンチ結合のテーブルのカラムは対象外
if wherenoindcols3[i].semianti_flg = 0 then
va1 := ' and ' || wherenoindcols3[i].t_name || '.' || wherenoindcols3[i].column_name || wherenoindcols3[i].col3;
vsql := vsql || va1;
end if;
end loop;
end if;

-- group by句ありの場合
if flg1 = 1 then
for i in 1..array_upper(selectcols3, 1) loop
if i = 1 then
va1 := ' group by ';
else
va1 := ',' ;
end if;
if selectcols3[i].column_name != 'NULL' then
va2 := va1 || selectcols3[i].t_name || '.' || selectcols3[i].column_name ;
else
va2 := va1 || selectcols3[i].column_name ;
end if;
vsql := vsql || va2;
end loop;
end if;

-- order by句ありの場合
if flg2 = 1 then
for i in 1..array_upper(orderbycols3, 1) loop
if i = 1 then
va1 := ' order by ';
else
va1 := ',' ;
end if;
va2 := va1 || orderbycols3[i].t_name || '.' || orderbycols3[i].column_name || orderbycols3[i].col3;
vsql := vsql || va2;
end loop;
end if;

o_vsql := vsql;
o_vsqlcolname := vsqlcolname;

if C_UNION_FLG = 1 and array_upper(io_vsqlcolname, 1) is null then
io_vsqlcolname := vsqlcolname;



end if;

end;
$$;

create or replace procedure main()
language plpgsql
as $$
declare
vsql varchar(4000); -- out変数再帰処理時とunionSQLで使用する
vsqlcolname grec; -- out変数再帰処理時に使用する
vsqlcolname2 grec[]; -- in out変数 unionSQLのカラムデータ型連携用

loop_num numeric := 1; -- proc_makesql実行回数

p_tab_num numeric;
p_outer_prb numeric;
p_joincond_num numeric;
p_whereind_num numeric;
p_wherenoind_num numeric;

p_select_num numeric;
p_orderby_num numeric;
p_desc_prb numeric;
p_joinnoind_prb numeric;
p_rami_prb numeric;

p_groupby_prb numeric;
p_orderby_prb numeric;
p_semianti_prb numeric;
p_semi_rto numeric;
p_level_max numeric;

p_recur_prb numeric;
p_union_prb numeric;
p_union_rto numeric;

p_tab_num_l numeric := 1;
p_tab_num_u numeric := 5;

p_outer_prb_l numeric := 0;
p_outer_prb_u numeric := 1;

p_joincond_num_l numeric := 1;
p_joincond_num_u numeric := 5;

p_whereind_num_l numeric := 1;
p_whereind_num_u numeric := 5;

p_wherenoind_num_l numeric := 1;
p_wherenoind_num_u numeric := 5;

p_select_num_l numeric := 1;
p_select_num_u numeric := 10;

p_orderby_num_l numeric := 1;
p_orderby_num_u numeric := 5;

p_desc_prb_l numeric := 0;
p_desc_prb_u numeric := 1;

p_joinnoind_prb_l numeric := 0;
p_joinnoind_prb_u numeric := 1;

p_rami_prb_l numeric := 0;
p_rami_prb_u numeric := 1;

p_groupby_prb_l numeric := 0;
p_groupby_prb_u numeric := 1;

p_orderby_prb_l numeric := 0;
p_orderby_prb_u numeric := 1;

p_semianti_prb_l numeric := 0;
p_semianti_prb_u numeric := 1;

p_semi_rto_l numeric := 0;
p_semi_rto_u numeric := 1;

p_level_max_l numeric := 0;
p_level_max_u numeric := 2;

p_recur_prb_l numeric := 0;
p_recur_prb_u numeric := 1;

p_union_prb_l numeric := 0;
p_union_prb_u numeric := 1;

p_union_rto_l numeric := 0;
p_union_rto_u numeric := 1;

begin

for i in 1..loop_num loop
select round( p_tab_num_l +( p_tab_num_u - p_tab_num_l)*random() ) into strict p_tab_num ;
select p_outer_prb_l+( p_outer_prb_u - p_outer_prb_l)*random() into strict p_outer_prb ;
select round( p_joincond_num_l +( p_joincond_num_u - p_joincond_num_l)*random() ) into strict p_joincond_num ;
select round( p_whereind_num_u +( p_whereind_num_u - p_whereind_num_l)*random() ) into strict p_whereind_num ;
select round( p_wherenoind_num_l +( p_wherenoind_num_u - p_wherenoind_num_l)*random() ) into strict p_wherenoind_num ;

select round( p_select_num_l +( p_select_num_u - p_select_num_l)*random() ) into strict p_select_num ;
select round( p_orderby_num_l +( p_orderby_num_u - p_orderby_num_l)*random() ) into strict p_orderby_num ;
select p_desc_prb_l +( p_desc_prb_u - p_desc_prb_l)*random() into strict p_desc_prb ;
select p_joinnoind_prb_l +( p_joinnoind_prb_u - p_joinnoind_prb_l)*random() into strict p_joinnoind_prb ;
select p_rami_prb_l +( p_rami_prb_u - p_rami_prb_l)*random() into strict p_rami_prb ;

select p_groupby_prb_l +( p_groupby_prb_u - p_groupby_prb_l)*random() into strict p_groupby_prb ;
select p_orderby_prb_l +( p_orderby_prb_u - p_orderby_prb_l)*random() into strict p_orderby_prb ;
select p_semianti_prb_l +( p_semianti_prb_u - p_semianti_prb_l)*random() into strict p_semianti_prb ;
select p_semi_rto_l +( p_semi_rto_u - p_semi_rto_l)*random() into strict p_semi_rto ;
select round( p_level_max_l +( p_level_max_u - p_level_max_l)*random() ) into strict p_level_max ;

select p_recur_prb_l +( p_recur_prb_u - p_recur_prb_l)*random() into strict p_recur_prb ;
select p_union_prb_l +( p_union_prb_u - p_union_prb_l)*random() into strict p_union_prb ;
select p_union_rto_l +( p_union_rto_u - p_union_rto_l)*random() into strict p_union_rto ;

call proc_makesql(
p_tab_num
,p_outer_prb
,p_joincond_num
,p_whereind_num
,p_wherenoind_num
,p_select_num
,p_orderby_num
,p_desc_prb
,p_joinnoind_prb
,p_rami_prb
,p_groupby_prb
,p_orderby_prb
,p_semianti_prb
,p_semi_rto
,0 -- p_level
,p_level_max
,vsql
,vsqlcolname
,p_recur_prb
,p_union_prb
,p_union_rto
,0 -- p_union_flg
,vsqlcolname2
);

vsql := vsql || ' ; ';
raise notice '%', vsql;
end loop;

end;
$$;

call main();

SQL Server

(2019)

create or alter procedure proc_makesql(
@p_tab_num numeric = 3
,@p_outer_prb numeric = 20
,@p_joincond_num numeric = 3
,@p_whereind_num numeric = 3
,@p_wherenoind_num numeric = 3
,@p_select_num numeric = 5
,@p_orderby_num numeric = 3
,@p_desc_prb numeric = 20
,@p_joinnoind_prb numeric = 10
,@p_rami_prb numeric = 50
,@p_groupby_prb numeric = 50
,@p_orderby_prb numeric = 50
,@p_semianti_prb numeric = 20
,@p_semi_rto numeric = 50
,@p_level numeric = 0
,@p_level_max numeric = 2
,@o_vsql varchar(4000) out
,@p_recur_prb numeric = 50
,@p_union_prb numeric = 50
,@p_union_rto numeric = 50
,@p_union_flg numeric = 0
)
as
begin
set nocount on;

-- パラメータ
declare @C_TAB_NUM numeric = @p_tab_num ; -- テーブル数
declare @C_OUTER_PRB numeric = @p_outer_prb ; -- 外部結合確率
declare @C_JOINCOND_NUM numeric = @p_joincond_num ; -- 結合条件数(結合毎)
declare @C_WHEREIND_NUM numeric = @p_whereind_num ; -- where条件数(インデックスあり)
declare @C_WHERENOIND_NUM numeric = @p_wherenoind_num ; -- where条件数(インデックスなし)
declare @C_SELECT_NUM numeric = @p_select_num ; -- select句カラム数
declare @C_ORDERBY_NUM numeric = @p_orderby_num ; -- order by句カラム数
declare @C_DESC_PRB numeric = @p_desc_prb ; -- desc確率
declare @C_JOINNOIND_PRB numeric = @p_joinnoind_prb ; -- インデックスなし結合確率
declare @C_RAMI_PRB numeric = @p_rami_prb ; -- 結合分岐確率
declare @C_GROUPBY_PRB numeric = @p_groupby_prb ; -- groupby発生確率
declare @C_ORDERBY_PRB numeric = @p_orderby_prb ; -- orderby発生確率
declare @C_SEMIANTI_PRB numeric = @p_semianti_prb ; -- セミアンチ結合発生確率(最大1個)
declare @C_SEMI_RTO numeric = @p_semi_rto ; -- セミ結合発生比率
declare @C_LEVEL numeric = @p_level ; -- 再帰階層レベル(0: 最上位の親, 1:子, 2:孫)
declare @C_LEVEL_MAX numeric = @p_level_max ; -- 再帰階層レベルの最大値
declare @C_RECUR_PRB numeric = @p_recur_prb ; -- 再帰 SQL発生確率
declare @C_UNION_PRB numeric = @p_union_prb ; -- union/union all SQL発生確率
declare @C_UNION_RTO numeric = @p_union_rto ; -- union発生比率
declare @C_UNION_FLG numeric = @p_union_flg ; -- unionSQL 実行フラグ (0: 通常のSQL, 1: unionSQL)

-- ワーク変数
declare @wk1 numeric;
declare @wk2 numeric;
declare @wk3 numeric;
declare @wk4 numeric;
declare @wk5 numeric;
declare @wk6 numeric;
declare @wk7 numeric;
declare @wk8 numeric;
declare @wk9 numeric;
declare @wk10 numeric;
declare @wk11 numeric;
declare @wk12 numeric;
declare @wk13 numeric;

declare @va1 varchar(4000);
declare @va2 varchar(4000);
declare @va3 varchar(4000);
declare @va4 varchar(4000);
declare @va5 varchar(4000);
declare @va6 varchar(4000);
declare @va7 varchar(4000);
declare @va8 varchar(4000);
declare @va9 varchar(4000);
declare @va10 varchar(4000);
declare @va11 varchar(4000);
declare @va12 varchar(4000);
declare @va13 varchar(4000);

-- フラグ
declare @flg1 numeric = 0; -- 0: group byなし、 1: group byあり
declare @flg2 numeric = 0; -- 0: order byなし、 1: order byあり
declare @flg3 numeric = 0; -- 0: セミアンチなし、 1: セミあり、 2: アンチあり
declare @flg4 numeric = 0; -- 0: 再帰なし、 1: 再帰あり

-- SQL文
declare @vsql varchar(4000);
declare @vsql_upper varchar(4000);

-- テーブル名配列
declare @tabnames table(id int identity(1,1)
,table_name varchar(4000)
,t_name varchar(30)
,semianti_flg numeric
);

-- カラム配列
declare @colnames table(id int identity(1,1)
,table_name varchar(4000)
,column_name varchar(30)
,system_type_id numeric
,max_length numeric
,precision numeric
,scale numeric
,ind_head_flg numeric
,t_name varchar(30)
,semianti_flg numeric
);

-- 結合テーブル組み合わせ配列
declare @tabcombi table(id int identity(1,1)
,table_no1 numeric
,table_no2 numeric
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,join_type varchar(30) -- innner join or left join
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

-- 結合条件配列
declare @joincols table(id int identity(1,1)
,tabcombi_id int
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

declare @joincols2 table(id int identity(1,1)
,tabcombi_id int
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

declare @joincols3 table(id int identity(1,1)
,tabcombi_id int
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
,pri numeric -- priority
,t_name1 varchar(30)
,t_name2 varchar(30)
,semianti_flg numeric
);

-- where条件配列(indexあり)
declare @whereindcols table(id int identity(1,1)
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- where条件
,t_name varchar(30)
,semianti_flg numeric
);

-- wherex条件配列(indexなし)
declare @wherenoindcols table(id int identity(1,1)
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- where条件
,t_name varchar(30)
,semianti_flg numeric
);

-- select句配列
declare @selectcols table(id int identity(1,1)
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- カラム別名
,t_name varchar(30)
,semianti_flg numeric
,system_type_id numeric -- 再帰 SQL用
,max_length numeric -- 再帰 SQL用
,precision numeric -- 再帰 SQL用
,scale numeric -- 再帰 SQL用
);

-- orderby句配列
declare @orderbycols table(id int identity(1,1)
,table_name varchar(4000)
,column_name varchar(30)
,col3 varchar(30) -- ソート順
,t_name varchar(30)
,semianti_flg numeric
);

-- カーソル関連
-- 通常変数を参照するカーソルは変数設定後に宣言する必要がある模様のため、それぞれ使用時に宣言する

if @C_LEVEL = 0
begin
-- 作成SQLのカラム情報(再帰処理用)
drop table if exists #grec;
create table #grec( level numeric
,column_name varchar(30)
,system_type_id numeric
,max_length numeric
,precision numeric
,scale numeric
);
-- 作成SQLのカラム情報(unionSQL用)
drop table if exists #grec2;
create table #grec2( id int identity(1,1)
,column_name varchar(30)
,system_type_id numeric
,max_length numeric
,precision numeric
,scale numeric
);
end;

-- unionSQLの判定
set @wk1 = ( select rand()*100 );
if @wk1 < @C_UNION_PRB and @C_LEVEL <= @C_LEVEL_MAX - 1 -- unionSQLの場合
begin

set @wk1 = @C_LEVEL+1;
-- 上段SQL実行
exec proc_makesql
@C_TAB_NUM
,@C_OUTER_PRB
,@C_JOINCOND_NUM
,@C_WHEREIND_NUM
,@C_WHERENOIND_NUM
,@C_SELECT_NUM
,@C_ORDERBY_NUM
,@C_DESC_PRB
,@C_JOINNOIND_PRB
,@C_RAMI_PRB
,@C_GROUPBY_PRB
,0 -- @C_ORDERBY_PRB
,@C_SEMIANTI_PRB
,@C_SEMI_RTO
,@wk1
,@C_LEVEL_MAX
,@vsql out
,@C_RECUR_PRB
,@C_UNION_PRB
,@C_UNION_RTO
,1 -- C_UNION_FLG

set @vsql_upper = @vsql;

-- 下段SQL実行
exec proc_makesql
@C_TAB_NUM
,@C_OUTER_PRB
,@C_JOINCOND_NUM
,@C_WHEREIND_NUM
,@C_WHERENOIND_NUM
,@C_SELECT_NUM
,@C_ORDERBY_NUM
,@C_DESC_PRB
,@C_JOINNOIND_PRB
,@C_RAMI_PRB
,@C_GROUPBY_PRB
,0 -- @C_ORDERBY_PRB
,@C_SEMIANTI_PRB
,@C_SEMI_RTO
,@wk1
,@C_LEVEL_MAX
,@vsql out
,@C_RECUR_PRB
,@C_UNION_PRB
,@C_UNION_RTO
,1 -- C_UNION_FLG

set @wk1 = ( select rand()*100 );
if @wk1 < @C_UNION_RTO
set @va1 = ' union ';
else
set @va1 = ' union all ';

-- order by句処理

set @wk1 = ( select rand()*100 );
if @wk1 < @C_ORDERBY_PRB
begin
set @wk1 = 1;
set @wk2 = ( select count(*) from #grec2 );

declare cur30 cursor for select t1.col1
from ( select row_number() over(order by object_id) col1 from sys.columns ) t1
where t1.col1 <= @wk2
order by newid();
open cur30;
fetch next from cur30 into @wk3;
while @@fetch_status = 0
begin
if @wk1 = 1
set @va2 = ' order by '+cast(@wk3 as varchar);
else
set @va2 = @va2+','+cast(@wk3 as varchar);

fetch next from cur30 into @wk3;
set @wk1 = @wk1+1;
end
close cur30;
deallocate cur30;
end;
else
set @va2 = '';

set @o_vsql = ' ( '+@vsql_upper+' ) '+@va1+' ( '+@vsql+' ) '+@va2;

-- 後続の処理なし
return;
end;

-- 再帰有無の判定
set @wk1 = ( select rand()*100 );
if @wk1 < @C_RECUR_PRB and @C_LEVEL <= @C_LEVEL_MAX - 1
set @flg4 = 1;

if @flg4 = 1
begin

set @wk1 = @C_LEVEL+1;
exec proc_makesql
@C_TAB_NUM
,@C_OUTER_PRB
,@C_JOINCOND_NUM
,@C_WHEREIND_NUM
,@C_WHERENOIND_NUM
,@C_SELECT_NUM
,@C_ORDERBY_NUM
,@C_DESC_PRB
,@C_JOINNOIND_PRB
,@C_RAMI_PRB
,@C_GROUPBY_PRB
,0 -- @C_ORDERBY_PRB 再帰でorder by句があるとエラー
,@C_SEMIANTI_PRB
,@C_SEMI_RTO
,@wk1
,@C_LEVEL_MAX
,@vsql out
,@C_RECUR_PRB
,@C_UNION_PRB
,@C_UNION_RTO
,@C_UNION_FLG

end;

-- group by句とorder by句有無の判定
set @wk1 = ( select rand()*100 );
set @wk2 = ( select count(*) from #grec2 );
set @wk3 = ( select max(id) from #grec2 );
set @wk4 = ( select system_type_id from #grec2 where id = @wk3 );
if @wk1 < @C_GROUPBY_PRB
begin
if @C_UNION_FLG = 1 and @wk2 > 0
begin
if @wk4 = 108
-- unionSQLの2回目以降は最後のカラムがnumeric型の場合のみgroup byを実施する
set @flg1 = 1;
end;
else
set @flg1 = 1;
end;

set @wk1 = ( select rand()*100 );
if @wk1 < @C_ORDERBY_PRB
set @flg2 = 1;

-- セミアンチ有無の判定
set @wk1 = ( select rand()*100 );
if @wk1 < @C_SEMIANTI_PRB
begin
set @wk2 = ( select rand()*100 );
if @wk2 < @C_SEMI_RTO
set @flg3 = 1;
else
set @flg3 = 2;
end;

-- ①テーブル名配列にテーブルを格納
-- テーブル数=3
declare cur10 cursor for select table_name from information_schema.tables order by newid();

set @wk1 = 1;
open cur10;
fetch next from cur10 into @va1;
while @@fetch_status = 0
begin
set @va2 = 't'+cast(@wk1 as varchar)+cast(@C_LEVEL as varchar);
insert into @tabnames(table_name,t_name,semianti_flg) values(@va1,@va2,0);
fetch next from cur10 into @va1;
set @wk1 = @wk1+1;
if @wk1 > @C_TAB_NUM
break;

end
close cur10;
deallocate cur10;

-- select * from @tabnames;

-- ②テーブルのカラム名、データ型をカラム配列(2次元)に格納
declare cur20 cursor for select object_name(object_id) table_name
,name column_name
,system_type_id
,coalesce(max_length,-1) max_length
,coalesce(precision,-1) precision
,coalesce(scale,-1) scale
from sys.columns
where object_name(object_id) in ( select table_name from @tabnames )
order by object_id,column_id;

open cur20;
fetch next from cur20 into @va1,@va2,@wk1,@wk2,@wk3,@wk4;
while @@fetch_status = 0
begin
insert into @colnames(table_name,column_name,system_type_id,max_length,precision,scale,ind_head_flg,semianti_flg) values(@va1,@va2,@wk1,@wk2,@wk3,@wk4,0,0);

set @wk5 = ( select count(*) from sys.indexes t1
inner join sys.index_columns t2
on t1.object_id = t2.object_id
and t1.index_id = t2.index_id
where object_name(t1.object_id) = @va1
and col_name(t2.object_id,t2.column_id) = @va2
and t2.key_ordinal = 1
);

if @wk5 > 0
update @colnames set ind_head_flg = 1 where table_name = @va1
and column_name = @va2;

update t1
set t1.t_name = t2.t_name
from @colnames t1 , @tabnames t2
where t1.table_name = t2.table_name
;

fetch next from cur20 into @va1,@va2,@wk1,@wk2,@wk3,@wk4;
end
close cur20;
deallocate cur20;

-- 再帰ありの場合、子から受け取ったカラム情報をテーブル配列とカラム配列に追加
if @flg4 = 1
begin
set @wk1 = ( select count(*) from @tabnames)+1;

set @va1 = ' ( ' + @vsql + ' ) ';
set @va2 = 't' + cast(@wk1 as varchar) + '_' + cast(@C_LEVEL as varchar);
insert into @tabnames(table_name,t_name,semianti_flg) values(@va1,@va2,0);

declare cur21 cursor for select level,column_name,system_type_id,max_length,precision,scale from #grec where level = @C_LEVEL+1;;
open cur21;
fetch next from cur21 into @wk2,@va3,@wk3,@wk4,@wk5,@wk6;
while @@fetch_status = 0
begin

insert into @colnames(table_name,column_name,system_type_id,max_length,precision,scale,ind_head_flg,t_name,semianti_flg)
values(@va1,@va3,@wk3,@wk4,@wk5,@wk6,0,@va2,0);

fetch next from cur21 into @wk2,@va3,@wk3,@wk4,@wk5,@wk6;
end
close cur21;
deallocate cur21;
end;

-- select * from @colnames;

-- ③結合テーブル組み合わせ配列の作成

set @wk1 = 1;
set @wk2 = ( select count(*) from @tabnames );

if @wk2 > 1
begin
-- 通常変数を参照するカーソルは変数設定後に宣言する必要がある模様
declare cur30 cursor for select t1.col1
from ( select row_number() over(order by object_id) col1 from sys.columns ) t1
where t1.col1 <= @wk2
order by newid();

open cur30;
fetch next from cur30 into @wk3;
while @@fetch_status = 0
begin

if @wk1 = 1
insert into @tabcombi(table_no1,join_type) values(@wk3,'I');
else if @wk1 < @wk2
begin
update @tabcombi set table_no2 = @wk3 where id = @wk1-1;
insert into @tabcombi(table_no1,join_type) values(@wk3,'I');
end
else
update @tabcombi set table_no2 = @wk3 where id = @wk1-1;

fetch next from cur30 into @wk3;
set @wk1 = @wk1+1;
end
close cur30;
deallocate cur30;
end;

-- 結合分岐設定
declare cur31 cursor for select id,table_no1 from @tabcombi order by id;

open cur31;
fetch next from cur31 into @wk1,@wk2;
while @@fetch_status = 0
begin
set @wk3 = ( select rand()*100 );
if @wk1 > 1 and @wk3 < @C_RAMI_PRB
update @tabcombi set table_no1 = @wk4 where id = @wk1;

set @wk4 = ( select table_no1 from @tabcombi where id = @wk1 );

fetch next from cur31 into @wk1,@wk2;
end
close cur31;
deallocate cur31;

-- 結合タイプ、テーブル名のセット
declare cur32 cursor for select id,table_no1,table_no2 from @tabcombi order by id;

open cur32;
fetch next from cur32 into @wk1,@wk2,@wk3;
while @@fetch_status = 0
begin

update @tabcombi set table_name1 = ( select table_name from @tabnames where id = @wk2 ) where id = @wk1;
update @tabcombi set table_name2 = ( select table_name from @tabnames where id = @wk3 ) where id = @wk1;
update @tabcombi set t_name1 = ( select t_name from @tabnames where id = @wk2 ) where id = @wk1;
update @tabcombi set t_name2 = ( select t_name from @tabnames where id = @wk3 ) where id = @wk1;
update @tabcombi set semianti_flg = 0 where id = @wk1;

-- 結合タイプ設定。外部結合確率20%
set @wk4 = ( select rand()*100 );
if @wk4 < @C_OUTER_PRB
update @tabcombi set join_type = 'O' where id = @wk1;

-- セミアンチありの場合、最後の1件にフラグを立てる
-- さかのぼり、テーブル名配列とカラム配列もフラグを立てる
if @wk1 > 1 and @wk1 = (select count(*) from @tabcombi) and @flg3 != 0
begin
update @tabcombi set semianti_flg = @flg3 where id = @wk1;

update @tabnames set semianti_flg = @flg3 where table_name in ( select table_name2 from @tabcombi where id = @wk1 );
update @colnames set semianti_flg = @flg3 where table_name in ( select table_name2 from @tabcombi where id = @wk1 );
end;


fetch next from cur32 into @wk1,@wk2,@wk3;
end
close cur32;
deallocate cur32;

-- select * from @tabcombi;

declare cur40 cursor for select id,table_name1,table_name2,semianti_flg from @tabcombi order by id;

open cur40;
fetch next from cur40 into @wk11,@va1,@va2,@wk13;
while @@fetch_status = 0
begin

set @wk12 = 1;

declare cur41 cursor for select table_name ,column_name ,system_type_id ,max_length ,precision ,scale ,ind_head_flg ,t_name
from @colnames where table_name = @va1;

open cur41;
fetch next from cur41 into @va3,@va4,@wk1,@wk2,@wk3,@wk4,@wk5,@va7;
while @@fetch_status = 0
begin

declare cur42 cursor for select table_name ,column_name ,system_type_id ,max_length ,precision ,scale ,ind_head_flg ,t_name
from @colnames where table_name = @va2;
open cur42;
fetch next from cur42 into @va5,@va6,@wk6,@wk7,@wk8,@wk9,@wk10,@va8;
while @@fetch_status = 0
begin

if @wk1 = @wk6
begin
if @wk2 = @wk7 and @wk3 = @wk9 and @wk4 = @wk9
begin
if @wk5 = 1 or @wk10 = 1
set @wk13 = 20;
else
set @wk13 = 15;
end;
else
begin
if @wk4 = 1 or @wk8 = 1
set @wk13 = 10;
else
set @wk13 = 5;
end;

insert into @joincols(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg) values(@wk11,@va3,@va5,@va4,@va6,@wk13,@va7,@va8,@wk13);
set @wk12 = @wk12+1;
end;

fetch next from cur42 into @va5,@va6,@wk6,@wk7,@wk8,@wk9,@wk10,@va8;
end
close cur42;
deallocate cur42;

fetch next from cur41 into @va3,@va4,@wk1,@wk2,@wk3,@wk4,@wk5,@va7;
end
close cur41;
deallocate cur41;

-- 結合条件がない場合、優先度0でレコード作成
if @wk12 = 0
insert into @joincols(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg) values(0,'','','','',0,'','',0);

fetch next from cur40 into @wk11,@va1,@va2,@wk13;
end
close cur40;
deallocate cur40;

-- select * from @joincols;

-- 結合条件配列のシャッフル
-- 結合条件配列の過剰分削除
-- テーブル組み合わせごとに条件数の上限=3

declare cur43 cursor for select id from @tabcombi order by id;
open cur43;
fetch next from cur43 into @wk1;
while @@fetch_status = 0
begin
set @wk2 = ( select rand()*100 );
if @wk2 < @C_JOINNOIND_PRB
begin
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 15 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 20 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 5 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 10 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 0 order by newid();
end;
else
begin
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 20 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 15 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 10 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 5 order by newid();
insert into @joincols2(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols where tabcombi_id = @wk1 and pri = 0 order by newid();
end;

set @wk3 = ( select min(id) from @joincols2 );
set @wk3 = @wk3 + @C_JOINCOND_NUM;

insert into @joincols3(tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg)
select tabcombi_id,table_name1,table_name2,column_name1,column_name2,pri,t_name1,t_name2,semianti_flg
from @joincols2 where id < @wk3;

delete from @joincols2;

fetch next from cur43 into @wk1;
end
close cur43;
deallocate cur43;

-- select * from @joincols3;

-- ⑤where条件配列の作成
-- インデックス有とインデックスなし
-- where条件配列のシャッフル
-- where条件配列の過剰分削除
-- 条件数の上限=3
-- int-> 56 bigint-> 127 numeric-> 108 decimal-> 106
-- char-> 175 varchar-> 167 nchar-> 239 nvarchar-> 231
-- date-> 40 datetime2-> 42

insert into @whereindcols(table_name,column_name,col3,t_name,semianti_flg)
select table_name,column_name, case when system_type_id in (56,127,108,106) then ' < 100 '
when system_type_id in (175,167,239,231) then ' like ''%A%'' '
else ' IS NOT NULL '
end col3
, t_name
, semianti_flg
from @colnames
where ind_head_flg = 1
order by newid();

insert into @wherenoindcols(table_name,column_name,col3,t_name,semianti_flg)
select table_name,column_name, case when system_type_id in (56,127,108,106) then ' < 100 '
when system_type_id in (175,167,239,231) then ' like ''%A%'' '
else ' IS NOT NULL '
end col3
, t_name
, semianti_flg
from @colnames
where ind_head_flg = 0
order by newid();

delete from @whereindcols where id > @C_WHEREIND_NUM;
delete from @wherenoindcols where id > @C_WHERENOIND_NUM;

-- select * from @whereindcols;
-- select * from @wherenoindcols;

set @wk1 = ( select count(*) from #grec2);
if @C_UNION_FLG = 1 and @wk1 > 0 -- unionSQLの2回目以降
begin
-- group byの場合、カラム数-1
if @flg1 = 1
set @wk2 = @wk1 - 1;
else
set @wk2 = @wk1;

declare cur60 cursor for select id,column_name ,system_type_id ,max_length ,precision ,scale from #grec2 order by id;
open cur60;
fetch next from cur60 into @wk3,@va1,@wk4,@wk5,@wk6,@wk7;
while ( @@fetch_status = 0 and @wk3 <= @wk2 )
begin
set @wk12 = 1;
declare cur61 cursor for select table_name ,column_name ,system_type_id ,max_length ,precision ,scale ,t_name
from @colnames where semianti_flg = 0 order by rand();
open cur61;
fetch next from cur61 into @va2,@va3,@wk8,@wk9,@wk10,@wk11,@va4;
while @@fetch_status = 0
begin

if @wk4 = @wk8
begin
-- 既に使用済みカラムかチェック
set @wk13 = ( select count(*) from @selectcols where table_name = @va2 and column_name = @va3 );
if @wk13 = 0
begin
set @va5 = 'col'+cast(@wk3 as varchar);
insert into @selectcols (table_name ,column_name ,col3 ,t_name ,system_type_id ,max_length ,precision ,scale)
values(@va2 ,@va3 ,@va5 ,@va4 ,@wk8 ,@wk9 ,@wk10 ,@wk11);
set @wk12 = @wk12+1;
goto END1;
end;
end;

fetch next from cur61 into @va2,@va3,@wk8,@wk9,@wk10,@wk11,@va4;
end
END1:
close cur61;
deallocate cur61;

if @wk12 = 1
begin
-- 同じデータ型のカラムがない場合カラム名としてNULLをセット
set @va5 = 'col'+cast(@wk3 as varchar);
insert into @selectcols (table_name ,column_name ,col3 ,t_name ,system_type_id ,max_length ,precision ,scale)
values('' ,'NULL' ,@va5 ,'' ,0 ,0 ,0 ,0);
end;


fetch next from cur60 into @wk3,@va1,@wk4,@wk5,@wk6,@wk7;
end;
close cur60;
deallocate cur60;
end;
else
begin
insert into @selectcols(table_name,column_name,t_name,semianti_flg ,system_type_id,max_length,precision,scale)
select table_name,column_name,t_name,semianti_flg ,system_type_id,max_length,precision,scale
from @colnames
where semianti_flg = 0
order by newid();

delete from @selectcols where id > @C_SELECT_NUM;
update @selectcols set col3 = 'col'+cast(id as varchar) ;
end;

-- select句の内容を親へ渡すためにout配列に格納

insert into #grec(level, column_name ,system_type_id,max_length,precision,scale )
select @C_LEVEL, col3 ,system_type_id,max_length,precision,scale from @selectcols;

-- select * from @selectcols;

-- order by句出力なしの場合も作成する
-- group by句出力ありの場合は、select句配列をコピーし、シャッフルと過剰分削除を行う
if @flg1 = 1
begin
insert into @orderbycols(table_name,column_name,t_name,semianti_flg)
select table_name,column_name,t_name,semianti_flg
from @selectcols
order by newid();
end;
else
begin
insert into @orderbycols(table_name,column_name,t_name,semianti_flg)
select table_name,column_name,t_name,semianti_flg
from @colnames
where semianti_flg = 0
order by newid();
end;

delete from @orderbycols where id > @C_ORDERBY_NUM;

update @orderbycols set col3 = case when rand( cast(substring(cast(newid() as varbinary),1,2) as int) ) < @C_DESC_PRB then ' desc ' else ' asc ' end;

-- select * from @orderbycols;

-- ⑧ SQL文出力
set @vsql = 'select ';

declare cur80 cursor for select id,t_name,column_name,col3 from @selectcols order by id;
open cur80;
fetch next from cur80 into @wk1,@va1,@va2,@va3;
while @@fetch_status = 0
begin
if @va2 != 'NULL'
set @va4 = @va1+'.'+@va2+' '+@va3;
else
set @va4 = @va2+' '+@va3;

if @wk1 > 1
set @va4 = ','+@va4;

set @vsql = @vsql+@va4;

fetch next from cur80 into @wk1,@va1,@va2,@va3;
end
close cur80;
deallocate cur80;

-- group by句ありの場合、count関数を出力
if @flg1 = 1
begin
set @wk1 = ( select count(*) from @selectcols );
set @wk1 = @wk1+1;
set @va1 = 'col'+cast(@wk1 as varchar);
if @wk1 > 1
set @vsql = @vsql+' ,count(*) '+@va1;
else
set @vsql = @vsql+' count(*) '+@va1;
insert into #grec(level, column_name ,system_type_id,max_length,precision,scale )
values( @C_LEVEL, @va1 ,108 ,9 ,18 ,0 );

end;

set @vsql = @vsql+' from ';

set @wk1 = ( select count(*) from @tabnames );
if @wk1 > 1
begin
declare cur81 cursor for select id,table_name1,table_name2,join_type,t_name1,t_name2 from @tabcombi where semianti_flg = 0 order by id; -- セミアンチ結合のテーブルは対象外
open cur81;
fetch next from cur81 into @wk1,@va1,@va2,@va3,@va12,@va13;
while @@fetch_status = 0
begin

if @va3 = 'I'
set @va4 = ' inner join ';
else
set @va4 = ' left outer join ';

if @wk1 = 1
set @va5 = @va1+' '+@va12+@va4+@va2+' '+@va13;
else
set @va5 = @va4+@va2+' '+@va13;

set @vsql = @vsql+@va5;

-- 結合条件セット
set @wk2 = 0;
declare cur82 cursor for select t_name1,t_name2,column_name1,column_name2,pri from @joincols3 where tabcombi_id = @wk1;
open cur82;
fetch next from cur82 into @va6,@va7,@va8,@va9,@wk3;
while @@fetch_status = 0
begin
if @wk3 != 0
begin
set @wk2 = @wk2+1;
if @wk2 = 1
set @va10 = ' on ';
else
set @va10 = ' and ';
end;

set @va11 = @va10+@va6+'.'+@va8+' = '+@va7+'.'+@va9;
set @vsql = @vsql+@va11;

fetch next from cur82 into @va6,@va7,@va8,@va9,@wk3;
end
close cur82;
deallocate cur82;

-- 結合条件がない場合
if @wk2 = 0
begin
set @va11 = ' on 1 = 1 ';
set @vsql = @vsql+@va11;
end;

fetch next from cur81 into @wk1,@va1,@va2,@va3,@va12,@va13;
end
close cur81;
deallocate cur81;
end;
else
begin
-- テーブル1個の場合
set @va1 = ( select table_name from @tabnames );
set @va2 = ( select t_name from @tabnames );
set @vsql = @vsql+@va1+' '+@va2;

end;

set @vsql = @vsql+' where 1 = 1 ';

-- セミアンチ結合対応

declare cur86 cursor for select id,table_name1,table_name2,join_type,t_name1,t_name2,semianti_flg from @tabcombi where semianti_flg != 0 order by id;
open cur86;
fetch next from cur86 into @wk1,@va1,@va2,@va3,@va12,@va13,@wk4;
while @@fetch_status = 0
begin

if @wk4 = 1
set @va4 = ' and exists ( select 1 from ';
else
set @va4 = ' and not exists ( select 1 from ';

set @va4 = @va4+' '+@va2+' '+@va13;
set @va4 = @va4+' where 1 = 1 ';

-- 結合条件セット
declare cur82 cursor for select t_name1,t_name2,column_name1,column_name2,pri from @joincols3 where tabcombi_id = @wk1;
open cur82;
fetch next from cur82 into @va6,@va7,@va8,@va9,@wk3;
while @@fetch_status = 0
begin
if @wk3 != 0 -- ダミーレコードは除く
set @va4 = @va4+' and '+@va7+'.'+@va9+' = '+@va6+'.'+@va8;

fetch next from cur82 into @va6,@va7,@va8,@va9,@wk3;
end
close cur82;
deallocate cur82;

set @vsql = @vsql+@va4;

-- where条件セット

declare cur87 cursor for select t_name,column_name,col3 from @whereindcols where semianti_flg != 0 order by id;
open cur87;
fetch next from cur87 into @va1,@va2,@va3;
while @@fetch_status = 0
begin
set @va4 = ' and '+@va1+'.'+@va2+@va3;
set @vsql = @vsql+@va4;
fetch next from cur87 into @va1,@va2,@va3;
end
close cur87;
deallocate cur87;

declare cur88 cursor for select t_name,column_name,col3 from @wherenoindcols where semianti_flg != 0 order by id;
open cur88;
fetch next from cur88 into @va1,@va2,@va3;
while @@fetch_status = 0
begin
set @va4 = ' and '+@va1+'.'+@va2+@va3;
set @vsql = @vsql+@va4;
fetch next from cur88 into @va1,@va2,@va3;
end
close cur88;
deallocate cur88;

set @vsql = @vsql+' ) ';

fetch next from cur86 into @wk1,@va1,@va2,@va3,@va12,@va13,@wk4;
end
close cur86;
deallocate cur86;

declare cur83 cursor for select t_name,column_name,col3 from @whereindcols where semianti_flg = 0 order by id; -- セミアンチ結合のテーブルのカラムは対象外
open cur83;
fetch next from cur83 into @va1,@va2,@va3;
while @@fetch_status = 0
begin
set @va4 = ' and '+@va1+'.'+@va2+@va3;
set @vsql = @vsql+@va4;
fetch next from cur83 into @va1,@va2,@va3;
end
close cur83;
deallocate cur83;

declare cur84 cursor for select t_name,column_name,col3 from @wherenoindcols where semianti_flg = 0 order by id; -- セミアンチ結合のテーブルのカラムは対象外
open cur84;
fetch next from cur84 into @va1,@va2,@va3;
while @@fetch_status = 0
begin
set @va4 = ' and '+@va1+'.'+@va2+@va3;
set @vsql = @vsql+@va4;
fetch next from cur84 into @va1,@va2,@va3;
end
close cur84;
deallocate cur84;

-- group by句ありの場合
if @flg1 = 1
begin
declare cur80 cursor for select id,t_name,column_name from @selectcols order by id;
open cur80;
fetch next from cur80 into @wk1,@va1,@va2;
while @@fetch_status = 0
begin

if @wk1 = 1
set @va4 = ' group by ';
else
set @va4 = ',';

if @va2 != 'NULL'
set @va5 = @va4+@va1+'.'+@va2;
else
set @va5 = @va4+@va2;
set @vsql = @vsql+@va5;

fetch next from cur80 into @wk1,@va1,@va2;
end
close cur80;
deallocate cur80;
end;

-- order by句ありの場合
if @flg2 = 1
begin
declare cur85 cursor for select id,t_name,column_name,col3 from @orderbycols order by id;
open cur85;
fetch next from cur85 into @wk1,@va1,@va2,@va3;
while @@fetch_status = 0
begin
if @wk1 = 1
set @va4 = ' order by ';
else
set @va4 = ',';

set @va5 = @va4+@va1+'.'+@va2+@va3;
set @vsql = @vsql+@va5;

fetch next from cur85 into @wk1,@va1,@va2,@va3;
end
close cur85;
deallocate cur85;
end;

-- set @vsql = @vsql+' ; ';
--select @vsql;

set @o_vsql = @vsql;

set @wk1 = (select count(*) from #grec2);

if @C_UNION_FLG = 1 and @wk1 = 0
insert into #grec2( column_name ,system_type_id ,max_length ,precision ,scale )
select column_name ,system_type_id ,max_length ,precision ,scale from #grec;

end
go

create or alter procedure main
as
begin
declare @vsql varchar(4000);

declare @loop_num numeric = 1; -- proc_makesql実行回数

declare @p_tab_num numeric;
declare @p_outer_prb numeric;
declare @p_joincond_num numeric;
declare @p_whereind_num numeric;
declare @p_wherenoind_num numeric;

declare @p_select_num numeric;
declare @p_orderby_num numeric;
declare @p_desc_prb numeric;
declare @p_joinnoind_prb numeric;
declare @p_rami_prb numeric;

declare @p_groupby_prb numeric;
declare @p_orderby_prb numeric;
declare @p_semianti_prb numeric;
declare @p_semi_rto numeric;
declare @p_level_max numeric;

declare @p_recur_prb numeric;
declare @p_union_prb numeric;
declare @p_union_rto numeric;

declare @p_tab_num_l numeric = 1;
declare @p_tab_num_u numeric = 5;

declare @p_outer_prb_l numeric = 0;
declare @p_outer_prb_u numeric = 100;

declare @p_joincond_num_l numeric = 1;
declare @p_joincond_num_u numeric = 5;

declare @p_whereind_num_l numeric = 1;
declare @p_whereind_num_u numeric = 5;

declare @p_wherenoind_num_l numeric = 1;
declare @p_wherenoind_num_u numeric = 5;

declare @p_select_num_l numeric = 1;
declare @p_select_num_u numeric = 10;

declare @p_orderby_num_l numeric = 1;
declare @p_orderby_num_u numeric = 5;

declare @p_desc_prb_l numeric = 0;
declare @p_desc_prb_u numeric = 100;

declare @p_joinnoind_prb_l numeric = 0;
declare @p_joinnoind_prb_u numeric = 100;

declare @p_rami_prb_l numeric = 0;
declare @p_rami_prb_u numeric = 100;

declare @p_groupby_prb_l numeric = 0;
declare @p_groupby_prb_u numeric = 100;

declare @p_orderby_prb_l numeric = 0;
declare @p_orderby_prb_u numeric = 100;

declare @p_semianti_prb_l numeric = 0;
declare @p_semianti_prb_u numeric = 100;

declare @p_semi_rto_l numeric = 0;
declare @p_semi_rto_u numeric = 100;

declare @p_level_max_l numeric = 0;
declare @p_level_max_u numeric = 2;

declare @p_recur_prb_l numeric = 0;
declare @p_recur_prb_u numeric = 100;

declare @p_union_prb_l numeric = 0;
declare @p_union_prb_u numeric = 100;

declare @p_union_rto_l numeric = 0;
declare @p_union_rto_u numeric = 100;

declare @wk1 numeric = 1;

while @wk1 <= @loop_num
begin
set @p_tab_num = ( select round( @p_tab_num_l +( @p_tab_num_u - @p_tab_num_l)*rand() ,0) );
set @p_outer_prb = (select @p_outer_prb_l+( @p_outer_prb_u - @p_outer_prb_l)*rand() ) ;
set @p_joincond_num = (select round( @p_joincond_num_l +( @p_joincond_num_u - @p_joincond_num_l)*rand() ,0) ) ;
set @p_whereind_num = (select round( @p_whereind_num_u +( @p_whereind_num_u - @p_whereind_num_l)*rand() ,0) ) ;
set @p_wherenoind_num = (select round( @p_wherenoind_num_l +( @p_wherenoind_num_u - @p_wherenoind_num_l)*rand() ,0) ) ;

set @p_select_num = (select round( @p_select_num_l +( @p_select_num_u - @p_select_num_l)*rand() ,0) ) ;
set @p_orderby_num = (select round( @p_orderby_num_l +( @p_orderby_num_u - @p_orderby_num_l)*rand() ,0) ) ;
set @p_desc_prb = (select @p_desc_prb_l +( @p_desc_prb_u - @p_desc_prb_l)*rand() ) ;
set @p_joinnoind_prb = (select @p_joinnoind_prb_l +( @p_joinnoind_prb_u - @p_joinnoind_prb_l)*rand() ) ;
set @p_rami_prb = (select @p_rami_prb_l +( @p_rami_prb_u - @p_rami_prb_l)*rand() ) ;

set @p_groupby_prb = (select @p_groupby_prb_l +( @p_groupby_prb_u - @p_groupby_prb_l)*rand() ) ;
set @p_orderby_prb = (select @p_orderby_prb_l +( @p_orderby_prb_u - @p_orderby_prb_l)*rand() ) ;
set @p_semianti_prb = (select @p_semianti_prb_l +( @p_semianti_prb_u - @p_semianti_prb_l)*rand() ) ;
set @p_semi_rto = (select @p_semi_rto_l +( @p_semi_rto_u - @p_semi_rto_l)*rand() ) ;
set @p_level_max = (select round( @p_level_max_l +( @p_level_max_u - @p_level_max_l)*rand() ,0) ) ;

set @p_recur_prb = (select @p_recur_prb_l +( @p_recur_prb_u - @p_recur_prb_l)*rand() ) ;
set @p_union_prb = (select @p_union_prb_l +( @p_union_prb_u - @p_union_prb_l)*rand() ) ;
set @p_union_rto = (select @p_union_rto_l +( @p_union_rto_u - @p_union_rto_l)*rand() ) ;

exec proc_makesql
@p_tab_num
,@p_outer_prb
,@p_joincond_num
,@p_whereind_num
,@p_wherenoind_num
,@p_select_num
,@p_orderby_num
,@p_desc_prb
,@p_joinnoind_prb
,@p_rami_prb
,@p_groupby_prb
,@p_orderby_prb
,@p_semianti_prb
,@p_semi_rto
,0 -- @p_level
,@p_level_max
,@vsql out
,@p_recur_prb
,@p_union_prb
,@p_union_rto
,0; -- @p_union_flg

set @vsql = @vsql+' ; ';
select @vsql

set @wk1 = @wk1+1;
end;

end
go

exec main