1. Oracleプリコンパイラのためのプログラマーズ・ガイド
  2. 埋込みSQLの使用方法

4 埋込みSQLの使用方法

この章は、次の項で構成されています。

この章は、埋込みSQLプログラミングの基本技術を理解し、利用する上で役立ちます。ホスト変数、インジケータ変数、カーソル、カーソル変数、およびOracleデータの挿入、更新、選択、削除を行う基本的なSQLコマンドの使用方法について学習します。

4.1 ホスト変数の使用方法について

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

4.1.1 出力変数と入力変数

その使用方法によって、ホスト変数は出力変数または入力変数と呼ばれます。SELECT文またはFETCH文のINTO句で使用されるホスト変数は、Oracleからの列値の出力を保持するので、出力ホスト変数と呼ばれます。Oracleでは、列値をINTO句の対応する出力ホスト変数に割り当てます。

SQL文のその他のホスト変数の値は、プログラムがそれをOracleに入力するため、すべて入力ホスト変数と呼ばれます。たとえば、入力ホスト変数は、INSERT文のVALUES句とUPDATE文のSET句で使用します。また、WHERE句、HAVING句およびFOR句でも使用します。実際に、値または式が使用可能であれば、1つのSQL文で複数の入力ホスト変数を使用できます。

ORDER BY句では、ホスト変数を使用使用できますが、定数またはリテラルとして扱われるため、ホスト変数のコンテンツには効果がありません。たとえば、次のようなSQL文があるとします。 

EXEC SQL SELECT ename, empno INTO :name, :number
 FROM emp
 ORDER BY :ord;

ここでは、入力ホスト変数ordが含まれているように見えます。しかし、この句のホスト変数は、定数として扱われ、ordの値にかかわらず、順序付けは行われません。

入力ホスト変数を使用して、SQLキーワードまたはデータベース・オブジェクトの名前を指定することはできません。したがって、ALTER、CREATEおよびDROPなどのデータ定義文(DDLとも呼ばれる)では、入力ホスト変数を使用できません。次の例では、DROP TABLE文は無効です。

EXEC SQL BEGIN DECLARE SECTION; 
 table_name CHARACTER(30); 
EXEC SQL END DECLARE SECTION; 
display 'Table name? '; 
read table_name; 
EXEC SQL DROP TABLE :table_name; -- host variable not allowed

入力ホスト変数を含むSQL文をOracleで実行する前に、それらの入力ホスト変数に値を割り当てる必要があります。次の例を検討してください:

EXEC SQL BEGIN DECLARE SECTION; 
 emp_number INTEGER; 
 emp_name CHARACTER(20); 
EXEC SQL END DECLARE SECTION; 
-- get values for input host variables 
display 'Employee number? '; 
read emp_number; 
display 'Employee name? '; 
read emp_name; 
EXEC SQL INSERT INTO EMP (EMPNO, ENAME) 
 VALUES (:emp_number, :emp_name);

INSERT文のVALUES句にある入力変数の前にコロンが付いていることに注意してください。

4.2 インジケータ変数の使用について

任意のホスト変数をオプションのインジケータ変数に関連付けることができます。標識変数に関連付けたホスト変数をSQL文内で使用するたびに、結果コードが対応する標識変数内に格納されます。したがって、インジケータ変数により、ホスト変数を監視できます。

インジケータ変数は、VALUES句またはSET句ではNULLを入力ホスト変数に割り当てるために、INTO句では出力ホスト変数でNULLまたは切り捨てられた値を検出するために使用します。

4.2.1 入力変数

入力ホスト変数の場合、プログラムがインディケータ変数に割り当てる値の意味は次のとおりです。

  • -1: Oracleでは、ホスト変数の値を無視して、NULLを列に割り当てます。

  • >= 0: Oracleでは、ホスト変数の値を列に割り当てます。

4.2.2 出力変数

