13 Oracle動的SQL
この章では、アプリケーションに柔軟性と機能性を持たせる高度なプログラミング技法であるOracle動的SQLの使用方法について説明します。SQL文を実行時に受け入れて処理するプログラムについて、4つの作成方法を紹介します。この章のトピックは、次のとおりです:
注意:
Oracle動的SQLは、オブジェクト型、カーソル変数、構造体の配列、DML RETURNING句、Unicode変数、およびLOBをサポートしていません。かわりにANSI動的SQL方法4を使用してください。
13.1 動的SQL
ほとんどのデータベース・アプリケーションでは、ある特定のジョブが実行されます。たとえば、ユーザーに従業員番号の入力を要求して、その後EMPおよびDEPTの表の行を更新するという単純なプログラムがあります。この場合は、プリコンパイル時にUPDATE文の構成がわかっています。つまり、変更する表、それぞれの表および列に定義されている制約、更新する列、それぞれの列のデータ型がわかっています。
しかし、アプリケーションによっては、様々なSQL文を実行時に受け入れ(または作成し)、処理する必要があります。たとえば汎用レポート・ライターでは、生成するレポートについてそれぞれ別のSELECT文を作成する必要があります。この場合、文の構成は実行時までわかりません。このような文は実行のたびに異なる可能性があります。このような文を動的SQL文と呼びます。
静的SQL文とは異なり、動的SQL文はソース・プログラム内には埋め込まれません。そのかわり、これらの文は実行時にプログラムに入力される(またはプログラムによって作成される)文字列に格納されます。動的SQL文は対話形式で入力できるのみでなく、ファイルから読み込むこともできます。
13.2 動的SQLの長所および短所
通常の埋込みSQLプログラムと比べると、動的に定義されたSQL文を受け入れて処理するホスト・プログラムの方が柔軟性は高くなります。動的SQL文は、SQLの知識がほとんどないユーザーでも対話形式で作成できます。
たとえば、SELECT文、UPDATE文またはDELETE文のWHERE句内で使用する検索条件の入力をユーザーに要求する単純なプログラムがあります。さらに複雑なプログラムでは、SQL処理、表およびビューの名前、列の名前などを表示されているメニューからユーザーが選択できるようになります。このように、動的SQLを使用すると柔軟性に富んだアプリケーションを記述できます。
ただし、動的問合せの中には複雑なコーディング、特殊なデータ構造体の使用および実行時の処理の増加が必要なものもあります。処理時間が増えることは支障がない場合もありますが、動的SQLの概念および方法を完全に理解するまではコーディングが難しく感じられることもあります。
13.4 動的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'
13.6 動的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)などの列名や式が含まれます。
13.6.2 方法2
この方法では、動的SQL文を受け入れるかまたは作成し、PREPAREおよびEXECUTEコマンドを使用してその文を処理できます。SQL文は問合せであってはなりません。プリコンパイル時に、入力ホスト変数のプレースホルダの数および入力ホスト変数のデータ型を明確にする必要があります。たとえば、次のホスト文字列はこのカテゴリに該当します。
'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)' 'DELETE FROM EMP WHERE EMPNO = :emp_number'
方法2では、SQL文の解析は一度しか行われませんが、ホスト変数に異なる値を指定して、このSQL文を複数回実行できます。SQLデータ定義文(CREATEやGRANTなど)は、PREPAREの際に実行されます。
13.6.3 方法3
この方法を使用すると、プログラムは動的問合せを受け入れ(または作成し)、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'
13.6.4 方法4
この方法を使用すると、プログラムは動的SQL文を受け入れ(または作成し)、記述子を使用してその文を処理します。選択リストの項目数、入力ホスト変数のプレース・ホルダー数および入力ホスト変数のデータ型は、実行時まで不明でもかまいません。たとえば、次のホスト文字列はこのカテゴリに該当します。
'INSERT INTO EMP (<unknown>) VALUES (<unknown>)' 'SELECT <unknown> FROM EMP WHERE DEPTNO = 20'
方法4は、選択リスト項目の数または入力ホスト変数の数が不明な動的SQL文を実行するときに必要です。
関連項目
13.6.5 ガイドライン
4つの方法はどれも、動的SQL文を文字列に格納する必要があり、この文字列は、ホスト変数または引用符で囲んだリテラルであることが必要です。SQL文を文字列に格納する場合は、キーワードEXEC SQLおよび文の終了記号「;」は省略してください。
方法2および方法3のときは、入力ホスト変数のプレースホルダの数と入力ホスト変数のデータ型をプリコンパイル時には明確にしてください。
方法の番号が大きくなるほどアプリケーションへの制約は少なくなりますが、コードの記述が難しくなります。通常は、最も簡単な方法を使用してください。ただし動的SQL文を方法1で繰り返し実行する場合は、実行のたびにその文が再解析されるのを避けるために方法2を使用します。
方法4は最も柔軟性に富んでいますが、複雑なコード記述方法および動的SQLの概念の完全な理解が求められます。通常、方法4を使用するのは、方法1、2または3を使用できない場合のみです。
図13-1の決定論理を基に、適切な方法を選択できます。
13.6.5.1 一般的なエラーの回避について
コマンドライン・オプションDBMS=V6_CHARと指定してプリコンパイルするときは、SQL文を格納する前に配列を空白文字で埋めてください。こうして余分な文字を消去します。別のSQL文を格納するために配列を再利用するときに、この処理が重要となります。原則として、SQL文を格納する前に必ずホスト文字列を初期化(または再初期化)してください。ホスト文字列にはヌル終端文字を使用しないでください。OracleではNULL終了文字は文字列の終了マーカーとはみなされません。SQL文の一部として扱われます。
コマンドライン・オプションDBMS=V8と指定してプリコンパイルするときは、PREPARE文またはEXECUTE IMMEDIATE文を実行する前に、文字列がヌル文字で終了していることを確認してください。
DBMSの値が何であっても、VARCHAR変数を使用して動的SQL文を格納するときは、PREPARE文またはEXECUTE IMMEDIATE文を実行する前に、VARCHARの長さが正しく設定(または再設定)されていることを確認してください。
13.7 方法1の使用方法
最も単純な動的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回しか実行しない文に最も適しています。一般に、データ定義言語がこのカテゴリに該当します。
13.7.1 サンプル・プログラム: 動的SQL方法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);
}13.8 方法2の使用方法
方法1では1ステップで実行し、方法2では2ステップに分けて実行します。動的SQL文(問合せは不可)は、まずPREPARE(名前の指定と解析)され、次にEXECUTEされます。
方法2では、SQL文には入力ホスト変数と標識変数のプレースホルダを指定できます。このSQL文は一度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データ型への変換がサポートされるため、これをfloatまたはcharとして宣言することも可能です。
13.8.1 USING句
SQL文がEXECUTEされると、USING句の入力ホスト変数は、PREPAREされた動的SQL文内の該当するプレースホルダに置換されます。
PREPAREされた動的SQL文のプレースホルダは、それぞれが必ずUSING句の個別のホスト変数に対応している必要があります。つまり、PREPAREされた文に同じプレースホルダが2回以上現れるときは、それぞれがUSING句のホスト変数に対応している必要があります。
プレースホルダの名前は、ホスト変数の名前と一致する必要はありません。ただし、PREPARE済動的SQL文のプレースホルダの順序は、USING句の対応するホスト変数の順序と一致する必要があります。
USING句のホスト変数のうち1つでも配列がある場合は、すべてのホスト変数が配列であることが必要です。
NULLを指定するために、標識変数をUSING句のホスト変数と対応付けることができます。
関連項目
13.8.2 サンプル・プログラム: 動的SQL方法2
次のプログラムでは、動的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);
}
13.9 方法3の使用方法
方法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;
各文の機能を次に説明します。
13.9.1 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%';
13.9.2 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文ではありません。
13.9.4 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方向モードを使用して結果セットにランダムにアクセスできます。
13.9.5 CLOSE (動的SQL)
CLOSEはカーソルを使用禁止にします。一度カーソルをCLOSEすると、それ以降はFETCHできなくなります。
次の例では、CLOSEによりemp_cursorが使用禁止になります。
EXEC SQL CLOSE emp_cursor;
13.9.6 サンプル・プログラム: 動的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);
}13.10 方法4の使用方法
この項では、動的SQL方法4の概要を説明します。Oracle動的SQL方法4は、オブジェクト型、結果セット、構造体の配列およびLOBをサポートしていません。
ANSI SQLでは、すべてのデータ型がサポートされます。すべての新しいアプリケーションでは、ANSI SQLを使用してください。
方法3を使用したプログラムでも処理できない種類の動的SQL文があります。選択リストの項目数または入力ホスト変数のプレースホルダの数が実行時までわからない場合は、プログラムで記述子を使用する必要があります。記述子とは、プログラムおよびOracleが動的SQL文内の変数の完全な記述を保存するためのメモリー領域です。
複数行の問合せのときに、宣言済の出力ホスト変数のリスト内に列の値をFETCH INTOしたことを思い出してください。この選択リストがわからないときは、プリコンパイル時にINTO句でホスト変数リストを作成できません。たとえば、次の問合せでは2つの列値が戻されます。
SELECT ename, empno FROM emp WHERE deptno = :dept_number;
ただし、この選択リストをユーザーに定義させると、その問合せによって戻される列の数はわからなくなります。
13.10.1 SQLDAの必要性
このような種類の動的問合せを処理するには、プログラムでDESCRIBE SELECT LISTコマンドを発行するとともに、SQL記述子領域(SQLDA)というデータ構造体を宣言する必要があります。この構造体は問合せ選択リストの列の記述を保持しているため、選択記述子とも呼ばれます。
また、動的SQL文で入力ホスト変数のプレースホルダの数が明確でない場合、プリコンパイル時にUSING句でホスト変数リストを作成できません。
このような動的SQL文を処理するには、プログラムでDESCRIBE BIND VARIABLESコマンドを発行し、入力ホスト変数のプレースホルダの説明を保存するために、バインド記述子という別の種類のSQLDAを宣言する必要があります。(入力ホスト変数はバインド変数とも呼ばれます。)
プログラムにアクティブSQL文が複数ある(たとえば、複数のカーソルをOPENしている)場合、それぞれの文には専用のSQLDAが必要になります。ただし、非並行のカーソルではSQLDAを再利用できます。なお、1つのプログラム内のSQLDAの数に制限はありません。
13.10.2 DESCRIBE文
DESCRIBEは、選択リスト項目または入力ホスト変数の記述を保持するために記述子を初期化します。
選択記述子を指定すると、DESCRIBE SELECT LIST文によってPREPAREされた動的問合せ内の各選択リスト項目が調べられ、その名前、データ型、制約、長さ、位取りおよび精度が確認されます。その後、この情報は選択記述子に格納されます。
バインド記述子を指定すると、DESCRIBE BIND VARIABLES文によってPREPAREされた動的SQL文の各プレースホルダが調べられ、その名前および長さ、関連付けられた入力ホスト変数のデータ型が確認されます。続いて、この情報がそのバインド記述子に格納されます。たとえば、プレースホルダ名を使用して、ユーザーに入力ホスト変数の値の入力を要求できます。
13.10.3 SQLDA
SQLDAはホスト・プログラムのデータ構造体です。この構造体は選択リスト項目または入力ホスト変数の記述を保持します。
SQLDA変数は、宣言部で定義されません。
選択SQLDAには問合せ選択リストに関する次の情報が格納されています。
-
DESCRIBEできる列の最大数
-
DESCRIBEによって検出された列の実際の数
-
列値を格納するバッファのアドレス
-
列値の長さ
-
列値のデータ型
-
標識変数の値のアドレス
-
列名を格納するバッファのアドレス
-
列名を格納するバッファのサイズ
-
列名の現行の長さ
バインドSQLDAには、SQL文の入力ホスト変数に関する次の情報が格納されています。
-
DESCRIBEできるプレースホルダの最大数
-
実際にDESCRIBEで検出されたプレースホルダの数
-
入力ホスト変数のアドレス
-
入力ホスト変数の長さ
-
入力ホスト変数のデータ型
-
標識変数のアドレス
-
プレースホルダ名を格納するバッファのアドレス
-
プレースホルダ名を格納するバッファのサイズ
-
プレースホルダ名の現在の長さ
-
標識変数名を格納するバッファのアドレス
-
標識変数名を格納するバッファのサイズ
-
標識変数名の現行の長さ
関連項目:
SQLDA構造体と変数名については、Oracle動的SQL: 方法4を参照してください。
13.10.4 Oracle方法4の実装について
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を非問合せにも使用できます。
13.11 DECLARE STATEMENT文の使用方法について
方法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;
13.11.1 ホスト配列の使用について
静的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に指示する必要があります。
関連項目
13.12 PL/SQLの使用について
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できません。
13.12.2 方法2の場合
PL/SQLブロック内の入力ホスト変数および出力ホスト変数の数がわかっていれば、方法2で通常どおりPL/SQL文字列をPREPAREおよびEXECUTEできます。
USING句にはすべてのホスト変数を指定する必要があります。このPL/SQL文字列をEXECUTEすると、USING句内のホスト変数はPREPARE済の文字列内の対応するプレースホルダに置き換わります。プリコンパイラでPL/SQLホスト変数がすべて入力ホスト変数として扱われても、値は正しく割り当てられます。入力(プログラム)値は入力ホスト変数に割り当てられ、出力(列)値は出力ホスト変数に割り当てられます。
PREPAREされたPL/SQL文字列中のプレースホルダは、それぞれUSING句のホスト変数に対応している必要があります。したがって、PREPAREされた文に同じプレースホルダが2回以上現れるときは、それぞれがUSING句の個別のホスト変数に対応している必要があります。
13.12.4 Oracle方法4の場合
PL/SQLブロックに数の不明な入力ホスト変数または出力ホスト変数が含まれている場合は、方法4を必ず使用します。
方法4を使用するには、すべての入力ホスト変数および出力ホスト変数について1つのバインド記述子を設定します。DESCRIBE BIND VARIABLESを実行すると、入力ホスト変数および出力ホスト変数に関する情報がそのバインド記述子に保存されます。プリコンパイラではPL/SQLホスト変数がすべて入力ホスト変数として扱われるため、DESCRIBE SELECT LISTを実行しても効果はありません。
警告:
動的SQL方法4では、TABLE型のパラメータを使用して、ホスト配列をPL/SQLプロシージャにバインドすることはできません。
関連項目
13.13 動的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);
}