select句別名をgroup byやorder byで使用できるか確認

 

OracleSQL Serverはgroup byで別名を使用できない

(8.0.29)

drop table tab1 ;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> OK

(19c)

drop table tab1 purge;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
commit;
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> ORA-00904: "COL3": 無効な識別子です。

(14)


drop table tab1 ;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> OK

 

(2019)

drop table tab1 ;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> 列名 'col3' が無効です。

 

プロシージャのオーバーロード

PostgreSQLオーバーロード可能
Oracleはパッケージ内のプロシージャならオーバーロード可能
MySQLSQL Serverオーバーロード不可

 

(8.0.29)

サポートされていない模様
念のため確認

drop procedure proc1;

delimiter //
create procedure proc1(
 in    param1 numeric
)
begin
  select param1;
end
//
delimiter ;

delimiter //
create procedure proc1(
 in    param1 varchar(30)
)
begin
  select param1;
end
//
delimiter ;


select *
from information_schema.routines
where routine_type = 'PROCEDURE'
and routine_name = 'proc1'\G


オーバーロードをサポートしていない

 

(19c)

-- 1. プロシージャの場合

create or replace procedure proc1(
 param1 in     number
)
as
begin
  dbms_output.put_line( param1 );
end;
/
show error;

create or replace procedure proc1(
 param1 in     varchar2
)
as
begin
  dbms_output.put_line( param1 );
end;
/
show error;

create or replace procedure proc1(
 param1 in     number
,param2 in     varchar2
)
as
begin
  dbms_output.put_line( param1 );
  dbms_output.put_line( param2 );
end;
/
show error;

select * from user_objects where object_type = 'PROCEDURE';

set serveroutput on
exec proc1(1);
exec proc1('A');
exec proc1(2,'B');

オーバーロードをサポートしていない

-- 2. プロシージャの場合

create or replace package pkg1
as
  procedure proc1  (param1 in number);
  procedure proc1  (param1 in varchar2);
  procedure proc1  (param1 in number, param2 in varchar2);
end;
/
show error;

create or replace package body pkg1
as
  procedure proc1(
   param1 in     number
  )
  as
  begin
    dbms_output.put_line( param1 );
  end;

  procedure proc1(
   param1 in     varchar2
  )
  as
  begin
    dbms_output.put_line( param1 );
  end;

  procedure proc1(
   param1 in     number
  ,param2 in     varchar2
  )
  as
  begin
    dbms_output.put_line( param1 );
    dbms_output.put_line( param2 );
  end;
end;
/
show error;

set serveroutput on
exec pkg1.proc1(1);
exec pkg1.proc1('A');
exec pkg1.proc1(2,'B');

オーバーロードをサポートしている

 

(14)


create or replace procedure proc1(
 param1 in     numeric
)
language plpgsql
as $$
declare
begin
  raise info '%', param1;
end;
$$;

create or replace procedure proc1(
 param1 in     varchar
)
language plpgsql
as $$
declare
begin
  raise info '%', param1;
end;
$$;

create or replace procedure proc1(
 param1 in     numeric
,param2 in     varchar
)
language plpgsql
as $$
declare
begin
  raise info '%', param1;
  raise info '%', param2;
end;
$$;


\df proc1

 

call proc1(1);
call proc1('A');
call proc1(2,'B');


オーバーロードをサポートしている

 

(2019)

サポートされていない模様
念のため試してみる

create or alter procedure proc1(
 @param1  numeric
)
as
begin
  print @param1;
end
go

create or alter procedure proc1(
 @param1  numeric
,@param2  varchar
)
as
begin
  print @param1;
end
go

select * from sys.objects where type in ('P');
go

exec proc1 1;
go
exec proc1 1,'A';
go

オーバーロードをサポートしていない

 

プロシージャの再帰呼び出し

 

4DBともにプロシージャの再帰実行は可能
ただし、MySQLはサーバパラメータの変更が必要

(8.0.29)

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


drop procedure proc1;

delimiter //
create procedure proc1(
 in   n numeric
,out  m numeric
)
begin
  declare wk1 numeric;
  declare wk2 numeric;
  
  if n = 0 then
    set m = 0;
  elseif n = 1 then
    set m = 1;
  else
    call proc1(n-2,wk1);
    call proc1(n-1,wk2);
    set m = wk1+wk2;
  end if;
