配列への選択について

ホスト配列は、SELECT文の出力変数として使用できます。選択によって戻される最大行数がわかっている場合、その数の要素でホスト配列の次元を設定してください。次の例では、3つのホスト配列へのデータを直接選択します。この選択で戻される行が50行以下であることがわかっているため、配列の次元を50要素に設定します。

EXEC SQL BEGIN DECLARE SECTION;
 emp_name (50) CHARACTER(20);
 emp_number (50) INTEGER;
 salary (50) REAL;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT ENAME, EMPNO, SAL
 INTO :emp_name, :emp_number, :salary
 FROM EMP
 WHERE SAL > 1000;

この例では、SELECT文により最大50行が戻されます。選択される行数が49行以下の場合、または50行のみを取り出す場合はこの方法を使用します。ただし、選択される行数が51行以上の場合は、この方法ではすべての行を取り出せません。SELECT文を再実行すると、他に条件を満たす行があっても、最初の50行が再び戻されます。この場合は、より大きな配列を設定するか、FETCH文で使用するカーソルを宣言する必要があります。

SELECT INTO文で設定した次元以上の行数が戻された場合、次のエラー・メッセージが表示されます。

SQL-02112: SELECT...INTO returns too many rows

これはSELECT_ERROR=NOを指定していないためです。SELECT_ERRORオプションの詳細は、SELECT_ERRORを参照してください。

一括フェッチ

選択で戻される最大行数がわからない場合には、cursor_nameを宣言してオープンし、そこから一括でフェッチできます。ループ内でバッチ・フェッチを実行すると、多数の行を簡単に取り出せます。フェッチを行うたびに、現行のアクティブ・セットから次のバッチの行が戻されます。次の例では、20行ずつまとめてフェッチします。

EXEC SQL BEGIN DECLARE SECTION;
 emp_number (20) INTEGER;
 salary (20) REAL;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE emp_cursor CURSOR FOR
 SELECT EMPNO, SAL FROM EMP;
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND DO ...
LOOP
 EXEC SQL FETCH emp_cursor INTO :emp_number, :salary;
 -- process batch of rows
ENDLOOP;

フェッチされる行数

各フェッチで戻されるのは、最大でも配列の次元の行数までです。次のような場合、配列の次元より少ない行数が戻されます。

アクティブ・セットの最後に達したとき:「データが見つかりません。「データが見つかりません」というOracleの警告コードがSQLCA内のSQLCODEに戻されます。たとえば、100次元の配列に行をフェッチして、20行しか戻されなかった場合にこれが発生します。
残っているフェッチ対象の行が、一括フェッチの全行数より少ないとき。たとえば、20次元の配列に70行をフェッチすると、3回目のフェッチの後にはフェッチ対象の行が10しか残っていないため、この状態が発生します。
行の処理中にエラーが検出されたとき。フェッチは失敗し、該当するOracleエラー・コードがSQLCODEに戻されます。

戻された累積行数は、SQLCA内のSQLERRDの3番目の要素(このマニュアルではSQLERRD(3)と呼びます)で確認できます。これはオープン状態のすべてのカーソルに適用されます。次の例では、各カーソルの状態が個別に管理されている様子がわまります。

EXEC SQL OPEN cursor1;
EXEC SQL OPEN cursor2;
EXEC SQL FETCH cursor1 INTO :array_of_20;
 -- now running total in SQLERRD(3) is 20
EXEC SQL FETCH cursor2 INTO :array_of_30;
 -- now running total in SQLERRD(3) is 30, not 50
EXEC SQL FETCH cursor1 INTO :array_of_20;
 -- now running total in SQLERRD(3) is 40 (20 + 20)
EXEC SQL FETCH cursor2 INTO :array_of_30;
 -- now running total in SQLERRD(3) is 60 (30 + 30)

制限

SELECT文のWHERE句でホスト配列を使用できるのは、副問合においてのみです。(例はWHERE句の使用についてを参照してください。)また、SELECTまたはFETCH文のINTO句で、単純なホスト変数とホスト配列を混用することはできません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。表9-1では、SELECT INTO文でどのホスト配列の使用が有効かを示しています。

表9-1 SELECT INTOで有効なホスト配列

INTO句	WHERE句	有効?
配列	配列	いいえ
スカラー	スカラー	はい
配列	スカラー	はい
スカラー	配列	いいえ

NULLのフェッチについて

インジケータ配列のないホスト配列にNULLを選択またはフェッチすると、Oracleでは処理を停止し、SQLERRD(3)を処理済行数に設定して次のエラー・メッセージを発行します。

ORA-01405: fetched column value is NULL

NULLや切り捨てられた値の見つけ方は、インジケータ変数の使用についてを参照してください。

切り捨てられた値のフェッチについて

DBMS=V7の場合、インジケータ配列のないホスト配列に切り捨てられた列値を選択またはフェッチすると、Oracleでは処理を停止し、SQLERRD(3)を処理済行数に設定して次のエラー・メッセージを発行します。

