この記事は約26分36秒で読むことができます。

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

Leave a Reply

Your email address will not be published. Required fields are marked *