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

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

Leave a Reply

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