1. プログラマーズ・ガイド
  2. 概要および概念
  3. 埋込みSQL

6 埋込みSQL

この章は、埋込みSQLプログラミングの基本技術を理解し、利用する上で役立ちます。この章のトピックは、次のとおりです:

6.1 ホスト変数

Oracleでは、ホスト変数を使用してプログラムにデータやステータス情報を渡します。一方、プログラムでは、ホスト変数を使用してOracleにデータを渡します。

6.1.1 出力変数と入力変数

ホスト変数は、使用方法によって出力ホスト変数または入力ホスト変数と呼ばれます。

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を使用します。

入力ホスト変数を含む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句内で入力ホスト変数の前にコロンが付いていることに注意してください。

関連項目

6.2 標識変数

任意のホスト変数に任意指定の標識変数を関連付けることができます。標識変数に関連付けたホスト変数をSQL文内で使用するたびに、結果コードが対応する標識変数内に格納されます。つまり、標識変数によってホスト変数を監視できます。

注意:

PL/SQLブロックでは、単一のホスト変数に対して複数の標識変数を使用できません。このように使用すると、「バインドされていない変数があります。」というエラーが発生します。

標識変数をVALUESまたはSET句に使用して、入力ホスト変数にNULLを割り当てます。また、INTO句に使用すると、出力ホスト変数内のNULL値または切り捨てられた値を検出できます。

入力時

プログラムが標識変数に割り当てる値の意味は、次のとおりです。

変数 説明

-1

Oracleによって、その列にNULLが割り当てられます。このホスト変数の値は無視されます。

>=0

Oracleは、このホスト変数の値を列に割り当てます。

出力時

Oracleが標識変数に割り当てる値の意味は、次のとおりです。

変数 説明

-1

この列の値はNULLです。したがって、このホスト変数の値は予測不能です。

0

列の値がそのままこのホスト変数に割り当てられました。

>0

切り捨てられた列の値がこのホスト変数に割り当てられました。標識変数によって返される整数は、列値の元の長さです。SQLCAのSQLCODEが0(ゼロ)に設定されます。

-2

Oracleによって切り捨てられた列値がこのホスト変数に割り当てられました。ただし、元の列値は決定できませんでした(LONG列など)。

標識変数は2バイトの整数として定義する必要があります。また、SQL文中では、標識変数の前にコロンを付けてホスト変数の直後に置く必要があります。

6.2.1 NULL値の挿入

標識変数を使用して、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);

6.2.2 戻されたNULL値

標識変数を使用すると、次の例に示すように、戻された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;

6.2.3 NULL値のフェッチ

DBMS=V7またはDBMS=V8のとき、SELECTまたはFETCHしたNULL値を標識変数と関連付けられていないホスト変数に入れると、Oracleは次のエラー・メッセージを発行します。 

ORA-01405: fetched column value is NULL

関連項目

6.2.4 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列に1つ以上の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));

6.2.5 切り捨てられた値

DBMS=V7またはV8のとき、切り捨てられた列の値をSELECTまたはFETCHして標識変数に関連付けられていないホスト変数に格納すると、エラーではなく警告が生成されます。

6.3 基本的なSQL文

実行SQL文を使用すると、Oracleデータの問合せ、操作および制御ができます。さらに、表、ビューおよび索引などのOracleオブジェクトの作成、定義およびメンテナンスができます。この章では、データの問合せおよび操作を行う文を重点的に説明しています。

INSERT、UPDATEまたはDELETEなどのデータ操作文を実行するときは、入力ホスト変数の値の設定以外に、文が成功するか失敗するかのみを考えます。これは、SQLCAを調べればわかります。(SQL文を実行すると、SQLCA変数が設定されます。)次の2つの方法でチェックできます。

  • WHENEVER文による暗黙的なチェック

  • SQLCA変数の明示的なチェック

ただし、SELECT文(問合せ)を実行している場合は、戻されたデータ行の処理もする必要があります。問合せは次のように分類されます。

  • 行を戻さない問合せ(有無のみを調べる)

  • 1行のみを戻す問合せ

  • 複数の行を戻す問合せ

複数の行を戻す問合せの場合は、カーソルを明示的に宣言するか、ホスト配列(配列として宣言されたホスト変数)を使用する必要があります。

注意:

ホスト配列によって、行の一括処理が可能です。

