So-net無料ブログ作成
検索選択

バルク・マージをやってみた。 [Oracle]


このエントリは JPOUG Advent Calendar 10日目への参加記事です。

初ブログとなりますがよろしくお願いします。


今回は職場のサンプルで使う結合配列(バインド配列)を用いたレコードの一括更新について
マージ文で検証してみました。よろしければお付き合いください。

■ことの発端

 コーディングレビューをしていると以下が目立つ。

  (1)文共有は出来ている。

  (2)バインド値をループで書き替えてクエリをリフレッシュしている。

  (3)画面上のレコード挿入・更新・削除に対応するため条件分岐している。


 →(2)と(3)をチューニングしたい。


というわけで、(2)を結合配列とバルク処理、(3)をマージ文にて一度に解消できるか

まずは自身で検証してみようと思いました。

特段目新しいことはしていません。

さっそくですが、使用したパッケージはこんな感じです。

サンプルユーザーOEのORDERS表とORDER_ITEMS表を1画面で更新するイメージです。
    
--サンプルパッケージ

create or replace package PKG_SAMPLE is

--ORDER_ITEMS表の各列

type TYP_ORDER_ID is table of ORDER_ITEMS.ORDER_ID%type index by binary_integer;

type TYP_LINE_ITEM_ID is table of ORDER_ITEMS.LINE_ITEM_ID%type index by binary_integer;

type TYP_PRODUCT_ID is table of ORDER_ITEMS.PRODUCT_ID%type index by binary_integer;

type TYP_UNIT_PRICE is table of ORDER_ITEMS.UNIT_PRICE%type index by binary_integer;

type TYP_QUANTITY is table of ORDER_ITEMS.QUANTITY%type index by binary_integer;


--画面上の更新状態(0:変更なし, 1:更新, 2:削除)

type TYP_ROW_STATUS is table of number(1) index by binary_integer;


--一括マージプロシージャ

procedure PRC_ORDER_ITEMS_MERGE(

pORDER_ID in out TYP_ORDER_ID,

pLINE_ITEM_ID in out TYP_LINE_ITEM_ID,

pPRODUCT_ID in out TYP_PRODUCT_ID,

pUNIT_PRICE in out TYP_UNIT_PRICE,

pQUANTITY in out TYP_QUANTITY,

pROW_STATUS in out TYP_ROW_STATUS

);

end PKG_SAMPLE;

/


レコード型の配列でないのがちょっと面倒ですね。

    
--サンプルパッケージ本体

create or replace package body PKG_SAMPLE is

--一括マージプロシージャ

procedure PRC_ORDER_ITEMS_MERGE(

pORDER_ID in out TYP_ORDER_ID,

pLINE_ITEM_ID in out TYP_LINE_ITEM_ID,

pPRODUCT_ID in out TYP_PRODUCT_ID,

pUNIT_PRICE in out TYP_UNIT_PRICE,

pQUANTITY in out TYP_QUANTITY,

pROW_STATUS in out TYP_ROW_STATUS

) is

begin

--結合配列最終行まで一括処理する(例外発生行あっても処理継続)

forall i in 1..pORDER_ID.count save exceptions


--マージ文で一括挿入・更新・削除

--Aが更新前のORDER_ITEMS表、Bが画面編集後のORDER_ITEMS表

merge into ORDER_ITEMS A

using (

select

pORDER_ID(i) as ORDER_ID

, pLINE_ITEM_ID(i) as LINE_ITEM_ID

, pUNIT_PRICE(i) as UNIT_PRICE

, pPRODUCT_ID(i) as PRODUCT_ID

, pQUANTITY(i) as QUANTITY

, pROW_STATUS(i) as ROW_STATUS

from DUAL) B

on (A.ORDER_ID = B.ORDER_ID and A.LINE_ITEM_ID = B.LINE_ITEM_ID and A.PRODUCT_ID = B.PRODUCT_ID)


--更新前表に主キーがあれば更新or削除

when matched then


--ROW_STATUSが1ならば更新

update set A.UNIT_PRICE = B.UNIT_PRICE, A.QUANTITY = B.QUANTITY

