PL/SQLの勉強13 forallのDML文

前回はforallについてやりました。

配列に入った複数件のデータを
1回のDBアクセスでINSERTできるので、
処理の高速化が図れるというモノでした。

実際の仕事では、
要件にもよると思いますが
INSERTではダメだ、となることもあると思います。

そんな時のために
DMLそれぞれの
サンプルを書いてみたいと思います。

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

前回と同じく…

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

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

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

あと、空のテーブルをもう1個用意します。

以下の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です。

FORALL … 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を5で割った時の余りを保持
      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;

2行目で、扱うデータを定義しています。

23行目で、配列「tbl_common」に
ゴソッとデータを持ち替えています。

26行目の「if (tbl_common.count > 0) then」は
配列にデータがあった場合に…という意味です。
もしデータが無い場合はエラーとなってしまうので
注意しましょう。

28〜44行目は、なんらかの加工です。
要件によって色々な場合が想定されますね。

47行目が「forall … insert」です。
配列の最初〜最後まで、一気にINSERTします。

FORALL … UPDATE のサンプル

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
    /*
    なんらかの加工
    */
    
    -- 配列のデータを一気にUPDATEする
    forall i in tbl_common.first..tbl_common.last
      update COMMON_MST_COPY
      set NAME = tbl_common(i).NAME
      where ID = tbl_common(i).ID
    ;
    dbms_output.put_line('更新件数:' || sql%rowcount);
    commit;
    
  end if;
end;

27行目で「forall … update」してますね。
普通のUPDATEと同じ感じで書けますね!

FORALL … DELETE のサンプル

FORALLで削除する場合は…
ロジックはいくつか考えられると思います。

せっかくなので
こんな感じにできるよね!
を書いてみたいと思います。

削除するデータを最初に定義する

declare
  cursor cur_common is
    select
      ID
     ,NAME
    from COMMON_MST
    where ID = 2
    order by
      ID
  ;
  
  type type_common_tbl is table of cur_common%rowtype index by pls_integer;
  tbl_common  type_common_tbl;
  
  n_cnt  number := 0;
begin
  
  -- 配列に持ち替える
  open cur_common;
  fetch cur_common bulk collect into tbl_common;
  close cur_common;
  
  if (tbl_common.count > 0) then
    
    forall i in tbl_common.first..tbl_common.last
      delete from COMMON_MST_COPY
      where ID = tbl_common(i).ID
    ;
    
    dbms_output.put_line('削除件数:' || sql%rowcount);
    commit;
    
  end if;
end;

2番目のデータだけを削除しようとしてます。

7行目で、カーソルを定義するときに
ID=2だけ!ってしています。

…でもこれ、わざわざFORALLってしなくても
普通にDELETEすればいいですねw

配列に"削除フラグ"的なのを用意しておく

アイディア勝負な気もしますが…
こんなことも出来ますよ!

でも僕的に本命はこっちです。
仕事でも書いたことあります。

declare
  cursor cur_common is
    select
      ID
     ,NAME
     ,0  DELETE_FLG  -- 削除フラグを追加(0:削除しない、1:削除する)
    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
      if (tbl_common(i).ID = 2) then
        -- 後で削除するためにフラグを立てておく
        tbl_common(i).DELETE_FLG := 1;
      end if;
    end loop;
    
    forall i in tbl_common.first..tbl_common.last
      delete from COMMON_MST_COPY
      where ID = tbl_common(i).ID
      and   tbl_common(i).DELETE_FLG = 1
    ;
    
    dbms_output.put_line('削除件数:' || sql%rowcount);
    commit;
    
  end if;
end;

6行目で「削除フラグ」的なのを用意しています。

24〜30行目の加工するところで
削除したいデータに"印"を付けておきます。

35行目で削除したいデータを指定してます。

FORALL … MERGE のサンプル

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
    /*
    なんらかの加工
    */
    
    forall i in tbl_common.first..tbl_common.last
      merge into COMMON_MST_COPY tbl
      using (
        select
          tbl_common(i).ID    ID
         ,tbl_common(i).NAME  NAME
        from dual
      ) common_data
      on (
        tbl.ID = common_data.ID
      )
      when matched then
        update set
          NAME = common_data.NAME
        where NAME <> common_data.NAME  -- 名前が違う時だけupdate
      when not matched then
        insert
        (
          ID
         ,NAME
        )
        values
        (
          common_data.ID
         ,common_data.NAME
        )
    ;
    
    dbms_output.put_line('マージ件数:' || sql%rowcount);
    commit;
    
  end if;
end;

26行目〜でMERGEしています。

40行目、地味に大切だと思ってます。
変更したいものだけ変更!としないと
何も変わってないけど全部UPDATEしてしまうし、
54行目の「sql%rowcount」もテーブル全部の件数が出てしまいます。

以上ですw

FORALLを使ったDMLについてやっていきました。

基本的に普通のDMLと一緒ですね!

頭にFORALL…って書くだけで基本OK。。。
でした!

PL/SQL

Posted by kiri