sql oracle 畳み込みクエリ ファンクション版

# まえがき

[畳み込み演算](https://ja.wikipedia.org/wiki/%E7%95%B3%E3%81%BF%E8%BE%BC%E3%81%BF)の動きをファンクション化してみた。

# 環境

```bash
$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 水 11月 27 22:04:40 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 火 11月 26 2019 06:29:27 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
```

# スクリプト

```sql
create or replace type liz is table of varchar2(4000);
/

create or replace function conv(tgt varchar2,n number)
return liz
as
rt liz;
begin
    select
        cast(collect(substr(tgt,level,n)) as liz) into rt
    from dual
    connect by level<=length(tgt);
    return rt;
end;
/
```


テスト

```sql
$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 水 11月 27 22:04:40 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 火 11月 26 2019 06:29:27 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
USER01@ORCLPDB01> column column_value for a30
USER01@ORCLPDB01> select column_value from table(conv('abcba',null));

レコードが選択されませんでした。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',-1));

レコードが選択されませんでした。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',0));

レコードが選択されませんでした。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',1));

COLUMN_VALUE
------------------------------
a
b
c
b
a

5行が選択されました。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',2));

COLUMN_VALUE
------------------------------
ab
bc
cb
ba
a

5行が選択されました。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',3));

COLUMN_VALUE
------------------------------
abc
bcb
cba
ba
a

5行が選択されました。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',4));

COLUMN_VALUE
------------------------------
abcb
bcba
cba
ba
a

5行が選択されました。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',5));

COLUMN_VALUE
------------------------------
abcba
bcba
cba
ba
a

5行が選択されました。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('abcba',6));

COLUMN_VALUE
------------------------------
abcba
bcba
cba
ba
a

5行が選択されました。

経過: 00:00:00.00
USER01@ORCLPDB01> select column_value from table(conv('🐭,🐮,🐱,🐵,',1));

COLUMN_VALUE
------------------------------
🐭
,
🐮
,
🐱
,
🐵
,

8行が選択されました。

経過: 00:00:00.01
USER01@ORCLPDB01> select column_value from table(conv('あ,いい,ccc,dddd,eeeee,',1));

COLUMN_VALUE
------------------------------
あ
,
い
い
,
c
c
c
,
d
d
d
d
,
e
e
e
e
e
,

20行が選択されました。

経過: 00:00:00.01
```

# あとがき

なんかの役に立つとは思っている。split的な使い方できそう。

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


# 20191127追記

使いどころ見つけたので、追記。

[L-systems の実例](https://ja.wikipedia.org/wiki/L-system#%E4%BE%8B1%EF%BC%9A%E8%97%BB%E9%A1%9E)に記載されている藻類のパタン生成をやってみようと思ったら使うことがあった。

たとえば以下のような区切り文字がない状態の文字列の場合が得られたとき。

```sql
with rec (grp,tgt,done)as(
select
    1
    ,:tgt
    ,case
        when substr(:tgt,1,1)='a' then replace(substr(:tgt,1,1),'a','ab') 
        when substr(:tgt,1,1)='b' then replace(substr(:tgt,1,1),'b','a') 
    end
from dual
union all
select
    grp+1
    ,:tgt
    ,done||case
        when substr(done,grp+1,1)='a' then replace(substr(done,grp+1,1),'a','ab') 
        when substr(done,grp+1,1)='b' then replace(substr(done,grp+1,1),'b','a') 
    end
from rec where grp<length(replace(:tgt,',',''))
)select grp,tgt,done,conv(done,1),strliz(conv(done,1)) from rec;
```

生成結果をカンマ区切りリストに変換できた。strlizとのコンボも相まって。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/3c98a32e-b16b-b667-6e74-00cc5fe7e74e.png)


```sql
create or replace function strliz(rsv_args liz)
return varchar2
as
rt varchar2(4000);
begin
    select listagg(column_value,',')within group(order by rownum) into rt from table(rsv_args);
    return rt;
end;
/

create or replace function mk_lsys_ptn(rsv_args varchar2)
return varchar2
as
rt varchar2(4000);
begin
    with rec (grp,tgt,done)as(
    select
        1
        ,rsv_args
        ,case
            when substr(rsv_args,1,1)='a' then replace(substr(rsv_args,1,1),'a','ab') 
            when substr(rsv_args,1,1)='b' then replace(substr(rsv_args,1,1),'b','a') 
        end
    from dual
    union all
    select
        grp+1
        ,rsv_args
        ,done||case
            when substr(done,grp+1,1)='a' then replace(substr(done,grp+1,1),'a','ab') 
            when substr(done,grp+1,1)='b' then replace(substr(done,grp+1,1),'b','a') 
        end
    from rec where grp<length(replace(rsv_args,',',''))
    )select max(strliz(conv(done,1))) into rt from rec having count(*)=max(grp);
    return rt;
end;
/
```

実際の使用感

```sql
with rec (grp,ptn)as(
    select 1,:init from dual
    union all
    select grp+1,mk_lsys_ptn(ptn) from rec
    where grp+1<=:end_grp
)select * from rec;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/8e61d415-f0ea-52ea-4630-fd63de07ae8c.png)