collect関数でmapが作成できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

タイプ作る

2つ今回は。

コード表示

drop type mp force;
/
drop type liz force;
/

create or replace type mp is object(ky clob,vl clob);
/
create or replace type liz is table of mp;
/


はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select * from sub;

           RN KY           VL
------------- ------------ ----
            0 001          A
            0 002          B
            0 003          C
            1 001          A
            1 002          B
            1 003          C
            1 004          D

7 rows selected.

Elapsed: 00:00:00.00

マップオブジェクトできた

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select rn,mp(ky,vl) as mp from sub;

実行例

コード表示

AINE@pdb1> col mp for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select rn,mp(ky,vl) as mp from sub;

           RN MP(KY, VL)
------------- ----------------------------------------------------------------------------------------------------
            0 MP('001', 'A')
            0 MP('002', 'B')
            0 MP('003', 'C')
            1 MP('001', 'A')
            1 MP('002', 'B')
            1 MP('003', 'C')
            1 MP('004', 'D')

7 rows selected.

Elapsed: 00:00:00.01

さまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select rn,cast(collect(mp(ky,vl)) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select rn,cast(collect(mp(ky,vl)) as liz) as liz from sub group by rn;

           RN LIZ(KY, VL)
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(MP('001', 'A'), MP('003', 'C'), MP('002', 'B'))
            1 LIZ(MP('001', 'A'), MP('004', 'D'), MP('003', 'C'), MP('002', 'B'))

2 rows selected.

Elapsed: 00:00:00.00

collect関数で入れ子できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

タイプ作る

3つ今回は。

コード表示

drop type val force;
/
drop type item force;
/
drop type liz force;
/

create or replace type val is object(val clob);
/
create or replace type item is object(rn number,vl val);
/
create or replace type liz is table of item;
/

はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select * from sub;

           RN STR
------------- ------------
            0 001
            0 002
            0 003
            1 001
            1 002
            1 003
            1 004

7 rows selected.

Elapsed: 00:00:00.00

strをオブジェクト型に変換

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,val(str) as val from sub;

実行例

コード表示

AINE@pdb1> col val for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,val(str) as val from sub;

           RN VAL(VAL)
------------- --------------------------------------------------
            0 VAL('001')
            0 VAL('002')
            0 VAL('003')
            1 VAL('001')
            1 VAL('002')
            1 VAL('003')
            1 VAL('004')

7 rows selected.

Elapsed: 00:00:00.01

mapみたいにitemでラップ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select item(rn,val(str)) as item from sub;

実行例

コード表示

AINE@pdb1> col item for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select item(rn,val(str)) as item from sub;

ITEM(RN, VL(VAL))
--------------------------------------------------
ITEM(0, VAL('001'))
ITEM(0, VAL('002'))
ITEM(0, VAL('003'))
ITEM(1, VAL('001'))
ITEM(1, VAL('002'))
ITEM(1, VAL('003'))
ITEM(1, VAL('004'))

7 rows selected.

Elapsed: 00:00:00.01

最後はさまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

           RN LIZ(RN, VL(VAL))
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('003')), ITEM(0, VAL('002')))
            1 LIZ(ITEM(1, VAL('001')), ITEM(1, VAL('004')), ITEM(1, VAL('003')), ITEM(1, VAL('002')))

2 rows selected.

Elapsed: 00:00:00.01

サマル単位を指定しないと

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select cast(collect(item(rn,val(str))) as liz)  as liz from sub;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select cast(collect(item(rn,val(str))) as liz) as liz from sub;

LIZ(RN, VL(VAL))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('002')), ITEM(0, VAL('003')), ITEM(1, VAL('001')), ITEM(1, VAL('002')), ITEM(1, VAL('003')), ITEM(1, VAL('004')))

1 row selected.

Elapsed: 00:00:00.01

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

qiitaに書きました。

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

collection型 multiset演算子 単一と複数

まえがき

collection使いこなせるようになるための練習。表現の幅を増やすため。multiset演算子の動きを確かめる

collection型 単一

単一カラムのみをコレクションにするだけなら、オブジェクト型の宣言は不要。

コード表示

create or replace type liz_single as table of number;
/

WITH src_a AS(
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10
),src_b AS (
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10
)
,sub AS (
SELECT
    A
    ,b
    ,A MULTISET UNION b AS a_union_b
    ,b MULTISET UNION A AS b_union_a
    ,A MULTISET UNION ALL b AS a_union_all_b
    ,b MULTISET UNION ALL A AS b_union_all_a
    ,A MULTISET UNION DISTINCT b AS a_union_distinct_b
    ,b MULTISET UNION DISTINCT A AS b_union_distinct_a
    ,A MULTISET INTERSECT b AS a_intersect_b
    ,b MULTISET INTERSECT A AS b_intersect_a
    ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b
    ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a
    ,A MULTISET EXCEPT b AS a_except_b
    ,b MULTISET EXCEPT A AS b_except_a
    ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b
    ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a
FROM
(SELECT CAST(COLLECT(rn) AS liz_single) AS A FROM src_a) 
    CROSS JOIN (SELECT CAST(COLLECT(rn) AS liz_single) AS b FROM src_b)
)
SELECT
    *
FROM
    sub
    UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a))
;


実行例

コード表示

