PL/SQLの勉強12 配列のデータをINSERT

2022-10-29

前回は配列の書き方をやっていきました。

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

実際のシステムを組むときは、
「配列をぐるぐるループする」
のが目的ではないですよね。

配列に持って
何か加工して
どこかのテーブルに入れる

という動作になるように
プログラムを書くはずです。

今回は配列のデータを
「どこかのテーブルに入れる」部分について
やっていきたいと思います。

1. まずはサンプルのデータを用意
2. ダメな例
3. 配列のデータを一気にINSERT
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)
)
;

create table COMMON_MST_COPY
(
  ID   number
 ,NAME varchar2(4000)
 ,constraint PK_COMMON_MST_COPY 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;

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

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

ダメな例

まず最初に「これはやっちゃダメ！」
な例から示したいと思います。

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;
  
  n_id_mod_5    number;
  v_name_title  varchar(10);
  
begin
  -- 入れ先のテーブルをリセットする
  execute immediate 'truncate table COMMON_MST_COPY';
  
  -- 配列に持ち替える
  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
      -- IDを５で割った時の余りを保持
      n_id_mod_5 := mod(tbl_common(i).ID, 5);
      -- 敬称を設定する
      if (n_id_mod_5 = 1) then
        v_name_title := 'くん';
      elsif (n_id_mod_5 = 3) then
        v_name_title := 'さん';
      elsif (n_id_mod_5 = 5) then
        v_name_title := 'ちゃん';
      else
        v_name_title := null;  -- 敬称いらない人もいたり…
      end if;
      -- 敬称を付ける
      tbl_common(i).NAME := tbl_common(i).NAME || v_name_title;
    end loop;
    
    -- 配列のデータをINSERTする
    for i in tbl_common.first..tbl_common.last
    loop
      insert into COMMON_MST_COPY
      (
        ID
       ,NAME
      )
      values
      (
        tbl_common(i).ID
       ,tbl_common(i).NAME
      )
      ;
    end loop;
    
    commit;
    
  end if;
end;

なんだかすっごく長いですが
注目して欲しいのは46行目です。

23行目で配列に保持したデータを、
28〜44行目で加工しています。

48行目でもう1回
配列をループしながら「1件ずつ」INSERTしています。

これ、最悪です。

一般的に、DBへの操作は遅いです。
（INSERTとかUPDATEとかDELETEとか…）
この遅い処理を「1件ずつ」「繰り返し」するのは
すごいヤバいです。。。
今回はサンプルデータが15件しかないので
すぐに終わりますが、
例えば1億件とかデータがあった場合…
全然終わらない処理になってしまいます！

じゃあどう書けばいいのか？
以下のように書いてください！

配列のデータを一気にINSERT

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;
  
  n_id_mod_5    number;
  v_name_title  varchar(10);
  
begin
  -- 入れ先のテーブルをリセットする
  execute immediate 'truncate table COMMON_MST_COPY';
  
  -- 配列に持ち替える
  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
      -- IDを５で割った時の余りを保持
      n_id_mod_5 := mod(tbl_common(i).ID, 5);
      -- 敬称を設定する
      if (n_id_mod_5 = 1) then
        v_name_title := 'くん';
      elsif (n_id_mod_5 = 3) then
        v_name_title := 'さん';
      elsif (n_id_mod_5 = 5) then
        v_name_title := 'ちゃん';
      else
        v_name_title := null;  -- 敬称いらない人もいたり…
      end if;
      -- 敬称を付ける
      tbl_common(i).NAME := tbl_common(i).NAME || v_name_title;
    end loop;
    
    -- 配列のデータを一気にINSERTする
    forall i in tbl_common.first..tbl_common.last
      insert into COMMON_MST_COPY
      (
        ID
       ,NAME
      )
      values
      (
        tbl_common(i).ID
       ,tbl_common(i).NAME
      )
    ;
    dbms_output.put_line('挿入件数：' || sql%rowcount);
    commit;
    
  end if;
end;

46行目までは
ダメな例と全く同じです。

47行目で新しいワードがありますね。
「forall」です。

ダメな例の時は「for」で一件ずつグルグルしていましたが
「forall」の場合は、一気に処理しています。

つまり1回のDBアクセスで
「一気に」INSERTしています。

これであれば何回もINSERTすることもなく
1回のINSERTで済むので、
処理の高速化を図ることができます！

以上ですｗ

配列に持ったデータを
１発でDBに入れる処理をやっていきました。

PL/SQLをやる上で基本的なものですが
某◯racle社でも出来てない人もいました。。。
ありえない！！！

これを読んでる人はそうならないために…
ぜひ超基本的な「forall」を覚えて欲しいと思います！

PL/SQL

Posted by kiri

PL/SQLの勉強13 forallのDML文

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

New Comments

No comments yet. Be the first one!