出力ホスト変数の場合、Oracleでインジケータ変数に割り当てられる値には、次の意味があります。

  • -2: Oracleでは切り捨てられた列値をホスト変数に割り当てましたが、数値が大きすぎるため、元の長さの列値をインジケータ変数に割り当てることができませんでした。

  • -1: 列値はNULLであるため、ホスト変数の値は不確定です。

  • 0: Oracleでは列値をそのままの形でホスト変数に割り当てました。

  • > 0: Oracleでは切り捨てられた列値をホスト変数に割り当て、元の列の長さ(マルチバイトのグローバリゼーション・サポート・ホスト変数の場合、バイト単位ではなく文字単位で表される)をインジケータ変数に割り当て、SQLCAのSQLCODEをゼロに設定しました。

インジケータ変数は、宣言部では2バイト整数として定義する必要があり、SQL文では(キーワードINDICATORを使用しない場合)前にコロンを付け、関連付けられたホスト変数に追加する必要があります。

4.2.3 NULLの挿入

インジケータ変数を使用して、NULLを挿入できます。挿入の前に、NULLにする列ごとに、該当するインジケータ変数を次の例に示すように-1に設定します。 

set ind_comm = -1; 
EXEC SQL INSERT INTO EMP (EMPNO, COMM) 
 VALUES (:emp_number, :commission:ind_comm);

インジケータ変数ind_commは、NULLがCOMM列に格納されるように指定します。

かわりに、次のようにNULLをハードコードすることもできます。 

EXEC SQL INSERT INTO EMP (EMPNO, COMM) 
 VALUES (:emp_number, NULL);

これは柔軟性が少なくなりますが、読みやすくなります。

通常、NULLは、次の例のように条件付きで挿入します。

display 'Enter employee number or 0 if not available: '; 
read emp_number; 
IF emp_number = 0 THEN 
 set ind_empnum = -1; ELSE 
 set ind_empnum = 0; 
ENDIF; 
EXEC SQL INSERT INTO EMP (EMPNO, SAL) 
 VALUES (:emp_number:ind_empnum, :salary);

4.2.4 戻された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 THEN 
 set pay = salary; -- commission is null; ignore it 
ELSE 
 set pay = salary + commission; 
ENDIF;

4.2.5 NULLのフェッチ

ただし、DBMSがNATIVE、V7またはV8の場合、インジケータ変数のないホスト変数にNULLを選択またはフェッチにより入れると、次のエラー・メッセージが表示されます。

ORA-01405: fetched column value is NULL

4.2.6 NULLの検査

WHERE句のインジケータ変数を使用して、次のようにNULLがないか検査できます。 

EXEC SQL SELECT ENAME, SAL 
 INTO :emp_name, :salary 
 FROM EMP 
 WHERE :commission:ind_comm IS NULL ...

ただし、NULLを互いに、あるいは他の値と比較するために比較演算子を使用することはできません。たとえば、次のSELECT文は、COMM列に1つ以上のNULLが含まれていれば失敗します。 

EXEC SQL SELECT ENAME, SAL 
 INTO :emp_name, :salary 
 FROM EMP 
 WHERE COMM = :commission:ind_comm;

次の例は、値のいくつかがNULLの可能性がある場合に、値を比較して等しいかどうかを調べる方法を示しています。

EXEC SQL SELECT ENAME, SAL 
 INTO :emp_name, :salary 
 FROM EMP 
 WHERE (COMM = :commission) OR ((COMM IS NULL) AND 
 (:commission:ind_comm IS NULL));

4.2.7 切り捨てられた値のフェッチ

切り捨てられた列値を、SELECT文またはFETCH文でインジケータ変数のないホスト変数に入れても、エラーは発生しません。

4.3 基本的なSQL文

実行可能なSQL文を使用すれば、Oracleデータの問合せ、操作および制御ができ、表、ビュー、索引などのOracleオブジェクトの作成、定義およびメンテナンスができます。この章では、データ操作文(DML)とカーソル制御文を中心に取り上げます。次のSQL文により、Oracleデータの問合せと操作ができます。

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

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

  • UPDATE: 表内の行を変更します。

  • DELETE: 表から行を削除します。

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

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

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

