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

方法3では、スクロール可能カーソルも使用できます。スクロール可能カーソルには、埋込みSQL文を次の順序で使用する必要があります。

PREPARE statement_name FROM { :host_string | string_literal }; 
DECLARE  cursor_name  SCROLL CURSOR FOR statement_name; 
OPEN cursor_name [USING host_variable_list]; 
FETCH  [ FIRST| PRIOR|NEXT|LAST|CURRENT | RELATIVE fetch_offset 
               |ABSOLUTE fetch_offset ]  cursor_name INTO host_variable_list; 
CLOSE cursor_name;

各文の機能を次に説明します。

PREPARE (動的SQL)

PREPAREはこの動的SQL文を解析し、名前を指定します。次の例では、PREPAREは文字列select_stmt内の問合せを解析し、これにsql_stmtという名前を指定します。

char select_stmt[132] =
     "SELECT MGR, JOB FROM EMP WHERE SAL < :salary"; 
EXEC SQL PREPARE sql_stmt FROM :select_stmt; 

一般的には、この問合せのWHERE句は実行時に端末から入力するか、またはアプリケーションによって生成されます。

識別子sql_stmtは、ホスト変数でもプログラム変数でもありませんが、一意にする必要があります。sql_stmtは特定の動的SQL文を指定します。

次の文も有効です。

EXEC SQL PREPARE sql_stmt FROM SELECT MGR, JOB FROM EMP WHERE SAL < :salary;

'%'ワイルドカードを使用する次のPREPARE文も正しい文です。

EXEC SQL PREPARE S FROM select ename FROM test WHERE ename LIKE 'SMIT%';

DECLARE (動的SQL)

DECLAREは、カーソルに名前を指定し、これを特定の問合せに関連付けてカーソルを定義します。次の例では、DECLAREによりemp_cursorという名前のカーソルを定義し、それをsql_stmtに関連付けています。

EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; 

識別子sql_stmtおよびemp_cursorは、ホスト変数でもプログラム変数でもありませんが、一意であることが必要です。同じ文名を使用して2つのカーソルを宣言すると、プリコンパイラではこの2つのカーソル名を同義とみなします。

emp_cursorという名前のスクロール可能カーソルを定義して、これをsql_stmtに対応付けすることが可能です。

EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR sql_stmt;

たとえば次の文を実行したとします。

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 (動的SQL)

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

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

EXEC SQL OPEN emp_cursor USING :salary; 

FETCH (動的SQL)

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

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

EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title; 

カーソルがSCROLLモードで宣言されている場合は、様々なFETCH方向モードを使用して結果セットにランダムにアクセスできます。

CLOSE (動的SQL)

CLOSEはカーソルを使用禁止にします。一度カーソルをCLOSEすると、それ以降はFETCHできなくなります。

次の例では、CLOSEによりemp_cursorが使用禁止になります。

EXEC SQL CLOSE emp_cursor; 

サンプル・プログラム: 動的SQL方法3

次のプログラムは、動的SQL方法3を使用してEMP表から指定された部門のすべての従業員の名前を検索します。このプログラムはdemoディレクトリのsample8.pc ファイルにあり、オンラインで利用できます。

/*
 *  sample8.pc:  Dynamic SQL Method 3
 *
 *  This program uses dynamic SQL Method 3 to retrieve the names
 *  of all employees in a given department from the EMP table.
 */

#include <stdio.h>
#include <string.h>

#define USERNAME "SCOTT"
#define PASSWORD "TIGER"

/* Include the SQL Communications Area, a structure through
 * which ORACLE makes runtime status information such as error
 * codes, warning flags, and diagnostic text available to the
 * program. Also include the ORACA.
 */
#include <sqlca.h>
#include <oraca.h>

/* The ORACA=YES option must be specified to enable use of
 * the ORACA.
 */
EXEC ORACLE OPTION (ORACA=YES);

char    *username = USERNAME;
char    *password = PASSWORD;
VARCHAR  dynstmt[80];
VARCHAR  ename[10];
int      deptno = 10;

void dyn_error();


