第 6 章: 動的 SQL

アプリケーションがコンパイルされる時点で、SQL 文のすべてが判明している場合に、その文は静的な SQL 文と呼ばれます。

しかし、アプリケーションが記述された時点で、SQL 文のテキストが完全にわかってはいない場合があります。たとえば、アプリケーションのエンドユーザが SQL 文を入力できるようにしなければならない場合です。この場合、 SQL 文は実行時に作成されなければなりません。これが、動的 SQL 文と呼ばれるものです。

6.1 動的 SQL 文のタイプ

動的 SQL 文には、次に示す 4 つのタイプがあります。

動的 SQL 文のタイプ	クエリーを実行するか？	データを返すか？
文を 1 回実行する	実行しない	成功または失敗だけを返す
文を複数回実行する	実行しない	成功または失敗だけを返す
所定の選択条件のセットで所定のデータのリストを選択する	実行する	返す
何らかの選択条件でデータを何回でも選択する	実行する	返す

これらの動的 SQL 文のタイプについては、以降でさらに詳しく説明します。

6.1.1 文を 1 回実行する

このタイプの動的 SQL 文では、ただちに文が実行されます。文が実行されるたびに、再度構文が解析されます。

6.1.2 同じ文を複数回実行する

このタイプの動的 SQL 文では、文が複数回実行できる、または文がホスト変数を必要とするのどちらかになります。後者のタイプについては、 SQL 文が実行可能になる前に、文を準備する必要があります。

6.1.3 所定のデータのリストを選択する

この動的 SQL 文は、ホスト変数の個数とタイプがわかっている SELECT 文です。SQL 文の通常の手順は、次のようになります。

文を準備する。
結果を保持するカーソルを宣言する。
カーソルを開く。
変数をフェッチする。
カーソルを閉じる。

6.1.4 任意の量のデータを選択する

このタイプの動的 SQL 文は、もっともコーディングが難しいタイプです。変数のタイプ/個数は、実行時にのみ決まります。SQL 文の通常の手順は、次のようになります。

文を準備する。
文にカーソルを宣言する。
使用する変数を記述する。
上記の変数を使用してカーソルを開く。
フェッチする変数を記述する。
それらの記述を使用して変数をフェッチする。
カーソルを閉じる。

入力ホスト変数または出力ホスト変数のどちらかがわかっている場合 (コンパイル時に) は、OPEN または FETCH がホスト変数を命名できるため、記述する必要はありません。

6.2 動的 SQL 文の準備

PREPARE 文は、動的 SQL 文が含まれている文字列を受け取り、名前と文を関連づけます。たとえば、次のように記述します。

move "INSERT INTO publishers VALUES (?,?,?,?)" to stmtbuf
EXEC SQL
   PREPARE stmt1 FROM :stmtbuf
END-EXEC

動的 SQL 文では、値のプレースホルダのように振る舞うパラメータマーカ、疑問符 (?) を記述することができます。上記の例では、文が実行されたときに、疑問符に置き換えられる値が提供されなければなりません。

一度 SQL 文を準備すると、次に示す 2 つの方法のどちらかを使用することができます。

準備された文を実行する
準備された文を参照するカーソルを開く

Oracle では、プレースホルダとして疑問符を使用しません。ホスト変数の表記法を使用します。規約により、プレースホルダは Vn で指定されますが、 n には文の中で一意のプレースホルダとなる番号です。読みやすくするため、同じプレースホルダを複数回使用することができますが、文が実行される (またはカーソル使用中にそれを開く)と、各プレースホルダに 1 つのホスト変数が必要になります。たとえば、次のように記述します。

string "update ordtab " delimited by size
          "set order_no = :v1, "
              "line_no = :v2, "
              "cust_code = :v3, "
              "part_no = :v4, "
              "part_name = :v5, "
              "order_val = :v6, "
              "pay_value = :v7 "
          "where order_no = :v1 and "
              "line_no = :v2 and "
              "cust_code = :v3 " delimited by size
        into Updt-Ord-Stmt-Arr
end-string
move 190                       to Updt-Ord-Stmt-Len

EXEC SQL PREPARE updt_ord FROM :Updt-Ord-Stmt END-EXEC

EXEC SQL EXECUTE updt_ord USING
         :dcl-order-no, :dcl-line-no, :dcl-cust-code,
         :dcl-part-no,  :dcl-part-name:ind-part-name,
         :dcl-order-val,:dcl-pay-value,
         :dcl-order-no, :dcl-line-no, :dcl-cust-code
END-EXEC

上記の Updt-Ord-Stmt は、 VARYING というホスト変数タイプとして定義されたものです。

6.2.1.2 PREPARE 文の物理的な位置

Oracle のプリコンパイラを使用する場合は、 PREPARE 文の物理的な位置が重要になります。 PREPARE 文は、EXECUTE または DECLARE 文の前になければなりません。

6.3 動的 SQL 文の実行

EXECUTE 文は、準備された特定の SQL 文を実行します。

