sqlでsqlを組み立てる_001_003

#まえがき

【ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方(Oracle)】
https://qiita.com/tlokweng/items/ead23e3ebd194f0fe851

@tlokwengさんの上記の記事を拝見させていただいて、
やってみたいことが浮かんだので、記事にしてみようかなと思いました。
こういうやり方もあるのかと驚くことばかりでいつもお世話になっています。


データ投入してみます。

```sql:init.sql

drop table ManageSql;

CREATE TABLE managesql (
	sql_manage_id          CHAR(3) NOT NULL
	, sql_manage_seq         NUMBER(3) NOT NULL
	, sql_articulation_kbn   CHAR(1) NULL
	, ref_tab                VARCHAR2(4000) NULL
	, col_order              NUMBER(3) NULL
	, ref_col                VARCHAR2(4000) NULL
	, proc_func              VARCHAR2(4000) NULL
	, opera              VARCHAR2(30) NULL
	, extract_val            VARCHAR2(4000) NULL
	, descend                CHAR(1) NULL
	, PRIMARY KEY ( sql_manage_id
	, sql_manage_seq )
);

SET DEFINE OFF;--これないと&(アンパサンド)が置換変数みたいに解釈されてﾎﾟｯﾌﾟｱｯﾌﾟ毎回求めてくる（&入れるようなデータないけど。。）
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',1,'1','EMP',1,'EMPNO',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',2,'1','EMP',2,'ENAME',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',3,'1','EMP',3,'JOB',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',4,'1','EMP',4,'MGR',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',5,'1','EMP',5,'HIREDATE',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',6,'1','EMP',6,'SAL',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',7,'1','EMP',7,'COMM',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',8,'1','EMP',8,'DEPTNO',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',9,'2','EMP',null,null,null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',10,'5','EMP',1,'EMPNO',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',11,'5','EMP',2,'ENAME',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',12,'5','EMP',3,'JOB',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',13,'5','EMP',4,'MGR',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',14,'5','EMP',5,'HIREDATE',null,null,null,'1');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',15,'5','EMP',6,'SAL',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',16,'5','EMP',7,'COMM',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',17,'5','EMP',8,'SAL',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',18,'3','EMP',1,'DEPTNO',null,'=','10',null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('001',19,'3','EMP',2,'SAL',null,'>=','1000',null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('002',1,'1','EMP',1,'DEPTNO',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('002',2,'1','EMP',2,'SAL','SUM(SAL)',null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('002',3,'2','EMP',null,null,null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('002',4,'4','EMP',1,'DEPTNO',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('002',5,'5','EMP',1,'DEPTNO',null,null,null,'0');
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',1,'1','dept_emp',null,'EMPNO',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',2,'1','dept_emp',null,'ENAME',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',3,'1','dept_emp',null,'JOB',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',4,'1','dept_emp',null,'MGR',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',5,'1','dept_emp',null,'HIREDATE',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',6,'1','dept_emp',null,'SAL',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',7,'1','dept_emp',null,'COMM',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',8,'1','dept_emp',null,'DEPTNO',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',9,'1','dept_emp',null,'DNAME',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',10,'1','dept_emp',null,'LOC',null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',11,'2','dept_emp',null,null,null,null,null,null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',12,'3','dept_emp',null,'JOB',null,'=',':JOB',null);
Insert into MANAGESQL (SQL_MANAGE_ID,SQL_MANAGE_SEQ,SQL_ARTICULATION_KBN,REF_TAB,COL_ORDER,REF_COL,PROC_FUNC,OPERA,EXTRACT_VAL,DESCEND) values ('003',13,'5','dept_emp',null,'EMPNO',null,null,null,'1');

commit;

```

そして、↓がsql作るためのsqlです。。