end
//
delimiter ;


call proc1(10,@wk1);
select @wk1;


drop procedure proc2;

delimiter //
create procedure proc2()
begin
  declare wk1 numeric;
  declare wk2 numeric;
  
  set wk1 = 0;
  while wk1 <= 20 do
    call proc1(wk1,wk2);
    select wk1,wk2;
    set wk1 = wk1 + 1;
  end while;
end
//
delimiter ;

call proc2();

 

--------------------------------

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


drop procedure proc1;

delimiter //
create procedure proc1(
in   param1 numeric
)
begin
declare i int;
declare wk1 int;

if param1 = 0 then
  drop temporary table if exists rec;
  create temporary table if not exists rec(
   id    int
  ,col1  numeric
  ,col2  varchar(30)
  );
end if;

set wk1 = ( select count(*) from rec );
set i = 1;
while i <= 3 do
  insert into rec values(wk1 + i, param1 * 100 + i, param1 * 100 + i);
  set i = i + 1;
end while;


if param1 < 5 then
  call proc1(param1+1);
end if;

if param1 = 0 then
  select * from rec;
end if;

end
//
delimiter ;


call proc1(0);

 

 

 

(19c)
https://qiita.com/nkojima/items/a046b4c295ed75977626


create or replace procedure proc1(
  n in  number
, m out number
)
is
  wk1 number;
  wk2 number;
begin
  if n = 0 then
    m := 0;
  elsif n = 1 then
    m := 1;
  else
    proc1(n-2,wk1);
    proc1(n-1,wk2);
    m := wk1+wk2;
  end if;
end;
/
sho error


variable wk1 number
exec proc1(10, :wk1)
print wk1


set serveroutput on
declare
  wk1 number;
begin
  for i in 0..20 loop
    proc1(i,wk1);
    dbms_output.put_line(i||':'||wk1);
  end loop;
end;
/

 

--------------------------------

https://itsakura.com/oracle-plsql-function-ret

-- ネストした表のタイプ作成

drop type gar;
drop type grec;

create or replace type grec as object(
 id   int
,col1 number
,col2 varchar2(30)
);
/

create or replace type gar as 
table of grec;
/


set serveroutput on
create or replace procedure proc1(
 param1 in     number
,param2 out    gar
)
as
ar1 gar := gar();
ar2 gar := gar();
wk1 int;
begin

  for i in 1..3 loop
    ar1.extend;
    ar1(i) := grec(i,param1 * 100 + i, param1 * 100 + i);
  end loop;

  if param1 < 5 then
    proc1(param1+1,ar2);
    wk1 := ar1.count;
    for i in 1..ar2.count loop
      ar1.extend;
      wk1 := wk1+1;
      ar1(wk1) := ar2(i);
    end loop;
  end if;
  
  if param1 = 0 then
    for i in 1..ar1.count loop
      dbms_output.put_line('ar1(' || i || '): ' || ar1(i).col1 || ',' || ar1(i).col2 );
    end loop;
  else
    param2 := ar1;
  end if;
end;
/
show error;


declare
ar1 gar := gar();
begin
  proc1(0,ar1);
end;
/

 

 

(14)

create or replace procedure proc1(
  n in     numeric
, m out    numeric
)
language plpgsql
as $$
declare
  wk1 numeric;
  wk2 numeric;
begin
  if n = 0 then
    m := 0;
  elsif n = 1 then
    m := 1;
  else
    call proc1(n-2,wk1);
    call proc1(n-1,wk2);
    m := wk1+wk2;
  end if;
end;
$$;

 

call proc1(10, null);

 

do $$
declare
  wk1 numeric;
begin
  for i in 0..20 loop
    call proc1(i,wk1);
    raise info '%:%', i,wk1;
  end loop;
end;
$$;

 

--------------------------------

drop type if exists grec CASCADE;
create type grec as(
 id   int
,col1 numeric
,col2 varchar(30)
);

\df proc1

