動的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)などの列名や式が含まれます。

方法1

この方法では、動的SQL文を受け入れるかまたは作成し、EXECUTE IMMEDIATEコマンドを使用してその文をすぐに実行できます。このSQL文では、問合せ(SELECT文)の使用や、入力ホスト変数に対するプレースホルダの組込みはできません。たとえば、次のホスト文字列は有効です。

'DELETE FROM EMP WHERE DEPTNO = 20' 
'GRANT SELECT ON EMP TO scott'

方法1では、SQL文は実行のたびに解析されます。

方法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の際に実行されます。

方法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'

方法4

この方法を使用すると、プログラムは動的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の概念の完全な理解が求められます。通常、方法4を使用するのは、方法1、2または3を使用できない場合のみです。

図13-1の決定論理を基に、適切な方法を選択できます。

一般的なエラーの回避について

コマンドライン・オプションDBMS=V6_CHARと指定してプリコンパイルするときは、SQL文を格納する前に配列を空白文字で埋めてください。こうして余分な文字を消去します。別のSQL文を格納するために配列を再利用するときに、この処理が重要となります。原則として、SQL文を格納する前に必ずホスト文字列を初期化(または再初期化)してください。ホスト文字列にはヌル終端文字を使用しないでください。OracleではNULL終了文字は文字列の終了マーカーとはみなされません。SQL文の一部として扱われます。

コマンドライン・オプションDBMS=V8と指定してプリコンパイルするときは、PREPARE文またはEXECUTE IMMEDIATE文を実行する前に、文字列がヌル文字で終了していることを確認してください。

DBMSの値が何であっても、VARCHAR変数を使用して動的SQL文を格納するときは、PREPARE文またはEXECUTE IMMEDIATE文を実行する前に、VARCHARの長さが正しく設定(または再設定)されていることを確認してください。

図13-1 適切な方法の選択

「図13-1 適切な方法の選択」の説明