10 動的SQLの使用方法
この章の項目は次のとおりです。
この章では、アプリケーションに柔軟性や機能性をもたらす高度なプログラミング技法である動的SQLの使用方法について説明します。動的SQLの長所と短所を比較した後、実行時にSQL文をその場で受け入れて処理するプログラムを記述する方法を、単純なものから複雑なものまで4つ紹介します。それぞれの方法の要件および制限事項、さらに実行するジョブに対する適切な方法の選択方法についても説明します。
10.1 動的SQL
ほとんどのデータベース・アプリケーションでは、特定のジョブが実行されます。たとえば、ユーザーに従業員番号の入力を要求して、その後EMPおよびDEPTの表の行を更新するという単純なプログラムがあります。この場合、プリコンパイル時にUPDATE
文の構成がわかっています。つまり、変更される表、各表および列に定義されている制約、更新される列、各列のデータ型がわかっています。
しかし、アプリケーションによっては、様々なSQL文を実行時に受け入れ(または作成し)、処理する必要があります。たとえば、汎用レポート・ライターでは、生成する各種レポートについてそれぞれ別のSELECT
文を作成する必要があります。この場合、文の構成は実行時までわかりません。このような文は実行のたびに異なる可能性があります。そこでこれらを動的SQL文と呼びます。
静的SQL文とは異なり、動的SQL文はソース・プログラム内には埋め込まれません。そのかわり、これらの文は実行時にプログラムに入力される(またはプログラムによって作成される)文字列に格納されます。対話形式で入力することも、ファイルから読み取ることもできます。
10.2 動的SQLの長所および短所
単純な埋込みSQLプログラムと比べると、動的に定義されたSQL文を受け入れて処理するホスト・プログラムは汎用性が高くなります。動的SQL文は、SQLの知識がほとんどないユーザーでも対話形式で作成できます。
たとえば、SELECT
文、UPDATE
文またはDELETE
文のWHERE
句内で使用する検索条件の入力をユーザーに求めるという単純なプログラムがあります。さらに複雑なプログラムでは、SQL処理、表およびビューの名前、列の名前などを表示されているメニューからユーザーが選択できるようになります。このように、動的SQLを使用すると柔軟性に富んだアプリケーションを記述できます。
ただし、動的問合せの中には、複雑なコーディング、特殊なデータ構造体の使用および実行時の処理の増加が必要なものもあります。処理時間が増えることは支障がない場合もありますが、動的SQLの概念および方法を完全に理解するまではコーディングが難しく感じられることもあります。
10.4 動的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'
10.5 動的SQL文の処理
一般に、アプリケーション・プログラムでは、ユーザーにSQL文のテキストおよびその文で使用するホスト変数の値の入力を要求します。そのSQL文はOracleにより解析されます。解析では、SQL文が構文規則に従っているか、有効なデータベース・オブジェクトを参照しているかについて調べられます。解析には、データベース・アクセス権のチェック、必要なリソースの予約、最適なアクセス・パスの確認も含まれます。
次に、このホスト変数がOracleによりSQL文にバインドされます。つまり、Oracleではホスト変数のアドレスを取得するため、値の読取りまたは書込みができます。
この後、OracleでSQL文が実行されます。つまり、そのSQL文の要求(表からの行の削除など)をOracleが実行します。
これらのホスト変数に別の値を指定すると、このSQL文を繰り返し実行できます。
10.6 動的SQLの使用方法
この項では、動的SQL文の定義に使用できる4つの方法を紹介します。まずそれぞれの方法の機能および制限事項を簡単に説明した後、適切な方法を選択するためのガイドラインを示します。その後の項では、これらの方法の使用方法について説明します。また、このマニュアルの各ホスト言語の補足資料には、サンプル・プログラムが記載されています。
この4つの方法は番号が大きくなるに従って対象が広がるようになっています。つまり方法2は方法1を包含し、方法3は方法1と方法2を包含するというようになります。ただし、表10-1に示すように、それぞれの方法は、特定の種類のSQL文の処理に適しています。
表10-1 動的SQLの使用方法の適用性
方法 | SQL文の種類 |
---|---|
1 |
入力ホスト変数のない非問合せ |
2 |
入力ホスト変数の数がわかっている非問合せ |
3 |
選択リスト項目の数および入力ホスト変数の数がわかっている問合せ |
4 |
選択リストの項目の数または入力ホスト変数の数が不明な問合せ |
選択リスト項目には、列名や式を使用します。
10.6.1 方法1
この方法では、動的SQL文を受け入れるかまたは作成し、 EXECUTE
IMMEDIATE
コマンドを使用してその文をすぐに実行できます。このSQL文では、問合せ(SELECT
文)の使用や、入力ホスト変数に対するプレースホルダの組込みはできません。たとえば、次のホスト文字列は有効です。
'DELETE FROM EMP WHERE DEPTNO = 20' 'GRANT SELECT ON EMP TO scott'
方法1では、SQL文が実行のたびに解析されます(HOLD_CURSOR
=YES
と指定した場合を除く)。
10.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文の解析は1回しか行われませんが(RELEASE_CURSOR
=YES
と指定している場合を除く)、ホスト変数に異なる値を指定すれば、複数回実行できます。SQLデータ定義文(CREATE
など)は、PREPARE
の際に実行されます。
10.6.3 方法3
この方法を使用すると、プログラムは動的問合せを受け入れるか、または作成し、DECLARE
、 OPEN
、FETCH
およびCLOSE
カーソル・コマンドを使用してその文を処理します。プリコンパイル時に、選択リストの項目数、入力ホスト変数のプレース・ホルダ数および入力ホスト変数のデータ型がわかっている必要があります。たとえば、次のホスト文字列は有効です。
'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO' 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number'
10.6.4 方法4
この方法を使用すると、プログラムは動的SQL文を受け入れるか、または作成し、記述子(「方法4の使用方法」を参照)を使用してその文を処理します。選択リストの項目数、入力ホスト変数のプレース・ホルダー数および入力ホスト変数のデータ型は、実行時まで不明でもかまいません。たとえば、次のホスト文字列はこのカテゴリに入ります。
'INSERT INTO EMP (<unknown>) VALUES (<unknown>)' 'SELECT <unknown> FROM EMP WHERE DEPTNO = 20'
方法4は、選択リスト項目の数または入力ホスト変数の数が不明な動的SQL文を実行するときに必要です。
10.6.5 ガイドライン
4つの方法はどれも、動的SQL文を文字列に格納する必要があり、この文字列は、ホスト変数または引用符で囲んだリテラルであることが必要です。SQL文を文字列に格納する場合は、キーワードEXEC SQLおよび文の終了記号は省略してください。
方法2および方法3では、プリコンパイル時に入力ホスト変数のプレース・ホルダ数および入力ホスト変数のデータ型がわかっている必要があります。
方法の番号が大きくなるほどアプリケーションへの制約は少なくなりますが、コードの記述が難しくなります。通常は、最も簡単な方法を使用してください。ただし、動的SQL文を繰り返し実行する場合、方法1では実行のたびに再解析されるので、これを避けるために方法2を使用してください。
方法4は最も柔軟性に富んでいますが、複雑なコード記述方法および動的SQLの概念の完全な理解が求められます。通常、方法4を使用するのは、方法1、2または3を使用できない場合のみです。図10-1の決定論理を参考にして、適切な方法を選択してください。
10.6.6 一般的なエラーの回避
動的SQL文を文字配列に格納する場合は、その配列に空白を埋め込んでからSQL文を格納してください。こうして余分な文字を消去します。別のSQL文を格納するために配列を再利用するときに、この処理が重要となります。一般に、SQL文を格納する前には必ずホスト文字列を初期化(または再初期化)してください。
ホスト文字列にはヌル終端文字を使用しないでください。OracleではNULL終了文字は文字列の終了マーカーとはみなされません。SQL文の一部として扱われます。
VARCHAR
変数を使用して動的SQL文を格納する場合、VARCHAR
の長さが正しく設定(または再設定)されていること確認してから、PREPARE文またはEXECUTE
IMMEDIATE
文を実行してください。
EXECUTE
を実行すると、SQLCA内のSQLWARN警告フラグはリセットされます。したがって、無条件の更新(WHERE
句の省略により発生)などの誤りを検出するには、PREPARE
文を実行してからEXECUTE
文を実行するまでの間にSQLWARNフラグをチェックしてください。
10.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'
10.7.1 EXECUTE IMMEDIATE文
方法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回しか実行しない文に最も適しています。通常、データ定義文は、このカテゴリに入ります。
10.7.2 例
次のプログラムは、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;
10.8 方法2の使用方法について
方法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
データ型への変換がサポートされているためです。
10.8.1 USING句
SQL文がEXECUTE
されると、USING句
の入力ホスト変数は、PREPARE
された動的SQL文内の該当するプレースホルダに置換されます。
PREPAREされた動的SQL文内のプレースホルダはそれぞれ、USING
句のホスト変数に対応している必要があります。したがって、PREPARE
済の文に同じプレースホルダが2回以上現れる場合、それぞれがUSING
句のホスト変数に対応している必要があります。USING
句のホスト変数のうち1つでも配列があれば、すべてのホスト変数が配列であることが必要です。
プレースホルダの名前は、ホスト変数の名前と一致する必要はありません。ただし、PREPARE
済動的SQL文のプレースホルダの順序は、USING
句の対応するホスト変数の順序と一致する必要があります。
NULLを指定するために、インジケータ変数をUSING
句のホスト変数と関連付けることができます。詳細は、インジケータ変数の使用についてを参照してください
10.8.2 例
次のプログラムでは、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;
10.9 方法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;
次に、それぞれの文の実行内容を説明します。
10.9.1 PREPARE
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_stmtは特定の動的SQL文を指定します。
10.9.2 DECLARE
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文ではありません。
10.9.5 CLOSE
CLOSE
は、カーソルを無効にします。カーソルをCLOSE
すると、そこからのFETCH
はできなくなります。例では次のように、CLOSE
文によりemp_cursorが無効になります。
EXEC SQL CLOSE emp_cursor;
10.9.6 例
次のプログラムでは、問合せの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;
10.10 方法4の使用方法
方法4の実装は、言語によって非常に異なります。したがって、この項では概要のみを説明します。詳細は、使用するホスト言語の補足資料を参照してください。
方法3を使用したプログラムでも処理できない種類の動的SQL文があります。選択リストの項目数または入力ホスト変数のプレースホルダの数が実行時までわからない場合は、プログラムで記述子を使用する必要があります。記述子とは、プログラムおよびOracleによって動的SQL文内の変数の完全な記述を保存するために使用されるメモリー領域です。
複数行の問合せの場合、宣言済の出力ホスト変数のリストに選択した列値をFETCH
INTO
したことを思い出してください。この選択リストがわからない場合は、プリコンパイル時にINTO
句でホスト変数リストを作成できません。たとえば、次の問合せでは2つの列値が戻されます。
SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number;
ただし、この選択リストをユーザーに定義させると、その問合せによって戻される列の数はわからなくなります。
10.10.1 SQLDAの必要性
この種の動的問合せを処理するには、プログラムでDESCRIBE
SELECT
LIST
コマンドを発行し、SQL記述子領域(SQLDA)というデータ構造体を宣言する必要があります。この構造体は、問合せ選択リストの列の記述を保存するため、選択記述子とも呼ばれます。
同様に、動的SQL文に含まれる入力ホスト変数のプレースホルダの数がわからない場合には、プリコンパイル時にUSING
句によってホスト変数リストを作成できません。
このような動的SQL文を処理するには、プログラムでDESCRIBE
BIND
VARIABLES
コマンドを発行し、入力ホスト変数のプレースホルダの説明を保存するために、バインド記述子という別の種類のSQLDAを宣言する必要があります。(入力ホスト変数はバインド変数とも呼ばれます。)
プログラムにアクティブSQL文が複数ある(たとえば、複数のカーソルをOPENしている)場合、それぞれの文には専用のSQLDAが必要になります。ただし、非並行のカーソルではSQLDAを再利用できます。なお、1つのプログラム内のSQLDAの数に制限はありません。
10.10.2 DESCRIBE文
DESCRIBEは、選択リストまたは入力ホスト変数の説明を保存するために記述子を初期化します。
選択記述子を指定すると、DESCRIBE
SELECT
LIST
文によってPREPARE
された動的問合せ内の各選択リスト項目が調べられ、その名前、データ型、制約、長さ、位取りおよび精度が確認されます。その後、この情報は選択記述子に格納されます。
バインド記述子を指定すると、DESCRIBE
BIND
VARIABLES
文によってPREPARE
された動的SQL文の各プレースホルダが調べられ、その名前および長さ、関連付けられた入力ホスト変数のデータ型が確認されます。続いて、この情報がそのバインド記述子に格納されます。たとえば、プレースホルダ名を使用して、ユーザーに入力ホスト変数の値の入力を要求できます。
10.10.3 SQLDA
SQLDAとは、選択リスト項目または入力ホスト変数の記述を保存するホストプログラムのデータ構造体です。
SQLDA変数は、宣言部で定義されません。
SQLDAはホスト言語によって異なりますが、一般的な選択SQLDAには、問合せ選択リストに関する次の情報が格納されています。
-
記述できる列の最大数
-
記述により検出された列の実際の数
-
列値を格納するバッファのアドレス
-
列値の長さ
-
列値のデータ型
-
インジケータ変数の値のアドレス
-
列名を格納するバッファのアドレス
-
列名を格納するバッファのサイズ
-
列名の現行の長さ
一般的なバインドSQLDAには、SQL文内の入力ホスト変数に関する次の情報が格納されています。
-
記述できるプレースホルダの最大数
-
記述で検出されたプレースホルダの実際の数
-
入力ホスト変数のアドレス
-
入力ホスト変数の長さ
-
入力ホスト変数のデータ型
-
標識変数のアドレス
-
プレースホルダ名を格納するバッファのアドレス
-
プレースホルダ名を格納するバッファのサイズ
-
プレースホルダ名の現在の長さ
-
標識変数名を格納するバッファのアドレス
-
標識変数名を格納するバッファのサイズ
-
標識変数名の現行の長さ
特定のホスト言語のSQLDA構造体および変数の名前を調べるには、使用するホスト言語の補足資料を参照してください。
10.10.4 方法4の実行
方法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文をどのように処理できるかについては、使用するホスト言語の補足資料を参照してください。
10.11 DECLARE STATEMENT文の使用方法について
方法2、3および4では、次の文を使用することが必要な場合があります。
EXEC SQL [AT db_name] DECLARE statement_name STATEMENT;
db_nameおよびstatement_nameは、プリコンパイラで使用される識別子で、ホスト変数でもプログラム変数でもありません。
DECLARE
STATEMENT
は、PREPARE
、EXECUTE
、DECLARE
CURSOR
およびDESCRIBE
が文を参照できるように、動的SQL文の名前を宣言します。デフォルト以外のデータベースで動的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;
10.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に指示する必要があります。この方法は、ホスト言語の補足資料を参照してください。
10.12 PL/SQLの使用について
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できません。
10.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句内のホスト変数に対応している必要があります。
10.12.4 方法4の場合
PL/SQLブロックに含まれる入力ホスト変数または出力ホスト変数の数がわからない場合は、方法4を使用してください。
方法4を使用するには、すべての入力ホスト変数および出力ホスト変数について1つのバインド記述子を設定します。DESCRIBE
BIND
VARIABLES
を実行すると、入力ホスト変数および出力ホスト変数に関する情報がそのバインド記述子に格納されます。プリコンパイラではPL/SQLホスト変数がすべて入力ホスト変数として扱われるため、DESCRIBE
SELECT
LIST
を実行しても効果はありません。
方法4でのバインド記述子の詳細は、ホスト言語補足を参照してください。
注意:
動的SQL方法4では、表型のパラメータを使用して、ホスト配列をPL/SQLプロシージャにバインドできません。