最も単純な動的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); }