この章ではスカラー・ホスト変数の使用を想定しています。

Oracleデータの問合せおよび操作は、次の埋込みSQL文で行います。

埋込みSQL文 説明

SELECT

1つ以上の表から行を戻します。

INSERT

表に新しい行を追加します。

UPDATE

表内の行を変更します。

DELETE

表から不要な行を削除します。

明示カーソルの定義および操作は、次の埋込みSQL文で行います。

埋込みSQL文 説明

DECLARE

カーソルに名前を付け、問合せに関連付けます。

OPEN

問合せを実行してアクティブ・セットを決定します。

FETCH

カーソルを移動して、アクティブ・セット内の各行を1つずつ取り出します。

CLOSE

カーソルを使用禁止にします(アクティブ・セットは未定義)。

以降の項では、最初にINSERT、UPDATE、DELETEおよび単一行のSELECT文を記述する方法を説明します。次に、複数行のSELECT文の使用方法を説明します。

関連項目

6.3.1 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行のみ戻される場合の形式は前述の例のようになります。ただし、1つの問合せで複数の行を戻す場合は、カーソルを使用してそれらの行をFETCHするか、それらの行をSELECTしてホスト変数の配列に入れる必要があります。カーソルとFETCH文はこの章で後述します。

1行のみを戻すように作成した問合せが実際には複数行を戻す場合、SELECTの結果は予測不能です。これがエラーの原因かどうかは、SELECT_ERRORオプションの指定方法によって異なります。デフォルト値であるYESの場合は、複数行が戻されるとエラーが発生します。

関連項目

6.3.1.1 使用可能な句

以下の標準SQL句はすべて、次の文内で使用できます。

SELECT文:

  • INTO

  • FROM

  • WHERE

  • CONNECT BY

  • START WITH

  • GROUP BY

  • HAVING

  • ORDER BY

  • FOR UPDATE OF

INTO句を除いて、埋込みSELECT文は、SQL*Plusを使用して対話形式で実行およびテストすることが可能です。SQL*Plusでは、入力ホスト変数のかわりに置換変数または定数を使用します。

6.3.2 INSERT文

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句に表内の各列に対する値がすべて指定されている場合は、この列リストを省略できます。

関連項目

6.3.2.1 副問合せの使用について

副問合せはネストされた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文では、中間結果を得るために副問合せを使用しています。

6.3.3 UPDATE文

UPDATE文を使用すると、表またはビュー内の指定した列の値を変更できます。次の例では、EMP表のSAL列とCOMM列を更新します。 

EXEC SQL UPDATE emp 
SET sal = :salary, comm = :commission 
WHERE empno = :emp_number;

オプションの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句を指定できます。オプションのWHERE条件の後にのみ指定できます。

関連項目

6.3.4 DELETE文

DELETE文を使用すると、表またはビューから行を削除できます。次の例では、EMP表から指定した部内の全従業員を削除します。 

EXEC SQL DELETE FROM emp 
WHERE deptno = :dept_number ;

オプションのWHERE句を使用して、行を削除する条件を指定しています。

RETURNING句オプションはDELETE文でも使用できます。オプションのWHERE条件の後に指定できます。前述の例では、削除する各従業員のフィールド値を事前に記録することをお薦めします。

関連項目

6.3.5 WHERE句

WHERE句を使用すると、表またはビュー内で検索条件を満たす行のみをSELECT、UPDATEまたはDELETEできます。WHERE句の検索条件はブール式であり、スカラー・ホスト変数、ホスト配列(SELECT文内を除く)、副問合せおよびユーザー定義のストアド・ファンクションを含めることができます。

WHERE句を省略した場合は、表またはビュー内のすべての行が処理されます。UPDATEまたはDELETE文でWHERE句を省略すると、OracleはSQLCAのsqlwarn[4]を「W」に設定して、すべての列が処理されたことを示します。

6.4 DML RETURNING句

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ではサポートされます。複数行に影響するDML RETURNING句を含むDML文は、ANSI動的SQLではサポートされていません。

関連項目

6.5 カーソル

問合せで複数行を戻す場合、明示的にカーソルを定義して次の処理を行うことができます。

  • 問合せによって戻された最初の行以後の処理

  • 現在どの行が処理されているかの追跡および記録

ホスト配列を使用することもできます。