AINE@pdb1> col sts for a50
AINE@pdb1> col cols for a100
AINE@pdb1> WITH src_a AS( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10 ),src_b AS ( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10 ) ,sub AS ( SELECT A ,b ,A MULTISET UNION b AS a_union_b ,b MULTISET UNION A AS b_union_a ,A MULTISET UNION ALL b AS a_union_all_b ,b MULTISET UNION ALL A AS b_union_all_a ,A MULTISET UNION DISTINCT b AS a_union_distinct_b ,b MULTISET UNION DISTINCT A AS b_union_distinct_a ,A MULTISET INTERSECT b AS a_intersect_b ,b MULTISET INTERSECT A AS b_intersect_a ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a ,A MULTISET EXCEPT b AS a_except_b ,b MULTISET EXCEPT A AS b_except_a ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a FROM (SELECT CAST(COLLECT(rn) AS liz_single) AS A FROM src_a) CROSS JOIN (SELECT CAST(COLLECT(rn) AS liz_single) AS b FROM src_b) ) SELECT * FROM sub UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a)) ;

STS                                                COLS
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
A                                                  LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10)
B                                                  LIZ_SINGLE(3, 5, 6, 9, 10)
A_UNION_B                                          LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10, 3, 5, 6, 9, 10)
B_UNION_A                                          LIZ_SINGLE(3, 5, 6, 9, 10, 2, 3, 4, 6, 8, 9, 10)
A_UNION_ALL_B                                      LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10, 3, 5, 6, 9, 10)
B_UNION_ALL_A                                      LIZ_SINGLE(3, 5, 6, 9, 10, 2, 3, 4, 6, 8, 9, 10)
A_UNION_DISTINCT_B                                 LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10, 5)
B_UNION_DISTINCT_A                                 LIZ_SINGLE(3, 5, 6, 9, 10, 2, 4, 8)
A_INTERSECT_B                                      LIZ_SINGLE(3, 6, 9, 10)
B_INTERSECT_A                                      LIZ_SINGLE(3, 6, 9, 10)
A_INTERSECT_DISTINCT_B                             LIZ_SINGLE(3, 6, 9, 10)
B_INTERSECT_DISTINCT_A                             LIZ_SINGLE(3, 6, 9, 10)
A_EXCEPT_B                                         LIZ_SINGLE(2, 4, 8)
B_EXCEPT_A                                         LIZ_SINGLE(5)
A_EXCEPT_DISTINCT_B                                LIZ_SINGLE(2, 4, 8)
B_EXCEPT_DISTINCT_A                                LIZ_SINGLE(5)

16 rows selected.

Elapsed: 00:00:00.00

collection型 複数

複数カラムをコレクションにするなら、オブジェクト型の宣言は必要。

コード表示

create or replace type liz_typ is object (id number,qty number);
/
create or replace type liz_multi is table of liz_typ;
/

WITH src_a AS(
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10
),src_b AS (
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10
)
,sub AS (
SELECT
    A
    ,b
    ,A MULTISET UNION b AS a_union_b
    ,b MULTISET UNION A AS b_union_a
    ,A MULTISET UNION ALL b AS a_union_all_b
    ,b MULTISET UNION ALL A AS b_union_all_a
    ,A MULTISET UNION DISTINCT b AS a_union_distinct_b
    ,b MULTISET UNION DISTINCT A AS b_union_distinct_a
    ,A MULTISET INTERSECT b AS a_intersect_b
    ,b MULTISET INTERSECT A AS b_intersect_a
    ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b
    ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a
    ,A MULTISET EXCEPT b AS a_except_b
    ,b MULTISET EXCEPT A AS b_except_a
    ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b
    ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a
FROM
(SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS A FROM src_a) 
    CROSS JOIN (SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS b FROM src_b)
)
SELECT
    *
FROM
    sub
    UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a))
;

実行例

コード表示

AINE@pdb1> col sts for a25
AINE@pdb1> col cols for a145
AINE@pdb1> WITH src_a AS( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10 ),src_b AS ( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10 ) ,sub AS ( SELECT A ,b ,A MULTISET UNION b AS a_union_b ,b MULTISET UNION A AS b_union_a ,A MULTISET UNION ALL b AS a_union_all_b ,b MULTISET UNION ALL A AS b_union_all_a ,A MULTISET UNION DISTINCT b AS a_union_distinct_b ,b MULTISET UNION DISTINCT A AS b_union_distinct_a ,A MULTISET INTERSECT b AS a_intersect_b ,b MULTISET INTERSECT A AS b_intersect_a ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a ,A MULTISET EXCEPT b AS a_except_b ,b MULTISET EXCEPT A AS b_except_a ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a FROM (SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS A FROM src_a) CROSS JOIN (SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS b FROM src_b) ) SELECT * FROM sub UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a)) ;

STS                       COLS(ID, QTY)
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
A                         LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
B                         LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
A_UNION_B                 LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(3, 15), LIZ_TY
                          P(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

B_UNION_A                 LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TY
                          P(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

A_UNION_ALL_B             LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(3, 15), LIZ_TY
                          P(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

B_UNION_ALL_A             LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TY
                          P(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

A_UNION_DISTINCT_B        LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(5, 25))
B_UNION_DISTINCT_A        LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(2, 10), LIZ_TYP(4, 20), LIZ_TYP(8, 40))
A_INTERSECT_B             LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
B_INTERSECT_A             LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
A_INTERSECT_DISTINCT_B    LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
B_INTERSECT_DISTINCT_A    LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
A_EXCEPT_B                LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(4, 20), LIZ_TYP(8, 40))
B_EXCEPT_A                LIZ_MULTI(LIZ_TYP(5, 25))
A_EXCEPT_DISTINCT_B       LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(4, 20), LIZ_TYP(8, 40))
B_EXCEPT_DISTINCT_A       LIZ_MULTI(LIZ_TYP(5, 25))

16 rows selected.

Elapsed: 00:00:00.01