方法3の使用について

方法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

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は、カーソルに名前を指定し、これを特定の問合せに関連付けてカーソルを定義します。カーソルの宣言は、そのプリコンパイル・ユニット内でのみ有効です。次の例では、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文ではありません。

OPEN

OPENは、アクティブ・セットを識別して、Oracleカーソルを割り当て、入力ホスト変数にバインドし、問合せを実行します。さらにOPENにより、アクティブ・セットの最初の行にカーソルを位置付け、SQLCA内のSQLERRDの3番目の要素に保存される処理済行数を0 (ゼロ)に設定します。USING句の入力ホスト変数は、PREPARE済動的SQL文内の対応するプレースホルダに置き換わります。

例では、次に示すように、OPENによりemp_cursorを割り当て、ホスト変数salaryをWHERE句に割り当てます。

EXEC SQL OPEN emp_cursor USING :salary;

FETCH

FETCHは、アクティブ・セットから行を戻し、選択リスト内の列値をINTO句の対応するホスト変数に割り当てた後、カーソルを次の行に進めます。行がなくなると、FETCHは「データが見つかりません」というOracleエラー・コードをSQLCA内のSQLCODEに戻します。

例では、次に示すように、FETCHによってアクティブ・セットから1行戻し、MGR列とJOB列の値をホスト変数のmgr_numberとjob_titleに割り当てます。

EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title;

CLOSE

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;