create or replace procedure proc1(
 param1 in     numeric
,param2 out    grec
)
language plpgsql
as $$
declare
ar1 grec
;
ar2 grec[];
wk1 numeric;
begin

  for i in 1..3 loop
    ar1[i].id   := i;
    ar1[i].col1 := param1 * 100 + i;
    ar1[i].col2 := param1 * 100 + i;
  end loop;

  if param1 < 5 then
    call proc1(param1+1,ar2);
    
    wk1 := array_upper(ar1, 1);
    for i in 1..array_upper(ar2, 1) loop
      wk1 := wk1+1;
      ar1[wk1] := ar2[i];
    end loop;
  end if;
  
  if param1 = 0 then
    for i in 1..array_upper(ar1, 1) loop
      raise info 'ar1(%): %,%', i,ar1[i].col1,ar1[i].col2;
    end loop;
  else
    param2 := ar1;
  end if;
end;
$$;


\df proc1


call proc1(0, null );

 

 

  • SQL Server

(2019)

create or alter procedure proc1(
  @n  numeric
, @m  numeric out
)
as
begin
  set nocount on;
  declare @wk1 numeric;
  declare @wk2 numeric;
  declare @wk3 numeric;
  declare @wk4 numeric;  
  if @n = 0
    set @m = 0;
  else if @n = 1
    set @m = 1;
  else
    begin
      set @wk1 = @n-2;
      set @wk2 = @n-1;
      exec proc1 @wk1 ,@wk3 out;
      exec proc1 @wk2 ,@wk4 out;
      set @m = @wk3+@wk4;
  end;
end
go

declare @wk1 numeric;
exec proc1 10,@wk1 out;
print @wk1;


set nocount on
declare @wk1 numeric;
declare @wk2 numeric;
set @wk1 = 0;
while @wk1 <= 20
begin
  exec proc1 @wk1 ,@wk2 out;
  print cast(@wk1 as varchar)+':'+cast(@wk2 as varchar);
  set @wk1 = @wk1 + 1;
end;
go

 

--------------------------------

 

create or alter procedure proc1(
 @param1  numeric
)
as
begin
  set nocount on;
  declare @i int;
  declare @wk1 int;
  declare @wk2 int;

  if @param1 = 0
    begin
      drop table if exists #rec;
      create table #rec(
       id    int
      ,col1  numeric
      ,col2  varchar(30)
      );
    end;

  set @wk1 = ( select count(*) from #rec );
  set @i = 1;
  while @i <= 3
    begin
      insert into #rec values(@wk1 + @i, @param1 * 100 + @i, @param1 * 100 + @i);
      set @i = @i + 1;
    end;

  if @param1 < 5
    begin
      set @wk1 = @param1+1;
      exec proc1 @wk1;
    end;
  if @param1 = 0
    select * from #rec;
end
go

exec proc1 0;

 

 

{IoT Core}3. IoT Core のセットアップ

 

https://catalog.us-east-1.prod.workshops.aws/workshops/b3e0b830-79b8-4c1d-8a4c-e10406600035/ja-JP

https://qiita.com/ekzemplaro/items/a95df050833204c266fb


モノ -- 証明書 -- ポリシー


-- 1. コマンド等のインストール

-- 1.1 aws cli version 2 インストール

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install
aws --version

 

-- 1.2 jqインストール
sudo yum -y install jq


-- 2. IoT Core のセットアップ


-- 2.1 ポリシーの作成

vim policy01.json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "iot:*",
      "Resource": "*"
    }
  ]
}


aws iot create-policy \
--policy-name policy01 \
--policy-document file://policy01.json

aws iot list-policies

aws  iot get-policy \
--policy-name policy01


-- 2.2 モノの作成

aws iot create-thing \
--thing-name thing01

aws iot list-things

aws iot describe-thing \
--thing-name thing01


-- 2.3 証明書の作成

cd

aws iot create-keys-and-certificate \
--set-as-active \
--certificate-pem-outfile  certificate.pem.crt \
--private-key-outfile  private.pem.key

aws iot list-certificates

ls -ltr


-- 2.4 モノに証明書をアタッチ


aws iot attach-thing-principal \
--thing-name thing01 \
--principal arn:aws:iot:ap-northeast-1:999999999999:cert/1111111111111111111111111111111111111111111111111111111111111111