または、MODE={ANSI|ANSI14}の場合、状態変数SQLSTATEまたはSQLCODEをチェックできます。詳細は、MODE={ANSI|ANSI14}の場合の状態変数の使用についてを参照してください。

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

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

  • 1行のみ戻す問合せ

  • 複数行を戻す問合せ

複数の行を戻す問合せには、明示的に宣言されたカーソルまたはカーソル変数(または、ホスト配列の使用方法で説明されているホスト配列の使用)が必要です。明示カーソルの定義および制御は、次の埋込みSQL文で行います。

  • DECLARE: カーソルを指定し、問合せと関連付けます。

  • OPEN: 問合せを実行し、アクティブ・セットを識別します。

  • FETCH: カーソルを進め、アクティブ・セット内の行を1行ずつ取得します。

  • CLOSE: カーソルを無効にします(アクティブ・セットは未定義になります)。

後続の項では、まずINSERT、UPDATE、DELETEと1行のSELECT文のコードの方法を学習します。その後、複数行のSELECT文に進みます。

4.3.1 行の選択

データベースに対する問合せは、一般的な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行が戻されると、その形式は前述の例のようになります(EMPNOは一意キーです)。ただし、1つの問合せで複数行を戻すことができる場合、カーソルを使用してそれらの行をフェッチするか、行を選択してホスト配列に入れる必要があります。

1行のみを戻す問合せを作成したにもかかわらず、実際には複数行が戻される場合、その結果は、SELECT_ERRORオプションをどのように指定するかによって異なります。SELECT_ERROR=YES(デフォルト)の場合、複数行が戻されると、次のメッセージが表示されます。 

ORA-01422: exact fetch returns more than requested number of rows

SELECT_ERROR=NOの場合、1行が戻され、エラーは発生しません。

4.3.2 使用可能な句

SELECT文では、次の標準的なSQL句をすべて使用できます。INTO、FROM、WHERE、CONNECT BY、START WITH、GROUP BY、HAVING、ORDER BYおよびFOR UPDATE OFです。

4.3.3 行の挿入

表またはビューに行を追加するには、INSERT文を使用します。次の例では、EMP表に1行追加します。 

EXEC SQL INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO) 
 VALUES (:emp_number, :emp_name, :salary, :dept_number);

列リストで指定する各列は、INTO句で指定した表のものである必要があります。VALUES句では、挿入される値の行を指定しています。値には、定数、ホスト変数、SQL式または擬似列(USERやSYSDATEなど)を指定できます。

VALUES句の値の数は、列リストにある名前の数と同じにする必要があります。ただし、VALUES句にCREATE TABLEで定義した表の各列の値が定義どおりの順序で含まれている場合は、列リストを省略してもかまいません。

4.3.4 副問合せの使用方法

副問合せは、ネストされたSELECT文です。副問合せを使用すれば、マルチパート検索を実行できます。これらは、次の目的で使用できます。

  • SELECT、UPDATEおよびDELETE文のWHERE、HAVINGおよびSTART WITH句での比較の値を指定

  • CREATE TABLEまたはINSERT文で挿入する行のセットを定義

  • UPDATE文のSET句の値を定義

たとえば、ある表から別の表に行をコピーするには、INSERT文のVALUES句を次のように副問合せに置き換えます。 

EXEC SQL INSERT INTO EMP2 (EMPNO, ENAME, SAL, DEPTNO) 
 SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP 
 WHERE JOB = :job_title;

中間結果を取得するために、INSERT文で副問合せがどのように使用されているかに注意してください。

4.3.5 行の更新

表またはビューの指定した列の値を変更するには、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;

4.3.6 行の削除

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

EXEC SQL DELETE FROM EMP 
 WHERE DEPTNO = :dept_number;

オプションのWHERE句を使用すれば、行が削除される条件を指定できます。

4.3.7 WHERE句の使用

