| Pro*C/C++プログラマーズ・ガイド 11g リリース1（11.1） E05689-02 |
|
 戻る |
 次へ |
この章では、アプリケーションに柔軟性と機能性を持たせる高度なプログラミング技法であるOracle動的SQLの使用方法について説明します。SQL文を実行時に受け入れて処理するプログラムについて、4つの作成方法を紹介します。この章の項目は、次のとおりです。
|
注意: Oracle動的SQLは、オブジェクト型、カーソル変数、構造体の配列、DML RETURNING句、Unicode変数、およびLOBをサポートしていません。かわりにANSI動的SQL方法4を使用してください。 |
ほとんどのデータベース・アプリケーションでは、ある特定のジョブが実行されます。たとえば、ユーザーに従業員番号の入力を要求して、その後EMPおよびDEPTという表の行を更新するという単純なプログラムがあります。この場合は、プリコンパイル時にUPDATE文の構成がわかっています。つまり、変更する表、それぞれの表および列に定義されている制約、更新する列、それぞれの列のデータ型がわかっています。
しかし一部のアプリケーションでは、様々なSQL文を実行時に受け入れ（または作成し）、処理する必要があります。たとえば、汎用レポート・ライターでは、生成するレポートについてそれぞれ別のSELECT文を作成する必要があります。この場合は、文の構成は実行時まではわかりません。このような文は実行のたびに異なる可能性があります。このような文は、動的SQL文と呼ばれます。
静的SQL文とは異なり、動的SQL文はソース・プログラムには埋め込まれません。そのかわり、これらの文は実行時にプログラムに入力される（または、プログラムによって作成される）文字列に格納されます。動的SQL文は対話形式で入力できるのみでなく、ファイルから読み込むこともできます。
通常の埋込みSQLプログラムと比べると、動的に定義されたSQL文を受け入れて処理するホスト・プログラムの方が柔軟性は高くなります。動的SQL文は、SQLの知識がほとんどないユーザーでも対話形式で作成できます。
たとえば、SELECT文、UPDATE文またはDELETE文のWHERE句内で使用する検索条件の入力をユーザーに求めるという単純なプログラムがあります。さらにプログラムが複雑になると、ユーザーはSQL処理、表およびビューの名前、列の名前などが表示されているメニューから選択できます。このように、動的SQLを使用すると柔軟性に富んだアプリケーションを記述できます。
ただし、動的問合せの中には複雑なコーディング、特殊なデータ構造体の使用、実行時の処理時間が必要になるものもあります。処理時間が増えることは気にならない場合もありますが、動的SQLの概念および技法を完全に理解するまで、コーディングは難しく感じられることもあります。
実際は、静的SQLによりプログラミング要件のほとんどを満たすことができます。動的SQLは、その高度な柔軟性が必要とされる場合にのみ使用してください。プリコンパイル時に次の項目のいずれかが不明の場合は、動的SQLの使用が適しています。
SQL文のテキスト（コマンド、句など）
ホスト変数の数
ホスト変数のデータ型
データべース・オブジェクトの参照（列、索引、順序、表、ユーザー名、ビューなど）
動的SQL文を記述するには、文字列に有効なSQL文を示すテキストが格納される必要がありますが、このときEXEC SQL句、文終了記号または次に示す埋込みSQLコマンドを含まないでください。
ALLOCATE
CLOSE
DECLARE
DESCRIBE
EXECUTE
FETCH
FREE
GET
INCLUDE
OPEN
PREPARE
SET
WHENEVER
ほとんどの場合、この文字列にはダミーのホスト変数を含むことができます。これらはSQL文内に実際のホスト変数のための場所を確保します。ダミーのホスト変数はプレースホルダにすぎないため宣言する必要はなく、しかも任意の名前を指定できます。たとえば、Oracleでは次の2つの文字列は区別されません。
'DELETE FROM EMP WHERE MGR = :mgr_number AND JOB = :job_title' 'DELETE FROM EMP WHERE MGR = :m AND JOB = :j'
一般にアプリケーション・プログラムでは、SQL文のテキストおよびその文で使用するホスト変数の値をユーザーが入力する必要があります。入力されたSQL文は、Oracleにより構文規則に従っているかどうかが解析されます。
次にこのホスト変数はOracleによりSQL文にバインドされます。つまり、Oracleがホスト変数のアドレスを取得するため、値の読込みと書込みができます。
その後、このSQL文が実行されます。つまり、OracleではこのSQL文で要求された処理（表からの行の削除など）が実行されます。
この項では、動的SQL文の定義に使用できる4つの方法を紹介します。まずそれぞれの方法の機能と制限事項を簡単に説明した後、適切な方法を選択するためのガイドラインを示します。この後の節でこれらの方法の使用方法を説明します。また、学習用にサンプル・プログラムを示します。
この4つの方法は番号が大きくなるに従って対象が広がります。つまり、方法2は方法1を包含し、方法3は方法1と方法2を包含します。ただし、表13-1のように、それぞれの方法は特定の種類のSQL文を処理するのに適しています。
表13-1 動的SQLの使用方法
| 方法 | SQL文の種類 |
|---|---|
|
1 |
ホスト変数のない非問合せ |
|
2 |
入力ホスト変数の数がわかっている非問合せ |
|
3 |
選択リスト項目の数と入力ホスト変数の数がわかっている問合せ |
|
4 |
選択リスト項目の数または入力ホスト変数の数が不明の問合せ |
|
注意: 選択リスト項目には、SAL * 1.10およびMAX(SAL)などの列名と式が含まれます。 |
この方法を使用すると、プログラムは動的SQL文を受け入れ（または作成し）、EXECUTE IMMEDIATEコマンドを使用してその文をすぐに実行します。このSQL文を問合せ（SELECT文）にしないでください。また、このSQL文中には入力ホスト変数のプレースホルダを指定できません。たとえば、次のホスト文字列は有効です。
'DELETE FROM EMP WHERE DEPTNO = 20' 'GRANT SELECT ON EMP TO scott'
方法1では、SQL文は実行のたびに解析されます。
この方法を使用すると、プログラムは動的SQL文を受け入れ（または作成し）、PREPAREおよびEXECUTEコマンドを使用してその文を処理します。SQL文を問合せにしないでください。プリコンパイル時に、入力ホスト変数のプレースホルダの数および入力ホスト変数のデータ型を明確にする必要があります。たとえば、次のホスト文字列はこのカテゴリに該当します。
'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)' 'DELETE FROM EMP WHERE EMPNO = :emp_number'
方法2では、SQL文の解析は1度しか行われませんが、ホスト変数に異なる値を指定して、このSQL文を複数回実行できます。SQLデータ定義文（CREATEやGRANTなど）は、PREPAREの際に実行されます。
この方法を使用すると、プログラムは動的問合せを受け入れ（または作成し）、DECLARE、OPEN、FETCHおよびCLOSEカーソル・コマンドとともにPREPAREコマンドを使用してその問合せを処理します。プリコンパイル時に、選択リスト項目の数、入力ホスト変数のプレースホルダの数および入力ホスト変数のデータ型を明確にする必要があります。たとえば、次のホスト文字列は有効です。
'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO' 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number'
この方法を使用すると、プログラムは動的SQL文を受け入れ（または作成し）、記述子を使用してその文を処理します。選択リスト項目の数、入力ホスト変数のプレースホルダの数および入力ホスト変数のデータ型は実行時まで不明にできます。たとえば、次のホスト文字列はこのカテゴリに該当します。
'INSERT INTO EMP (<unknown>) VALUES (<unknown>)' 'SELECT <unknown> FROM EMP WHERE DEPTNO = 20'
方法4は、選択リスト項目の数または入力ホスト変数の数が不明の動的SQL文を実行するときに必要です。
4つの方法はいずれも、動的SQL文を文字列に格納する必要があります。このとき指定する文字列は、ホスト変数または引用符で囲んだリテラルにする必要があります。SQL文を文字列に格納するときには、EXEC SQLキーワードと文の終了記号「;」を省略します。
方法2および方法3のときは、入力ホスト変数のプレースホルダの数と入力ホスト変数のデータ型をプリコンパイル時には明確にしてください。
方法の番号が大きくなるほどアプリケーションへの制約は少なくなりますが、コードの記述は難しくなります。原則として、できるだけ簡単な方法を使用してください。ただし動的SQL文を方法1で繰り返し実行する場合は、実行のたびにその文が再解析されるのを避けるために方法2を使用します。
方法4は最も柔軟性に富んでいますが、複雑なコード記述方法および動的SQLの概念の完全な理解が求められます。一般には、方法1、2、3を使用できないときにのみ方法4を使用します。
図13-1の決定論理を基に、適切な方法を選択できます。
コマンドライン・オプションDBMS=V6_CHARと指定してプリコンパイルするときは、SQL文を格納する前に配列を空白文字で埋めてください。こうして余分な文字を消去します。別のSQL文を格納するために配列を再利用するときに、この処理が特に重要となります。原則として、SQL文を格納する前に必ずホスト文字列を初期化（または再初期化）してください。ホスト文字列はヌル文字で終了しないでください。Oracleでは、ヌル終端文字は文字列の終了とみなされません。ヌル文字はSQL文の一部とみなされます。
コマンドライン・オプションDBMS=V8と指定してプリコンパイルするときは、PREPARE文またはEXECUTE IMMEDIATE文を実行する前に、文字列がヌル文字で終了していることを確認してください。
DBMSの値が何であっても、VARCHAR変数を使用して動的SQL文を格納するときは、PREPARE文またはEXECUTE IMMEDIATE文を実行する前に、VARCHARの長さが正しく設定（または再設定）されていることを確認してください。
最も単純な動的SQL文では、結果が成功か失敗のどちらかで、ホスト変数は使用されません。次に例を示します。
'DELETE FROM table_name WHERE column_name = constant' 'CREATE TABLE table_name ...' 'DROP INDEX index_name' 'UPDATE table_name SET column_name = constant' 'GRANT SELECT ON table_name TO username' 'REVOKE RESOURCE FROM username'
方法1では、SQL文を解析すると、EXECUTE IMMEDIATEコマンドを使用してその文をすぐに実行します。コマンドには実行用のSQL文を含む文字列（ホスト変数またはリテラル）が続きます。この文を問合せにしないでください。
EXECUTE IMMEDIATE文の構文は次のとおりです。
EXEC SQL EXECUTE IMMEDIATE { :host_string | string_literal };
次の例では、ホスト変数dyn_stmtを使用して、ユーザーが入力するSQL文を格納します。
char dyn_stmt[132];
...
for (;;)
{
printf("Enter SQL statement: ");
gets(dyn_stmt);
if (*dyn_stmt == '\0')
break;
/* dyn_stmt now contains the text of a SQL statement */
EXEC SQL EXECUTE IMMEDIATE :dyn_stmt;
}
...
次の例のように、文字列リテラルを使用することも可能です。
EXEC SQL EXECUTE IMMEDIATE 'REVOKE RESOURCE FROM MILLER';
EXECUTE IMMEDIATEは入力されているSQL文を実行するたびに解析するため、方法1は1回しか実行しない文に最も適しています。一般に、データ定義言語がこのカテゴリに該当します。
次のプログラムでは動的SQL方法1を使用して、表の作成、行の挿入、挿入のコミット、表の削除を実行します。このプログラムはdemoディレクトリのsample6.pcファイルにあり、オンラインで利用できます。
/*
* sample6.pc: Dynamic SQL Method 1
*
* This program uses dynamic SQL Method 1 to create a table,
* insert a row, commit the insert, then drop the table.
*/
#include <stdio.h>
#include <string.h>
/* 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.
*/
#include <sqlca.h>
/* Include the ORACLE Communications Area, a structure through
* which ORACLE makes additional runtime status information
* available to the program.
*/
#include <oraca.h>
/* The ORACA=YES option must be specified to enable you
* to use the ORACA.
*/
EXEC ORACLE OPTION (ORACA=YES);
/* Specifying the RELEASE_CURSOR=YES option instructs Pro*C
* to release resources associated with embedded SQL
* statements after they are executed. This ensures that
* ORACLE does not keep parse locks on tables after data
* manipulation operations, so that subsequent data definition
* operations on those tables do not result in a parse-lock
* error.
*/
EXEC ORACLE OPTION (RELEASE_CURSOR=YES);
void dyn_error();
main()
{
/* Declare the program host variables. */
char *username = "SCOTT";
char *password = "TIGER";
char *dynstmt1;
char dynstmt2[10];
VARCHAR dynstmt3[80];
/* Call routine dyn_error() if an ORACLE error occurs. */
EXEC SQL WHENEVER SQLERROR DO dyn_error("Oracle error:");
/* Save text of current SQL 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");
/* Execute a string literal to create the table. This
* usage is actually not dynamic because the program does
* not determine the SQL statement at run time.
*/
puts("CREATE TABLE dyn1 (col1 VARCHAR2(4))");
EXEC SQL EXECUTE IMMEDIATE
"CREATE TABLE dyn1 (col1 VARCHAR2(4))";
/* Execute a string to insert a row. The string must
* be null-terminated. This usage is dynamic because the
* SQL statement is a string variable whose contents the
* program can determine at run time.
*/
dynstmt1 = "INSERT INTO DYN1 values ('TEST')";
puts(dynstmt1);
EXEC SQL EXECUTE IMMEDIATE :dynstmt1;
/* Execute a SQL statement in a string to commit the insert.
* Pad the unused trailing portion of the array with spaces.
* Do NOT null-terminate it.
*/
strncpy(dynstmt2, "COMMIT ", 10);
printf("%.10s\n", dynstmt2);
EXEC SQL EXECUTE IMMEDIATE :dynstmt2;
/* Execute a VARCHAR to drop the table. Set the .len field
* to the length of the .arr field.
*/
strcpy(dynstmt3.arr, "DROP TABLE DYN1");
dynstmt3.len = strlen(dynstmt3.arr);
puts((char *) dynstmt3.arr);
EXEC SQL EXECUTE IMMEDIATE :dynstmt3;
/* Commit any outstanding changes and disconnect from Oracle. */
EXEC SQL COMMIT RELEASE;
puts("\nHave a good day!\n");
return 0;
}
void
dyn_error(msg)
char *msg;
{
/* This is the Oracle error handler.
* Print diagnostic text containing the error message,
* current SQL statement, and location of error.
*/
printf("\n%.*s\n",
sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
printf("in \"%.*s...\'\n",
oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
printf("on line %d of %.*s.\n\n",
oraca.oraslnr, oraca.orasfnm.orasfnml,
oraca.orasfnm.orasfnmc);
/* Disable Oracle error checking to avoid an infinite loop
* should another error occur within this routine as a
* result of the rollback.
*/
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Roll back any pending changes and disconnect from Oracle. */
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
方法1では1段階で実行することを、方法2では2段階に分けて実行します。動的SQL文（問合せは不可）は、まずPREPARE（名前の指定と解析）され、次にEXECUTEされます。
方法2では、SQL文には入力ホスト変数と標識変数のプレースホルダを指定できます。このSQL文は1度PREPAREすれば、ホスト変数に別の値を指定して繰り返しEXECUTEできます。したがって、（ログアウトして再接続しないかぎり）COMMITまたはROLLBACKの後に再度SQL文をPREPAREする必要はありません。
方法4では、非問合せにEXECUTEを使用できます。
PREPARE文の構文は次のとおりです。
EXEC SQL PREPARE statement_name
FROM { :host_string | string_literal };
PREPAREはこのSQL文を解析して名前を指定します。
statement_nameは、ホスト変数やプログラム変数ではなく、プリコンパイラにより使用される識別子であり、そのため宣言部で宣言されません。これはEXECUTEの対象としてPREPAREした文を示しているにすぎません。
EXECUTE文の構文は次のとおりです。
EXEC SQL EXECUTE statement_name [USING host_variable_list];
host_variable_listの構文は、次のとおりです。
:host_variable1[:indicator1] [, host_variable2[:indicator2], ...]
解析したSQL文は、それぞれの入力ホスト変数に指定済の値を使用してEXECUTEにより実行されます。
次の例では、入力されたSQL文にプレースホルダnが含まれています。
...
int emp_number INTEGER;
char delete_stmt[120], search_cond[40];;
...
strcpy(delete_stmt, "DELETE FROM EMP WHERE EMPNO = :n AND ");
printf("Complete the following statement's search condition--\n");
printf("%s\n", delete_stmt);
gets(search_cond);
strcat(delete_stmt, search_cond);
EXEC SQL PREPARE sql_stmt FROM :delete_stmt;
for (;;)
{
printf("Enter employee number: ");
gets(temp);
emp_number = atoi(temp);
if (emp_number == 0)
break;
EXEC SQL EXECUTE sql_stmt USING :emp_number;
}
...
方法2では、プリコンパイル時に入力ホスト変数のデータ型を明確にする必要があります。最後の例では、emp_numberがintとして宣言されています。Oracleでは、floatやcharなど、全データ型のOracle内部NUMBERデータ型への変換がサポートされるため、emp_numberをfloatまたはcharとして宣言することも可能です。
SQL文がEXECUTEされると、USING句の入力ホスト変数は、PREPAREされた動的SQL文内の該当するプレースホルダに置換されます。
PREPAREされた動的SQL文のプレースホルダは、それぞれが必ずUSING句の個別のホスト変数に対応している必要があります。つまり、PREPAREされた文に同じプレースホルダが2回以上現れるときは、それぞれがUSING句のホスト変数に対応している必要があります。
プレースホルダの名前はホスト変数名と一致する必要はありません。ただし、PREPAREされた動的SQL文のプレースホルダの順序は、USING句の対応するホスト変数の順序と一致する必要があります。
USING句のホスト変数のうち1つでも配列があれば、すべてのホスト変数が配列である必要があります。
NULL値を指定するために、標識変数をUSING句のホスト変数と対応付けることができます。
次のプログラムでは、動的SQL方法2を使用して2つの行をEMP表に挿入し、その後でそれらの行を削除しています。このプログラムはdemoディレクトリのsample7.pcファイルにあり、オンラインで利用できます。
/*
* sample7.pc: Dynamic SQL Method 2
*
* This program uses dynamic SQL Method 2 to insert two rows into
* the EMP table, then delete them.
*/
#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.
*/
#include <sqlca.h>
/* Include the ORACLE Communications Area, a structure through
* which ORACLE makes additional runtime status information
* available to the program.
*/
#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];
int empno = 1234;
int deptno1 = 97;
int deptno2 = 99;
/* Handle SQL runtime errors. */
void dyn_error();
main()
{
/* Call dyn_error() whenever an error occurs
* processing an embedded SQL statement.
*/
EXEC SQL WHENEVER SQLERROR DO dyn_error("Oracle error");
/* Save text of current SQL 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 statement to the VARCHAR dynstmt. Both
* the array and the length parts must be set properly.
* Note that the statement contains two host-variable
* placeholders, v1 and v2, for which actual input
* host variables must be supplied at EXECUTE time.
*/
strcpy(dynstmt.arr,
"INSERT INTO EMP (EMPNO, DEPTNO) VALUES (:v1, :v2)");
dynstmt.len = strlen(dynstmt.arr);
/* Display the SQL statement and its current input host
* variables.
*/
puts((char *) dynstmt.arr);
printf(" v1 = %d, v2 = %d\n", empno, deptno1);
/* The PREPARE statement associates a statement name with
* a string containing a SQL 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 EXECUTE statement executes a PREPAREd SQL statement
* USING the specified input host variables, which are
* substituted positionally for placeholders in the
* PREPAREd statement. 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.
*
* A single PREPAREd statement can be EXECUTEd more
* than once, optionally USING different input host
* variables.
*/
EXEC SQL EXECUTE S USING :empno, :deptno1;
/* Increment empno and display new input host variables. */
empno++;
printf(" v1 = %d, v2 = %d\n", empno, deptno2);
/* ReEXECUTE S to insert the new value of empno and a
* different input host variable, deptno2.
* A rePREPARE is unnecessary.
*/
EXEC SQL EXECUTE S USING :empno, :deptno2;
/* Assign a new value to dynstmt. */
strcpy(dynstmt.arr,
"DELETE FROM EMP WHERE DEPTNO = :v1 OR DEPTNO = :v2");
dynstmt.len = strlen(dynstmt.arr);
/* Display the new SQL statement and its current input host
* variables.
*/
puts((char *) dynstmt.arr);
printf(" v1 = %d, v2 = %d\n", deptno1, deptno2);
/* RePREPARE S FROM the new dynstmt. */
EXEC SQL PREPARE S FROM :dynstmt;
/* EXECUTE the new S to delete the two rows previously
* inserted.
*/
EXEC SQL EXECUTE S USING :deptno1, :deptno2;
/* Commit any pending changes and disconnect from Oracle. */
EXEC SQL COMMIT RELEASE;
puts("\nHave a good day!\n");
exit(0);
}
void
dyn_error(msg)
char *msg;
{
/* This is the ORACLE error handler.
* Print diagnostic text containing error message,
* current SQL statement, and location of error.
*/
printf("\n%s", msg);
printf("\n%.*s\n",
sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
printf("in \"%.*s...\"\n",
oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
printf("on line %d of %.*s.\n\n",
oraca.oraslnr, oraca.orasfnm.orasfnml,
oraca.orasfnm.orasfnmc);
/* Disable ORACLE error checking to avoid an infinite loop
* should another error occur within this routine.
*/
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Roll back any pending changes and
* disconnect from Oracle.
*/
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
方法3は方法2に似ていますが、PREPARE文をカーソルの定義および処理に必要な文と結合する点で異なります。これにより、プログラムで問合せを受け入れて処理できます。実際、動的SQL文が問合せの場合は、方法3または4を使用する必要があります。
方法3では、プリコンパイル時に問合せ選択リストの列数と入力ホスト変数のプレースホルダの数を明確にする必要があります。ただし、表および列などのデータベース・オブジェクトの名前は実行時まで指定する必要はありません。データベース・オブジェクトの名前はホスト変数に指定できません。問合せ結果を限定、分類、ソートする句（WHERE、GROUP BY、ORDER BYなど）も実行時に指定できます。
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は文字列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は、カーソルに名前を指定するとともにこれを特定の問合せに対応付けてカーソルを定義します。前述の例に続いて、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すると、select_stmtに格納されている動的SQL文ではなく、delete_stmtに格納されている動的SQL文が処理されます。
OPENはアクティブ・セットを識別して、Oracleカーソルを割り当て、入力ホスト変数をバインドし、問合せを実行します。OPENはさらに、アクティブ・セットの最初の行にカーソルを位置付け、SQLCA内のsqlerrdの3番目の要素に保存される処理済行数を0（ゼロ）に設定します。USING句内の入力ホスト変数は、PREPAREされた動的SQL文内の対応するプレースホルダに置換されます。
前述の例に続いて、OPENは次に示すようにemp_cursorを割り当て、ホスト変数salaryをWHERE句に割り当てます。
EXEC SQL OPEN emp_cursor USING :salary;
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はカーソルを使用禁止にします。一度カーソルをクローズすると、それ以降はFETCHできません。
次の例では、CLOSEによりemp_cursorが使用禁止になります。
EXEC SQL CLOSE emp_cursor;
次のプログラムは、動的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);
}
この項では、動的SQL方法4の概要を説明します。Oracle動的SQL方法4は、オブジェクト型、結果セット、構造体の配列およびLOBをサポートしていません。
ANSI SQLでは、すべてのデータ型がサポートされます。すべての新しいアプリケーションでは、ANSI SQLを使用してください。
動的SQL文には、方法3を使用してもプログラムで処理できないものがあります。選択リスト項目の数または入力ホスト変数のプレースホルダの数が実行時まで不明な場合は、プログラムで記述子を使用する必要があります。記述子とは、プログラムおよびOracleが動的SQL文内の変数の完全な記述を保存するためのメモリー領域です。
複数行の問合せのときに、宣言済の出力ホスト変数のリスト内に列の値をFETCH INTOしたことを思い出してください。この選択リストが不明な場合は、プリコンパイル時にINTO句でホスト変数リストを作成できません。たとえば、開発者は次の問合せでは2つの列値が戻されることを知っています。
SELECT ename, empno FROM emp WHERE deptno = :dept_number;
しかし、この選択リストをユーザーに定義させる場合、その問合せによって戻される列の数は開発者にはわからない場合があります。
この種の動的問合せを処理するには、プログラムでDESCRIBE SELECT LISTコマンドを発行し、SQL記述子領域（SQLDA）というデータ構造体を宣言する必要があります。この構造体は問合せ選択リストの列の記述を保持しているため、選択記述子とも呼ばれます。
また、動的SQL文で入力ホスト変数のプレースホルダの数が明確でない場合、プリコンパイル時にUSING句でホスト変数リストを作成できません。
動的SQL文を処理するには、プログラムでDESCRIBE BIND VARIABLESコマンドを発行し、バインド記述子と呼ばれる別のSQLDAを宣言して、入力ホスト変数のプレースホルダの記述を保存する必要があります。（入力ホスト変数はバインド変数とも呼ばれます。）
プログラム内にアクティブなSQL文が複数ある（たとえばプログラムが複数のカーソルをOPENしている）ときは、それぞれの文に専用のSQLDAが必要になります。ただし、カーソルが同時に実行されなければSQLDAを再利用できます。なお、1つのプログラム内のSQLDAの数に制限はありません。
DESCRIBEは、選択リスト項目または入力ホスト変数の記述を保存するために記述子を初期化します。
選択記述子を指定すると、PREPAREした動的問合せのそれぞれの選択リスト項目がDESCRIBE SELECT LIST文によりチェックされます。これにより、選択リスト項目の名前、データ型、制約、長さ、位取りおよび精度が決定されます。続いて、この情報がその選択記述子に格納されます。
バインド記述子を指定すると、PREPARE文で作成された動的SQL文の各プレースホルダがDESCRIBE BIND VARIABLES文によりチェックされます。これにより、プレースホルダの名前と長さ、プレースホルダに対応付けられている入力ホスト変数のデータ型が決定されます。続いて、この情報がそのバインド記述子に格納されます。たとえば、プレースホルダ名を使用すると、ホスト変数の値の入力をユーザーに要求できます。
SQLDAはホスト・プログラムのデータ構造体です。この構造体は選択リスト項目または入力ホスト変数の記述を保持します。
SQLDA変数は、宣言部では定義されません。
選択SQLDAには問合せ選択リストに関する次の情報が格納されています。
DESCRIBEできる列の最大数
実際にDESCRIBEで検出された列数
列値を格納するバッファのアドレス
列値の長さ
列値のデータ型
標識変数の値のアドレス
列名を格納するバッファのアドレス
列名を格納するバッファのサイズ
列名の現行の長さ
バインドSQLDAには、SQL文の入力ホスト変数に関する次の情報が格納されています。
DESCRIBEできるプレースホルダの最大数
実際にDESCRIBEで検出されたプレースホルダの数
入力ホスト変数のアドレス
入力ホスト変数の長さ
入力ホスト変数のデータ型
標識変数のアドレス
プレースホルダ名を格納するバッファのアドレス
プレースホルダ名を格納するバッファのサイズ
プレースホルダ名の現行の長さ
標識変数名を格納するバッファのアドレス
標識変数名を格納するバッファのサイズ
標識変数名の現行の長さ
Oracle方法4では、一般に次の順序で埋込みSQL文を使用します。
EXEC SQL PREPARE statement_name
FROM { :host_string | string_literal };
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name;
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name
INTO bind_descriptor_name;
EXEC SQL OPEN cursor_name
[USING DESCRIPTOR bind_descriptor_name];
EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name
INTO select_descriptor_name;
EXEC SQL FETCH cursor_name
USING DESCRIPTOR select_descriptor_name;
EXEC SQL CLOSE cursor_name;
ただし、選択記述子とバインド記述子が同時に動作する必要はありません。したがって、問合せ選択リストの列数が明確でも入力ホスト変数のプレースホルダの数が不明な場合は、方法4のOPEN文とともに次の方法3のFETCH文を使用できます。
EXEC SQL FETCH emp_cursor INTO host_variable_list;
逆に、入力ホスト変数のプレースホルダの数は明確でも問合せ選択リストの列数が不明な場合は、方法4のFETCH文とともに次の方法3のOPEN文を使用できます。
EXEC SQL OPEN cursor_name [USING host_variable_list];
方法4では、EXECUTEを非問合せにも使用できます。
方法2、3および4では、次の文を使用する必要がある場合があります。
EXEC SQL [AT db_name] DECLARE statement_name STATEMENT;
db_nameおよびstatement_nameは、プリコンパイラにより使用される識別子で、ホスト変数でもプログラム変数でもありません。
DECLARE STATEMENTによって動的SQL文の名前が宣言されます。すると、この動的SQL文はPREPARE、EXECUTE、DECLARE CURSORおよびDESCRIBEで参照できます。デフォルト以外のデータベースで動的SQL文を実行するときに、この文が必要になります。方法2での使用例を次に示します。
EXEC SQL AT remote_db DECLARE sql_stmt STATEMENT; EXEC SQL PREPARE sql_stmt FROM :dyn_string; EXEC SQL EXECUTE sql_stmt;
この例では、どこでSQL文をEXECUTEするかをremote_dbによってOracleに指示します。
方法3および方法4では、次の例に示すようにDECLARE CURSOR文がPREPARE文の前にあるときにもDECLARE STATEMENTが必要です。
EXEC SQL DECLARE sql_stmt STATEMENT; EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; EXEC SQL PREPARE sql_stmt FROM :dyn_string;
一般的な文の順序は次のとおりです。
EXEC SQL PREPARE sql_stmt FROM :dyn_string; EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
静的SQLおよび動的SQL内でのホスト配列の使用方法は似ています。たとえば、動的SQL方法2で入力ホスト配列を使用するには、次の構文を使用します。
EXEC SQL EXECUTE statement_name USING host_array_list;
host_array_listには1つ以上のホスト配列が格納されます。
同様に、方法3で入力ホスト配列を使用するには、次の構文を使用します。
OPEN cursor_name USING host_array_list;
また方法3で出力ホスト配列を使用するには、次の構文を使用します。
FETCH cursor_name INTO host_array_list;
方法4では、オプションのFOR句を使用してOracleに入力ホスト配列または出力ホスト配列のサイズを指示する必要があります。
Pro*C/C++プリコンパイラでは、PL/SQLブロックが単一のSQL文として取り扱われます。したがってSQL文と同様に、PL/SQLブロックを文字列ホスト変数またはリテラルに格納できます。文字列にPL/SQLブロックを格納する場合は、EXEC SQL EXECUTEキーワード、END-EXECキーワードおよび文の終了記号「;」を省略します。
ただし、プリコンパイラによるSQLとPL/SQLの処理方法には次の2つの違いがあります。
PL/SQLホスト変数のPL/SQLブロック内での役割が入力ホスト変数、出力ホスト変数、あるいはその両方のうちどの場合であっても、プリコンパイラではPL/SQLホスト変数がすべて入力ホスト変数として扱われます。
PL/SQLブロックに格納できるSQL文の数には制限がないため、PL/SQLブロックからはFETCHできません。
PL/SQLブロック内の入力ホスト変数および出力ホスト変数の数が明確である場合は、方法2で通常どおりPL/SQL文字列をPREPAREおよびEXECUTEできます。
USING句には、すべてのホスト変数を指定する必要があります。このPL/SQL文をEXECUTEすると、USING句内のホスト変数はPREPAREされた文字列内の対応するプレースホルダに置換されます。プリコンパイラがPL/SQLホスト変数をすべて入力ホスト変数として扱っても、値は正しく代入されます。入力（プログラム）値は入力ホスト変数に代入されます。また出力（列）値は出力ホスト変数に代入されます。
PREPAREされたPL/SQL文字列中のプレースホルダは、それぞれUSING句のホスト変数に対応している必要があります。したがって、PREPAREされた文に同じプレースホルダが2回以上現れるときは、それぞれがUSING句の個別のホスト変数に対応している必要があります。
PL/SQLブロックに含まれる入力または出力ホスト変数の数が不明な場合は、方法4を使用する必要があります。
方法4を使用するには、すべての入力ホスト変数および出力ホスト変数について1つのバインド記述子を設定します。DESCRIBE BIND VARIABLESを実行すると、入力ホスト変数および出力ホスト変数に関する情報がそのバインド記述子に格納されます。プリコンパイラではPL/SQLホスト変数がすべて入力ホスト変数として扱われるため、DESCRIBE SELECT LISTを実行しても効果はありません。
|
注意: 動的SQL方法4では、TABLE型のパラメータを使用して、ホスト配列をPL/SQLプロシージャにバインドすることはできません。 |
文のキャッシュとは、セッションごとに文のキャッシュを提供し、管理する機能のことです。サーバーでは、文を再度解析することなく、カーソルがいつでも使用できることを意味します。文のキャッシュは、プリコンパイラ・アプリケーションで有効にできます。この機能は、動的SQL文に依存するすべてのアプリケーションのパフォーマンス向上に役立ちます。つまり、再利用時に動的文を解析するオーバーヘッドをなくすことで、パフォーマンスの向上が実現します。
このパフォーマンスの向上は、新しいコマンドライン・オプションstmt_cache（文のキャッシュ・サイズ用）を使用することで実現できます。このオプションにより、動的文の文のキャッシュが有効になります。新しいオプションを有効にすることで、文のキャッシュがセッション作成時に作成されます。キャッシュは動的文に対してのみ適用され、静的文のカーソル・キャッシュとこの機能は共存します。
コマンドライン・オプションstmt_cacheには、0〜65535の任意の値を指定できます。文のキャッシュは、デフォルトでは無効です（値0）。stmt_cacheオプションは、アプリケーションで予想される数の個別の動的SQL文を保持するために設定できます。
例13-1 stmt_cacheオプションの使用方法
この例では、stmt_cacheオプションの使用方法を示しています。このプログラムでは、表に複数の行を挿入し、ループでカーソルを使用することで、挿入された行を選択します。stmt_cacheオプションをこのプログラムのプリコンパイルに使用すると、通常のプリコンパイルと比べてパフォーマンスが向上します。
/*
* stmtcache.pc
*
* NOTE:
* When this program is used to measure the performance with and without
* stmt_cache option, do the following changes in the program,
* 1. Increase ROWSCNT to high value, say 10000.
* 2. Remove all the print statements, usually which comsumes significant
* portion of the total program execution time.
*
* HINT: In Linux, gettimeofday() can be used to measure time.
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <oraca.h>
#define ROWSCNT 10
char *username = "scott";
char *password = "tiger";
/* Function prototypes */
void sql_error(char *msg);
void selectdata();
void insertdata();
int main()
{
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");
/* Connect using the default schema scott/tiger */
EXEC SQL CONNECT :username IDENTIFIED BY :password;
/* core functions to insert and select the data */
insertdata();
selectdata();
/* Rollback pll the changes and disconnect from Oracle. */
EXEC SQL ROLLBACK WORK RELEASE;
exit(0);
}
/*Insert the data for ROWSCNT items into tpc2sc01 */
void insertdata()
{
varchar dynstmt[80];
int i;
varchar ename[10];
float comm;
char *str;
/* Allocates temporary buffer */
str = (char *)malloc (11 * sizeof(char));
strcpy ((char *)dynstmt.arr,
"INSERT INTO bonus (ename, comm) VALUES (:ename, :comm)");
dynstmt.len = strlen(dynstmt.arr);
EXEC SQL PREPARE S FROM :dynstmt;
printf ("Inserts %d rows into bonus table using dynamic SQL statement\n",
ROWSCNT);
for (i=1; i<=ROWSCNT; i++)
{
sprintf (str, "EMP_%05d",i);
strcpy (ename.arr, str);
comm = i;
ename.len = strlen (ename.arr);
EXEC SQL EXECUTE S USING :ename, :comm;
}
free(str);
}
/* Select the data using the cursor */
void selectdata()
{
varchar dynstmt[80];
varchar ename[10];
float comm;
int i;
strcpy((char *)dynstmt.arr,
"SELECT ename, comm FROM bonus WHERE comm = :v1");
dynstmt.len = (unsigned short)strlen((char *)dynstmt.arr);
printf ("Fetches the inserted rows using using dynamic SQL statement\n\n");
printf (" ENAME COMMISSION\n\n");
for (i=1; i<=ROWSCNT; i++)
{
/* Do the prepare in the loop so that the advantage of stmt_caching
is visible*/
EXEC SQL PREPARE S FROM :dynstmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C USING :i;
EXEC SQL WHENEVER NOT FOUND DO break;
/* Loop until the NOT FOUND condition is detected. */
for (;;)
{
EXEC SQL FETCH C INTO :ename, :comm;
ename.arr[ename.len] = '\0';
printf ("%10s %7.2f\n", ename.arr, comm);
}
/* Close the cursor so that the reparsing is not required for stmt_cache */
EXEC SQL CLOSE C;
}
}
void sql_error(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 WORK RELEASE;
exit(1);
}