main()
{
/* Call dyn_error() function on any error in
 * an embedded SQL statement.
 */
    EXEC SQL WHENEVER SQLERROR DO dyn_error("Oracle error");

/* Save text of SQL current statement in the ORACA if an
 * error occurs.
 */
    oraca.orastxtf = ORASTFERR;

/* Connect to Oracle. */

    EXEC SQL CONNECT :username IDENTIFIED BY :password;
    puts("\nConnected to Oracle.\n");

/* Assign a SQL query to the VARCHAR dynstmt.  Both the
 * array and the length parts must be set properly.  Note
 * that the query contains one host-variable placeholder,
 * v1, for which an actual input host variable must be
 * supplied at OPEN time.
 */
    strcpy(dynstmt.arr,
        "SELECT ename FROM emp WHERE deptno = :v1");
    dynstmt.len = strlen(dynstmt.arr);

/* Display the SQL statement and its current input host
 * variable.
 */
    puts((char *) dynstmt.arr);
    printf("   v1 = %d\n", deptno);
    printf("\nEmployee\n");
    printf("--------\n");

/* The PREPARE statement associates a statement name with
 * a string containing a SELECT statement.  The statement
 * name is a SQL identifier, not a host variable, and
 * therefore does not appear in the Declare Section.

 * A single statement name can be PREPAREd more than once,
 * optionally FROM a different string variable.
 */
    EXEC SQL PREPARE S FROM :dynstmt;

/* The DECLARE statement associates a cursor with a
 * PREPAREd statement.  The cursor name, like the statement
 * name, does not appear in the Declare Section.

 * A single cursor name cannot be DECLAREd more than once.
 */
    EXEC SQL DECLARE C CURSOR FOR S;

/* The OPEN statement evaluates the active set of the
 * PREPAREd query USING the specified input host variables,
 * which are substituted positionally for placeholders in
 * the PREPAREd query.  For each occurrence of a
 * placeholder in the statement there must be a variable
 * in the USING clause.  That is, if a placeholder occurs
 * multiple times in the statement, the corresponding
 * variable must appear multiple times in the USING clause.

 * The USING clause can be omitted only if the statement
 * contains no placeholders.  OPEN places the cursor at the
 * first row of the active set in preparation for a FETCH.

 * A single DECLAREd cursor can be OPENed more than once,
 * optionally USING different input host variables.
 */
    EXEC SQL OPEN C USING :deptno;

/* Break the loop when all data have been retrieved. */

    EXEC SQL WHENEVER NOT FOUND DO break;

/* Loop until the NOT FOUND condition is detected. */

    for (;;)
    {
/* The FETCH statement places the select list of the
 * current row into the variables specified by the INTO
 * clause, then advances the cursor to the next row.  If
 * there are more select-list fields than output host
 * variables, the extra fields will not be returned.
 * Specifying more output host variables than select-list
 * fields results in an ORACLE error.
 */
        EXEC SQL FETCH C INTO :ename;

/* Null-terminate the array before output. */
        ename.arr[ename.len] = '\0';
        puts((char *) ename.arr);
        }

/* Print the cumulative number of rows processed by the
 * current SQL statement.
 */
    printf("\nQuery returned %d row%s.\n\n", sqlca.sqlerrd[2],
        (sqlca.sqlerrd[2] == 1) ? "" : "s");

/* The CLOSE statement releases resources associated with
 * the cursor.
 */
    EXEC SQL CLOSE C;

/* Commit any pending changes and disconnect from Oracle. */
    EXEC SQL COMMIT RELEASE;
    puts("Sayonara.\n");
    exit(0);
}

void
dyn_error(msg)
char *msg;
{
    printf("\n%s", msg);
    sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
    oraca.orastxt.orastxtc[oraca.orastxt.orastxtl] = '\0';
    oraca.orasfnm.orasfnmc[oraca.orasfnm.orasfnml] = '\0';
    printf("\n%s\n", sqlca.sqlerrm.sqlerrmc);
    printf("in \"%s...\"\n", oraca.orastxt.orastxtc);
    printf("on line %d of %s.\n\n", oraca.oraslnr,
       oraca.orasfnm.orasfnmc);

/* Disable ORACLE error checking to avoid an infinite loop
 * should another error occur within this routine.
 */
    EXEC SQL WHENEVER SQLERROR CONTINUE;

/* Release resources associated with the cursor. */
    EXEC SQL CLOSE C;

/* Roll back any pending changes and disconnect from Oracle. */  
    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}