```sql:build.sql

select
	to_clob(build_sql.b_sql) as b_sql
from
	(
		with select_clause as (
			select
				s1.sql_manage_id
				,Listagg(nvl(s1.proc_func,s1.ref_col), ',') within group (order by S1.COL_ORDER) as  select_cla
			from
				managesql s1
			where
				s1.sql_manage_id =:sql_manage_id
			and s1.sql_articulation_kbn = '1'
			group by
				s1.sql_manage_id
		)
		,from_clause as (
			select
				s1.sql_manage_id
				,S1.REF_TAB as FROM_CLA
			from
				managesql s1
			where
				s1.sql_manage_id =:sql_manage_id
			and s1.sql_articulation_kbn = '2'
			group by
				s1.sql_manage_id
				,S1.REF_TAB
		)
		,where_clause as (
			select
				s1.sql_manage_id
				,LISTAGG(nvl(s1.proc_func,s1.ref_col) || ' ' || s1.OPERA || ' ' ||  case when regexp_count(s1.EXTRACT_VAL,':') > 0 then '' else '''' end  || s1.EXTRACT_VAL ||    case when regexp_count(s1.EXTRACT_VAL,':') > 0 then '' else '''' end  ,' and ') WITHIN group(order by s1.col_order) as where_cla
			from
				managesql s1
			where
				s1.sql_manage_id =:sql_manage_id
			and s1.sql_articulation_kbn = '3'
			group by
				s1.sql_manage_id)
		,groupby_clause as (
			select
				s1.sql_manage_id
				,Listagg(nvl(s1.proc_func,s1.ref_col), ',') within group (order by S1.COL_ORDER) as  groupby_cla
			from
				managesql s1
			where
				s1.sql_manage_id =:sql_manage_id
			and s1.sql_articulation_kbn = '4'
			group by
				s1.sql_manage_id
		)
		,orderby_clause as (
			select
				s1.sql_manage_id
				,LISTAGG(nvl(s1.proc_func,s1.ref_col) || ' ' || case when DESCEND = '0' then 'ASC' else 'DESC' end,',') within group ( order by s1.col_order) as orderby_cla
			from
				managesql s1
			where
				s1.sql_manage_id =:sql_manage_id
			and s1.sql_articulation_kbn = '5'
			group by
				s1.sql_manage_id
		)
		select
			' select '
			|| S1.SELECT_CLA
			||' from '
			|| S2.FROM_CLA
			|| case when s3.sql_manage_id is null then '' else ' where ' end
			|| case when s3.sql_manage_id is null then '' else S3.WHERE_CLA end
			|| case when s4.sql_manage_id is null then '' else ' group by ' end
			|| case when s4.sql_manage_id is null then '' else S4.GROUPBY_CLA end
			|| case when s5.sql_manage_id is null then '' else ' order by ' end
			|| case when s5.sql_manage_id is null then '' else S5.ORDERBY_CLA end
			||';'
			as b_sql
		from
			select_clause s1
				left outer join from_clause s2
					on
						s1.sql_manage_id = s2.sql_manage_id
				left outer join where_clause s3
					on
						s1.sql_manage_id = s3.sql_manage_id
				left outer join groupby_clause s4
					on
						s1.sql_manage_id = s4.sql_manage_id
				left outer join orderby_clause s5
					on
						s1.sql_manage_id = s5.sql_manage_id
	) build_sql
where
	rownum <= 1
;

```
【実行時例】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/8487d7f8-35a4-87e2-9284-9fdcb8f6a983.png)

作りたいsqlが紐づいているsql管理idをバインド変数で入力し、実行します。

sql管理idに001をいれて実行します。

【build_bind_001.sqlの取得結果】

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/5bcd099b-a31b-2d67-f84b-eb7cc4bd5683.png)


返却されてきたsql(return.sql)をCtrl + F7で整形し、いけてるか確認します。


```sql:return_bind_001.sql

SELECT
	empno
	, ename
	, job
	, mgr
	, hiredate
	, sal
	, comm
	, deptno
FROM
	emp
WHERE
	deptno = '10'
	AND sal >= '1000'
ORDER BY
	empno ASC
	, ename ASC
	, job ASC
	, mgr ASC
	, hiredate DESC
	, sal ASC
	, comm ASC
	, sal ASC;



```

いけてそうなので、流れに身を任せて実行します。

【return_bind_001.sqlの取得結果】

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/9cd23091-6336-32a5-b0f6-d2b84d5f9216.png)


とれました！


sql管理idに002をいれて実行します。