表またはビューで、検索条件を満たす行のみを選択、更新または削除するには、WHERE句を使用します。WHERE句の検索条件はブール式で、スカラー・ホスト変数、ホスト配列(SELECT文では不可)および副問合せを含めることができます。

WHEREを省略すると、表またはビュー内の行がすべて処理されます。UPDATEまたはDELETE文でWHERE句を省略すると、OracleではSQLCAのSQLWARN(5)を'W'に設定し、すべての行が処理されたことを警告します。

4.4 カーソル

1つの問合せで複数の行が戻される場合、カーソルを明示的に定義すれば、次のことができます。

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

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

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

  • DECLARE

  • OPEN

  • FETCH

  • CLOSE

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

OPEN文により問合せが実行され、問合せの検索条件を満たす行がすべて識別されます。これらの行は、カーソルのアクティブ・セットと呼ばれる集合を形成します。このカーソルをオープンした後、これを使用して、関連付けられた問合せによって戻された行を取得できます。

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

アクティブ・セットからの行の取得が完了したら、CLOSE文でこのカーソルを無効にします。アクティブ・セットは未定義になります。

4.4.1 カーソルの宣言

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

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文は宣言文のため、カーソルを参照する他のすべての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;

カーソル制御文はすべて、同じプリコンパイル・ユニット内で指定する必要があります。たとえば、Aファイルの中でカーソルを宣言して、それをBファイルでオープンすることはできません。

ホスト・プログラムでは、必要な数のカーソルを宣言できます。ただし、1つのファイルでは、すべてのDECLARE文は一意である必要があります。つまり、カーソルの適用範囲は1つのファイル全体に及ぶため、1つのプリコンパイル・ユニット内には、たとえ別のブロックやプロシージャ内であっても、同じ名前のカーソルを2つ宣言することはできません。多数のカーソルを使用する場合は、MAXOPENCURSORSオプションの指定が必要になることがあります。詳細は、MAXOPENCURSORSを参照してください。

4.4.2 カーソルのオープン

問合せを実行して、アクティブ・セットを識別するには、OPEN文を使用します。次の例では、emp_cursorという名前のカーソルをオープンします。 

EXEC SQL OPEN emp_cursor;

OPENにより、カーソルはアクティブ・セットの最初の行の直前に位置付けられます。また、SQLCA内のSQLERRD(3)に保存されている処理済の行数もゼロに設定されます。ただし、この時点では実際に取り出される行はありません。それはFETCH文によって行われます。

カーソルをオープンすると、問合せの入力ホスト変数は、カーソルを再オープンするまで再検査されません。つまり、アクティブ・セットは変更されません。アクティブ・セットを変更するには、カーソルを再オープンする必要があります。

通常、カーソルはクローズしてから再オープンする必要があります。ただし、MODE=ORACLEを指定する場合(デフォルト)、カーソルをクローズしてから再オープンする必要はありません。これによってパフォーマンスが向上する可能性があります。詳細は、パフォーマンス・チューニングを参照してください。

OPENによって行われる作業量は、HOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSの3つのプリコンパイラ・オプションの値によって決まります。詳細は、プリコンパイラ・オプションの使用についてを参照してください。

4.4.3 カーソルからのフェッチ

アクティブ・セットから行を取得して、結果を格納する出力ホスト変数を指定するには、FETCH文を使用します。カーソルに関連付けられたSELECT文には、INTO句を指定できないことを思い出してください。INTO句と出力ホスト変数のリストは、FETCH文で指定します。次の例では、フェッチした行を3つのホスト変数に格納します。 

EXEC SQL FETCH emp_cursor 
 INTO :emp_name, :emp_number, :salary;

カーソルは、あらかじめ宣言し、オープンしておく必要があります。最初にFETCH文を実行すると、カーソルは、アクティブ・セットの最初の行の前から最初の行に移動します。この行がカレント行になります。その後FETCH文を実行するたびに、カーソルはアクティブ・セット内の次の行に進み、現在の行が変わっていきます。カーソルはアクティブ・セット内を順方向にしか進みません。すでにフェッチした行に戻るには、カーソルを再オープンし、アクティブ・セットの最初の行からやり直す必要があります。

