PL/SQLの勉強11 配列の書き方

2022-10-20

今回は「配列」について
やっていきたいと思います。

1. まずはサンプルのデータを用意
2. 配列の書き方（bulk collect into）
3. とんでもなく大量のデータを扱う時（limit 〜）
4. 以上ですｗ

まずはサンプルのデータを用意

前回と同じく…

そんなのいらないよ！
と言う方は、次に行ってくださいｗ

こんな感じのデータを用意しますよ。

ID	名前
1	一郎
2	二郎
3	三郎
4	四郎
5	五郎
6	六郎
7	七郎
8	八郎
9	九郎
10	十郎
11	十一郎
12	十二郎
13	十三郎
14	十四郎
15	十五郎

以下のDDL・DMLを全部コピーして
Oracle Live SQLに貼り付け・実行すれば
自分で試すためのデータは用意できますよ♪

create table COMMON_MST
(
  ID   number
 ,NAME varchar2(4000)
 ,constraint PK_COMMON_MST primary key (ID)
)
;

insert into COMMON_MST values (1, '一郎');
insert into COMMON_MST values (2, '二郎');
insert into COMMON_MST values (3, '三郎');
insert into COMMON_MST values (4, '四郎');
insert into COMMON_MST values (5, '五郎');
insert into COMMON_MST values (6, '六郎');
insert into COMMON_MST values (7, '七郎');
insert into COMMON_MST values (8, '八郎');
insert into COMMON_MST values (9, '九郎');
insert into COMMON_MST values (10, '十郎');
insert into COMMON_MST values (11, '十一郎');
insert into COMMON_MST values (12, '十二郎');
insert into COMMON_MST values (13, '十三郎');
insert into COMMON_MST values (14, '十四郎');
insert into COMMON_MST values (15, '十五郎');

commit;

試しにちゃんとデータが入っているか
確認してみましょう。

select
*
from COMMON_MST
;

大丈夫そうですね！
これでデータの準備はOKです。

配列の書き方（bulk collect into）

配列についていろいろググると、
VARRAYとか色々出てきますが…
正直使ったことありません。

僕的には
以下のやり方の一択です！

declare
  cursor cur_common is
    select
      ID
     ,NAME
    from COMMON_MST
    order by
      ID
  ;
  
  type type_common_tbl is table of cur_common%rowtype index by pls_integer;
  tbl_common  type_common_tbl;
  
begin
  open cur_common;
  fetch cur_common bulk collect into tbl_common;
  close cur_common;
  
  if (tbl_common.count > 0) then
    for i in tbl_common.first..tbl_common.last
    loop
      dbms_output.put_line(tbl_common(i).ID || ' : ' || tbl_common(i).NAME);
    end loop;
  end if;
end;

2行目で、扱うデータをカーソルで定義します。

11行目で、配列の型を定義しています。
「is table of (カーソル名)%…」はこのまま覚えてますｗ

12行目で、配列の名前を定義しています。

16行目で、定義した配列にカーソルのデータを持ち替えます。
配列の持ち替え方は、
1行ずつと複数行一気にの２種類ありますが
何か理由がない限りは一気にやります。
（一気にやった方が効率は段違いにいいです！）
「bulk collect into」とはそう言う意味です。

19行目の「0より大きい時」は無いとダメです。
配列にデータが無いとき、処理が落ちます！

20行目で、配列に持ったデータを
「最初から最後までループ」しています。

とんでもなく大量のデータを扱う時（limit 〜）

今回のサンプルデータは15件しかないですが
実際は数1000万・数億件のデータを扱うことがあります。

そんな大量のデータを
一気に配列に持とうとすると、
「プロセスメモリが不足しました」というエラーが出て
処理が落ちてしまいます。

…というか、私が保守してたシステムが
落ちたことがあります。。。

じゃあどうするのか？というお話です。

結論を行ってしまうと…
「複数回に分けて」配列を持ち替えるんです。

例えば全体で300万件あったとすると…
50万件ずつ持ち替える。。。
すると、
1回目 → 1〜500,000行目のデータを処理
2回目 → 500,001〜1,000,000行目のデータを処理
3回目 → 1,000,001〜1,500,000行目のデータを処理
・・・
というふうにできます！

こんな感じに書きますよ！
（5件ずつ持ち替えるように書いてます。）

declare
  cursor cur_common is
    select
      ID
     ,NAME
    from COMMON_MST
    order by
      ID
  ;
  
  type type_common_tbl is table of cur_common%rowtype index by pls_integer;
  tbl_common  type_common_tbl;
  
begin
  open cur_common;
  
  loop
    fetch cur_common bulk collect into tbl_common limit 5;
    dbms_output.put_line('***** 持ち替えた！');
    exit when tbl_common.count = 0;
    
    for i in tbl_common.first..tbl_common.last
    loop
      dbms_output.put_line(tbl_common(i).ID || ' : ' || tbl_common(i).NAME);
    end loop;
    
  end loop;
  
  close cur_common;
end;

17〜27行目でループしています。

18行目で、配列に持ち替えようとしていますが
さっきと違って「limit 5」と書いてあります。
これは、配列には最大5件だけしか持たないよ！
としているんです。

20行目で、配列に持とうとしたデータが0件だったら
ループを抜け出して27行目に行くようにしています。

実行結果は以下のようになるはずです！