【build_bind_002.sqlの取得結果】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/4dd668e4-df26-8555-3645-e837be325d6d.png)


返却されてきたsql(return.sql)をCtrl + F7で整形し、いけてるか確認します。

```sql:return_bind_002.sql

SELECT
	deptno
	, SUM(sal)
FROM
	emp
GROUP BY
	deptno
ORDER BY
	deptno ASC;


```

いけてそうなので、流れに身を任せて実行します。

【return_bind_002.sqlの取得結果】

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/4d7b7dc9-db6f-3a21-2ca7-3da0f86d9d78.png)


とれました！

結合どうしましょう。

ビューにしてみます。

```sql:create_view.sql

create or replace view dept_emp  (
	DEPTNO
	,DNAME
	,LOC
	,EMPNO
	,ENAME
	,JOB
	,MGR
	,HIREDATE
	,SAL
	,COMM
)as
select
	s1.DEPTNO
	,s1.DNAME
	,s1.LOC
	,s2.EMPNO
	,s2.ENAME
	,s2.JOB
	,s2.MGR
	,s2.HIREDATE
	,s2.SAL
	,s2.COMM
from
	dept s1
		inner join emp s2
			on
				s1.deptno = s2.deptno
;


```

sql管理idに003をいれて実行します。

【build_bind_003.sqlの取得結果】

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/d992edce-4ab9-6b97-206d-87d4ce0c0293.png)

返却されてきたsql(return.sql)をCtrl + F7で整形し、いけてるか確認します。


```sql:return_bind_003.sql

SELECT
	comm
	, deptno
	, dname
	, empno
	, ename
	, hiredate
	, job
	, loc
	, mgr
	, sal
FROM
	dept_emp
WHERE
	job =:job
ORDER BY
	empno DESC;


```

いけてそうなので、流れに身を任せて実行します。


【return_bind_003.sqlの取得結果】

バインド変数にはSALESMANを入れてみます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/c4f3d1bf-5bea-d35c-20a3-1cbc8a1b1292.png)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/229e3cf4-f2cf-3b7b-ef76-bbc943ec2d57.png)


とれました！

#ManageSqlのメンテナンス

こんなかんじでデータいれます。。なれるまでたいへん。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/06e9437c-7f9b-9c21-3879-1898a6987844.png)

sql_articulation_kbnの仕様としては

1がselect句
2がfrom句
3がwhere句
4がgroup by句
5がorder by句
にいったんしています。のこり6,7,8,9があいています。。

相関サブクエリは木構造とかで表現できそうかもです。。
親のメインクエリのsql管理idを保持する「親sql管理id」とか「別名列」とか。
「演算子」まわりも軽い気持ちではじめたので、
他にどんな列が必要かあまり煮詰められてないですね。。バリエーション多いので。
DMLとか。。（insert delete update merge select-insert...）


でも面白そうです！

#あとがき

sql管理idを連番３桁ゼロ埋めで採番したので、
buildできたsqlが発見できたら、upしていこうと思います。

こういうテーブル設計で、
こんなbuildのほうがいいかもみたいなのがあれば、
共有していただけると、幸いです。

以上、ありがとうございました。









以下は自分用のメモです↓気になさらないでください。
（字幅削減＆目立たないように半角で書きました。）
｢ｺﾉｷﾉｳﾃﾞﾊｺﾉsqlｶﾝﾘidﾃﾞｶﾝﾘｻﾚﾃｲﾙsqlｦﾂｶｳ」ﾐﾀｲﾅﾉｶﾞｷﾏﾚﾊﾞ､
ｷﾉｳﾍﾞﾂsqlｼﾖｳｶﾝﾘﾃｰﾌﾞﾚﾙﾐﾀｲﾅﾉﾂｸｯﾃﾐﾙﾄｶ｡｡(ﾃｷﾄｰ)
crudｽﾞﾔerｽﾞﾔjobflowﾄｶ(ｼﾞｯｺｳｼﾞｭﾝｼﾞｮﾚﾂﾄｶﾓｳｹﾃﾄｶ)ｶﾞﾒﾝﾃﾗｸﾆﾅﾙｶﾓ｡｡(ﾃｷﾄｰ)