aws iot list-thing-principals \
--thing-name thing01

 

-- 2.5 証明書にポリシーをアタッチ


aws iot attach-principal-policy \
--principal arn:aws:iot:ap-northeast-1:999999999999:cert/1111111111111111111111111111111111111111111111111111111111111111 \
--policy-name policy01

aws iot list-principal-policies \
--principal arn:aws:iot:ap-northeast-1:999999999999:cert/1111111111111111111111111111111111111111111111111111111111111111

 

 

-- 3. デバイスのセットアップ

-- 3.1 AWS IoT Device SDK Python v2 を使った dummy client の設定


pip3 install --user awsiotsdk

mkdir -p ~/environment/dummy_client/certs/
cd ~/environment/dummy_client/

wget https://awsj-iot-handson.s3-ap-northeast-1.amazonaws.com/aws-iot-core-workshop/dummy_client/device_main.py -O device_main.py


-- 3.2 ルートCA証明書のダウンロード
cd ~/environment/dummy_client
wget https://www.amazontrust.com/repository/AmazonRootCA1.pem -O certs/AmazonRootCA1.pem


-- 3.3 Device SDK を使ったデバイスの起動

aws iot describe-endpoint \
--endpoint-type iot:Data-ATS


cd
cp certificate.pem.crt ~/environment/dummy_client/certs
cp private.pem.key ~/environment/dummy_client/certs

cd ~/environment/dummy_client/
python3 device_main.py --device_name thing01 --endpoint 22222222222222-ats.iot.ap-northeast-1.amazonaws.com

-- 3.4 疎通テスト

AWS IoT Coreのコンソール画面の「MQTT テストクライアント」から実施


data/thing01

 

-- 4. クリーンアップ

-- モノの削除

aws iot list-things

aws iot detach-thing-principal \
--thing-name thing01 \
--principal arn:aws:iot:ap-northeast-1:999999999999:cert/1111111111111111111111111111111111111111111111111111111111111111


aws iot delete-thing \
--thing-name thing01


-- 証明書の削除


aws iot list-certificates

aws iot update-certificate \
--certificate-id 1111111111111111111111111111111111111111111111111111111111111111 \
--new-status INACTIVE


aws iot delete-certificate \
--certificate-id 1111111111111111111111111111111111111111111111111111111111111111 \
--force-delete

 

-- ポリシーの削除

aws iot list-policies

aws iot delete-policy \
--policy-name policy01

 

 

 

 

 

 

 

{IoT Events}シンプルな step-by-step 例

https://dev.classmethod.jp/articles/aws-iot-events-ga/

https://docs.aws.amazon.com/ja_jp/iotevents/latest/developerguide/iotevents-getting-started.html

https://docs.aws.amazon.com/ja_jp/iotevents/latest/developerguide/iotevents-simple-example.html


-- 1. コマンド等のインストール

-- 1.1 aws cli version 2 インストール

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install
aws --version

-- 1.2 jqインストール
sudo yum -y install jq

 

-- 2. SNSトピック作成

aws sns list-topics
aws sns list-subscriptions

aws sns create-topic --name topic01
aws sns create-topic --name topic02

aws sns subscribe \
--topic-arn arn:aws:sns:ap-northeast-1:999999999999:topic01 \
--protocol email \
--notification-endpoint hoge@example.com


aws sns subscribe \
--topic-arn arn:aws:sns:ap-northeast-1:999999999999:topic02 \
--protocol email \
--notification-endpoint hoge@example.com

 


-- 3. IAMポリシー作成

vim policy01.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "sns:*"
            ],
            "Effect": "Allow",
            "Resource": [ "arn:aws:sns:ap-northeast-1:999999999999:topic01",
                          "arn:aws:sns:ap-northeast-1:999999999999:topic02"
                        ]
        }
    ]
}


aws iam create-policy \
--policy-name policy01 \
--policy-document file://policy01.json


-- 4. IAMロール作成
vim role01.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Service": "iotevents.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json


-- 5. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01 \
--role-name role01

 

-- 6. デバイスデータをキャプチャするための入力を作成します

