| Oracle® Database Oracleプリコンパイラのためのプログラマーズ・ガイド 11g リリース2(11.2) B61344-01 |
|
 前へ |
 次へ |
この章の項目は次のとおりです。
この章では、アプリケーションに柔軟性や機能性をもたらす高度なプログラミング技法である動的SQLの使用方法について説明します。動的SQLの長所と短所を比較した後、実行時にSQL文をその場で受け入れて処理するプログラムを記述する方法を、単純なものから複雑なものまで4つ紹介します。それぞれの方法の要件および制限事項、さらに実行するジョブに対する適切な方法の選択方法についても説明します。
ほとんどのデータベース・アプリケーションでは、特定のジョブが実行されます。たとえば、ユーザーに従業員番号の入力を要求して、その後EMP表およびDEPT表の行を更新するという単純なプログラムがあります。この場合、プリコンパイル時にUPDATE文の構成がわかっています。つまり、変更される表、各表および列に定義されている制約、更新される列、各列のデータ型がわかっています。
しかし、アプリケーションによっては、実行時に様々なSQL文を受け入れ(または作成し)、処理する必要があります。たとえば、汎用レポート・ライターでは、生成する各種レポートについてそれぞれ別のSELECT文を作成する必要があります。この場合、文の構成は実行時までわかりません。このような文は、実行のたびに変わる可能性があります。そこでこれらを動的SQL文と呼びます。
静的SQL文とは異なり、動的SQL文はソース・プログラムに埋め込まれません。かわりに、これらの文は、実行時にプログラムに入力される、またはプログラムにより作成される文字列に格納されます。対話形式で入力することも、ファイルから読み取ることもできます。
単純な埋込みSQLプログラムと比べると、動的に定義されたSQL文を受け入れて処理するホスト・プログラムは汎用性が高くなります。動的SQL文は、SQLの知識がほとんどないユーザーでも対話形式で作成できます。
たとえば、SELECT文、UPDATE文またはDELETE文のWHERE句で使用する検索条件の入力をユーザーに求めるという単純なプログラムがあるとします。さらに複雑なプログラムでは、ユーザーはSQL処理、表およびビューの名前、列の名前などが表示されているメニューから選択できます。
ただし、動的問合せの中には、複雑なコーディング、特殊なデータ構造体の使用および実行時の処理の増加が必要なものもあります。処理時間の増加には気付かなくても、動的SQLの概念や方法を完全に理解していなければ、コーディングが難しいと感じられることがあります。
実際には、静的SQLでプログラミングに必要なものはほとんど満たされます。動的SQLは、その制約のない柔軟性が必要な場合にのみ使用してください。その使用をお薦めするのは、次の項目でプリコンパイル時に不明なものが1つ以上ある場合です。
SQL文のテキスト(コマンド、句など)
ホスト変数の数
ホスト変数のデータ型
データベース・オブジェクト(列、索引、順序、表、ユーザー名、ビューなど)の参照
動的SQL文を表すには、文字列に有効なSQL文のテキストを含める必要がありますが、EXEC SQL句、ホスト言語のデリミタまたは文の終了記号または次の埋込みコマンドは含めないでください。
CLOSE
DECLARE
DESCRIBE
EXECUTE
FETCH
INCLUDE
OPEN
PREPARE
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文のテキストおよびその文で使用するホスト変数の値の入力を要求します。その文はOracleにより解析されます。つまり、Oracleでは、SQL文が構文規則に従っているかどうか検査し、有効なデータベース・オブジェクトを参照します。解析には、データベース・アクセス権のチェック、必要なリソースの予約、最適なアクセス・パスの確認も含まれます。
次に、このホスト変数がOracleによりSQL文にバインドされます。つまり、Oracleではホスト変数のアドレスを取得するため、値の読取りまたは書込みができます。
その後、SQL文が実行されます。つまり、OracleではSQL文で要求された処理(表からの行の削除など)が実行されます。
ホスト変数に新しい値を指定すれば、このSQL文を繰り返し実行できます。
この項では、動的SQL文の定義に使用できる4つの方法を紹介します。それぞれの方法の機能と制限事項を簡単に説明した後、適切な方法を選択するためのガイドラインを示します。その後の項では、これらの方法の使用方法について説明します。また、このマニュアルの各ホスト言語の補足資料には、サンプル・プログラムが記載されています。
この4つの方法は、番号が大きくなるに従い汎用性が高まります。つまり、方法2は方法1を包含し、方法3は方法1と方法2を包含します。ただし、表10-1に示すように、それぞれの方法は、特定の種類のSQL文の処理に適してます。
表10-1 動的SQLの使用方法の適用性
| 方法 | SQL文の種類 |
|---|---|
|
1 |
入力ホスト変数のない非問合せ |
|
2 |
入力ホスト変数の数がわかっている非問合せ |
|
3 |
選択リスト項目の数および入力ホスト変数の数がわかっている問合せ |
|
4 |
選択リストの項目の数または入力ホスト変数の数が不明な問合せ |
選択リスト項目には、列名や式が含まれます。
この方法を使用すると、プログラムでは動的SQL文を受け入れるか、または作成し、EXECUTE IMMEDIATEコマンドを使用してその文をすぐに実行します。このSQL文は問合せ(SELECT文)にしないでください。また、この文には入力ホスト変数のプレースホルダを指定できません。たとえば、次のホスト文字列は有効です。
'DELETE FROM EMP WHERE DEPTNO = 20' 'GRANT SELECT ON EMP TO scott'
方法1では、SQL文が実行のたびに解析されます(HOLD_CURSOR=YESと指定した場合を除く)。
この方法を使用すると、プログラムは動的SQLを受け入れるか、または作成し、PREPAREコマンドおよびEXECUTEコマンドを使用してその文を実行します。このSQL文を問合せにしないでください。プリコンパイル時に、入力ホスト変数のプレースホルダの数および入力ホスト変数のデータ型がわかっている必要があります。たとえば、次のホスト文字列はこのカテゴリに入ります。
'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)' 'DELETE FROM EMP WHERE EMPNO = :emp_number'
方法2では、SQL文の解析は1回しか行われませんが(RELEASE_CURSOR=YESと指定している場合を除く)、ホスト変数に異なる値を指定すれば、複数回実行できます。SQLデータ定義文(CREATEなど)は、PREPAREの際に実行されます。
この方法を使用すると、プログラムは動的問合せを受け入れるか、または作成し、DECLARE、OPEN、FETCHおよびCLOSEカーソル・コマンドを使用してその文を処理します。プリコンパイル時に、選択リストの項目数、入力ホスト変数のプレース・ホルダ数および入力ホスト変数のデータ型がわかっている必要があります。たとえば、次のホスト文字列は有効です。
'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO' 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number'
この方法を使用すると、プログラムは動的SQL文を受け入れるか、または作成し、記述子(「方法4の使用方法」を参照)を使用してその文を処理します。選択リストの項目数、入力ホスト変数のプレース・ホルダー数および入力ホスト変数のデータ型は、実行時まで不明でもかまいません。たとえば、次のホスト文字列はこのカテゴリに入ります。
'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を使用してください。図10-1の決定論理を参考にして、適切な方法を選択してください。
動的SQL文の格納に文字配列を使用する場合、SQL文を格納する前に配列の空白を埋め込んでください。こうして、余分な文字を消去します。これは、特に別のSQL文の格納に配列を再利用するときに重要です。一般に、SQL文を格納する前には必ずホスト文字列を初期化(または再初期化)してください。
ホスト文字列はNULLで終わらせないでください。OracleではNULL終了文字は文字列の終了マーカーとはみなされません。かわりに、SQL文の一部として扱われます。
VARCHAR変数を使用して動的SQL文を格納する場合、VARCHARの長さが正しく設定(または再設定)されていること確認してから、PREPARE文またはEXECUTE IMMEDIATE文を実行してください。
EXECUTEを実行すると、SQLCA内のSQLWARN警告フラグはリセットされます。したがって、無条件の更新(WHERE句の省略により発生)などの誤りを検出するには、PREPARE文を実行してからEXECUTE文を実行するまでの間にSQLWARNフラグをチェックしてください。
最も単純な種類の動的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 };
次の例では、ホスト変数sql_stmtを使用してユーザーが入力するSQL文を格納しています。
EXEC SQL BEGIN DECLARE SECTION; ... sql_stmt CHARACTER(120); EXEC SQL END DECLARE SECTION; ... LOOP display 'Enter SQL statement: '; read sql_stmt; IF sql_stmt is empty THEN exit loop; ENDIF; -- sql_stmt now contains the text of a SQL statement EXEC SQL EXECUTE IMMEDIATE :sql_stmt; ENDLOOP;
次の例のように、文字列リテラルを使用することもできます。
EXEC SQL EXECUTE IMMEDIATE 'REVOKE RESOURCE FROM MILLER';
EXECUTE IMMEDIATEでは、入力されたSQL文を実行するたびに解析するため、1回しか実行しない文には方法1が最も適しています。通常、データ定義文はこのカテゴリに入ります。
次のプログラムは、UPDATE文のWHERE句で使用する検索条件の入力をユーザーに求め、方法1を使用して文を実行します。
EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); update_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 update_stmt = 'UPDATE EMP SET COMM = 500 WHERE '; display 'Enter a search condition for the following statement:'; display update_stmt; read search_cond; concatenate update_stmt, search_cond; EXEC SQL EXECUTE IMMEDIATE :update_stmt; EXEC SQL COMMIT WORK RELEASE; exit program; sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error;
方法1では1ステップで実行することを、方法2では2ステップに分けて実行します。動的SQL文(問合せは不可)は、まずPREPARE(名前の指定および解析)され、その後実行されます。
方法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が含まれています。
EXEC SQL BEGIN DECLARE SECTION; ... emp_number INTEGER; delete_stmt CHARACTER(120); EXEC SQL END DECLARE SECTION; search_cond CHARACTER(40); ... set delete_stmt = 'DELETE FROM EMP WHERE EMPNO = :n AND '; display 'Complete the following statement's search condition:'; display delete_stmt; read search_cond; concatenate delete_stmt, search_cond; EXEC SQL PREPARE sql_stmt FROM :delete_stmt; LOOP display 'Enter employee number: '; read emp_number; IF emp_number = 0 THEN exit loop; EXEC SQL EXECUTE sql_stmt USING :emp_number; ENDLOOP;
方法2では、プリコンパイル時に入力ホスト変数のデータ型がわかっている必要があります。前の例では、emp_numberがINTEGER型として宣言されています。これは、CHARACTER型やREAL型としても宣言できますが、これはOracleではこれらすべてのデータ型のNUMBERデータ型への変換がサポートされているためです。
SQL文がEXECUTEされると、USING句の入力ホスト変数は、PREPARE済動的SQL文の対応するプレースホルダに置き換わります。
PREPAREされた動的SQL文内のプレースホルダはそれぞれ、USING句のホスト変数に対応している必要があります。したがって、PREPARE済の文に同じプレースホルダが2回以上現れる場合、それぞれがUSING句のホスト変数に対応している必要があります。USING句のホスト変数のうち1つでも配列があれば、すべてのホスト変数が配列であることが必要です。
プレースホルダの名前は、ホスト変数の名前と一致する必要はありません。ただし、PREPARE済動的SQL文のプレースホルダの順序は、USING句の対応するホスト変数の順序と一致する必要があります。
NULLを指定するために、インジケータ変数をUSING句のホスト変数と関連付けることができます。詳細は、「インジケータ変数の使用方法」を参照してください。
次のプログラムでは、UPDATE文のWHERE句で使用する検索条件の入力をユーザーに求め、その後方法2を使用してその文を準備し実行します。UPDATE文のSET句にはプレースホルダ(c)が含まれています。
EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); sql_stmt CHARACTER(80); empno INTEGER VALUE 1234; deptno1 INTEGER VALUE 97; deptno2 INTEGER VALUE 99; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; EXEC ORACLE OPTION (ORACA=YES); EXEC SQL WHENEVER SQLERROR GOTO sql_error; display 'Username? '; read username; display 'Password? '; read password; EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; set sql_stmt = 'INSERT INTO EMP (EMPNO, DEPTNO) VALUES (:v1, :v2)'; display "V1 = ", empno, "V2 = ", deptno1; EXEC SQL PREPARE S FROM :sql_stmt; EXEC SQL EXECUTE S USING :empno, :deptno1; set empno = empno + 1; display "V1 = ", empno, "V2 = ", deptno2; EXEC SQL EXECUTE S USING :empno, :deptno2; set sql_stmt = 'DELETE FROM EMP WHERE DEPTNO = :v1 OR DEPTNO = :v2")'; display "V1 = ", deptno1, "V2 = ", deptno2; EXEC SQL PREPARE S FROM :sql_stmt; EXEC SQL EXECUTE S USING :deptno1, :deptno2; EXEC SQL COMMIT WORK RELEASE; exit program; sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; display 'Processing error'; EXEC SQL ROLLBACK WORK RELEASE; exit program with an error;
方法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は動的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文を指定します。
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は、アクティブ・セットを識別して、Oracleカーソルを割り当て、入力ホスト変数にバインドし、問合せを実行します。OPENはさらに、アクティブ・セットの最初の行にカーソルを位置付け、SQLCA内のSQLERRDの3番目の要素に保存される処理済行数をゼロに設定します。USING句の入力ホスト変数は、PREPARE済動的SQL文内の対応するプレースホルダに置き換わります。
例では、次に示すように、OPENによりemp_cursorを割り当て、ホスト変数salaryをWHERE句に割り当てます。
EXEC SQL OPEN emp_cursor USING :salary;
FETCHは、アクティブ・セットから行を戻し、選択リスト内の列値をINTO句の対応するホスト変数に割り当てた後、カーソルを次の行に進めます。行がなくなると、FETCHは「データが見つかりません」というOracleエラー・コードをSQLCQ内のSQLCODEに戻します。
例では、次に示すように、FETCHによってアクティブ・セットから1行戻し、MGR列とJOB列の値をホスト変数のmgr_numberとjob_titleに割り当てます。
EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title;
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;
方法4の実装は、言語によって非常に異なります。したがって、この項では概要のみを説明します。詳細は、使用するホスト言語の補足資料を参照してください。
動的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は、選択リストまたは入力ホスト変数の説明を保存するために記述子を初期化します。
選択記述子を指定すると、DESCRIBE SELECT LIST文によってPREPAREされた動的問合せ内の各選択リスト項目が調べられ、その名前、データ型、制約、長さ、位取りおよび精度が確認されます。その後、この情報は選択記述子に格納されます。
バインド記述子を指定すると、DESCRIBE BIND VARIABLES文によってPREPAREされた動的SQL文の各プレースホルダが調べられ、その名前および長さ、関連付けられた入力ホスト変数のデータ型が確認されます。その後、この情報は後から使用できるようにそのバインド記述子に格納されます。たとえば、プレースホルダ名を使用して、ユーザーに入力ホスト変数の値の入力を要求できます。
SQLDAとは、選択リスト項目または入力ホスト変数の記述を保存するホストプログラムのデータ構造体です。
SQLDA変数は、宣言部で定義されません。
SQLDAはホスト言語によって異なりますが、一般的な選択SQLDAには、問合せ選択リストに関する次の情報が格納されています。
記述できる列の最大数
記述により検出された列の実際の数
列値を格納するバッファのアドレス
列値の長さ
列値のデータ型
インジケータ変数の値のアドレス
列名を格納するバッファのアドレス
列名を格納するバッファのサイズ
列名の現在の長さ
一般的なバインドSQLDAには、SQL文内の入力ホスト変数に関する次の情報が格納されています。
記述できるプレースホルダの最大数
記述で検出されたプレースホルダの実際の数
入力ホスト変数のアドレス
入力ホスト変数の長さ
入力ホスト変数のデータ型
インジケータ変数のアドレス
プレースホルダ名を格納するバッファのアドレス
プレースホルダ名を格納するバッファのサイズ
プレースホルダ名の現在の長さ
インジケータ変数名を格納するバッファのアドレス
インジケータ変数名を格納するバッファのサイズ
インジケータ変数名の現在の長さ
特定のホスト言語のSQLDA構造体および変数の名前を調べるには、使用するホスト言語の補足資料を参照してください。
方法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;
逆に、入力ホスト変数のプレースホルダの数がわかっていて、選択リストの列数がわからない場合は、次の方法3のOPEN文と方法4のFETCH文を併用できます。
EXEC SQL OPEN cursor_name [USING host_variable_list];
方法4では、問合せ以外の文にEXECUTEを使用できるので注意してください。
これらの文によりプログラムで記述子を使用して動的SQL文をどのように処理できるかについては、使用するホスト言語の補足資料を参照してください。
方法2、3および4では、次の文を使用することが必要な場合があります。
EXEC SQL [AT db_name] DECLARE statement_name STATEMENT;
db_nameおよびstatement_nameは、プリコンパイラで使用される識別子で、ホスト変数でもプログラム変数でもありません。
DECLARE STATEMENTにより、動的SQL文の名前が宣言されます。宣言された文は、PREPARE、EXECUTE、DECLARE CURSORおよびDESCRIBEで参照できます。デフォルト以外のデータベースで動的SQL文を実行する場合に、これが必要になります。方法2を使用した例は次のとおりです。
EXEC SQL AT remote_db DECLARE sql_stmt STATEMENT; EXEC SQL PREPARE sql_stmt FROM :sql_string; EXEC SQL EXECUTE sql_stmt;
この例では、remote_dbによって、SQL文をどこでEXECUTEするかを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 :sql_string;
一般的な文の順序は次のとおりです。
EXEC SQL PREPARE sql_stmt FROM :sql_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に指示する必要があります。この方法は、ホスト言語の補足資料を参照してください。
Oracleプリコンパイラでは、PL/SQLブロックが1つの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を実行しても効果はありません。
方法4でバインド記述子を使用する方法については、使用するホスト言語の補足資料を参照してください。
|
注意: 動的SQL方法4では、表型のパラメータを使用して、ホスト配列をPL/SQLプロシージャにバインドできません。 |
