TOracleQueryによるPL/SQLの実行 - パラメータの使用

SQLプロパティに設定するSQLテキスト中に、パラメータ（変数）を使用することができます。BDEではパラメータ(Parameter)と呼んでいますが、DOAではこれを変数(Variable)と呼んでいますので、ヘルプの原文を参照する際には注意してください。このセクションでは私も「変数」と「パラメータ」を多少混同して使うかも知れませんが、ご了承ください。SQL中で、「:」で始まる識別子がパラメータとして扱われます。

パラメータには、クエリに対してデータを与えるinput
variableと、PL/SQLブロック中でセットされた値を取り出すためのoutput
variableの、input/output両方に使うinput/output
variableの三種類の使われ方があります。パラメータの使用上の最重要ポイントは以下の通りです。

  1. input variable を使用するには、(1)まず、variableを
    宣言し、(2)次に、variableに
    値をセットしてから、クエリを実行する必要があります。
  2. output variable を使用するには、(2)まず、variableを
    宣言し、クエリを実行後に、(2)variableの
    値を参照します。
  3. input/output variableを使用するには、(1)まず、variableを
    宣言し、(2)
    値をセットしてから、クエリを実行し、(3)返された
    値を参照します。

パラメータの宣言

とにもかくにも、宣言しないことにはパラメータが使えないことがおわかりいただけたと思います。DOAを使うにあたり、パラメータを宣言する方法は二つあります。どちらの方法で宣言してもO.K.ですが、パラメータの宣言を忘れてクエリを実行すると、"ORA-01008:
not all variables bound"エラーが発生します。

  1. 設計時に、TOracleQueryコンポーネントの
    Variablesプロパティのプロパティエディタによって、静的にパラメータの宣言を行う。
  2. 実行時に、TOracleQuery.
    DeclareVaiableメソッドを呼び出して、動的にパラメータ宣言を行う。
    実行時にSQLプロパティが動的に書き換えられ、パラメータの数やデータ型が実行時にならないと決まらないという場合を除き、ほとんどのケースでは、設計時に
    Variablesプロパティの専用プロパティエディタの助けを借りて宣言する方が楽です。
  • TOracleQuery

    • property
      Variables :
      設計時にのみ使用できるプロパティです。Variablesプロパティのプロパティエディタは、SQLをスキャンして「:」で始まるパラメータを見つけ出して列挙し、かつそれぞれのパラメータのデータ型などの入力を支援します。この支援機能を使うには、当然のことながら、SQLプロパティを先にセットしてから、Variablesプロパティのプロパティエディタを起動する必要があります。
    • procedure
      SetVariable: input
      variable(入力パラメータ)に値をセットします。SQL実行前に使います。
    • function
      GetVariable: output
      variable(出力パラメータ)の値を参照します。SQL実行後に使います。

パラメータの使用例1(input variable)
次のようなSQLをTOracleQueryにセットしたケースを考えます

  UPDATE EMP
  SET sal = :new_sal
  WHERE empno = :empno_to_update

上記のSQLをSQLプロパティにセットしてから、Variablesプロパティエディタを起動し、「
Scan
SQL」ボタンを押すと、自動的に二つのパラメータが発見されます。プロパティエディタは、パラメータを発見するところまでは自動で行いますが、パラメータのデータ型は開発者が適切にセットしなければなりません。ここで不備があれば、SQLの実行時エラーが発生します。