アクティブ・セットを変更する場合、カーソルに関連付けられた問合せの入力ホスト変数に新しい値を割り当て、カーソルを再オープンしてください。MODE={ANSI | ANSI14 | ANSI13}に設定されている場合、カーソルを一度クローズしてから、再オープンする必要があります。

次の例に示すように、異なる出力ホスト変数セットを使用して同じカーソルからフェッチできます。ただし、各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 DO ... 
LOOP 
 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; 
 ... 
ENDLOOP;

アクティブ・セットが空か、それ以上の列がない場合、FETCHは、データが見つからないことを示すOracle警告コードをSQLCAのSQLCODE (または、MODE=ANSIの場合は状態変数SQLSTATE)に戻します。出力ホスト変数のステータスは不確定です。(通常のプログラムでは、WHENEVER NOT FOUND文がこのエラーを検出します。)カーソルを再利用するには、再オープンする必要があります。

4.4.4 カーソルのクローズ

アクティブ・セットからの行のフェッチが終了したら、カーソルをクローズし、カーソルのオープンによって確保していたリソース(記憶域など)を解放します。カーソルがクローズされると、解析ロックが解放されます。どのリソースが解放されるかは、オプションHOLD_CURSORおよびRELEASE_CURSORの指定によって異なります。次の例では、emp_cursorという名前のカーソルをクローズします。 

EXEC SQL CLOSE emp_cursor;

クローズしたカーソルのアクティブ・セットは未定義になるため、クローズしたカーソルからフェッチすることはできません。必要であれば、(たとえば、入力ホスト変数に新しい値を指定して)カーソルを再オープンできます。

MODE={ANSI13|ORACLE}の場合、コミットまたはロールバックを発行すると、CURRENT OF句で参照されるカーソルがクローズされます。他のカーソルには、コミットまたはロールバックによる影響はなく、オープンの場合は、オープンのままです。ただし、MODE={ANSI|ANSI14}の場合は、コミットまたはロールバックを発行すると、すべての明示カーソルがクローズされます。

4.4.5 CURRENT OF句の使用方法

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

UPDATEまたはDELETE文のCURRENT OF句で参照されるカーソルを宣言するとき、FOR UPDATE OF句をオプションとして指定できます。CURRENT OF句は、必要に応じてFOR UPDATE句を追加するようにプリコンパイラに指示します。詳細は、FOR UPDATE OF句の使用についてを参照してください。

次の例では、CURRENT OF句を使用して、emp_cursorという名前のカーソルから最後にフェッチされた行を参照します。 

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 DO ... 
LOOP 
 EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; 
 ... 
 EXEC SQL UPDATE EMP SET SAL = :new_salary 
 WHERE CURRENT OF emp_cursor; 
ENDLOOP;

4.4.6 制限事項

明示的なFOR UPDATE OFまたは暗黙的なFOR UPDATEによって行の排他ロックが取得されます。いずれの行も、フェッチされるときではなくオープン時にロックされ、コミットまたはロールバックを行うとロックは解除されます。コミット後にFOR UPDATEカーソルからフェッチしようとすると、次のエラーが発生します。 

ORA-01002: fetch out of sequence

CURRENT OF句ではホスト配列を使用できません。他の方法は、CURRENT OF句の擬似実行についてを参照してください。また、関連付けられた1つのFOR UPDATE OF句で複数の表を参照することもできず、つまり、CURRENT OF句との結合ができないということです。さらに、動的SQLではCURRENT OF句を使用できません。

4.4.7 一般的な文の順序

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

-- 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; 
-- Exit if the last row was already fetched.
EXEC SQL WHENEVER NOT FOUND DO no_more; 