vim input01.json 
{
  "inputName": "input01",
  "inputDescription": "Pressure readings from a motor",
  "inputDefinition": {
    "attributes": [
      { "jsonPath": "sensorData.pressure" },
      { "jsonPath": "motorid" }
    ]
  }
}

aws iotevents create-input  --cli-input-json file://input01.json

aws iotevents list-inputs

aws iotevents describe-input \
--input-name input01

 


-- 7. デバイスの状態を表すディテクターモデルを作成する

vim model01.json
{
  "detectorModelName": "model01",
  "detectorModelDefinition": {
    "states": [
      {
        "stateName": "Normal",
        "onEnter": {
          "events": [
            {
              "eventName": "init",
              "condition": "true",
              "actions": [
                {
                  "setVariable": {
                    "variableName": "pressureThresholdBreached",
                    "value": "0"
                  }
                }
              ]
            }
          ]
        },
        "onInput": {
          "transitionEvents": [
            {
              "eventName": "Overpressurized",
              "condition": "$input.input01.sensorData.pressure > 70",
              "actions": [
                {
                  "setVariable": {
                    "variableName": "pressureThresholdBreached",
                    "value": "$variable.pressureThresholdBreached + 3"
                  }
                }
              ],
              "nextState": "Dangerous"
            }
          ]
        }
      }, 
      {
        "stateName": "Dangerous",
        "onEnter": {
          "events": [
            {
              "eventName": "Pressure Threshold Breached",
              "condition": "$variable.pressureThresholdBreached > 1",
              "actions": [
                {
                  "sns": {
                    "targetArn": "arn:aws:sns:ap-northeast-1:999999999999:topic01"
                  }
                }
              ]
            }
          ]
        },
        "onInput": {
          "events": [
            {
              "eventName": "Overpressurized",
              "condition": "$input.input01.sensorData.pressure > 70",
              "actions": [
                {
                  "setVariable": {
                    "variableName": "pressureThresholdBreached",
                    "value": "3"
                  }
                }
              ]
            },
            {
              "eventName": "Pressure Okay",
              "condition": "$input.input01.sensorData.pressure <= 70",
              "actions": [
                {
                  "setVariable": {
                    "variableName": "pressureThresholdBreached",
                    "value": "$variable.pressureThresholdBreached - 1"
                  }
                }
              ]
            }
          ],
          "transitionEvents": [
            {
              "eventName": "BackToNormal",
              "condition": "$input.input01.sensorData.pressure <= 70 && $variable.pressureThresholdBreached <= 1",
              "nextState": "Normal"
            }
          ]
        },
        "onExit": {
          "events": [
            {
              "eventName": "Normal Pressure Restored",
              "condition": "true",
              "actions": [
                {
                  "sns": {
                    "targetArn": "arn:aws:sns:ap-northeast-1:999999999999:topic02"
                  }
                }
              ]
            }
          ]
        }
      }
    ],
    "initialStateName": "Normal"
  },
  "key" : "motorid",
  "roleArn": "arn:aws:iam::999999999999:role/role01"
}


aws iotevents create-detector-model  --cli-input-json file://model01.json

aws iotevents list-detector-models


aws iotevents describe-detector-model \
--detector-model-name model01

 

-- 8. ディテクターへの入力としてメッセージを送信する

echo -n '{"motorid": "Fulton-A32", "sensorData": {"pressure": 80, "temperature": 39} }' | base64

 

