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

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

第65回 「バルクバインド(配列内の添え字を選んでFORALLする方法)」(2013.07.08)

2013.07.08

こんにちは。インストラクターの蓑島です。

前回は、配列の添え字番号が連続しない場合のFORALL構文をご紹介しました。

利用シーンとして、配列から無効なデータを削除などしたので、配列の添え字番号が連続しないといった場合に、エラーにならずにFORALLできる方法を解説したわけです。
しかしデータを削除するということは、データを失うことにもなるので、処理の流れによっては、配列からデータを削除せずに配列内の必要なデータだけでFORALLしたいニーズもあります。

そこで、今回のテーマは、そのような場合の対処方法です。
つまりタイトルにあるとおり、配列内の添え字を選んで、つまり、必要なデータだけでFORALLする方法です。

簡単にいうと、その方法は『処理したいデータの添え字番号を他の配列で管理』するものです。

例えば元の配列の添え字が、「1,2,3」とあるときに、添え字「1,3」だけでFORALLしたければ、添え字「1,3」の値を他の配列に格納しておきます。
そして、その配列の値を使って、元のデータ用の配列を使ってFORALLするというものです。

ここで処理するデータを格納した配列を「データ配列」と呼び、データ配列の処理対象の添え字を格納する配列を「索引配列」と呼ぶことにしたいと思います。(PL/SQLでは配列の添え字のことを、索引とも言いますので)

そういったデータ配列、索引配列両方を使ったFORALLの構文は以下の通りです。

  FORALL 添え字変数 IN VALUES OF 索引配列
    データ配列を使った1つのDML文;

これだけではなんのことかわからないですね。具体的な事例をみれば簡単です。

まず、索引配列ですが、添え字(BINARY_INTYEGER型)を格納する配列なので、その型は「TABLE OF BINARY_INTEGER」です。
そして、その配列の添え字の型も BINARY_INTEGER型です。配列の添え字の型は、決まり文句になりますが、「INDEX BY BINARY_INTEGER」と指定します。よって、索引配列の型は以下のように定義します。

TYPE 型名 IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

前回と同じように A列、B列をもつTEST01表を用意して、A列用のデータ配列として、A_TAB, B列用のデータ配列として B_TABを使いますが、検証しやすいように、パッケージPAC1に格納します。

さらに今回は、索引配列も使いますので、それを C_TABとして、同じくパッケージPAC1に格納しておきます。

まずTEST01表ですが、前回と同じですが、今一度定義を確認します。

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

A列、B列だけの単純な表です。

次に配列、A_TAB, B_TAB, C_TABをもつパッケージPAC1を作成します。
今回から以下のようにソースコードをコピーしやすいようにしてます。
コピペして、SQL*Plusや、SQL*Developerなどで実際に試してみてください。

SQL> L

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE PACKAGE PAC1
    /*******************************/
    -- パッケージを作る
    /*******************************/
IS
    /******************************/
    -- A列用の配列 (データ配列)
    /******************************/
     TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
     INDEX BY BINARY_INTEGER;
     A_TAB   A_TAB_TYPE;
    /******************************/
    --B列用の配列 (データ配列)
    /******************************/
     TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
     INDEX BY BINARY_INTEGER;
     B_TAB   B_TAB_TYPE;
    /******************************/
    --★★  添え字用の配列 (索引配列) ★★
    /******************************/
     TYPE C_TAB_TYPE IS TABLE OF BINARY_INTEGER
     INDEX BY BINARY_INTEGER;
     C_TAB   C_TAB_TYPE;
END ;
/
 
パッケージが作成されました。

ここで、21~23行目が添え字用の配列の定義です。
ご覧のように、BINARY_INTEGER型のTABLE型であることがわかると思います。

では、早速前回と同じように、A列用、B列用のデータ配列に添え字1番~3番でデータを格納します。
前回と同じですが以下のコードです。

SQL> L

1
2
3
4
5
6
7
BEGIN
    PAC1.A_TAB(1) := 10;  PAC1.A_TAB(2) := 20;  PAC1.A_TAB(3) := 30;
    PAC1.B_TAB(1) := 'A' ; PAC1.B_TAB(2) := 'B' ; PAC1.B_TAB(3) := 'C' ;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

これで、PAC1パッケージのA_TAB, B_TAB配列に添え字1番~3番でデータが格納されました。

では、今回はこのうち、添え字番号を1番と3番だけで、FORALLでTEST01表にINSERTしたい訳です。
つまり添え字番号2番は対象外です。
そのためには、添え字番号1番と3番を索引配列C_TABに格納します。
C_TAB配列の添え字番号はなんでもいいので、ここでは混同しないように10番、20番ということにします。

SQL> L

1
2
3
4
5
6
7
BEGIN
    PAC1.C_TAB(10) := 1;
    PAC1.C_TAB(20) := 3;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

これでPAC1パッケージのC_TAB配列の添え字10と20のところに値 1と3が格納されました。
C_TAB配列には 「1と3」の値があります。

現在、TEST01表は0件です。

SQL> SELECT * FROM TEST01;

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

では早速、A_TAB, B_TABのデータ配列、C_TABの索引配列を使って、TEST01表にFORALLでINSERTします。

SQL> L

1
2
3
4
5
6
7
BEGIN
   FORALL  J  IN  VALUES OF  PAC1.C_TAB
       INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(J), PAC1.B_TAB(J));
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

これで、索引配列 PAC1.C_TABの値(VALUES OF PAC1.C_TAB)、すなわち1と3を添え字 J として、PAC1.A_TAB(J), PAC1.B_TAB(J)の値をTEST01表にFORALLで一括INSERTしたわけです。
VALUES」というキーワードが配列の「」を表すキーワードであることがポイントです。
ちなみに、前回は「INDICES」というキーワードが配列の「添え字」を表すキーワードであることがポイントでしたね。

確認してみましょう。

SQL> SELECT * FROM TEST01;

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

実際にA_TAB配列、B_TAB配列の添え字1と3の値を使ってINSERTできましたね。

このようにデータ配列のすべての値でFORALLするのではなく、あらかじめ処理したいデータの添え字番号を索引配列に格納しておいて、その添え字番号でFORALLするわけです。

今回の解説は最初は少しややこしく感じられたかもしれませんが、こうしてみると単純なことがお判りいただけると思います。

それでは今回はここまでです。
次回はバルク・バインドの最後の解説としてバルク・バインドでのエラー処理について解説します。
ご期待ください。

先頭へ戻る