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

collection型 単一と複数

まえがき

collection使いこなせるようになるための練習。表現の幅を増やすため。

collection型 単一

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

コード表示

select level as rn from dual connect by level <= 5;

create or replace type liz_single as table of number;
/

with sub as(
select level as rn from dual connect by level <= 5
)select cast(collect(rn) as liz_single) as liz from sub;

with sub as(
select level as rn from dual connect by level <= 5
),coll as(
select cast(collect(rn) as liz_single) as liz from sub
)select * from table(select liz from coll);

実行例

コード表示


[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 Sat Mar 9 10:56:27 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Mar 09 2019 08:32:32 +09:00

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

AINE@pdb1> select level as rn from dual connect by level <= 5;

           RN
-------------
            1
            2
            3
            4
            5

5 rows selected.

Elapsed: 00:00:00.01
AINE@pdb1>

AINE@pdb1> create or replace type liz_single as table of number;
  2  /

Type created.

Elapsed: 00:00:00.04
AINE@pdb1>

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as(
  2  select level as rn from dual connect by level <= 5
  3  )select cast(collect(rn) as liz_single) as liz from sub;

LIZ
----------------------------------------------------------------------------------------------------
LIZ_SINGLE(1, 2, 3, 4, 5)

1 row selected.

Elapsed: 00:00:00.01

AINE@pdb1> with sub as(
  2  select level as rn from dual connect by level <= 5
  3  ),coll as(
  4  select cast(collect(rn) as liz_single) as liz from sub
  5  )select * from table(select liz from coll);

 COLUMN_VALUE
-------------
            1
            2
            3
            4
            5

5 rows selected.

Elapsed: 00:00:00.02

collection型 複数

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

コード表示


select level as rn,level * 5 as qty from dual connect by level <= 5;

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

with sub as(
select level as id,level * 5 as qty from dual connect by level <= 5
)select cast(collect(liz_typ(id,qty)) as liz_multi) as liz from sub;

with sub as(
select level as id,level * 5 as qty from dual connect by level <= 5
),coll as(
select cast(collect(liz_typ(id,qty)) as liz_multi) as liz from sub
)select * from table(select liz from coll);

実行例

コード表示

AINE@pdb1> select level as rn,level * 5 as qty from dual connect by level <= 5;

           RN           QTY
------------- -------------
            1             5
            2            10
            3            15
            4            20
            5            25

5 rows selected.

Elapsed: 00:00:00.00
AINE@pdb1> create or replace type liz_typ is object (id number,qty number);
  2  /

Type created.

Elapsed: 00:00:00.02
AINE@pdb1> create or replace type liz_multi is table of liz_typ;
  2  /

Type created.

Elapsed: 00:00:00.01
AINE@pdb1> with sub as(
  2  select level as id,level * 5 as qty from dual connect by level <= 5
  3  )select cast(collect(liz_typ(id,qty)) as liz_multi) as liz from sub;

LIZ(ID, QTY)
----------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ_TYP(1, 5), LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(5, 25))

1 row selected.

Elapsed: 00:00:00.03
AINE@pdb1> with sub as(
  2  select level as id,level * 5 as qty from dual connect by level <= 5
  3  ),coll as(
  4  select cast(collect(liz_typ(id,qty)) as liz_multi) as liz from sub
  5  )select * from table(select liz from coll);

           ID           QTY
------------- -------------
            1             5
            2            10
            3            15
            4            20
            5            25

5 rows selected.

Elapsed: 00:00:00.01
AINE@pdb1>

Leave a Reply

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