カーソルは、問合せによって戻された行の集合内におけるカレント行を示します。これによって、プログラムは一度に1行ずつ処理できます。次の文を使用してカーソルを定義および操作します。

  • DECLARE CURSOR

  • OPEN

  • FETCH

  • CLOSE

まず、DECLARE CURSOR文を使用して、カーソルに名前を付け、問合せに関連付けます。

OPEN文によって問合せが実行され、この問合せの検索条件を満たす行がすべて判別されます。これらの行は、カーソルのアクティブ・セットと呼ばれる集合を形成します。カーソルをOPENした後、対応する問合せによって戻された行を取り出すことができます。

アクティブ・セットの行は1行ずつ取り出されます(ホスト配列を使用していない場合)。FETCH文を使用してアクティブ・セット内のカレント行を取り出します。FETCHは、すべての行が取り出されるまで繰返し実行できます。

アクティブ・セットからの行のFETCHが終了した後、CLOSE文によってカーソルを使用禁止にします(アクティブ・セットは未定義になります)。

以降の項では、アプリケーション・プログラム内でのこれらのカーソル制御文の使用方法について説明します。

関連項目

6.5.1 DECLARE CURSOR文

次の例のように、DECLARE CURSOR文を使用して、カーソルに名前を付け、問合せに関連付けることで、カーソルを定義できます。 

EXEC SQL DECLARE emp_cursor CURSOR FOR 
     SELECT ename, empno, sal 
     FROM emp 
     WHERE deptno = :dept_number;

カーソル名は、ホスト変数やプログラム変数ではなく、プリコンパイラで使用される識別子であるため、宣言部では定義しないでください。したがって、あるプリコンパイル単位から別のプリコンパイル・ユニットにカーソル名を渡すことはできません。カーソル名にハイフンは使用できません。長さは任意ですが、重要な意味があるのは先頭の31文字までです。ANSI互換性を維持するため、カーソル名は18文字までにしてください。

コマンドラインまたは構成ファイルでプリコンパイラ・オプションCLOSE_ON_COMMITが使用できます。CLOSE_ON_COMMIT=YESに設定すると、WITH HOLD句なしで宣言されたカーソルはすべてCOMMITまたはROLLBACKの後でクローズされます。

CLOSE_ON_COMMITより高いレベルでMODEが指定されていると、MODEが優先されます。デフォルトはMODE=ORACLEおよびCLOSE_ON_COMMIT=NOです。MODE=ANSIと指定した場合は、WITH HOLD句を使用していないカーソルはCOMMIT時にクローズされます。カーソルのクローズおよび再オープンの回数が多くなるため、アプリケーションの動作は遅くなります。MODE=ANSIのときは、CLOSE_ON_COMMIT=NOと設定するとパフォーマンスが向上します。MODEなどのマクロ・オプションがCLOSE_ON_COMMITなどのマイクロ・オプションに与える影響は、オプション値の優先順位を参照してください。

カーソルに関連付けられたSELECT文にINTO句を含めることはできません。INTO句および出力ホスト変数のリストはFETCH文の一部として指定します。

DECLARE CURSOR文は宣言部なので、そのカーソルを参照する他のすべてのSQL文よりも物理的に(論理的にというだけでなく)前にあることが必要です。つまり、カーソルの前方参照は許可されていません。次の例では、OPEN文の位置が誤っています。 

... 
EXEC SQL OPEN emp_cursor; 
*    -- MISPLACED OPEN STATEMENT
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することはできません。

MODE=ANSIまたはCLOSE_ON_COMMIT=YESを使用する場合は、DECLAREセクションにWITH HOLD句を使用して、2つのオプションで定義される動作をオーバーライドできます。これらのオプションを設定すると、COMMITが発行されたときにすべてのカーソルがクローズされます。この場合、処理を続行するのにカーソルを再びオープンする必要があるため、オーバーヘッドが生じてパフォーマンスが低下します。WITH HOLD句を適切に使用して処理を高速化するには、プリコンパイラがANSI規格に適合しているプログラムであることが必要です。

多数のカーソルを使用する場合は、MAXOPENCURSORSオプションの指定が必要になることがあります。

関連項目

6.5.2 OPEN文

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によって行われる作業量は、HOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSの3つのプリコンパイラ・オプションの値によって決まります。

関連項目

6.5.3 FETCH文

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した後

6.5.4 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を発行すると、すべての明示カーソルがクローズされます。

関連項目

