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

bulk collect句に関して

まえがき

bulk collectについて少し調べたので。。

テストデータ

rnは数値型、alpは文字列型。

コード表示

DROP TABLE test___$___ PURGE;
CREATE TABLE test___$___ AS
SELECT
	LEVEL AS rn
	,CHR(64 + LEVEL) AS alp
FROM
	dual
CONNECT BY
	LEVEL <= 26
;

AINE@pdb1> SELECT * FROM test___$___;

           RN ALP
------------- ----
            1 A
            2 B
            3 C
            4 D
            5 E
            6 F
            7 G
            8 H
            9 I
           10 J
           11 K
           12 L
           13 M
           14 N
           15 O
           16 P
           17 Q
           18 R
           19 S
           20 T
           21 U
           22 V
           23 W
           24 X
           25 Y
           26 Z

26 rows selected.

Elapsed: 00:00:00.00

collection型 単一

collection型 単一はbulk collectできない。単一だから。たぶんね。複数列ある場合のアドバンテージだとおもう。

コード表示

CREATE OR REPLACE TYPE liz_single IS TABLE OF VARCHAR2(10);
/

CREATE OR REPLACE FUNCTION rt_nobulk_coll_single
RETURN liz_single
AS rt liz_single;
BEGIN
    SELECT CAST(COLLECT(alp) AS liz_single) INTO rt
    FROM test___$___;
	RETURN rt;
END;
/

テーブルからカーサ取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT collect(alp) AS liz FROM test___$___;
SELECT collect(rn) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> SELECT collect(alp) AS liz FROM test___$___;

LIZ
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST000016AdvYGuV4fgUwPIqMDcQg=('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')

1 row selected.

Elapsed: 00:00:00.01

AINE@pdb1> SELECT collect(rn) AS liz FROM test___$___;

LIZ
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST000016AdvYF0V4fgUwPIqMDcQg=(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)

1 row selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作って取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT liz_single(alp) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> SELECT liz_single(alp) AS liz FROM test___$___;

LIZ
----------------------------------------------------------------------------------------------------
LIZ_SINGLE('A')
LIZ_SINGLE('B')
LIZ_SINGLE('C')
LIZ_SINGLE('D')
LIZ_SINGLE('E')
LIZ_SINGLE('F')
LIZ_SINGLE('G')
LIZ_SINGLE('H')
LIZ_SINGLE('I')
LIZ_SINGLE('J')
LIZ_SINGLE('K')
LIZ_SINGLE('L')
LIZ_SINGLE('M')
LIZ_SINGLE('N')
LIZ_SINGLE('O')
LIZ_SINGLE('P')
LIZ_SINGLE('Q')
LIZ_SINGLE('R')
LIZ_SINGLE('S')
LIZ_SINGLE('T')
LIZ_SINGLE('U')
LIZ_SINGLE('V')
LIZ_SINGLE('W')
LIZ_SINGLE('X')
LIZ_SINGLE('Y')
LIZ_SINGLE('Z')

26 rows selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作ってコレクションにキュッと詰めて取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT CAST(COLLECT(alp) AS liz_single) as liz FROM test___$___;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> SELECT CAST(COLLECT(alp) AS liz_single) AS liz FROM test___$___;

LIZ
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_SINGLE('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')

1 row selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作ってコレクションにキュッと詰めたあと、テーブルに変換して返却

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT COLUMN_VALUE as liz FROM TABLE(SELECT CAST(COLLECT(alp) AS liz_single) FROM test___$___);

実行例

コード表示

AINE@pdb1> col liz for a10
AINE@pdb1> SELECT COLUMN_VALUE as liz FROM TABLE(SELECT CAST(COLLECT(alp) AS liz_single) FROM test___$___);

LIZ
----------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

26 rows selected.

Elapsed: 00:00:00.01

コレクションを返すファンクションの戻り値をテーブルに変換して取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT COLUMN_VALUE as liz FROM TABLE(rt_nobulk_coll_single());

実行例

コード表示

AINE@pdb1> SELECT COLUMN_VALUE as liz FROM TABLE(rt_nobulk_coll_single());

LIZ
----------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

26 rows selected.

Elapsed: 00:00:00.00

collection型 複数

複数列はbulk collectいいかんじ。

コード表示

CREATE OR REPLACE TYPE liz IS OBJECT (rn NUMBER, alp VARCHAR2(10));
/

CREATE OR REPLACE TYPE liz_multi IS TABLE OF liz;
/

CREATE OR REPLACE FUNCTION rt_bulk_coll_multi
RETURN liz_multi
AS rt liz_multi;
BEGIN
    -- テーブルからBULK COLLECTでコレクションに代入
    SELECT liz(rn, alp) BULK COLLECT INTO rt
    FROM test___$___;
	RETURN rt;
END;
/

テーブルからカーサ取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT collect(rn,alp) AS liz FROM test___$___;
SELECT collect(liz(rn,alp)) AS liz FROM test___$___;
SELECT cast(collect(liz(rn,alp)) as liz_multi)  AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> SELECT collect(rn,alp) AS liz FROM test___$___;
SELECT collect(rn,alp) AS liz FROM test___$___
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_NT_COLLECT'


Elapsed: 00:00:00.01
AINE@pdb1> SELECT collect(liz(rn,alp)) AS liz FROM test___$___;