上記の例の場合、:new_sal
はFloat型、empno_to_updateはInteger型として宣言する必要があります。(この例では、Oracleのデモデータベースに含まれるテーブルEMPを使っています。）

宣言が終ったパラメータには、TOracleQuery.SetVariableメソッドで値を代入できるようになります。以下にクエリの実行例を示します。

  procedure Button1Click(Sender: TObject);
  begin
    with OracleQuery do begin
      SetVariable('empno_to_update', 7894);
      SetVariable('new_sal', 3500.00);
      try
        Execute;
        showmessage(IntToStr(RowsProcessed) + 'row affected .');
      except
        on E: EOracleError do showmessage(E.Message);
      end;
    end;
  end;

パラメータの使用例2(output variable)

以下の例では、入力パラメータで与えられたデータを挿入すると同時に、Oracleのシーケンスを使って生成された新しいキー値が返されています。

  // InsertQuery.SQL =
  // begin
  //   select empsequence.nextval into :empno from dual;
  //   insert into emp (empno, ename, sal)
  //   values (:empno, :ename, :sal);
  //   commit;
  // end;
  try
    with InsertQuery do
    begin
      SetVariable('ENAME', Emp.EName);
      SetVariable('SAL',   Emp.Sal);
      Execute;
      Emp.EmpNo := GetVariable('EMPNO');
      showmessage('新たにEMPNO:' + IntToStr(Emp.EmpNo) + 'として挿入しました');
    end;
  except
    on E:EOracleError do ShowMessage(E.Message);
  end;

なお、上記の例では、empnoはInteger, salはFloat, ename はString として宣言します

同じ宣言を、動的に行う場合は以下のようになります。

  with InsertQuery do
  begin
    DeleteVariables;
    DeclareVariable('ENAME', otString);
    DeclareVariable('SAL',   otFloat);
    DeclareVariable('EMPNO', otInteger);
  end;
  • TOracleQuery * procedure
    DeleteVarialbles : すべてのパラメータ
    定義をクリアします

    • procedure
      DeclareVariable : パラメータの宣言をします
    • procedure ClearVariables : 宣言済みのすべてのパラメータの
      値をNULLにセットします(DeleteVariablesと紛らわしいので注意)

output variable を使用することで、Oracle
からいろいろなデータを取り出すことができます。以下にサーバーの時刻を取得する例を示します。

  //OracleQuery1.SQL = 
  //  BEGIN
  //    SELECT sysdate INTO :SD FROM DUAL;
  //  END;

  //OracleQuery1.Variables :
  // (name, type) =  ('SD', otDate)

  procedure TForm1.Button1Click(Sender: TObject);
  var
    CurrentDateTime: TDateTime;
  begin
    with OracleQuery1 do begin
      Execute;
      CurrentDateTime := GetVariable('SD');
      showmessage(FormatDateTime('yyyy/mm/dd hh:nn:ss',CurrentDateTime));
    end;
  end;

パラメータの型

DOAで使えるパラメータの型は以下の通りです。詳しくは、TOracleQuery.Varilables
のヘルプを見てください。

  • 基本的な型
    • Integer: 整数型 = NUMBER(1)～NUMBER(9)に相当します。
    • Float : 実数型 = NUMBER
      型に相当します。ただし、DelphiのDouble型の精度は15桁しかないことに注意が必要です。(OracleのNUMBER型で、それ以上の有効桁数を持つものは、正確にバインドできません。）
    • Date : 日付時刻型 = DATE型に相当します。
    • String : 文字列型 = VARCHAR2
      に相当します。Oracle8で4000文字(Oracle7で2000文字)が上限。
    • Char : 文字列型 =
      CHAR型に相当し、WHERE句の条件でCHAR型のカラムとの比較に使われたときに、フィールド幅一杯にスペースで埋めて比較が行われます。
    • PL/SQL String :
      PL/SQLの中で使える、最大32KBまでのサイズの文字列型です
  • 大容量フィールド型
    • Long/Long Raw : 最大2GBまでの文字列/バイナリデータ。DOAでのLong/Long
      Rawの利用には幾つかの制約が課せられます。別途ヘルプで確認してください。
    • CLOB/BLOB/BFile :
      Oracle8/Net8接続で利用できる大容量フィールド型。直接変数からフィールド値を読み書きするのではなく、TLOBLocatorクラスのオブジェクトを介してアクセスを行います。LOBアクセスについては別途説明します。
      * その他、特殊なパラメータ
    • Object/Referece : Oracle8のオブジェクト拡張機能で利用できる機能です。(DOA Object
      Version のみ)
    • PL/SQL Table : String/Integer/Float/Date
      の４つの要素型から成る配列を、PL/SQLストアドプロシージャの引数に渡すことができます。PL/SQL
      Tableについては別途セクションを設けて説明します。
    • Cursor : TOracleQueryからoutput variable
      として取得したカーソルを、別のTOracleQuery(またはTOracleDataSet))に結びつけ、このTOracleQueryコンポーネントを使って、カーソルから値を順次読み出すことが可能です。この方法については、別途実例を示します。
    • Substitute :
      これは、Oracleに対して渡される本来の意味でのパラメータではありません。TOracleQueryはSQLを実行する直前に、SQL文中のSubstituteパラメータの出現箇所を、パラメータに与えられた文字列で完全に置換してから、Oracleとの通信を実行します。したがって、このパラメータは、SQL,PL/SQLの文法と無関係に、どこででも使えます。
カテゴリー
DOA
前の記事
TOracleQuery による INSERT/UPDATE/DELETE
2010年4月12日
次の記事
TOracleQuery とカーソル変数
2010年4月14日