6.6 スクロール可能カーソル

スクロール可能カーソルは、SQL文が実行され、実行中に処理された情報が格納される作業領域です。

カーソルが実行されると、結果セットと呼ばれる一連の行に問合せ結果が入れられます。結果セットは、順番にフェッチすることも、順不同でフェッチすることもできます。順不同の結果セットをスクロール可能カーソルと呼びます。

スクロール可能カーソルを使用すると、ユーザーは前から、後ろからまたはランダムな方法でデータベース結果セットの行にアクセスできます。これにより、プログラムは結果セットの任意の行をフェッチできます。詳細は、リリース9.2.0の『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。

6.6.1 スクロール可能なカーソルの使用について

次の文を使用して、スクロール可能カーソルを定義および操作します。

6.6.1.1 DECLARE SCROLL CURSOR

DECLARE <cursor name> SCROLL CURSOR文を使用して、スクロール可能カーソルに名前を付け、問合せに対応付けます。

6.6.1.2 スクロール可能カーソル用のOPEN

OPEN文は、スクロール不可カーソルの場合と同じように使用できます。

6.6.1.3 スクロール可能カーソル用のFETCH

FETCH文を使用すると、必要な行をランダムな方法でフェッチできます。アプリケーションでは、行を上方向へまたは下方向へフェッチしたり、最初または最後の行を直接フェッチしたり、または任意の1行をランダムにフェッチできます。

FETCH文には次のオプションがあります。

  1. FETCH FIRST

    結果セットの最初の行をフェッチします。

  2. FETCH PRIOR

    カレント行の直前の行をフェッチします。

  3. FETCH NEXT

    現在位置の次の行をフェッチします。これは、スクロール不可カーソルのFETCHに相当します。

  4. FETCH LAST

    結果セットの最後の行をフェッチします。

  5. FETCH CURRENT

    カレント行をフェッチします。

  6. FETCH RELATIVE n

    カレント行を基準にしてn番目の行をフェッチします。nはオフセットです。

  7. 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;
6.6.1.4 スクロール可能カーソル用のCLOSE

CLOSE文は、スクロール不可カーソルの場合と同じように使用できます。

注意:

スクロール可能カーソルをREFカーソルとして使用することはできません。

6.6.2 CLOSE_ON_COMMITプリコンパイラ・オプション

CLOSE_ON_COMMITマイクロ・プリコンパイラ・オプションを使用すると、マクロ・オプションMODE=ANSIでCOMMITが実行されるときにすべてのカーソルをクローズするかどうかを選択できます。MODE=ANSIのとき、CLOSE_ON_COMMITのデフォルト値はYESです。明示的にCLOSE_ON_COMMIT=NOと設定すると、COMMITが実行されてもカーソルはクローズされず、カーソルを再オープンして解析する必要がなくなるためパフォーマンスが向上します。

6.6.3 PREFETCHプリコンパイラ・オプション

プリコンパイラ・オプション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値を選択してください。そのためには、コマンドラインのPREFECTHオプションのかわりにインラインのPREFECTHオプションを使用します。

注意:

FETCH文にホスト変数とともに標識変数を使用するだけで、多数の大型アプリケーションのパフォーマンスを改善できます。

プリコンパイラ・アプリケーションで、単一行フェッチに対するPREFETCHオプションの使用による最大の利点が得られるように、標識変数を使用することをお薦めします。

6.7 オプティマイザ・ヒント

Pro*C/C++プリコンパイラは、SQL文中のオプティマイザ・ヒントをサポートしています。オプティマイザ・ヒントとは、Oracle SQLオプティマイザへの提案機能であり、通常行われる最適化アプローチを上書きできます。ヒントを使用して、次の事項を指定できます。

  • SQL文の最適化アプローチ

  • 参照されているそれぞれの表へのアクセス・パス

  • 結合のための結合順序

  • 表を結合する方法

ヒントによって、ルールベースの最適化およびコストベースの最適化のどちらかを選択できます。コストベースの最適化を使用する場合は、この他にスループットまたは応答速度を最大にするためのヒントを使用できます。

6.7.1 ヒントの発行

オプティマイザ・ヒントは、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;

この文で示されているように、コメントには、オプティマイザ・ヒントのみでなく、他のコメントも組み込めます。

6.8 実行計画の修正

