| Pro*C/C++プログラマーズ・ガイド 11g リリース1（11.1） E05689-02 |
|
 戻る |
 次へ |
この章では、埋込みSQLプログラムの基本的な技法およびその適用方法について説明します。この章の項目は、次のとおりです。
Oracleはホスト変数を使用してデータおよびステータス情報をプログラムに渡します。同様にプログラムはホスト変数を使用してデータをOracleに渡します。
ホスト変数はその使用方法によって、出力ホスト変数または入力ホスト変数と呼ばれます。
SELECT文またはFETCH文のINTO句内のホスト変数は、Oracleによって出力される列の値が入るため出力ホスト変数と呼ばれます。Oracleは列の値をINTO句内の対応する出力ホスト変数に割り当てます。
SQL文のその他のホスト変数の値は、プログラムがそれをOracleに入力するため、すべて入力ホスト変数と呼ばれます。たとえば、INSERT文のVALUES句内およびUPDATE文のSET句内では入力ホスト変数を使用します。入力ホスト変数はWHERE句、HAVING句、FOR句内でも使用されます。入力ホスト変数は、SQL文で値または式を使用できる位置であればどこにでも使用できます。
|
注意: ORDER BY句では、ホスト変数を使用できますが、定数またはリテラルとして扱われます。このためホスト変数の内容には何の効力もありません。たとえば、次のようなSQL文があるとします。EXEC SQL SELECT ename, empno INTO :name,:number FROM emp ORDER BY :ord; この文には、見かけ上、:ordという入力ホスト変数が入っています。ただし、この場合のホスト変数は定数として扱われるため、:ordの値が何であっても、順序付けは行われません。 |
SQLキーワードまたはデータベース・オブジェクトの名前を指定する場合には、入力ホスト変数を使用できません。つまり、ALTER、CREATE、DROPなどのデータ定義文内で入力ホスト変数は使用できません。次の例のDROP TABLE文は無効です。
char table_name[30];
printf("Table name? ");
gets(table_name);
EXEC SQL DROP TABLE :table_name; -- host variable not allowed
データベース・オブジェクト名を実行時に変更する必要があるときは、動的SQLを使用します。第13章「Oracle動的SQL」も参照してください。
入力ホスト変数を含むSQL文をOracleで実行する前に、それらの入力ホスト変数に値を割り当てる必要があります。次に例を示します。
int emp_number;
char temp[20];
VARCHAR emp_name[20];
/* get values for input host variables */
printf("Employee number? ");
gets(temp);
emp_number = atoi(temp);
printf("Employee name? ");
gets(emp_name.arr);
emp_name.len = strlen(emp_name.arr);
EXEC SQL INSERT INTO EMP (EMPNO, ENAME)
VALUES (:emp_number, :emp_name);
INSERT文のVALUES句内の入力ホスト変数の前にコロンが付いていることに注意してください。
任意のホスト変数をオプションの標識変数に対応付けることができます。標識変数に対応付けたホスト変数をSQL文内で使用するたびに、対応する標識変数に結果コードが格納されます。つまり、標識変数によってホスト変数を監視できます。
|
注意: PL/SQLブロックでは、単一のホスト変数に対して複数の標識変数を使用できません。このように使用すると、「バインドされていない変数があります。」というエラーが発生します。 |
標識変数をVALUESまたはSET句に使用して、入力ホスト変数にNULLを割り当てます。また、INTO句に使用すると、出力ホスト変数内のNULL値または切り捨てられた値を検出できます。
入力時
次は、プログラムが標識変数へ割り当てる値と、その意味です。
| 変数 | 説明 |
|---|---|
| -1 | Oracleによってその列にNULL値が割り当てられます。ホスト変数の値は無視されます。 |
| >=0 | Oracleによってこのホスト変数の値がその列に割り当てられます。 |
出力時
次は、Oracleが標識変数へ割り当てる値と、その意味です。
| 変数 | 説明 |
|---|---|
| -1 | 列の値はNULLです。したがってこのホスト変数の値は予測不能です。 |
| 0 | Oracleによって列の値がそのままこのホスト変数に割り当てられました。 |
| >0 | Oracleによって切り捨てられた列の値がこのホスト変数に割り当てられました。標識変数により戻された整数は、この列の値の元の長さを示し、SQLCA内のSQLCODEは0に設定されます。 |
| -2 | Oracleによって、切り捨てられた列の値がこのホスト変数に割り当てられましたが、元の列の値（たとえば、LONG列）が特定できません。 |
なお、標識変数は2バイトの整数として定義してください。また、SQL文内では、標識変数の前にコロンを付けて、ホスト変数の直後に置く必要があります。
標識変数を使用すると、NULL値をINSERTできます。INSERTの前に、次の例に示すように、NULL値を設定する各列に対して適切に標識変数を-1に設定します。
set ind_comm = -1;
EXEC SQL INSERT INTO emp (empno, comm)
VALUES (:emp_number, :commission:ind_comm);
標識変数ind_commはCOMM列にNULL値を格納することを示します。
次のようにして、NULL値をハードコードすることもできます。
EXEC SQL INSERT INTO emp (empno, comm)
VALUES (:emp_number, NULL);
この方法は柔軟性に欠けますが、非常に理解しやすい方法です。一般的には、次の例に示すように条件的にNULLを挿入します。
printf("Enter employee number or 0 if not available: ");
scanf("%d", &emp_number);
if (emp_number == 0)
ind_empnum = -1;
else
ind_empnum = 0;
EXEC SQL INSERT INTO emp (empno, sal)
VALUES (:emp_number:ind_empnum, :salary);
標識変数を使用すると、次の例で示すように、戻されたNULL値を操作することもできます。
EXEC SQL SELECT ename, sal, comm
INTO :emp_name, :salary, :commission:ind_comm
FROM emp
WHERE empno = :emp_number;
if (ind_comm == -1)
pay = salary; /* commission is NULL; ignore it */
else
pay = salary + commission;
DBMS=V7またはDBMS=V8のときは、SELECTまたはFETCHしたNULL値を標識変数と対応付けられていないホスト変数に入れると、Oracleは次のエラー・メッセージを発行します。
ORA-01405: fetched column value is NULL
WHERE句で次の例のように標識変数を使用して、NULLをテストできます。
EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE :commission INDICATOR :ind_comm IS NULL ...
ただし、関係演算子を使用してNULLとNULL、あるいはNULLと他の値を比較することはできません。たとえば、COMM列にNULLのデータがある場合、次のSELECT文は失敗します。
EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE comm = :commission;
次の例は、値のうちのいくつかがNULLの可能性がある場合に、値の等価性を比較する方法を示します。
EXEC SQL SELECT ename, sal
INTO :emp_name, :salary
FROM emp
WHERE (comm = :commission) OR ((comm IS NULL) AND
(:commission INDICATOR :ind_comm IS NULL));
実行SQL文を使用すると、Oracleデータの問合せ、操作および制御ができ、表、ビュー、索引などのOracleオブジェクトを作成、定義およびメンテナンスできます。この章では、データの問合せおよび操作を行う文を重点的に説明しています。
INSERT、UPDATEまたはDELETEなどのDML文を実行する場合、入力ホスト変数の値を設定すること以外の考慮事項は、その文が成功したか失敗したかのみです。これは、SQLCAを調べることでわかります。（SQL文を実行すると、SQLCA変数が設定されます。）次の2通りの方法で調べることができます。
WHENEVER文による暗黙的なチェック
SQLCA変数の明示的なチェック
SELECT文（問合せ）を実行するときは、戻されるデータの行も処理する必要があります。問合せは次のように分類できます。
行を戻さない問合せ（有無を調べるのみ）
1行のみを戻す問合せ
複数の行を戻す問合せ
複数の行を戻す問合せの場合は、カーソルを明示的に宣言するか、ホスト配列（配列として宣言されたホスト変数）を使用する必要があります。
|
注意: ホスト配列によって、行の一括処理が可能です。 |
この章ではスカラー・ホスト変数の使用を想定しています。
次の埋込みSQL文を使用すると、Oracleデータの問合せおよび操作ができます。
| 埋込みSQL文 | 説明 |
|---|---|
| SELECT | 1つ以上の表から行を戻します。 |
| INSERT | 表に新しい行を追加します。 |
| UPDATE | 表内の行を変更します。 |
| DELETE | 表から行を削除します。 |
次の埋込みSQL文を使用すると、明示カーソルの定義および操作ができます。
| 埋込みSQL文 | 説明 |
|---|---|
| DECLARE | カーソルに名前を付け、問合せに対応付けます。 |
| OPEN | 問合せを実行してアクティブ・セットを決定します。 |
| FETCH | カーソルを移動してアクティブ・セット内の各行を1つずつ取り出します。 |
| CLOSE | カーソルを使用禁止にします。（アクティブ・セットは未定義になります。） |
以降の項では、最初にINSERT、UPDATE、DELETEおよび単一行のSELECT文を記述する方法を説明します。その後、複数行のSELECT文の説明に進みます。
データベースへの問合せは日常的なSQL処理です。問合せを発行するには、SELECT文を使用します。次の例では、EMP表を問い合せます。
EXEC SQL SELECT ename, job, sal + 2000 INTO :emp_name, :job_title, :salary FROM emp WHERE empno = :emp_number;
SELECTキーワードの後に続く列名および式が選択リストを構成します。この例の選択リストには3つの項目が含まれています。WHERE句（および存在する場合は後続する句）内で指定した条件に基づいて、OracleはINTO句内のホスト変数に列の値を戻します。
選択リスト内の項目の数はINTO句のホスト変数の数と一致している必要があります。これにより、戻された値すべてに格納場所が確保されます。
最も簡単なのは、問合せが1行のみを戻す場合で、前述の例の形式をとります。問合せが複数の行を戻す場合は、カーソルを使用してこれらの行をFETCHするか、これらの行をホスト変数の配列内にSELECTする必要があります。カーソルとFETCH文はこの章で後述します。配列処理の詳細は、「ホスト配列」を参照してください。
1行のみを戻すように作成した問合せが実際には複数行を戻す場合、SELECTの結果は予測不能です。これがエラーの原因かどうかは、SELECT_ERRORオプションの指定方法によって異なります。デフォルトの設定であるYESの場合は、複数行が戻されるとエラーが発生します。
INSERT文を使用すると、表またはビューに行を追加できます。次の例では、EMP表に1行追加します。
EXEC SQL INSERT INTO emp (empno, ename, sal, deptno) VALUES (:emp_number, :emp_name, :salary, :dept_number);
列リストでは、INTO句で指定した表に含まれている列を指定する必要があります。VALUES句には挿入される行の値を指定します。これらの値は、定数、ホスト変数、SQL式、SQL関数（USER、SYSDATEなど）またはユーザー定義のPL/SQL関数のうち、どの値であってもかまいません。
VALUES句内の値の数は、列リスト内の名前の数と一致している必要があります。ただし、表に定義されている順序で、VALUES句に表内の各列に対する値がすべて指定されている場合は、この列リストを省略できます。
副問合せはネストされたSELECT文です。副問合せを使用すると、複数部分の検索を処理できます。これを使用できるのは次の場合です。
SELECT、UPDATEおよびDELETE文のWHERE、HAVINGおよびSTART WITH句に比較対象の値を指定する場合
CREATE TABLEまたはINSERT文で挿入する行のセットを定義する場合
UPDATE文のSET句の値を定義する場合
次の例では、INSERT文内に副問合せを使用して、1つの表から別の表に行をコピーします。
EXEC SQL INSERT INTO emp2 (empno, ename, sal, deptno) SELECT empno, ename, sal, deptno FROM emp WHERE job= :job_title ;
このINSERT文では、中間結果を得るために副問合せを使用しています。
UPDATE文を使用すると、表またはビュー内の指定した列の値を変更できます。次の例では、EMP表のSALおよびCOMM列を更新します。
EXEC SQL UPDATE emp SET sal = :salary, comm = :commission WHERE empno = :emp_number;
オプションのWHERE句を使用すると、行を更新する条件を指定できます。「WHERE句」も参照してください。
SET句には、値を指定する必要のある1つ以上の列の名前をリストします。次の例に示すように、副問合せを使用すると値を指定できます。
EXEC SQL UPDATE emp SET sal = (SELECT AVG(sal)*1.1 FROM emp WHERE deptno = 20) WHERE empno = :emp_number;
INSERTおよびDELETE文と同様に、UPDATE文ではオプションでRETURNING句を指定できます。RETURNING句はオプションのWHERE条件の後にのみ置くことができます。
詳細は、「UPDATE（実行可能埋込みSQL）」も参照してください。
DELETE文を使用すると、表またはビューから行を削除できます。次の例では、EMP表から指定した部内の全従業員を削除します。
EXEC SQL DELETE FROM emp WHERE deptno = :dept_number ;
オプションのWHERE句を使用して、行を削除する条件を指定しています。
RETURNING句オプションはDELETE文でも使用できます。オプションのWHERE条件の後に指定します。前述の例では、削除する各従業員のフィールド値を事前に記録することをお薦めします。
INSERT、UPDATEおよびDELETE文では、オプションでDML RETURNING句を設定し、標識変数ivを付けて列値の式exprをホスト変数hvに戻すことができます。DML RETURNING句は次のように設定します。
{RETURNING | RETURN} {expr [,expr]}
INTO {:hv [[INDICATOR]:iv] [, :hv [[INDICATOR]:iv]]}
式の数はホスト変数の数と同一にしてください。この句を使用すると、アプリケーションに情報として記録する必要がある場合に、INSERTまたはUPDATEの後、あるいはDELETEの前に行を選択する必要がありません。RETURNING句を使用すると、非効率的なネットワークのラウンドトリップや余分な処理を削減し、サーバーのメモリーを節約できます。
Oracle動的SQL方法4ではDML RETURNING句はサポートされませんが、ANSI動的SQL方法4ではサポートされます。
検索で複数行が戻されるとき、カーソルを明示的に定義することで、次のことができます。
問合せによって戻された最初の行の後を処理します。
現在どの行が処理されているかを追跡します。
ホスト配列を使用することもできます。
カーソルは、問合せによって戻された行の集合内の現在行を示します。これを利用して、プログラムは行を1行ずつ処理できます。次の文を使用してカーソルを定義および操作します。
DECLARE CURSOR
OPEN
FETCH
CLOSE
最初に、DECLARE CURSOR文を使用してカーソルに名前を付け、問合せに対応付けます。
OPEN文によって問合せが実行され、この問合せの検索条件を満たす行がすべて判別されます。これらの行は、カーソルのアクティブ・セットと呼ばれる集合を形成します。このカーソルをOPENした後、対応する問合せによって戻された行を取り出すことができます。
アクティブ・セットの行は1行ずつ取り出されます（ホスト配列を使用していない場合）。FETCH文を使用してアクティブ・セット内の現在行を取り出します。FETCHは、すべての行が取り出されるまで繰返し実行できます。
アクティブ・セットからの行のFETCHが終了した後に、このカーソルをCLOSE文によって使用禁止にします。（アクティブ・セットは未定義になります。）
以降の項では、アプリケーション・プログラム内でのこれらのカーソル制御文の使用方法について説明します。
次の例に示すように、DECLARE CURSOR文を使用してカーソルに名前を付け、問合せに対応付けて定義できます。
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, empno, sal
FROM emp
WHERE deptno = :dept_number;
カーソル名は、ホスト変数やプログラム変数ではなくプリコンパイラで使用される識別子のため、宣言部では定義しないでください。カーソル名にハイフンは使用できません。長さは任意ですが、意味があるのは先頭の31文字までです。ANSI互換性を維持するため、カーソル名は18文字以内にしてください。
カーソルに対応付けられたSELECT文にINTO句を含めることはできません。INTO句および出力ホスト変数のリストはFETCH文の一部として指定します。
DECLARE CURSOR文は宣言部のため、カーソルを参照する他のすべてのSQL文より（論理的にではなく）物理的に前に配置する必要があります。つまり、カーソルの前方参照は許可されていません。次の例ではOPEN文の位置が誤っています。
...
EXEC SQL OPEN emp_cursor;
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, empno, sal
FROM emp
WHERE ename = :emp_name;
カーソル制御文（DECLARE、OPEN、FETCH、CLOSE）はすべて同一のプリコンパイル・ユニット内で指定する必要があります。たとえば、ファイルAの中でカーソルをDECLAREしてファイルBでOPENすることはできません。
ホスト・プログラムでは、カーソルを必要な数だけDECLAREできます。ただし、指定されたファイル内ではそれぞれのDECLARE文は一意である必要があります。つまり、カーソルの適用範囲は1つのファイル全体のため、1つのプリコンパイル・ユニット内には、別のブロックまたはプロシージャ内であっても、同じ名前のカーソルを2つはDECLAREできません。
カーソルを数多く使用するときは、MAXOPENCURSORSオプションの指定が必要な場合があります。
OPEN文を使用すると、問合せを実行してアクティブ・セットを決定できます。次の例では、emp_cursorという名前のカーソルをOPENします。
EXEC SQL OPEN emp_cursor;
OPENでは、SQLCA内のSQLERRDの第3要素に保存されている処理済行数が0に設定されます。ただし、この時点ではアプリケーションから参照できる行はありません。これはFETCH文で処理されます。
OPENによって、カーソルはアクティブ・セットの最初の行の直前に位置付けられます。また、SQLCA内のSQLERRDの第3要素に保存されている処理済行数が0に設定されます。ただし、この時点では実際に取り出された行はありません。行の取出しはFETCH文によって行われます。
カーソルをOPENすると、問合せの入力ホスト変数はカーソルを再度OPENするまでは再度検査されません。つまり、アクティブ・セットは変更されません。アクティブ・セットを変更するには、そのカーソルを再度OPENします。
通常、カーソルは再度OPENする前にCLOSEする必要があります。ただし、MODE=ORACLE（デフォルト）を指定すると、カーソルを再度OPENする前にCLOSEする必要はありません。これによりパフォーマンスが向上します。
OPENによって行われる作業量は、3つのプリコンパイラ・オプションHOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSの値に応じて異なります。
FETCH文を使用すると、アクティブ・セットから行を取り出し、結果を格納する出力ホスト変数を指定できます。カーソルに対応付けられたSELECT文にはINTO句を組み込めないことを思い出してください。INTO句および出力ホスト変数のリストはFETCH文の一部として指定します。次の例では、3つの出力ホスト変数に対してFETCH INTOを実行します。
EXEC SQL FETCH emp_cursor INTO :emp_name, :emp_number, :salary;
カーソルはあらかじめDECLAREおよびOPENしておく必要があります。最初にFETCH文を実行すると、アクティブ・セットの最初の行より前にあるカーソルがその最初の行に移動します。この行が現在行になります。その後FETCHを実行するたびに、カーソルはアクティブ・セットの次の行に進みます（現在行が変更されます）。カーソルはアクティブ・セット内を順方向にしか進みません。すでにFETCHを完了した行に戻るには、このカーソルを再度OPENして、その後このアクティブ・セットの最初の行からもう一度始めます。
アクティブ・セットを変更する場合は、カーソルに対応付けられた問合せ内の入力ホスト変数に新しい値を割り当ててから、カーソルを再度OPENしてください。MODE=ANSIのときは、カーソルを再度OPENする前に一度CLOSEする必要があります。
次の例に示すとおり、出力ホスト変数の異なる集合を使用して、同じカーソルからFETCHできます。しかし、各FETCH文のINTO句内の対応するホスト変数は、同じデータ型を持つ必要があります。
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE deptno = 20;
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name1, :salary1;
EXEC SQL FETCH emp_cursor INTO :emp_name2, :salary2;
EXEC SQL FETCH emp_cursor INTO :emp_name3, :salary3;
...
}
アクティブ・セットが空か、それ以上の行を含んでいない場合、FETCHによって「データが見つかりません」というエラー・コードがSQLCAのsqlcode、またはSQLCODEまたはSQLSTATE状態変数に戻されます。出力ホスト変数のステータスは予測不能です。（通常のプログラムでは、WHENEVER NOT FOUND文でこのエラーが検出されます。）このカーソルを再利用するには、このカーソルを再度OPENする必要があります。
次の場合、カーソル上でのFETCHはエラーになります。
カーソルをオープン（OPEN）する前
「データが見つかりません」条件の後
カーソルをクローズ（CLOSE）した後
アクティブ・セットから行のFETCHを終了した後に、そのカーソルをCLOSEして、カーソルのOPENによって取得していたリソース（記憶域など）を解放します。カーソルがクローズされると、解析ロックは解除されます。どのリソースが解放されるかは、HOLD_CURSORおよびRELEASE_CURSORオプションの設定によって異なります。次の例では、emp_cursorという名前のカーソルをCLOSEします。
EXEC SQL CLOSE emp_cursor;
アクティブ・セットが未定義になっているため、クローズしたカーソルからはFETCHはできません。必要に応じて、カーソルを再度OPENする（たとえば、入力ホスト変数に新しい値を指定するなど）ことができます。
MODE=ORACLEのときに、COMMITまたはROLLBACKを発行すると、CURRENT OF句内で参照されているカーソルがクローズされます。他のカーソルはCOMMITまたはROLLBACKによって影響されません。オープンである場合は、オープンのままです。ただし、MODE=ANSIのときは、COMMITまたはROLLBACKを発行すると、すべての明示カーソルがクローズされます。
スクロール可能カーソルとは、OracleがSQL文を実行し、実行中に処理される情報を格納する作業領域です。
カーソルが実行されると、結果セットと呼ばれる一連の行に問合せ結果が入れられます。結果セットは、順番にフェッチすることも、順不同でフェッチすることもできます。順不同の結果セットをスクロール可能カーソルと呼びます。
スクロール可能カーソルを使用すると、ユーザーは前から、後ろからまたはランダムな方法でデータベース結果セットの行にアクセスできます。これを利用して、プログラムは結果セット内の任意の行をフェッチできます。『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
次の文を使用して、スクロール可能カーソルを定義および操作します。
FETCH文を使用すると、必要な行をランダムな方法でフェッチできます。アプリケーションでは、行を上方向へまたは下方向へフェッチしたり、最初または最後の行を直接フェッチしたり、または任意の1行をランダムにフェッチすることができます。
FETCH文には次のオプションがあります。
FETCH FIRST
結果セットの最初の行をフェッチします。
FETCH PRIOR
現在行の直前の行をフェッチします。
FETCH NEXT
現在位置の次の行をフェッチします。これは、スクロール不可カーソルのFETCHに相当します。
FETCH LAST
結果セットの最後の行をフェッチします。
FETCH CURRENT
現在行をフェッチします。
FETCH RELATIVE n
現在行を基準にしてn番目の行をフェッチします。nはオフセットです。
FETCH ABSOLUTE n
n番目の行をフェッチします。nは、結果セットの始まりからのオフセットです。
次の例は、結果セットの最後のレコードをFETCHする方法を示します。
EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR SELECT ename, sal FROM emp WHERE deptno=20; ... EXEC SQL OPEN emp_cursor; EXEC SQL FETCH LAST emp_cursor INTO :emp_name, :sal; EXEC SQL CLOSE emp_cursor;
CLOSE_ON_COMMITマイクロ・プリコンパイラ・オプションを使用すると、マクロ・オプションMODE=ANSIでCOMMITが実行されるときにすべてのカーソルをクローズするかどうかを選択できます。MODE=ANSIのとき、CLOSE_ON_COMMITのデフォルト値はYESです。CLOSE_ON_COMMIT=NOと明示的に設定すると、COMMITが実行されてもカーソルはクローズされず、カーソルを再オープンして解析する必要がなくなるためパフォーマンスが向上します。
|
関連項目
|
PREFETCHプリコンパイラ・オプションを使用すると、一定の行数をプリフェッチすることで、より効率的に問合せできます。これにより、サーバー・ラウンドトリップとメモリー使用量全体を減らすことができます。PREFETCHオプション値で設定する行数は、標準的な慣例に従い、明示カーソルに関係するすべての問合せに使用されます。PREFETCHオプションをインラインで使用する場合は、次のカーソル文よりも先に指定する必要があります。
EXEC SQL OPEN cursor
EXEC SQL OPEN cursor USING host_var_list
EXEC SQL OPEN cursor USING DESCRIPTOR desc_name
OPENを実行すると、問合せ実行時にプリフェッチする行数がPREFETCHの値により指定されます。0（プリフェッチなし）〜65535の値を設定できます。デフォルトは1です。
|
注意: PREFETCHオプションのデフォルト値は1で、ラウンドトリップごとに1行が戻されます。PREFETCHオプションを使用しないように選択する場合は、コマンドラインでPREFETCHオプションを0に設定して、明示的に使用禁止にする必要があります。LONGまたはLOB列が取り出される場合は、PREFETCHが自動的に使用禁止になります。 |
|
注意: PREFETCHは、主として単一行フェッチのパフォーマンス強化に使用します。配列フェッチの使用時には、PREFETCHは無効です。 |
|
注意: PREFETCHオプションは、状況に応じて広範囲に使用する必要があります。特定のFETCH文のパフォーマンスが最適化されるように、適切なプリフェッチ値を選択してください。そのためには、コマンドラインのPREFETCHオプションのかわりにインラインのPREFETCHオプションを使用します。 |
|
注意: FETCH文にホスト変数とともに標識変数を使用するだけで、多数の大型アプリケーションのパフォーマンスを改善できます。プリコンパイラ・アプリケーションで、単一行フェッチに対するPREFETCHオプションの使用による最大のメリットが得られるように、標識変数を使用することをお薦めします。 |
Pro*C/C++プリコンパイラは、SQL文中のオプティマイザ・ヒントをサポートしています。オプティマイザ・ヒントとは、Oracle SQLオプティマイザへの提案機能であり、通常行われる最適化アプローチを上書きできます。ヒントを使用して、次の事項を指定できます。
SQL文のための最適化アプローチ
参照されているそれぞれの表へのアクセス・パス
結合のための結合順序
表を結合するための方法
ヒントによって、ルールベースの最適化およびコストベースの最適化のどちらかを選択できます。コストベースの最適化を使用する場合は、この他にスループットまたは応答速度を最大にするためのヒントを使用できます。
オプティマイザ・ヒントは、SELECT、DELETE、UPDATEコマンドの直後に、C形式またはC++形式のコメントの中で発行できます。コメント開始記号の後に間にスペースを空けないでプラス記号（+）を入力し、コメントに1つまたは複数のヒントが含まれていることを示します。たとえば、次の文では最善のスループットを得るために文のコストベース・アプローチの最適化を行うALL_ROWSヒントを使用しています。
EXEC SQL SELECT /*+ ALL_ROWS (cost-based) */ empno, ename, sal, job
INTO :emp_rec FROM emp
WHERE deptno = :dept_number;
この文で示されているように、コメントには、オプティマイザ・ヒントのみでなく、他のコメントも組み込めます。
コストベースのオプティマイザとオプティマイザ・ヒントの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
モジュールが1つの環境で開発され、別の環境に統合およびデプロイされるアプリケーション開発環境では、アプリケーションのパフォーマンスに影響が及びます。プリコンパイラ・アプリケーションのパフォーマンスが、データベース環境での変更の影響を受ける場合があります。これらの変更には、オプティマイザ統計に対する変更、またはメモリー構造体のサイズに影響を与えるパラメータに対する変更が考えられます。
開発環境のPro*C/C++で使用されるSQLの実行計画を修正するには、プリコンパイル時にOracleのアウトライン機能を使用する必要があります。アウトラインは、SQL文に関連付けられたオプティマイザ・ヒントのセットとして実装されます。その文に対してアウトラインの使用を有効にすると、Oracleでは、自動的にストアド・ヒントを検討し、ヒントに従って実行計画が生成されます。このようにして、モジュールが異なる環境に統合またはデプロイされる場合、パフォーマンスに影響が及ばないようにすることができます。
次のSQL文を使用すると、Pro*C/C++でアウトラインを作成できます。
SELECT
DELETE
UPDATE
INSERT ... SELECT
CREATE TABLE ... AS SELECT
アウトライン・オプションが設定されている場合、プリコンパイラでは、プリコンパイルが正常に終了した時点で、SQLファイルとLOGファイルの2ファイルが生成されます。コマンドライン・オプションのoutlineとoutlnprefixが、アウトラインの生成を制御します。
生成された各アウトラインの名前は一意です。アプリケーションで使用されるファイル名が一意であるため、この情報はアウトライン名の生成で使用されます。さらに、カテゴリ名も接頭辞として前に付きます。
|
注意: Oracleでは、アウトライン名に30バイトしか使用できません。この制限を超えると、プリコンパイラはエラーのフラグを立てます。アウトライン名の長さは、outlnprefixオプションを使用すれば制限できます。 |
例6-1 アウトラインを含むSQLファイルの生成
このプログラムで、すべてのアウトライン・サポートSQL文のアウトラインを含むSQLファイルを生成するために、アウトライン・オプションを使用して、次のプログラムをプリコンパイルする必要があります。
/*
* outlndemo.pc
*
* Outlines will be created for the following SQL operations,
* 1. CREATE ... SELECT
* 2. INSERT ... SELECT
* 3. UPDATE
* 4. DELETE
* 5. SELECT
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>
/* Error handling function. */
void sql_error(char *msg)
{
exec sql whenever sqlerror continue;
printf("\n%s\n", msg);
printf("%.70s\n", sqlca.sqlerrm.sqlerrmc);
exec sql rollback release;
exit(EXIT_FAILURE);
}
int main()
{
varchar ename[10];
varchar job[9];
float sal, comm;
exec sql begin declare section;
char *uid = "scott/tiger";
exec sql end declare section;
exec sql whenever sqlerror do sql_error("ORACLE error--\n");
exec sql connect :uid;
exec sql insert into bonus
select ename, job, sal, comm from emp where job like 'SALESMAN';
exec sql update bonus set sal = sal * 1.1 where sal < 1500;
exec sql declare c1 cursor for
select ename, job, sal, comm from bonus order by sal;
exec sql open c1;
printf ("Contents of updated BONUS table\n\n");
printf ("ENAME JOB SALARY COMMISSION\n\n");
exec sql whenever not found do break;
while (1)
{
exec sql fetch c1 into :ename, :job, :sal, :comm;
ename.arr[ename.len]='\0';
job.arr[job.len]='\0';
printf ("%-9s %-9s %8.2f %8.2f\n", ename.arr,
job.arr, sal, comm);
}
exec sql close c1;
exec sql whenever not found do sql_error("ORACLE error--\n");
exec sql delete from bonus;
exec sql create table outlndemo_tab as
select empno, ename, sal from emp where deptno = 10;
/* Outline will not be created for this DDL statement */
exec sql drop table outlndemo_tab;
exec sql rollback work release;
exit(EXIT_SUCCESS);
}
生成されるファイル名は、次の書式になります。
<filename>_<filetype>.sql
Pro*Cでは、abc.pcファイルの場合、生成されるSQLファイルはabc_pc.sqlとなります。
生成されるファイルの書式
outlnprefixオプションが使用されない場合、アウトライン名およびコメントとして使用される一意の識別子の書式は次のようになります。
<category_name>_<filename>_<filetype>_<sequence no.>
outlnprefixオプションが使用される場合（outlnprefix=<prefix_name>）、アウトライン名およびコメントとして使用される一意の識別子の書式は次のようになります。
<prefix_name>_<sequence no.>
outline=yesの場合（デフォルト・カテゴリ）、<category_name>はDEFAULTで、アウトライン名は次のようになります。
DEFAULT_<filename>_<filetype>_<sequence no.>
または
<prefix_name>_<sequence no.>
<sequence no.>の許容範囲は0000〜9999です。
生成されたプリコンパイル済ファイルでのSQLには、そのSQLのアウトラインに出現する際にコメントが付きます。
次の例を考えます。
例I
abc.pcに次の文がある場合
EXEC SQL select * from emp where empno=:var; EXEC SQL select * from dept;
outline=mycat1で、outlnprefixが使用されないとします。
abc_pc.sqlの内容
select * from emp where empno=:b1 /* mycat1_abc_pc_0000 */;で、カテゴリmycat1のアウトラインmycat1_abc_pc_0000を作成または置換します。
select * from dept /* mycat1_abc_pc_0001 */;で、カテゴリmycat1のアウトラインmycat1_abc_pc_0001を作成または置換します。
abc.cの内容
sqlstm.stmt = select * from emp where empno=:b1 /* mycat1_abc_pc_0000 */; sqlstm.stmt = select * from dept /* mycat1_abc_pc_0001 */;
例II
abc.pcに次の文がある場合
EXEC SQL select * from emp where empno=:var; EXEC SQL select * from dept;
outline=mycat1で、outlnprefix=myprefixであるとします。
abc_pc.sqlの内容
select * from emp where empno=:b1 /* myprefix_0000 */;で、カテゴリmycat1のアウトラインmyprefix_0000を作成または置換します。
select * from dept /* myprefix_0001 */;で、カテゴリmycat1のアウトラインmyprefix_0001を作成または置換します。
abc.cの内容
sqlstm.stmt = select * from emp where empno=:b1 /* myprefix_0000 */; sqlstm.stmt = select * from dept /* myprefix_0001 */;
例III
abc.pcに次の文がある場合
EXEC SQL select * from emp where empno=:var; EXEC SQL select * from dept;
outline=yesで、outlnprefix=myprefixであるとします。
abc_pc.sqlの内容
select * from emp where empno=:b1 /* myprefix_0000 */;で、アウトラインmyprefix_0000を作成または置換します。
select * from dept /* myprefix_0001 */;で、アウトラインmyprefix_0001を作成または置換します。
abc.cの内容
sqlstm.stmt = "select * from emp where empno=:b1 /* myprefix_0000 */; sqlstm.stmt = "select * from dept /* myprefix_0001 */";
生成されるファイル名は、次の書式になります。
<filename>_<filetype>.log
Pro*Cでは、abc.pcファイルの場合、生成されるLOGファイルはabc_pc.logとなります。
次の例を考えます。
例I
abc.pcに次の文がある場合
EXEC SQL select * from emp;
abc_pc.logの内容
CATEGORY <Category_name>
Source SQL_0
SELECT * FROM emp
OUTLINE NAME
abc_pc_0000
OUTLINE SQL_0
Select * from emp /* abc_pc_0000 */
DELETE文またはUPDATE文でCURRENT OF cursor_name句を使用すると、指定したカーソルから最後にフェッチした行を参照できます。カーソルをオープンし、行に位置付ける必要があります。FETCHが実行されていない場合、あるいはそのカーソルがオープンされていない場合は、CURRENT OF句の結果はエラーとなり、行は処理されません。
UPDATE文またはDELETE文のCURRENT OF句で参照されたカーソルをDECLAREする場合、FOR UPDATE OF句はオプション指定です。CURRENT OF句では、必要に応じてFOR UPDATE句を追加するようにプリコンパイラに指示します。
次の例では、CURRENT OF句を使用して、emp_cursorという名前のカーソルから最後にFETCHした行を参照します。
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE job = 'CLERK'
FOR UPDATE OF sal;
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;) {
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
EXEC SQL UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
}
CURRENT OF句を索引構成表に使用することはできません。
明示的なまたは暗黙的なFOR UPDATE句では、行の排他ロックが取得されます。すべての行はFETCHされるときではなく、OPENされるときにロックされます。COMMITまたはROLLBACKすると、行ロックは解除されます。したがって、COMMIT後はFOR UPDATEカーソルからFETCHできません。FETCHを試みると、Oracleから01002エラー・コードが戻されます。
また、ホスト配列はCURRENT OF句と一緒には使用できません。別の方法については、「CURRENT OFの擬似実行」を参照してください。
さらに、関連するFOR UPDATE OF句で複数の表は参照できません。つまり、CURRENT OF句とは結合できません。
次の例に、アプリケーション・プログラムでのカーソル制御文の一般的な順序を示します。
...
/* define a cursor */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, job
FROM emp
WHERE empno = :emp_number
FOR UPDATE OF job;
/* open the cursor and identify the active set */
EXEC SQL OPEN emp_cursor;
/* break if the last row was already fetched */
EXEC SQL WHENEVER NOT FOUND DO break;
/* fetch and process data in a loop */
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title;
/* optional host-language statements that operate on
the FETCHed data */
EXEC SQL UPDATE emp
SET job = :new_job_title
WHERE CURRENT OF emp_cursor;
}
...
/* disable the cursor */
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
...
次に、カーソルとFETCH文を使用した完全なプログラム例を示します。このプログラムでは部門番号の入力要求が行われ、その後、その部内の全従業員の名前が表示されます。
最後のFETCHを除くすべてのFETCHは1行を戻し、さらにそのFETCHの実行中にエラーが検出されなければ正常処理を示すステータス・コードを戻します。最後のFETCHは失敗して、「データが見つかりません」というエラー・コードをsqlca.sqlcodeに戻します。実際にFETCHされた行の累積数は、SQLCA内のsqlerrd[2]に示されます。
#include <stdio.h>
/* declare host variables */
char userid[12] = "SCOTT/TIGER";
char emp_name[10];
int emp_number;
int dept_number;
char temp[32];
void sql_error();
/* include the SQL Communications Area */
#include <sqlca.h>
main()
{ emp_number = 7499;
/* handle errors */
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
/* connect to Oracle */
EXEC SQL CONNECT :userid;
printf("Connected.\n");
/* declare a cursor */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename
FROM emp
WHERE deptno = :dept_number;
printf("Department number? ");
gets(temp);
dept_number = atoi(temp);
/* open the cursor and identify the active set */
EXEC SQL OPEN emp_cursor;
printf("Employee Name\n");
printf("-------------\n");
/* fetch and process data in a loop
exit when no more data */
EXEC SQL WHENEVER NOT FOUND DO break;
while (1)
{
EXEC SQL FETCH emp_cursor INTO :emp_name;
printf("%s\n", emp_name);
}
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
void
sql_error(msg)
char *msg;
{
char buf[500];
int buflen, msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &msglen);
printf("%s\n", msg);
printf("%*.s\n", msglen, buf);
exit(1);
}
次のプログラムは、スクロール可能カーソルとFETCH文により使用される様々なオプションの使用方法を示します。
#include <stdio.h>
/* declare host variables */
char userid[12]="SCOTT/TIGER";
char emp_name[10];
void sql_error();
/* include the SQL Communications Area */
#include<sqlca.h>
main()
{
/* handle errors */
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
/* connect to Oracle */
EXEC SQL CONNECT :userid;
printf("Connected.\n");
/* declare a scrollable cursor */
EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR
SELECT ename FROM emp;
/* open the cursor and identify the active set */
EXEC SQL OPEN emp_cursor;
/* Fetch the last row */
EXEC SQL FETCH LAST emp_cursor INTO :emp_name;
/* Fetch row number 5 */
EXEC SQL FETCH ABSOLUTE 5 emp_cursor INTO :emp_name;
/* Fetch row number 10 */
EXEC SQL FETCH RELATIVE 5 emp_cursor INTO :emp_name;
/* Fetch row number 7 */
EXEC SQl FETCH RELATIVE -3 emp_cursor INTO :emp_name;
/* Fetch the first row */
EXEC SQL FETCH FIRST emp_cursor INTO :emp_name;
/* Fetch row number 2*/
EXEC SQL FETCH my_cursor INTO :emp_name;
/* Fetch row number 3 */
EXEC SQL FETCH NEXT my_cursor INTO :emp_name;
/* Fetch row number 3 */
EXEC SQL FETCH CURRENT my_cursor INTO :emp_name;
/* Fetch row number 2 */
EXEC SQL FETCH PRIOR my_cursor INTO :emp_name;
}
void
sql_error(msg)
char *msg;
{
char buf[500];
int buflen , msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK TRANSACTION;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &mesglen);
printf("%s\n",msg);
printf("%*.s\n",msglen,buf);
exit(1);
}
次の抜粋例は、ユニバーサルROWIDを使用した位置付け更新を示しています。「ユニバーサルROWID」も参照してください。
#include <oci.h>
...
OCIRowid *urowid;
...
EXEC SQL ALLOCATE :urowid;
EXEC SQL DECLARE cur CURSOR FOR
SELECT rowid, ... FROM my_table FOR UPDATE OF ...;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :urowid, ...;
/* Process data */
...
EXEC SQL UPDATE my_table SET ... WHERE CURRENT OF cur;
EXEC SQL CLOSE cur;
EXEC SQL FREE :urowid;
...