ORA-01406: fetched column value was truncated

SQLERRD(3)を調べると、切捨てが行われるまでに処理された行数がわかります。処理済行数には切捨てエラーが発生した行も含まれます。

MODE=ANSIの場合、切捨てはエラーとはみなされません。したがって、Oracleでは処理を続行します。

ここでも、配列の選択およびフェッチを行うときには、必ずインジケータ配列を使用してください。そうすれば、Oracleで1つ以上の切り捨てられた列値が出力ホスト配列に割り当てられた場合に、関連付けられたインジケータ配列で列値の元の長さがわかります。

配列での挿入について

ホスト配列は、INSERT文で入力変数として使用できます。INSERT文を実行する前に、プログラムで配列にデータが含まれているかどうかを確認してください。配列内に不適切な要素がある場合、FOR句を使用して挿入される行数を制御できます。FOR句の使用についてを参照してください。

ホスト配列による挿入の例は次のとおりです。

EXEC SQL BEGIN DECLARE SECTION;
 emp_name (50) CHARACTER(20);
 emp_number (50) INTEGER;
 salary (50) REAL;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)
 VALUES (:emp_name, :emp_number, :salary);

挿入された累計行数は、SQLERRD(3)で確認できます。

機能的には次の文と同等ですが、前の例のINSERT文の方が、Oracleへのコールが1回のみであるためはるかに効率的です。

FOR i = 1 TO array_dimension
 EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)
 VALUES (:emp_name[i], :emp_number[i], :salary[i]);
ENDFOR;

この仮想の例(SQL文ではホスト変数に添字を付けられないため仮想)では、FORループを使用して、すべての配列要素に順次アクセスします。

制限

INSERT文のVALUES句内ではポインタ配列は使用できません。つまり配列要素はすべてデータ項目である必要があります。また、INSERT文のVALUES句で、単純なホスト変数とホスト配列を混用することはできません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。

配列での更新について

次の例に示すように、ホスト配列をUPDATE文の入力変数としても使用できます。

EXEC SQL BEGIN DECLARE SECTION;
 emp_number (50) INTEGER;
 salary (50) REAL;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
EXEC SQL UPDATE EMP SET SAL = :salary WHERE EMPNO = :emp_number;

更新された累計行数は、SQLERRD(3)で確認できます。この数には更新カスケードによって処理された行は含まれていません。

配列の要素に不適切なものがある場合、FOR句を使用して更新される行数を制限できます。

前の例では、一意のキー(emp_number)を使用した一般的な更新を示しています。各配列要素で更新できる行は1行のみです。次の例では、各配列要素で複数の行を操作できます。

EXEC SQL BEGIN DECLARE SECTION;
 job_title (10) CHARACTER(10);
 commission (50) REAL;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
EXEC SQL UPDATE EMP SET COMM = :commission WHERE JOB = :job_title;

制限事項: UPDATE文のSET句またはWHERE句では、単純ホスト変数とホスト配列を併用できません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。さらに、SET句でホスト配列を使用する場合は、WHERE句のホスト配列を使用する必要があります。ただし、それらの次元やデータ型を一致させる必要はありません。

UPDATE文のCURRENTOF句では、ホスト配列は使用できません。かわりの方法については、CURRENT OF句の擬似実行についてを参照してください。

表9-2では、UPDATE文でどのホスト配列の使用が有効かを示しています。

表9-2 UPDATEで有効なホスト配列

SET句	WHERE句	有効?
配列	配列	はい
スカラー	スカラー	はい
配列	スカラー	いいえ
スカラー	配列	いいえ

配列での削除について

DELETE文でもホスト配列を入力変数として使用できます。これは、WHERE句内のホスト配列の連続した要素を使用して、DELETE文を繰り返し実行するのと同様です。つまり1回の実行で表から0行、1行または複数行が削除されます。ホスト配列による削除の例は次のとおりです。

EXEC SQL BEGIN DECLARE SECTION;
 ...
 emp_number (50) INTEGER;
 EXEC SQL END DECLARE SECTION;
-- populate the host array
EXEC SQL DELETE FROM EMP WHERE EMPNO = :emp_number;

削除された行の累計数は、SQLERRD(3)を調べます。この累計数には、削除カスケードで処理された行は含まれません。

前の例では、一意のキー(emp_number)を使用した一般的な削除を示しています。各配列要素で削除できる行は1行のみです。次の例では、各配列要素で複数の行を操作できます。

EXEC SQL BEGIN DECLARE SECTION;
 ...
 job_title (10) CHARACTER(10);
EXEC SQL END DECLARE SECTION;
-- populate the host array
EXEC SQL DELETE FROM EMP WHERE JOB = :job_title;

制限

DELETE文のWHERE句で、単純なホスト変数とホスト配列を混用することはできません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。また、DELETE文のCURRENT OF句ではホスト配列は使用できません。かわりの方法については、CURRENT OF句の擬似実行についてを参照してください。