e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第64回 「バルク・バインド(添え字番号が連続しないときのFORALL構文)」

2013.07.01

こんにちは。インストラクターの蓑島です。早いもので、もう7月ですね。

今回もバルク・バインドについて解説します。
表題のとおり、「添え字番号が連続しないときのFORALL構文」です。

バルク・バインドで、DML(INSERT, UPDATE, DELETE)を処理するときは、FORALLという構文であることは以前解説しました(バックナンバー第59号「バルク・バインド(DMLの場合)」参照)。

しかしそのときに紹介したFORALL構文では、配列の添え字番号が連続するケースしか使えません。
不連続だとエラーとなります。

しかし実際に、さまざまな事情から添え字番号が連続しないケースがありえます。

例えば、ある会社では、ネットから受け付けた注文データを仮の注文表にINSERTして蓄積している。
そして夜間にその仮の注文表から、バルク・バインドで、注文データを配列にバルクフェッチする。
このとき、配列の添え時番号は1番から連続します。
その配列のデータをチェックプログラムで検証し、無効な注文であれば、配列から削除する。
これにより配列の添え字番号は不連続になります。
最後にその配列で、本当の注文表にバルクバインドのFOALL構文で一括INSERTする。

上記のケースはあくまでも例ですが、配列の添え字番号が連続しないことはありえるわけです。

先に結論を言えば、添え字番号が連続しない場合のFORALLは特別な構文を使う必要があります。

--添え字番号が連続する場合(バックナンバー第59号で解説)
FORALL  添え字変数  IN  下限値..上限値
                配列を使った1つのDML文;

--添え字番号が連続しない場合(今回、解説する内容)
FORALL  添え字変数  IN  INDICES OF 配列名
                配列を使った1つのDML文;

ここで、INDICESというのは、添え字番号の値の集合を示します。
つまり、その配列の添え字番号の値の集合を使ってFORALLするということであり、その値は不連続であってもかまわないわけです。

それでは早速、具体的かつ単純な例で試してみましょう。

まず、いつものように、TEST01表を使って検証します。

SQL> DESC TEST01
 名前                                               NULL?                型
 ----------------------------------------- -------- ----------------------------
 A                                                                           NUMBER
 B                                                                           VARCHAR2(10)

ご覧のように、A列、B列だけの単純な表です。

SQL> SELECT * FROM TEST01;

レコードが選択されませんでした。

現在、TEST01表にデータはありません。

では、A列用の配列と、B列用の配列をそれぞれ、A_TAB, B_TABという名前でPAC1パッケージに格納します。
以下のコードを実行しますが、このコードはバックナンバー第60回 「バルク・バインドのスピードを計測してみる」からそのままコピーしたものです。

SQL> CREATE OR REPLACE PACKAGE PAC1
  2     /*******************************/
  3     -- パッケージを作る
  4     /*******************************/
  5  IS
  6      /******************************/
  7     -- A列用の配列
  8     /******************************/
  9      TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
 10      INDEX BY BINARY_INTEGER;
 11      A_TAB   A_TAB_TYPE;
 12      /******************************/
 13     --B列用の配列
 14     /******************************/
 15      TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
 16      INDEX BY BINARY_INTEGER;
 17      B_TAB   B_TAB_TYPE;
 18  END;
 19  /

パッケージが作成されました。

本メルマガでは、単純化のために一貫して、A列用の配列、B列用の配列のように、列ごとに配列を用意していますが、A列、B列をレコード型でまとめて、一つのレコード型配列を使っても、もちろん、かまいません。

では、A_TAB, B_TABにデータを格納します。添え字番号を1番から3番にします。

SQL> BEGIN
  2     PAC1.A_TAB(1) := 10;  PAC1.A_TAB(2) := 20;  PAC1.A_TAB(3) := 30;
  3     PAC1.B_TAB(1) := 'A'; PAC1.B_TAB(2) := 'B'; PAC1.B_TAB(3) := 'C';
  4  END;
  5  /

PL/SQLプロシージャが正常に完了しました。

ご覧のように、A_TAB, B_TABともに、添え字番号は 1番から3番で格納されました。

ではここで、添え字番号2番が、先ほどの例で無効な注文ということにして、配列から削除します。

SQL> BEGIN
  2    PAC1.A_TAB.DELETE(2);
  3    PAC1.B_TAB.DELETE(2);
  4  END;
  5  /

PL/SQLプロシージャが正常に完了しました。

これで、A_TAB、B_TABともに、添え字番号2番のデータが削除されました。
したがって、現在、添え字番号は、1番と3番であり、2番は存在しないので、不連続な状態です。

では添え字が不連続だとエラーになるFORALLの構文で TEST01表にINSERTしてみます。

SQL> BEGIN
  2    FORALL  I IN  1..3
  3       INSERT INTO TEST01 (A,B)
  4       VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
  5  END;
  6  /
BEGIN
*
行1でエラーが発生しました。:
ORA-22160: 要素の索引[2]が存在しません
ORA-06512: 行2

ご覧のように、添え字番号2番が存在しないので、エラーとなりました。
ここで、A_TAB, B_TABの添え字の下限値は確かに1で、上限値は3ですが、ソースコード2行目のように、「1..3」と記述すると「1番から3番まで」という意味になるので、番号が連続していない場合はエラーになるわけです。

では、次に添え字が不連続でもエラーにならないFORALL構文で同じことを行います。

SQL> BEGIN
  2    FORALL  I IN  INDICES OF PAC1.A_TAB
  3       INSERT INTO TEST01 (A,B)
  4       VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
  5  END;
  6  /

PL/SQLプロシージャが正常に完了しました。

今度は正常に処理できました。
ここで、ソースコード2行目の 「IN INDICES OF PAC1.A_TAB」という意味は、「PAC1.A_TAB配列の添え字の値の集合を使って」という意味です。ですから、添え字番号 1番と3番を使ってFORALLするという意味です。
なお、B_TAB配列も同じ添え字番号ですから、2行目のA_TABをB_TABに置き換えて、「IN INDICES OF PAC1.B_TAB」と記述しても、同じ結果であり問題ありません。
TEST01表にデータが格納されたことも確認しましょう。
以下のように2行格納できました。

SQL> SELECT * FROM TEST01;

         A B
---------- ----------
        10 A
        30 C

このように、FORALL構文では、配列の添え字番号が不連続の場合は特別な構文を使う必要があるので注意してください。
また添え字番号が不連続な配列のことを「疎コレクション」ともいいますので記憶しておかれるとよいと思います。

最後に、次回に向けての問題提起なのですが、今回の注文処理を行う会社の事例では、無効な注文データを配列から削除しました。
しかし実際には配列からデータを削除せずに対応したいニーズも多いと思います。
そういった場合、配列の添え字は連続したままですが、そのようなケースで有効な注文だけを選んでFORALLのバルク・バインド構文で注文表にINSERTできるでしょうか?
答えはもちろん、YESです。次回はそういった事例に対応できるFORALL構文を解説します。

それでは、次回をご期待ください。

先頭へ戻る