注: 結果を返さない文にかぎり、この方法で実行することができます。

準備された文にパラメータマーカが記述されている場合は、ホスト変数を使用してパラメータ値を提供する using :hvar オプションか、すでにアプリケーションによって設定された SQLDA データ構造体を識別する using descriptor :sqlda_struct オプションのどちらかを EXECUTE 文に記述する必要があります。準備された文中のパラメータマーカの数は、 SQLDATA エントリ (using descriptor :sqlda) またはホスト変数 (using :hvar) の数と一致しなければなりません。

move "INSERT INTO publishers VALUES (?,?,?,?)" to stmtbuf
EXEC SQL
   PREPARE stmt1 FROM :stmtbuf
END-EXEC
...
EXEC SQL
   EXECUTE stmt1 USING :pubid,:pubname,:city,:state
END-EXEC.

この例では、4 つのパラメータマーカは、EXECUTE 文の USING 句から提供されたホスト変数の内容に置き換えられます。

6.3.1 EXECUTE IMMEDIATE 文

動的 SQL 文にパラメータマーカが含まれていない場合は、 PREPARE とその後に続く EXECUTE の代わりに EXECUTE IMMEDIATE を使用することができます。たとえば、次のように記述します。

move "DELETE FROM emp WHERE last_name = 'Smith'" to stmtbuf
EXEC SQL
   EXECUTE IMMEDIATE :stmtbuf
END-EXEC

EXECUTE IMMEDIATE を使用すると、実行のたびに SQL 文が構文解析されます。文が何度も使用されるようであれば、文を PREPARE (準備) して、必要に応じて EXECUTE (実行) するとよいでしょう。

6.3.2 FREE 文 (COBSQL Informix)

Informix プリコンパイラでは、準備された文やカーソルに割り当てられたリソースを開放する FREE 文を提供しています。

一度、準備された文を終了すると、FREE 文を使用することができます。たとえば、次のように記述します。

move "INSERT INTO publishers VALUES (?,?,?,?)" to stmtbuf
EXEC SQL
  PREPARE stmt1 FROM :stmtbuf
END-EXEC
... 
EXEC SQL
  EXECUTE stmt1 USING :pubid,:pubname,:city,:state
END-EXEC.
...
EXEC SQL
  FREE stmt1
END-EXEC

6.4 動的 SQL 文とカーソル

動的 SQL 文が結果を返す場合は、EXECUTE 文を使用できません。その代わりに、カーソルを宣言して使用する必要があります。

まず、DECLARE CURSOR 文を使用してカーソルを宣言します。

EXEC SQL
   DECLARE C1 CURSOR FOR dynamic_sql
END-EXEC

上記の例では、dynamic_sql が動的 SQL 文の名前です。カーソルを開く前に、PREPARE 文を使用して動的 SQL 文を準備する必要があります。たとえば、次のように記述します。

move "SELECT char_col FROM mfesqltest WHERE int_col = ?" 
                                                 to sql-text
EXEC SQL
   PREPARE dynamic_sql FROM :sql-text
END-EXEC

次に、OPEN 文を使用して、カーソルを開き、準備された文を実行します。

EXEC SQL
   OPEN C1 USING :int-col
END-EXEC

準備された文がパラメータマーカを使用している場合は、ホスト変数か SQLDA 構造のどちらかを指定して、これらのパラメータに OPEN 文で値を提供する必要があります。

カーソルが開いたら、FETCH 文を使用してデータを取り出します。たとえば、次のように記述します。

EXEC SQL
   FETCH C1 INTO :char-col
END-EXEC

FETCH 文に関する十分な検討は、『カーソル』の章を参照してください。

最後に、CLOSE 文を使用してカーソルを閉じます。

EXEC SQL
   CLOSE C1
END-EXEC

CLOSE 文に関する十分な検討は、『カーソル』の章を参照してください。

6.4.1 CALL 文

CALL 文は、動的 SQL文として準備し、実行することができます。これは Open ESQL のプリコンパイラでのみサポートされています。

静的 SQL でホスト変数を使用していれば、パラメータマーカ (?) を動的 SQL に使用することができます。

パラメータマーカに続く IN、INPUT、OUT、OUTPUT、INOUT、CURSOR キーワードの使用は、静的 SQL のホスト変数パラメータの後でのそれらの使用と同じです。 CALL 文全体は ODBC の標準的なストアドプロシージャの文法に準拠しており、 { } で囲まなければなりません (Open　ESQL プリコンパイラは、静的 SQL ではユーザの代わりにこれを行います)。たとえば、次のように記述します。

     move '{call myproc(?, ? out)}' to sql-text
     exec sql prepare mycall from :sql-text end-exec
     exec sql execute mycall using :parm1, :param2 end-exec

配列パラメータを使用する場合は、EXECUTE の上にある FOR 句で使用する要素数を制限することができます。たとえば、次のように記述します。

     move 5 to param-count
     exec sql 
       for :param-count execute mycall using :parm1, :param2
     end-exec