動的SQL

アプリケーションのコンパイル時にソースコードに完全に記述されている SQL 文を、静的 SQL と呼びます。

ただし、アプリケーションの作成時に SQL 文の内容が完全に特定できないこともあります。たとえば、アプリケーションの実行時にエンドユーザーが任意の SQL 文を入力する場合などです。この場合は、SQL 文を実行時に作成する必要があります。このような文を、動的 SQL 文を呼びます。

動的 SQL 文のタイプ
動的 SQL 文の準備
動的 SQL 文の実行
動的 SQL 文とカーソル

動的 SQL 文のタイプ

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

動的 SQL 文のタイプ	クエリを実行するかどうか	データを返すかどうか
文を 1 回実行する	実行しない	成功または失敗のみ返す
文を複数回実行する	実行しない	成功または失敗のみ返す
特定の選択基準を使用して、特定のデータリストを選択する	実行する	返す
何らかの選択基準で任意の量のデータを選択する	実行する	返す

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

文を 1 回実行する

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

文を複数回実行する

このタイプの動的 SQL 文は、複数回実行する可能性のある文か、または、ホスト変数を必要とする文です。この場合は、PREPARE 文で定義した文を実行する必要があります。

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

このタイプの動的 SQL 文は、ホスト変数の数と型が判明している SELECT 文です。この SQL 文の通常のシーケンスは次のとおりです。

文を準備します。
結果を保持するカーソルを宣言します。
カーソルをオープンします。
変数を取り出します。
カーソルをクローズします。

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

この動的 SQL 文は、コーディングするのが最も難しいタイプです。変数の型と個数の両方、または一方は実行時のみに解決されます。この SQL 文の通常のシーケンスは次のとおりです。

文を準備します。
文に対してカーソルを宣言します。
使用する変数を記述します。
記述した変数を使用してカーソルをオープンします。
取り出す変数を記述します。
その記述内容を使用して変数を取りします。
カーソルをクローズします。

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

動的 SQL 文の準備

PREPARE 文では、動的 SQL 文を含む文字列を受け取り、名前と文を関連付けます。次に例を示します。

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

動的 SQL 文では、値に対するプレースホルダーとしてパラメーターマーカーである、疑問符 (?) を使用できます。上記の例では、4 つの疑問符 (?) に対応する値を、文の実行時に指定する必要があります。

PREPARE 文で定義した SQL 文は、次のどちらかの方法で実行します。

定義した文を実行する。
定義した文を参照するカーソルをオープンする。

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 型のホスト変数として定義されています。

PREPARE 文の物理的な位置

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

動的 SQL 文の実行

PREPARE 文で定義した SQL 文は、EXECUTE 文で個別に実行されます。

注：この方法で実行できるのは、結果を返さない SQL 文のみです。

PREPARE 文で定義した文にパラメーターマーカーが含まれている場合は、EXECUTE 文で using :hvar オプションを使用し、ホスト変数名を記述してパラメーターを指定するか、または、using descriptor :sqlda_struct オプションを使用し、アプリケーションによって値がすでに格納されている SQLDA データ構造体を識別する必要があります。PREPARE 文で定義した文に含まれるパラメーターマーカー数は、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 句で指定した 4 つのホスト変数の値によって置き換えます。

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 を使用する場合は、文が実行されるたびに構文解析が再度行われます。文を何回も使用するような場合には、文を PREPARE として実行し、必要に応じて EXECUTE を実行するほうが効率的です。

FREE 文 (COBSQL Informix)

Informix プリコンパイラには、PREPARE 文で定義された文またはカーソルに割り当てられたリソースを解放する FREE 文があります。

PREPARE 文で定義された文が終了した後に、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

動的 SQL 文とカーソル

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

まず、DECLARE CURSOR 文で次のようにカーソルを宣言します。

EXEC SQL
   DECLARE C1 CURSOR FOR dynamic_sql
END-EXEC

この例では、dynamic_sql が動的 SQL 文の名前です。この動的 SQL 文は、宣言したカーソルをオープンする前に PREPARE 文で定義する必要があります。

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

そして、OPEN 文を使用してカーソルをオープンする場合は、PREPARE 文で定義した文が実行されます。

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

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

カーソルをオープンした後に、FETCH 文を使用してデータを取り出すことができます。次に例を示します。

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

FETCH 文の詳細は、『カーソル』の章を参照してください。

最後に、CLOSE 文を使用してカーソルをクローズします。

EXEC SQL
   CLOSE C1
END-EXEC

CLOSE 文の詳細は、『カーソル』の章を参照してください

CALL 文

CALL 文は、動的 SQL として準備および実行できます。これは、OpenESQL プリコンパイラのみでサポートされます。

静的 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
```

例

次に、データソース「SQLServer 2000」を使用してストアドプロシージャ「mfexecsptest」を作成し、動的 SQL でカーソル「c1」を使用して「publishers」テーブルからデータを取り出すプログラム例を示します。

$SET SQL
 WORKING-STORAGE SECTION.

 EXEC SQL INCLUDE SQLCA  END-EXEC

*> SQL エラーが発生する場合は、ここに詳細なメッセージテキストが示されます。
 01 MFSQLMESSAGETEXT  PIC X(250).
 01 IDX               PIC X(04)  COMP-5.

 EXEC SQL BEGIN DECLARE SECTION  END-EXEC
*> 他の COBOL コンパイラに移植する必要がある場合は、
*> ここにホスト変数を記述します。

 01  stateParam          pic xx.
 01  pubid               pic x(4).
 01  pubname             pic x(40).
 01  pubcity             pic x(20).

 01  sql-stat            pic x(256).

 EXEC SQL END DECLARE SECTION END-EXEC

 PROCEDURE DIVISION.

     EXEC SQL
         WHENEVER SQLERROR perform OpenESQL-Error
     END-EXEC

     EXEC SQL
         CONNECT TO 'SQLServer 2000' USER 'SA'
     END-EXEC

*> プログラムロジックと SQL 文をここに記述します。

     EXEC SQL
         create procedure mfexecsptest
                 (@stateParam char(2) = 'NY' ) as

         select pub_id, pub_name, city from publishers
          where state = @stateParam
     END-EXEC

     exec sql
         declare c1 scroll cursor for dsql2 for read only
     end-exec

     move "{call mfexecsptest(?)}" to sql-stat
     exec sql prepare dsql2 from :sql-stat end-exec

     move "CA" to stateParam
     exec sql
         open c1 using :stateParam
     end-exec

     display "ストアドプロシージャをもつカーソルをテストします"
     perform until exit
         exec sql
             fetch c1 into :pubid, :pubname, :pubcity
         end-exec

         if sqlcode = 100
             exec sql close c1 end-exec
             exit perform
         else
             display pubid " " pubname " " pubcity
         end-if
     end-perform

      EXEC SQL close c1  END-EXEC

      EXEC SQL DISCONNECT CURRENT END-EXEC
      EXIT PROGRAM.
      STOP RUN.
*> デフォルトの SQL エラールーチン。
*> 必要に応じて修正してプログラムを停止します。
 OpenESQL-Error Section.

     display "SQL エラー = " sqlstate " " sqlcode
     display MFSQLMESSAGETEXT
      *> stop run
     exit.

上位ヘルプ: 動的SQL