PL/SQL Tables

PL/SQLテーブルはPL/SQL内で使われる配列のような変数です。 このように宣言されます：

type <type_name> is table of <data_type> index by binary_integer;

PL/SQLテーブルは、格納された関数かプロシージャーにパラメーターとして渡されるPL/SQLブロック内で使われます。 これで一度の呼び出しで多くの情報を転送することが出来ます。 クライアント/サーバー状況下では、 これは大いにネットワークトラフィックを減少させることが出来ます。 PL/SQLテーブルは入力・出力・入出力変数となりうります。必要に応じて動的に大きくしたり小さくしたり出来ます。

PL/SQLテーブルを宣言する

TOracleQueryかTOracleDataSetの中でPL/SQLテーブルとして変数を宣言するためには、変数パラメーターエディタ内のPL/SQLテーブルチェックボックスを見てください。 このチェックボックスは、変数のデータ型が文字列、整数、浮動小数点かデータである場合のみ使用可能になります。 他のデータ型のPL/SQL Tableは現在サポートされていません。

このあと、テーブルの最大値を定義できます。 サーバー上ではPL/SQL Tableはどこまでも大きくなれますが、クライアント上では前もって定義されたそれ以上大きくなれない最大値を持ちます。 もし変数が文字列のPL/SQL Tableならば、最大文字列サイズも定義しなくてはいけません。

PL/SQL Tableが占有できるメモリ量は、Oracle7では32512バイトになります。Oracle8では特に制限はありません。 変数プロパティエディタはPL/SQL Tableが占有するメモリを表示します。 それがOracle7の制限を越えたとき、赤で表示されます。

実行時にも2つのステップを経てPL/SQL Tableを宣言することが出来ます。最初に通常通りDeclareVariableを呼び出し、続いてDimPLSQLTableを呼びます。

Package Wizardを通じて生成されたパッケージ内の関数やプロシージャーへの呼び出しでパラメータとして使われるPL/SQL Tableのために、代わりに TPLSQLTableオブジェクトを使うことが出来ます。

PL/SQL Tableを使う

PL/SQL Tableに変数を割り当てるのは、array DMLに対して割り当てるのに非常に似ています。 変数配列を宣言して、SetVariableメソッドと共にこの配列を使う必要があります。 次のサンプルは変数を�Numbers�という名前でPL/SQL Tableに割り当てます:

Table := VarArrayCreate([0, 2], varVariant);

Table[0] := 1.5;

Table[1] := 3.0;

Table[2] := 4.0;

MyQuery.SetVariable('Numbers', Table);

PL/SQL Tableは入出力か出力変数である時、PL/SQLブロックを実行したあと変更されたテーブルを取得する必要があります。 こうするにはGetVariableメソッドを使ってください。 入力時にあったのと同じlow boundを持つ変数配列を返します。 PL/SQL Tableが出力変数(言い換えると、PL/SQLブロックが実行される前には値を割り当てられていない)の時、配列はゼロベースになります。

PL/SQL Tableサンプル

次のサンプルはemployee numberを持つPL/SQL Tableに基づいてemployeeレコードを削除する格納されたプロシージャーの使い方を示します。 出力PL/SQL Tableとして、レコードを削除する時に起こったそれぞれのエラーメッセージを返します。 うまく削除できたレコードの数も返します。

格納されたプロシージャー定義

パッケージEmployeeはemployee numberとエラーメッセージ付きでPL/SQL Tableに対して２つの型を定義します。DeleteEmployeesプロシージャーはemployee numbersでループして、これらレコードを削除しようと試みます。 エラー時にはエラーメッセージテーブルが更新されます。

type t_EmpNoTable is table of emp.empno%type index by binary_integer;

type t_ErrorTable is table of varchar2(200) index by binary_integer;

procedure DeleteEmployees(p_EmpNoTable in  t_EmpNoTable,

                         p_ErrorTable out t_ErrorTable,

                         p_Deleted    out integer) is

l_Deleted integer;

i integer;

begin

i := 0;

l_Deleted := 0;

loop

   begin

     i := i + 1;

     delete emp where empno = p_EmpNoTable(i);

     p_ErrorTable(i) := 'Okay';

     l_Deleted := l_Deleted + 1;

   exception

     -- End of PL/SQL Table

     when no_data_found then exit;

     -- Error deleting record

     when others then p_ErrorTable(i) := sqlerrm;

   end;

end loop;

p_Deleted := l_Deleted;

end;

プロシージャーを実行するためのPL/SQLブロック

ToracleQueryのために必要となるPL/SQLブロックはDeleteEmployeesパッケージプロシージャーへの呼び出しを含んでいるに過ぎません。 p_EmpNoTable変数は整数のPL/SQL Tableとして宣言され、p_ErrorTableは文字列のPL/SQL Tableで、p_Deletedは整数です。

begin

Employee.DeleteEmployees(:p_EmpNoTable, :p_ErrorTable, :p_Deleted);

end;

employeesを削除するたのDelphiコード

次のDelphiコードは、employees 7839, 7369と7788の削除を試みます。プロシージャーを実行したあと、 いくつのレコードがうまく削除できたか、そしてどのようなエラーがそれぞれのrmployeeに対して起こったかを示します。

with DeleteEmployeesQuery do

begin

EmpNoTable := VarArrayCreate([0, 2], varVariant);

EmpNoTable[0] := 7839;

EmpNoTable[1] := 7369;

EmpNoTable[2] := 7788;

SetVariable('p_EmpNoTable', EmpNoTable);

Execute;

Session.Commit;

ErrorTable := GetVariable('p_ErrorTable');

s := string(GetVariable('p_Deleted')) + ' record(s) deleted'#13#10;

for i := 0 to 2 do

   s := s + string(EmpNoTable[i]) + ' - ' + string(ErrorTable[i]) + #13#10;

ShowMessage(s);

end;