vim high.json
{
  "messages": [
    {
      "messageId": "00001",
      "inputName": "input01",
      "payload": "eyJtb3RvcmlkIjogIkZ1bHRvbi1BMzIiLCAic2Vuc29yRGF0YSI6IHsicHJlc3N1cmUiOiA4MCwgInRlbXBlcmF0dXJlIjogMzl9IH0="
    }
  ]


aws iotevents-data batch-put-message --cli-input-json file://high.json

警告メールの受信確認


echo -n '{"motorid": "Fulton-A32", "sensorData": {"pressure": 60, "temperature": 29} }' | base64


vim normal.json
{
  "messages": [
    {
      "messageId": "00002",
      "inputName": "input01",
      "payload": "eyJtb3RvcmlkIjogIkZ1bHRvbi1BMzIiLCAic2Vuc29yRGF0YSI6IHsicHJlc3N1cmUiOiA2MCwgInRlbXBlcmF0dXJlIjogMjl9IH0="
    }
  ]


aws iotevents-data batch-put-message --cli-input-json file://normal.json


sed -i 's/"messageId": "00002"/"messageId": "00003"/' normal.json
aws iotevents-data batch-put-message --cli-input-json file://normal.json

sed -i 's/"messageId": "00003"/"messageId": "00004"/' normal.json
aws iotevents-data batch-put-message --cli-input-json file://normal.json


復旧メールの受信確認

 

-- 9. クリーンアップ


-- ディテクターモデルの削除

aws iotevents list-detector-models


aws iotevents delete-detector-model \
--detector-model-name model01


-- 入力の削除

aws iotevents list-inputs

aws iotevents delete-input \
--input-name input01

 

-- IAMロールの削除
aws iam list-roles | grep role01

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::999999999999:policy/policy01


aws iam delete-role --role-name role01


-- IAMポリシーの削除
aws iam list-policies | grep policy01

aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01

 

-- SNSトピック削除
aws sns list-topics
aws sns list-subscriptions


aws sns unsubscribe --subscription-arn arn:aws:sns:ap-northeast-1:999999999999:topic01:11111111-1111-1111-1111-111111111111
aws sns delete-topic --topic-arn arn:aws:sns:ap-northeast-1:999999999999:topic01

aws sns unsubscribe --subscription-arn arn:aws:sns:ap-northeast-1:999999999999:topic02:22222222-2222-2222-2222-222222222222
aws sns delete-topic --topic-arn arn:aws:sns:ap-northeast-1:999999999999:topic02

 

SQL文作成ツール

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

 

 

 

 

 

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

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

  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;

 

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

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


  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;

        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;
 
        wherenoindcols[wk2].col3 := va1;
 
        wk2 := wk2+1;
      end if;
    end loop;
  end loop;

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

 

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

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

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;


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

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;

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

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

 

{IoT Analytics}Getting started with AWS IoT Analytics (console)

 

https://catalog.us-east-1.prod.workshops.aws/workshops/03a4f79f-6971-441f-bc12-e8b755392d2c/ja-JP
https://docs.aws.amazon.com/iotanalytics/latest/userguide/quickstart.html#quickstart-create-channel


-- 1. コマンド等のインストール

-- 1.1 aws cli version 2 インストール

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install
aws --version

-- 1.2 jqインストール
sudo yum -y install jq

 

-- 2. S3バケットの作成

-- 2.1 S3バケットの作成(チャネル用)

aws s3 mb s3://bucket123ch

aws s3 ls

-- 2.2 S3バケットの作成(データストア用)

aws s3 mb s3://bucket123ds

aws s3 ls

-- 2.3 バケットポリシー設定(チャネル用)

vim b1.json

{
    "Version": "2012-10-17",
    "Id": "MyPolicyID",
    "Statement": [
        {
            "Sid": "MyStatementSid",
            "Effect": "Allow",
            "Principal": {
                "Service": "iotanalytics.amazonaws.com"
            },
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::bucket123ch",
                "arn:aws:s3:::bucket123ch/*"
            ]
        }
    ]
}

aws s3api put-bucket-policy \
--bucket bucket123ch \
--policy file://b1.json

aws s3api get-bucket-policy \
--bucket bucket123ch

-- 2.4 バケットポリシー設定(データストア用)

vim b2.json

{
    "Version": "2012-10-17",
    "Id": "MyPolicyID",
    "Statement": [
        {
            "Sid": "MyStatementSid",
            "Effect": "Allow",
            "Principal": {
                "Service": "iotanalytics.amazonaws.com"
            },
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::bucket123ds",
                "arn:aws:s3:::bucket123ds/*"
            ]
        }
    ]
}

aws s3api put-bucket-policy \
--bucket bucket123ds \
--policy file://b2.json

aws s3api get-bucket-policy \
--bucket bucket123ds

 

-- 3. ロールの作成(チャネル用)

-- 3.1 ポリシーの作成


