方法3は方法2に似ていますが、PREPARE
文をカーソルの定義および操作に必要な文と結合する点が異なります。これによって、プログラムで問合せを受け入れて処理できます。実際、動的SQL文が問合せの場合、方法3または4を使用する必要があります。
方法3では、プリコンパイル時に、問合せ選択リストの列数と入力ホスト変数のプレースホルダの数がわかっている必要があります。ただし、表や列などのデータベース・オブジェクトの名前は、実行時に指定できます(ホスト変数と重複する名前は無効です)。問合せ結果を限定、分類、ソートする句(WHERE
、GROUP
BY
およびORDER
BY
など)も、実行時に指定できます。
方法3では、埋込みSQL文を次のような順序で使用します。
PREPARE statement_name FROM { :host_string | string_literal }; DECLARE cursor_name CURSOR FOR statement_name; OPEN cursor_name [USING host_variable_list]; FETCH cursor_name INTO host_variable_list; CLOSE cursor_name;
次に、それぞれの文の実行内容を説明します。
PREPARE
は動的SQL文を解析し、名前を指定します。次の例では、PREPARE
は文字列select_stmtに格納されている問合せを解析し、これにsql_stmtという名前を指定します。
set select_stmt = 'SELECT MGR, JOB FROM EMP WHERE SAL < :salary'; EXEC SQL PREPARE sql_stmt FROM :select_stmt;
通常、問合せのWHERE
句は、実行時に端末から入力するか、アプリケーションによって生成されます。
識別子sql_stmtは、ホスト変数でもプログラム変数でもありませんが、一意にする必要があります。sql_stmtは特定の動的SQL文を指定します。
DECLARE
は、カーソルに名前を指定し、これを特定の問合せに関連付けてカーソルを定義します。カーソルの宣言は、そのプリコンパイル・ユニット内でのみ有効です。次の例では、DECLARE
によりemp_cursorという名前のカーソルを定義し、それをsql_stmtに関連付けています。
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
識別子sql_stmtおよびemp_cursorは、ホスト変数でもプログラム変数でもありませんが、一意であることが必要です。同じ文名を使用して2つのカーソルを宣言すると、プリコンパイラではこの2つのカーソル名を同義とみなします。たとえば次の文を実行したとします。
EXEC SQL PREPARE sql_stmt FROM :select_stmt; EXEC SQL DECLARE emp_cursor FOR sql_stmt; EXEC SQL PREPARE sql_stmt FROM :delete_stmt; EXEC SQL DECLARE dept_cursor FOR sql_stmt;
この場合、emp_cursorをOPENすると、処理の対象となるのはdelete_stmtに格納されている動的SQL文で、select_stmt.に格納されている動的SQL文ではありません。
CLOSE
は、カーソルを無効にします。カーソルをCLOSE
すると、そこからのFETCH
はできなくなります。例では次のように、CLOSE
文によりemp_cursorが無効になります。
EXEC SQL CLOSE emp_cursor;
次のプログラムでは、問合せのWHERE
句で使用する検索条件の入力をユーザーに求めてから、方法3を使用して問合せを準備し、実行します。
EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); dept_number INTEGER; emp_name CHARACTER(10); salary REAL; select_stmt CHARACTER(120); EXEC SQL END DECLARE SECTION; search_cond CHARACTER(40); EXEC SQL INCLUDE SQLCA; display 'Username? '; read username; display 'Password? '; read password; EXEC SQL WHENEVER SQLERROR GOTO sql_error; EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; set select_stmt = 'SELECT ENAME,SAL FROM EMP WHERE '; display 'Enter a search condition for the following statement:'; display select_stmt; read search_cond; concatenate select_stmt, search_cond; EXEC SQL PREPARE sql_stmt FROM :select_stmt; EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND GOTO no_more; display 'Employee Salary'; display '-------- ------'; LOOP EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; display emp_name, salary; ENDLOOP; no_more: EXEC SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; exit program; sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; exit program with an error;