ある環境で開発した複数のモジュールを統合して、別の環境にデプロイするアプリケーション開発環境では、アプリケーションのパフォーマンスが影響を受けます。プリコンパイラ・アプリケーションのパフォーマンスは、データベース環境の変更によって影響を受けることがあります。これらの変更には、オプティマイザ統計の変更、オプティマイザ設定の変更またはメモリー構造のサイズに影響するパラメータの変更が含まれます。

開発環境においてPro*C/C++で使用されるSQLの実行計画を修正するには、プリコンパイル時にOracleのアウトライン機能を使用する必要があります。アウトラインは、SQL文と関連付けられた一連のオプティマイザ・ヒントとして実装されます。SQL文のアウトラインの使用を有効にすると、Oracleでは、格納されたヒントが自動的に考慮され、それらのヒントに従って実行計画を生成しようとします。これにより、モジュールの統合時および異なる環境へのデプロイ時に、パフォーマンスは影響を受けません。

次のSQL文を使用して、Pro*C/C++でアウトラインを作成できます。

  • SELECT

  • DELETE

  • UPDATE

  • INSERT ... SELECT

  • CREATETABLE...ASSELECT

アウトライン・オプションが設定されている場合、プリコンパイルが正常に終了すると、2つのファイル(SQLファイルおよびLOGファイル)が生成されます。コマンドライン・オプションoutlineおよびoutlnprefixは、アウトラインの生成を制御します。

生成される各アウトライン名は一意です。アプリケーションで使用するファイル名が一意であるため、アウトライン名の生成時にこの情報が使用されます。また、カテゴリ名も接頭辞として使用されます。

注意:

アウトライン名の最大長は128バイトです。この制限を超えると、プリコンパイラではエラーが発生します。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);
}

6.8.1 SQLファイル

生成されるファイル名の書式は次のとおりです。

<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のアウトラインで表示されるように、コメントが追加されます。

6.8.1.1 例

次の例を考えてみます。

例1

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 */;

例2

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 */;

例3

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 */";

6.8.2 LOGファイル

生成されるファイル名の書式は次のとおりです。

<filename>_<filetype>.log

Pro*Cでは、ファイル「abc.pc」の場合、生成されるLOGファイルはabc_pc.logになります。

次に例を示します。

例1

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 */

6.9 CURRENT OF句

DELETE文またはUPDATE文でCURRENT OF cursor_name句を使用すると、指定したカーソルから最後にFETCHした行を参照できます。カーソルをオープンし、行に位置付けておく必要があります。FETCHが一度も行われていない場合や、そのカーソルがオープンされていない場合には、CURRENT OF句を使用するとエラーが発生し、1行も処理されません。

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; 
}

関連項目

6.9.1 制限事項(FOR UPDATE OF)

CURRENT OF句は索引構成表では使用できません。

明示的なFOR UPDATE OF句または暗黙的なFOR UPDATE句では、行の排他ロックが取得されます。行はすべて、FETCHされるときではなく、OPEN時にロックされ、COMMITまたはROLLBACKを行うとロックが解除されます。したがって、COMMIT後はFOR UPDATEカーソルからFETCHできません。これを試みると、Oracleから1002エラー・コードが戻されます。

また、ホスト配列はCURRENT OF句と一緒には使用できません。代替方法については、CURRENT OF句の擬似実行についても参照してください。

さらに、関連するFOR UPDATE OF句で複数の表は参照できません。つまり、CURRENT OF句とは結合できないということです。

最後に、動的SQLはCURRENT OF句と一緒には使用できません。

6.10 カーソル文

次の例は、アプリケーション・プログラムでのカーソル制御文の一般的な順序を示しています。 

   ...
/* 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; 
   ...

6.11 スクロール不可カーソルを使用する完全な例

次の完全なプログラム例では、カーソルとFETCH文の使用方法を示します。プログラムでは、部門番号の入力を要求してから、その部門の全従業員の名前を表示します。

最後の1つのFETCHを除くすべてのフェッチで1行ずつ戻され、FETCH中にエラーが検出されなければ、成功のステータス・コードが戻されます。最後のFETCHは失敗し、「データが見つかりません。」というOracleエラー・コードが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); 
}

6.12 スクロール可能カーソルを使用する完全な例

次のプログラムでは、スクロール可能カーソルと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); 
}

6.13 位置付け更新

次の抜粋例は、ユニバーサル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;
...

関連項目