vim policy01.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::bucket123ch",
                "arn:aws:s3:::bucket123ch/*"
            ]
        }
    ]
}


aws iam create-policy \
--policy-name policy01 \
--policy-document file://policy01.json


-- 3.2 ロールの作成

vim role01.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "iotanalytics.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

 

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json

-- 3.3 ポリシーをロールにアタッチ

aws iam attach-role-policy --policy-arn arn:aws:iam::999999999999:policy/policy01 --role-name role01


-- 4. ロールの作成(データストア用)

-- 4.1 ポリシーの作成


vim policy02.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:DeleteObject",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::bucket123ds",
                "arn:aws:s3:::bucket123ds/*"
            ]
        }
    ]
}

 

aws iam create-policy \
--policy-name policy02 \
--policy-document file://policy02.json


-- 4.2 ロールの作成

vim role02.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "iotanalytics.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

 

aws iam create-role \
--role-name role02 \
--assume-role-policy-document file://role02.json

-- 4.3 ポリシーをロールにアタッチ

aws iam attach-role-policy --policy-arn arn:aws:iam::999999999999:policy/policy02 --role-name role02


-- 5. ロールの作成(ログ用)

-- 5.1 ポリシーの作成


vim policy03.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:CreateLogStream"
            ],
            "Resource": [
                "arn:aws:logs:*:*:*"
            ]
        }
    ]
}

 

aws iam create-policy \
--policy-name policy03 \
--policy-document file://policy03.json


-- 5.2 ロールの作成

vim role03.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "iotanalytics.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

 

aws iam create-role \
--role-name role03 \
--assume-role-policy-document file://role03.json

-- 5.3 ポリシーをロールにアタッチ

aws iam attach-role-policy --policy-arn arn:aws:iam::999999999999:policy/policy03 --role-name role03

 

-- 6. AWS IoT Analyticsの作成

-- 6.1 チャネルの作成

aws iotanalytics create-channel \
--channel-name channel01 \
--channel-storage '{
            "customerManagedS3": {
                "bucket": "bucket123ch",
                "roleArn": "arn:aws:iam::999999999999:role/role01"
            }
        }'

aws iotanalytics list-channels

aws iotanalytics describe-channel \
--channel-name channel01

 

-- 6.2 データストアの作成

aws iotanalytics create-datastore \
--datastore-name store01 \
--datastore-storage '{
            "customerManagedS3": {
                "bucket": "bucket123ds",
                "roleArn": "arn:aws:iam::999999999999:role/role02"
            }
        }'

 

aws iotanalytics list-datastores

aws iotanalytics describe-datastore \
--datastore-name store01

-- 6.3 パイプラインの作成

aws iotanalytics create-pipeline \
--pipeline-name pipe01 \
--pipeline-activities ' [
            {
                "channel": {
                    "name": "10",
                    "channelName": "channel01",
                    "next": "20"
                }
            },
            {
                "datastore": {
                    "name": "20",
                    "datastoreName": "store01"
                }
            }
        ]'


aws iotanalytics list-pipelines

aws iotanalytics describe-pipeline \
--pipeline-name pipe01


-- 7. データセットの作成


-- 7.1 データ送信

vim main.py

# Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
# SPDX-License-Identifier: Apache-2.0
import boto3
import copy
from datetime import datetime, date, timedelta
import random
import uuid
import json

iota = boto3.client('iotanalytics')

# AWS IoT Analyticsに送信するデータの構造
DATA_TEMPLATE = {
  "timestamp": "",
  "device_name": "",
  "temperature": 0,
  "humidity": 0
}

# AWS IoT Analyticsのチャンネル名(自分の環境に合わせて修正してください)
CHANNEL_NAME="channel01"

# 過去30日分のダミーデータを作成する。もっと長い期間で試したい場合は、days=の数字を変えてください
now = datetime.now()
n_days_ago = now - timedelta(days=30)
start_time = int(n_days_ago.timestamp())
end_time = int(now.timestamp())

cnt = 0
total = 0
messages =