-- Fetch and process data in a loop.
LOOP 
 EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title; 
 -- host-language statements that operate on the fetched data 
 EXEC SQL UPDATE EMP 
 SET JOB = :new_job_title 
 WHERE CURRENT OF emp_cursor; 
ENDLOOP; 
... 
ROUTINE no_more
BEGIN
-- Disable the cursor.
 EXEC SQL CLOSE emp_cursor; 
 EXEC SQL COMMIT WORK RELEASE; 
 exit program; 
END no_more;

4.4.8 完全な例

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

最後の1つのフェッチを除くすべてのフェッチで1行ずつ戻され、フェッチ中にエラーが検出されなければ、成功のステータス・コードが戻されます。最後のフェッチは失敗し、「データが見つかりません」というOracle警告コードがSQLCAのSQLCODEに戻されます。実際にフェッチされた行の累積数は、SQLCAのSQLERRD(3)に示されています。 

-- declare host variables 
EXEC SQL BEGIN DECLARE SECTION; 
 username CHARACTER(20); 
 password CHARACTER(20); 
 emp_name CHARACTER(10); 
 dept_number INTEGER; 
EXEC SQL END DECLARE SECTION; 
-- copy in the SQL Communications Area 
EXEC SQL INCLUDE SQLCA; 

display 'Username? '; 
read username; 
display 'Password? '; 
read password; 

-- handle processing errors 
EXEC SQL WHENEVER SQLERROR DO sql_error; 

-- log on to Oracle 
EXEC SQL CONNECT :username IDENTIFIED BY :password; 
display 'Connected to Oracle'; 

-- declare a cursor 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
 SELECT ENAME FROM EMP WHERE DEPTNO = :dept_number; 

display 'Department number? '; 
read dept_number; 

-- open the cursor and identify the active set 
EXEC SQL OPEN emp_cursor; 

-- exit if the last row was already fetched 
EXEC SQL WHENEVER NOT FOUND DO no_more; 

display 'Employee Name'; 
display '-------------'; 

-- fetch and process data in a loop 
LOOP 
 EXEC SQL FETCH emp_cursor INTO :emp_name; display emp_name; 
ENDLOOP;
ROUTINE no_more
BEGIN
 EXEC SQL CLOSE emp_cursor; 
 EXEC SQL COMMIT WORK RELEASE; 
 display 'End of program'; 
 exit program; 
END no_more;

ROUTINE sql_error
BEGIN
 EXEC SQL WHENEVER SQLERROR CONTINUE; 
 EXEC SQL ROLLBACK WORK RELEASE; 
 display 'Processing error'; 
 exit program with an error; 
END sql_error;

4.5 カーソル変数

この項では、カーソル変数の概要を簡単に説明します。詳細は、ホスト言語の補足資料および「カーソル変数」を参照してください。

Pro*COBOLおよびPro*FORTRANプリコンパイラで静的埋込みSQLを使用すると、カーソル変数を宣言できます。カーソル変数は、カーソルと同じように、複数行の問合せのアクティブ・セットの中のカレント行を指します。カーソルとカーソル変数との違いは、定数と変数との違いと同じです。カーソルは静的で、カーソル変数は特定の問合せに結び付けられていないため、動的です。カーソル変数は、型の互換性のある任意の問合せに対してオープンできます。

また、カーソル変数に新しい値を割り当て、サブプログラム(Oracleデータベースに格納されているサブプログラムなど)にパラメータとして渡せます。これにより、データ検索を簡単に集中化できます。

まず、カーソル変数を宣言します。カーソル変数を宣言した後、次の4つの文を使用してカーソル変数を制御します。

  • ALLOCATE

  • OPEN ... FOR

  • FETCH

  • CLOSE

カーソル変数を宣言して、メモリーを割り当てたら、そのカーソル変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。サーバー側でOPEN、FORを使用して複数行の問合せのためにカーソル変数をオープンし、クライアント側ではその変数からFETCHを行い、サーバー側かクライアント側のいずれかでCLOSEします。

4.5.1 カーソル変数の宣言について