LIZ(RN, ALP)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ(1, 'A'), LIZ(2, 'B'), LIZ(3, 'C'), LIZ(4, 'D'), LIZ(5, 'E'), LIZ(6, 'F'), LIZ(7, 'G'), LIZ(8, 'H'), LIZ(9, 'I'), LIZ(10, 'J'), LIZ(11, 'K'), LIZ(12, 'L'), L
IZ(13, 'M'), LIZ(14, 'N'), LIZ(15, 'O'), LIZ(16, 'P'), LIZ(17, 'Q'), LIZ(18, 'R'), LIZ(19, 'S'), LIZ(20, 'T'), LIZ(21, 'U'), LIZ(22, 'V'), LIZ(23, 'W'), LIZ(24, 'X'), LIZ
(25, 'Y'), LIZ(26, 'Z'))


1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> SELECT cast(collect(liz(rn,alp)) as liz_multi)  AS liz FROM test___$___;

LIZ(RN, ALP)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ(1, 'A'), LIZ(2, 'B'), LIZ(3, 'C'), LIZ(4, 'D'), LIZ(5, 'E'), LIZ(6, 'F'), LIZ(7, 'G'), LIZ(8, 'H'), LIZ(9, 'I'), LIZ(10, 'J'), LIZ(11, 'K'), LIZ(12, 'L'), L
IZ(13, 'M'), LIZ(14, 'N'), LIZ(15, 'O'), LIZ(16, 'P'), LIZ(17, 'Q'), LIZ(18, 'R'), LIZ(19, 'S'), LIZ(20, 'T'), LIZ(21, 'U'), LIZ(22, 'V'), LIZ(23, 'W'), LIZ(24, 'X'), LIZ
(25, 'Y'), LIZ(26, 'Z'))


1 row selected.

Elapsed: 00:00:00.01

テーブルからオブジェクト作って取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT liz(rn, alp) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> SELECT liz(rn, alp) AS liz FROM test___$___;

LIZ(RN, ALP)
----------------------------------------------------------------------------------------------------
LIZ(1, 'A')
LIZ(2, 'B')
LIZ(3, 'C')
LIZ(4, 'D')
LIZ(5, 'E')
LIZ(6, 'F')
LIZ(7, 'G')
LIZ(8, 'H')
LIZ(9, 'I')
LIZ(10, 'J')
LIZ(11, 'K')
LIZ(12, 'L')
LIZ(13, 'M')
LIZ(14, 'N')
LIZ(15, 'O')
LIZ(16, 'P')
LIZ(17, 'Q')
LIZ(18, 'R')
LIZ(19, 'S')
LIZ(20, 'T')
LIZ(21, 'U')
LIZ(22, 'V')
LIZ(23, 'W')
LIZ(24, 'X')
LIZ(25, 'Y')
LIZ(26, 'Z')

26 rows selected.

Elapsed: 00:00:00.01

テーブルからオブジェクト作ってコレクションにキュッと詰めて取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT CAST(COLLECT(liz(rn, alp)) AS liz_multi) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> SELECT CAST(COLLECT(liz(rn, alp)) AS liz_multi) AS liz FROM test___$___;

LIZ(RN, ALP)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ(1, 'A'), LIZ(2, 'B'), LIZ(3, 'C'), LIZ(4, 'D'), LIZ(5, 'E'), LIZ(6, 'F'), LIZ(7, 'G'), LIZ(8, 'H'), LIZ(9, 'I'), LIZ(10, 'J'), LIZ(11, 'K'), LIZ(12, 'L'), L
IZ(13, 'M'), LIZ(14, 'N'), LIZ(15, 'O'), LIZ(16, 'P'), LIZ(17, 'Q'), LIZ(18, 'R'), LIZ(19, 'S'), LIZ(20, 'T'), LIZ(21, 'U'), LIZ(22, 'V'), LIZ(23, 'W'), LIZ(24, 'X'), LIZ
(25, 'Y'), LIZ(26, 'Z'))


1 row selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作ってコレクションにキュッと詰めたあと、テーブルに変換して返却

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT * FROM TABLE(SELECT CAST(COLLECT(liz(rn,alp)) AS liz_multi) FROM test___$___);

実行例

コード表示

AINE@pdb1> SELECT * FROM TABLE(SELECT CAST(COLLECT(liz(rn,alp)) AS liz_multi) FROM test___$___);

           RN ALP
------------- ----------
            1 A
            2 B
            3 C
            4 D
            5 E
            6 F
            7 G
            8 H
            9 I
           10 J
           11 K
           12 L
           13 M
           14 N
           15 O
           16 P
           17 Q
           18 R
           19 S
           20 T
           21 U
           22 V
           23 W
           24 X
           25 Y
           26 Z

26 rows selected.

Elapsed: 00:00:00.00

コレクションを返すファンクションの戻り値をテーブルに変換して取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT * FROM TABLE(rt_bulk_coll_multi());

実行例

コード表示

AINE@pdb1> SELECT * FROM TABLE(rt_bulk_coll_multi());

           RN ALP
------------- ----------
            1 A
            2 B
            3 C
            4 D
            5 E
            6 F
            7 G
            8 H
            9 I
           10 J
           11 K
           12 L
           13 M
           14 N
           15 O
           16 P
           17 Q
           18 R
           19 S
           20 T
           21 U
           22 V
           23 W
           24 X
           25 Y
           26 Z

26 rows selected.

Elapsed: 00:00:00.01

あとがき

基本的にオブジェクトタイプとコレクションタイプ知ってればいいと思う。(きっと困んないはず。)オブジェクトタイプdropするときは参照先からdrop。childからdrop。

Leave a Reply

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