sqlでsqlを組み立てる_001_003

まえがき

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

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

データ投入してみます。

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です。。

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
;

【実行時例】

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

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

【build_bind_001.sqlの取得結果】

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

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の取得結果】

とれました！

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

【build_bind_002.sqlの取得結果】

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

return_bind_002.sql

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

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

【return_bind_002.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の取得結果】

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

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を入れてみます。

とれました！

ManageSqlのメンテナンス

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

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ﾄｶ(ｼﾞｯｺｳｼﾞｭﾝｼﾞｮﾚﾂﾄｶﾓｳｹﾃﾄｶ)ｶﾞﾒﾝﾃﾗｸﾆﾅﾙｶﾓ｡｡(ﾃｷﾄｰ)