# 現在まで、1分ごとのデータを作成します
while end_time > start_time:
  dt = datetime.fromtimestamp(start_time)
  item = copy.copy(DATA_TEMPLATE)
  item["timestamp"] = dt.strftime("%Y-%m-%d %H:%M:%S")
  item["device_name"] = "test_device"
  item["temperature"] = random.randrange(10, 39)
  item["humidity"] = random.randrange(40, 99)

  messages.append({'messageId':str(uuid.uuid4()),'payload': json.dumps(item)})
  cnt = cnt + 1

  # batch putで送れるデータは、デフォルトだと100件までとなっています
  # https://docs.aws.amazon.com/iotanalytics/latest/userguide/limits.html
  if cnt >= 100:
    total = total + cnt
    print("batch put:", total)
    ret = iota.batch_put_message(channelName=CHANNEL_NAME, messages=messages)
    cnt = 0
    messages =

  start_time = start_time + 60

if cnt > 0:
  total = total + cnt
  print("batch put:", total)
  ret = iota.batch_put_message(channelName=CHANNEL_NAME, messages=messages)

 

pip3 install boto3
python3 main.py


-- 7.2 データセットの作成

aws iotanalytics create-dataset \
--dataset-name ds01 \
--actions "[
            {
                \"actionName\": \"action01\",
                \"queryAction\": {
                    \"sqlQuery\": \"SELECT date_trunc('hour', date_parse(timestamp, '%Y-%m-%d %H:%i:%S')) as dt_hour,\ndevice_name,\nround(avg(temperature), 1) as temp_avg,\nround(min(temperature), 1) as temp_min,\nround(max(temperature), 1) as temp_max,\nround(avg(humidity), 1) as hud_avg,\nround(min(humidity), 1) as hud_min,\nround(max(humidity), 1) as hud_max\nFROM store01\nGROUP BY 1, 2\",
                    \"filters\": []
                }
            }
        ]" \
--triggers ' [
            {
                "schedule": {
                    "expression": "cron(0/30 * * * ? *)"
                }
            }
        ]' \
--retention-period '{
  "unlimited": false,
  "numberOfDays": 3
}'

 

 


aws iotanalytics list-datasets

aws iotanalytics describe-dataset \
--dataset-name ds01

 

 

-- 7.3 データセットの取得

aws iotanalytics create-dataset-content \
--dataset-name ds01

aws iotanalytics get-dataset-content \
--dataset-name ds01

 

 

-- 8. IoT Analyticsのログ記録有効化

aws iotanalytics describe-logging-options

aws iotanalytics put-logging-options \
--logging-options '{
        "roleArn": "arn:aws:iam::999999999999:role/role03",
        "level": "ERROR",
        "enabled": true
    }'

 


-- 9. クリーンアップ

-- IoT Analyticsのログ記録無効化

aws iotanalytics describe-logging-options

aws iotanalytics put-logging-options \
--logging-options '{
        "roleArn": "arn:aws:iam::999999999999:role/role03",
        "level": "ERROR",
        "enabled": false
    }'

 

-- データセットの削除
aws iotanalytics list-datasets
aws iotanalytics delete-dataset \
--dataset-name ds01


-- パイプラインの削除
aws iotanalytics list-pipelines
aws iotanalytics delete-pipeline \
--pipeline-name pipe01


-- データストアの削除

aws iotanalytics list-datastores
aws iotanalytics delete-datastore \
--datastore-name store01

-- チャネルの削除

aws iotanalytics list-channels
aws iotanalytics delete-channel \
--channel-name channel01


-- ロールの削除
aws iam list-roles | grep role01
aws iam list-roles | grep role02
aws iam list-roles | grep role03

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::999999999999:policy/policy01

aws iam detach-role-policy \
--role-name role02 \
--policy-arn arn:aws:iam::999999999999:policy/policy02

aws iam detach-role-policy \
--role-name role03 \
--policy-arn arn:aws:iam::999999999999:policy/policy03

aws iam delete-role --role-name role01
aws iam delete-role --role-name role02
aws iam delete-role --role-name role03

-- ポリシーの削除
aws iam list-policies | grep policy01
aws iam list-policies | grep policy02
aws iam list-policies | grep policy03

aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01

aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy02

aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy03


-- S3バケット削除

aws s3 ls

aws s3 rb s3://bucket123ch --force
aws s3 rb s3://bucket123ds --force