まえがき
畳み込み演算の動きをファンクション化してみた。
環境
$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
に接続されました。
スクリプト
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;
/
テスト
$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 の実例に記載されている藻類のパタン生成をやってみようと思ったら使うことがあった。
たとえば以下のような区切り文字がない状態の文字列の場合が得られたとき。
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とのコンボも相まって。
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;
/
実際の使用感
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;