where B.ROW_STATUS = 1


--ROW_STATUSが2ならば削除

delete

where B.ROW_STATUS = 2


--更新前表に主キーがなければ挿入

when not matched then

insert values (B.ORDER_ID, B.LINE_ITEM_ID, B.PRODUCT_ID, B.UNIT_PRICE, B.QUANTITY);


commit;


end PRC_ORDER_ITEMS_MERGE;

end PKG_SAMPLE;

/

動作確認をします。


取りあえず画面がないので、レコードの値を直接操作して確認します。
    
--テスト前

SQL> select * from order_items where order_id = 2398 order by line_item_id;


ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY

---------- ------------ ---------- ---------- ----------

2398 1 2471 482.9 5

2398 2 2537 193.6 23

2398 3 2594 9 27


--テスト実施。こんな感じにする

ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY

---------- ------------ ---------- ---------- ----------
2398 1 2471 482.9 100 <-更新

2398 2 2537 193.6 23 <-削除

2398 3 2594 9 27 <-変更なし

2398 4 3182 61 77 <-挿入

 

    
--テストコード

declare


vORDER_ID PKG_SAMPLE.TYP_ORDER_ID;

vLINE_ITEM_ID PKG_SAMPLE.TYP_LINE_ITEM_ID;

vPRODUCT_ID PKG_SAMPLE.TYP_PRODUCT_ID;

vUNIT_PRICE PKG_SAMPLE.TYP_UNIT_PRICE;

vQUANTITY PKG_SAMPLE.TYP_QUANTITY;

vROW_STATUS PKG_SAMPLE.TYP_ROW_STATUS;


begin


--レコード取得

select ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY

bulk collect into vORDER_ID, vLINE_ITEM_ID, vPRODUCT_ID, vUNIT_PRICE, vQUANTITY from order_items

where order_id = 2398 order by line_item_id;


--レコード値を編集

--1レコード目(更新)

vQUANTITY(1) := 100;

vROW_STATUS(1) := 1;


--2レコード目(削除)

vROW_STATUS(2) := 2;


--3レコード目(変更なし)

vROW_STATUS(3) := NULL;


--4レコード目(挿入)

vORDER_ID(4) := 2398;

vLINE_ITEM_ID(4) := 4;

vPRODUCT_ID(4) := 3182;

vUNIT_PRICE(4) := 61;

vQUANTITY(4) := 77;

vROW_STATUS(4) := NULL;


--一括マージプロシージャを実行します

PKG_SAMPLE.PRC_ORDER_ITEMS_MERGE(vORDER_ID, vLINE_ITEM_ID, vPRODUCT_ID, vUNIT_PRICE, vQUANTITY, vROW_STATUS);

commit;

end;

/


うん、コード自体はうまくいきました。

    

SQL> select * from order_items where order_id = 2398 order by line_item_id;


ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY

---------- ------------ ---------- ---------- ----------

2398 1 2471 482.9 100

2398 3 2594 9 27

2398 4 3182 61 77

結果も想定どおり。


PL/SQLでは上記のような流れになりましたが、その他のプログラミング言語においては

下のような手法で結合配列を宣言し、値を割り当てるそうです。

・PHPであれば、oci_bind_array_by_nameを使用する。

・.NET FRAMEWORKであれば、OracleParameterクラスのCollectionTypeプロパティ

OracleCollectionType.PLSQLAssociativeArray定数を指定する。


Google検索で結構ヒットしますので、興味のある方は調べてみてください。

ボロが出るのでこれ以上は書きません…。

さて、ざっくりバルク・マージの流れを書いてきましたが、性能的にどんな結果になったか

気になる方はまたいらしてみて下さい。

個人的に盲点だったのが、DELETE句の評価がUPDATE対象レコードと重複するということでした。

(UPDATE実施後、DELETE文が重複して実行される。)

これに気付かずアップが遅れたのですが、無駄骨にならないといいなぁと思っています。

 

明日の扉は @sora_to_umi さんです。よろしくお願いします。


この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。

×

この広告は1年以上新しい記事の更新がないブログに表示されております。