引数としてのPL/SQLテーブル利用

PL/SQLストアドプロシージャは、引数にPL/SQLテーブルと呼ばれる、配列データをとることができま、DOAを用いれば、一次元配列データをパラメータとして受け渡しすることが可能になります。

OracleのPL/SQLで利用できるコレクション型には、
PL/SQL表(索引付き表)、NESTED TABLE、vararray の3種類がありますが、このうちの
PL/SQL表型のパラメータを、DOAを用いることでDelphiアプリケーションから操作することができます。3種類のコレクション型の中で、PL/SQLが索引付き表とホストアプリケーションの配列データとの間の暗黙の型変換をサポートしているためです。

なお、DOAがサポートするのは、string, integer, float, date
型の配列に限られます。(OracleではVARCHAR2, NUMBER, DATEに相当します)

PL/SQLテーブルを引数に持つプロシージャの作り方

PL/SQL表を使うには、PL/SQL表を型として宣言する必要があります。

パッケージプロシージャを宣言する場合には、パッケージの宣言部において型を宣言し、パッケージプロシージャの引数に使い、パッケージに属さないプロシージャを宣言する場合には、PL/SQL表型を宣言するためのパッケージを使います。以下に例示します。

  //例１) パッケージプロシージャが引数にPL/SQL表をとる例
  create or replace package EmpPackage 
  as
    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;
  end;

  create or replace package body EmpPackage
  as
    procedure DeleteEmployees(p_EmpNoTable in  t_EmpNoTable,
                              p_ErrorTable out t_ErrorTable,
                              p_Deleted    out integer)
    as
      DeletedCount integer;
      i integer;
    begin
      i := 0;
      DeletedCount := 0;
      loop
        begin
          i := i + 1;
          delete emp where empno = p_EmpNoTable(i);
          p_ErrorTable(i) := 'Okay';

          DeletedCount := DeletedCount + 1;
        exception
          when no_data_found then exit;
          when others then p_ErrorTable(i) := sqlerrm;
        end;
      end loop;
      p_Deleted := DeleteCount;
    end;
  end;

  //例２）パッケージの外でプロシージャを定義する場合(型宣言用のパッケージを作ります)
  create or replace package EmpType 
  as
    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;
  end;

  create or replace procedure DeleteEmployees(
    p_EmpNoTable in  EmpType.t_EmpNoTable,
    p_ErrorTable out EmpType.t_ErrorTable,
    p_Deleted    out integer
    ) 
  as
      DeletedCount integer;
      i integer;
   egin
    i := 0;
    DeletedCount := 0;
    loop
      begin
        i := i + 1;
        delete emp where empno = p_EmpNoTable(i);
        p_ErrorTable(i) := 'Okay';
        DeletedCount := DeletedCount + 1;
      exception
        when no_data_found then exit;
        when others then p_ErrorTable(i) := sqlerrm;
      end;
    end loop;
    p_Deleted := DeleteCount;
  end;

DelphiからPL/SQL表をパラメータにストアドプロシージャを実行する方法

TOracleQueryコンポーネント(DelteEmployeesQuery)のSQLプロパティを以下のようにセットします

  begin
    EmpPackage.DeleteEmployees(:p_EmpNoTable, :p_ErrorTable, :p_Deleted);
  end;

TOracleQueryコンポーネント(DelteEmployeesQuery)のVariablesプロパティエディタで、以下のように変数を宣言します

• p_EmpNoTableはinteger型のPL/SQL表
• p_ErrorTableはstring型のPL/SQL表
• p_Deletedはinteger

そのうえで以下ようなのコードで、EmpNoが7839,7369,7788の3件のDELETE操作を行うことができます

  var
    EmpNoTable : Variant;

  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;

• TOracleQuery
  • Variables: PL/SQL表型の変数を、
    Variant配列のVariant変数にバインドできます

Variant以外の配列のVariant変数や、配列変数をバインドすることはできないので注意が必要です。すなわち、PL/SQL表にバインドする変数は、Delphi側では常に
Variantとして宣言する必要があり、VarArrayCreate([0, N], VarType)のVarTypeは
varVariantでなければなりません