カーソル

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

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

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

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

• DECLARE

• OPEN

• FETCH

• CLOSE

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

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

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

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

カーソルの宣言

次の例のように、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を参照してください。

カーソルのオープン

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

EXEC SQL OPEN emp_cursor;

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

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

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

OPENによって行われる作業量は、HOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSの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文がこのエラーを検出します。)カーソルを再利用するには、再オープンする必要があります。

カーソルのクローズ

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

EXEC SQL CLOSE emp_cursor;

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

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

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;

制限

明示的な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句を使用できません。

一般的な文の順序

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

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

完全な例

次のプログラムは、カーソルと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;