PL/SQLの勉強13 forallのDML文

2022-11-05

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

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

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

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

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

1. まずはサンプルのデータを用意
2. FORALL … INSERT のサンプル
3. FORALL … UPDATE のサンプル
4. FORALL … DELETE のサンプル
- 4.1. 削除するデータを最初に定義する
- 4.2. 配列に"削除フラグ"的なのを用意しておく
5. FORALL … MERGE のサンプル
6. 以上ですw

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

前回と同じく…

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

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

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です。

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を５で割った時の余りを保持
      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すればいいですねｗ

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

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

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

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

PL/SQL 配列のINDEXが飛び飛びな時のFORALL

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

New Comments

No comments yet. Be the first one!