カーソル変数の宣言方法は、使用ホスト言語により異なります。カーソル変数の宣言に関する指示は、使用ホスト言語の補足資料を参照してください。

4.5.2 カーソル変数の割当て

カーソル変数にメモリーを割り当てるには、ALLOCATE文を使用します。構文は次のとおりです。 

EXEC SQL ALLOCATE <cursor_variable>;

4.5.3 カーソル変数のオープン

OPEN ... FOR文を使用して、カーソル変数を複数行問合せに関連付け、問合せを実行し、アクティブ・セットを特定します。構文は次のとおりです。 

EXEC SQL OPEN <cursor_variable> FOR <select_statement>;

SELECT文では、入力ホスト変数およびPL/SQL変数、パラメータ、ファンクションを参照できますが、FOR UPDATEには使用できません。次の例では、emp_cvという名前のカーソル変数をオープンします。 

EXEC SQL OPEN emp_cv FOR SELECT * FROM EMP;

カーソル変数はサーバー側でオープンする必要があります。カーソル変数を入力ホスト変数として無名のPL/SQLブロックに渡すことでオープンします。実行時に、そのブロックはOracleサーバーに送られ、実行されます。次の例では、カーソル変数を宣言し、初期化してから、PL/SQLブロックに渡し、そこでカーソル変数がオープンされます。 

EXEC SQL BEGIN DECLARE SECTION;
 ...
-- declare cursor variable
 emp_cur SQL_CURSOR;
EXEC SQL END DECLARE SECTION;
 
-- initialize cursor variable
EXEC SQL ALLOCATE :emp_cur;
 
EXEC SQL EXECUTE
 -- pass cursor variable to PL/SQL block
 BEGIN
 -- open cursor variable
 OPEN :emp_cur FOR SELECT * FROM EMP;
 END;
END-EXEC;

一般に、カーソル変数を仮パラメータの1つとして宣言するストアド・プロシージャをコールすることで、カーソル変数をPL/SQLに渡します。たとえば、次のパッケージ・プロシージャは、emp_cvという名前のカーソル変数をオープンします。 

CREATE PACKAGE emp_data AS
 -- define REF CURSOR type
 TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
 -- declare formal paramter of that type
 PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
 
CREATE PACKAGE BODY emp_data AS
 PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
 BEGIN
 -- open cursor variable
 OPEN emp_cv FOR SELECT * FROM emp;
 END open_emp_cv;
END emp_data;

このパッケージ・プロシージャは、次のように、どのアプリケーションからでもコールできます。

EXEC SQL EXECUTE
 BEGIN 
 emp_data.open_emp_cv(:emp_cur); 
 END; 
END-EXEC;

4.5.4 カーソル変数からのフェッチ

複数行問合せのためにカーソル変数をオープンした後、FETCH文を使用して、アクティブ・セットから行を1行ずつ取得します。構文は次のとおりです。 

EXEC SQL FETCH cursor_variable_name 
 INTO {record_name | variable_name[, variable_name, ...]};

カーソル変数によって戻される各列値は、データ型に互換性がある場合には、INTO句で対応するフィールドまたは変数に割り当てられます。

FETCH文は、クライアント側で実行する必要があります。次の例では、行をフェッチして、emp_recという名前のホスト・レコードに入れます。 

-- exit loop when done fetching
EXEC SQL WHENEVER NOT FOUND DO no_more;
LOOP
 -- fetch row into record
 EXEC SQL FETCH :emp_cur INTO :emp_rec; 
 -- process the data
ENDLOOP;

4.5.5 カーソル変数のクローズ

カーソル変数をクローズするには、CLOSE文を使用し、この時点で、アクティブ・セットは未定義になります。構文は次のとおりです。 

EXEC SQL CLOSE cursor_variable_name;

CLOSE文は、クライアント側またはサーバー側で実行できます。次の例では、最後の行が処理されてから、カーソル変数emp_curをクローズします。 

-- close cursor variable
EXEC SQL CLOSE :emp_cur;