ホスト配列の使用方法

9.1 ホスト配列

配列とは、1つの変数名に関連付けられた要素と呼ばれる関連データ項目の集合です。ホスト変数として宣言されたとき、配列はホスト配列と呼ばれます。同様に、配列として宣言されたインジケータ変数はインジケータ配列と呼ばれます。インジケータ配列は、任意のホスト配列に関連付けることができます。

9.2 配列を使用する理由

配列を使用すると、プログラミングを簡単にでき、パフォーマンスも改善されます。アプリケーションの作成時には、通常、大量のデータ集合の保存や操作の問題に直面します。配列は、各集合の個々の項目の命名および参照のタスクを簡略化します。

配列を使用すると、アプリケーションのパフォーマンスが向上します。配列により、1つのSQL文でデータ項目の集合全体を操作できます。このため、特にネットワーク環境では、Oracleの通信オーバーヘッドが大幅に軽減されます。たとえば、およそ300人の従業員に関する情報をEMPという表に挿入する必要があるとします。配列がないと、プログラムは300の個々のINSERT(各従業員に1つ)を実行する必要があります。配列を使用すれば、INSERTの実行は1回で済みます。

9.3 ホスト変数の宣言

ホスト配列は、単純なホスト変数と同様に宣言部で宣言します。ホスト配列の次元の設定(サイズの設定)も宣言部で行います。次の例では、3つのホスト配列を宣言するとともに、それぞれの次元を50要素に設定しています。

EXEC SQL BEGIN DECLARE SECTION;
 emp_name (50) CHARACTER(20);
 emp_number (50) INTEGER;
 salary (50) REAL;
EXEC SQL END DECLARE SECTION;

9.3.1 配列の次元設定

ホスト配列の最大次元は32,767要素です。この最大次元を超えるホスト配列を使用すると、「パラメータの値が有効範囲外です」というランタイム・エラーが発生します。1つのSQL文で複数のホスト配列を使用する場合は、それらの次元を同じにする必要があります。同じでない場合には、プリコンパイル時に「配列サイズが一致しません」という警告メッセージが出ます。この警告を無視すると、プリコンパイラではSQLの操作で最小のサイズが使用されます。

9.3.2 制限事項

ポインタのホスト配列は宣言できません。また、1つのSQLで参照できるホスト配列は1次元(要素)に制限されます。したがって、次の例で宣言されている2次元配列は無効です。

EXEC SQL BEGIN DECLARE SECTION;
 hi_lo_scores (25, 25) INTEGER; -- not allowed
EXEC SQL END DECLARE SECTION;

9.4 SQL文での配列の使用方法について

Oracleプリコンパイラでは、データ操作文でホスト配列を使用できます。ホスト配列は、INSERT文、UPDATE文およびDELETE文では入力変数として、SELECT文およびFETCH文のINTO句では出力変数として使用できます。

MODE=ANSI14の場合、配列処理はできません。つまり、SQL文のホスト配列を参照できるのは、MODE={ANSI|ANSI13|ORACLE}の場合のみです。

ホスト配列と単純ホスト変数に使用される構文はほとんど同じです。ただし、オプションのFOR句で配列処理が制御できるという点に違いがあります。また、ホスト配列と単純ホスト変数を1つのSQL文で併用するときにも制限があります。

後続の項では、データ操作文でのホスト配列の使用方法を説明します。

9.5 配列への選択について

ホスト配列は、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を参照してください。

9.5.1 一括フェッチ

選択で戻される最大行数がわからない場合には、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;

9.5.2 フェッチされる行数

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

アクティブ・セットの最後に達したとき:「データが見つかりません。「データが見つかりません」という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)

9.5.3 制限事項

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

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

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

9.5.4 NULLのフェッチについて

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

ORA-01405: fetched column value is NULL

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

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

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

ORA-01406: fetched column value was truncated

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

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

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

9.5.6 配列での挿入について

ホスト配列は、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つでも配列があれば、すべてのホスト変数を配列にする必要があります。

9.5.7 配列での更新について

次の例に示すように、ホスト配列を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句	有効?
配列	配列	はい
スカラー	スカラー	はい
配列	スカラー	いいえ
スカラー	配列	いいえ

9.5.8 配列での削除について

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;

9.5.9 制限事項

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

9.6 インジケータ配列の使用方法について

入力ホスト配列へのNULLの割当て、および入力ホスト変数でのNULLまたは切り捨てられた値の検出には、インジケータ配列を使用します。次の例は、インジケータ配列による挿入の方法を示しています。

EXEC SQL BEGIN DECLARE SECTION;
 emp_number (50) INTEGER;
 dept_number (50) INTEGER;
 commission (50) REAL;
 ind_comm (50) SMALLINT; -- indicator array
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
-- populate the indicator array; to insert a null into
-- the COMM column, assign -1 to the appropriate element in
-- the indicator array
EXEC SQL INSERT INTO EMP (EMPNO, DEPTNO, COMM)
 VALUES (:emp_number, :dept_number, :commission:ind_comm);

インジケータ配列の次元は、ホスト配列の次元より大きくする必要があります。

9.7 FOR句の使用方法について

オプションのFOR句を使用すれば、次のSQL文で処理される配列要素の数を設定できます。

DELETE
EXECUTE
FETCH
INSERT
OPEN
UPDATE

FOR句は特にUPDATE文、INSERT文およびDELETE文で役に立ちます。これらの文では、配列全体を使用する必要のない場合があります。FOR句を使用すると、次の例のように、使用する要素を必要な数に制限できます。

EXEC SQL BEGIN DECLARE SECTION;
 emp_name (100) CHARACTER(20);
 salary (100) REAL;
 rows_to_insert INTEGER;
EXEC SQL END DECLARE SECTION;
-- populate the host arrays
set rows_to_insert = 25; -- set FOR-clause variable
EXEC SQL FOR :rows_to_insert -- will process only 25 rows
 INSERT INTO EMP (ENAME, SAL)
 VALUES (:emp_name, :salary);

FOR句では、配列要素数をカウントするために整数型のホスト変数を使用する必要があります。たとえば、次の文は無効です。

EXEC SQL FOR 25 -- illegal
 INSERT INTO EMP (ENAME, EMPNO, SAL)
 VALUES (:emp_name, :emp_number, :salary);

FOR句の変数は、処理する配列要素数を指定します。この数は、最小の配列次元を超えないように設定します。また、この数は正数であることが必要です。負数またはゼロを指定すると、行は処理されません。

9.7.1 制限事項

2つの制限事項により、FOR句の意味が明確になります。FOR句はSELECT文では使用できません。また、CURRENT OF句とも使用することはできません。

9.7.2 SELECT文での使用

SELECT文でFOR句を使用すると、次のエラー・メッセージが表示されます。

PCC-E-0056: FOR clause not allowed on SELECT statement at ...

意味が不明確なため、SELECT文ではFOR句を使用できません。それは「このSELECT文をn回実行する」という意味でしょうか?それとも、「このSELECT文を実行するのは1度だが、n行戻す」という意味でしょうか。問題は、前者の場合、実行のたびに複数の行が戻される可能性があることです。後者の場合、カーソルを宣言し、次のように、FETCH文でFOR句を使用した方がよいでしょう。

EXEC SQL FOR :limit FETCH emp_cursor INTO ...

9.7.3 CURRENT OF句との併用

次の例のように、UPDATE文またはDELETE文でCURRENT OF句を使用すると、FETCH文によって戻される最後の行を参照できます。

EXEC SQL DECLARE emp_cursor CURSOR FOR
 SELECT ENAME, SAL FROM EMP WHERE EMPNO = :emp_number;
...
EXEC SQL OPEN emp_cursor;
...
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
EXEC SQL UPDATE EMP SET SAL = :new_salary
 WHERE CURRENT OF emp_cursor;

ただし、FOR句とCURRENT OF句は併用できません。次の文は、limitの論理値が1に限定されているため無効です(つまり、現在の行を更新または削除できるのは1回のみです)。

EXEC SQL FOR :limit UPDATE EMP SET SAL = :new_salary
 WHERE CURRENT OF emp_cursor;
...
EXEC SQL FOR :limit DELETE FROM EMP
 WHERE CURRENT OF emp_cursor;

9.8 WHERE句の使用方法について

Oracleでは、要素数nのホスト配列を含むSQL文を、同じSQL文をn個の異なるスカラー変数(個々の配列要素)でn回実行するのと同様に扱います。このように扱いがあいまいなときにかぎり、プリコンパイラから次のエラー・メッセージが発行されます。

PCC-S-0055: Array <name> not allowed as bind variable at ...

たとえば、次のような宣言をしたとします。

EXEC SQL BEGIN DECLARE SECTION;
 mgr_number (50) INTEGER;
 job_title (50) CHARACTER(20);
EXEC SQL END DECLARE SECTION;

次の文の場合、あいまいになります。

EXEC SQL SELECT MGR INTO :mgr_number FROM EMP
 WHERE JOB = :job_title;

次の仮想の文のように処理されるためです。

FOR i = 1 TO 50
 SELECT MGR INTO :mgr_number[i] FROM EMP
 WHERE JOB = :job_title[i];
ENDFOR;

これは、WHERE句の検索条件を満たす行が複数あっても、データの受取りに使用できる出力変数は1つしかないためです。したがって、エラー・メッセージが出力されます。

しかし、次の文の場合、あいまいになりません。

EXEC SQL UPDATE EMP SET MGR = :mgr_number
 WHERE EMPNO IN (SELECT EMPNO FROM EMP WHERE JOB = :job_title);

次の仮想の文のように処理されるためです。

FOR i = 1 TO 50
 UPDATE EMP SET MGR = :mgr_number[i]
 WHERE EMPNO IN
 (SELECT EMPNO FROM EMP WHERE JOB = :job_title[i]);
ENDFOR;

これは、各job_titleが複数の行に一致する場合でも、WHERE句のjob_titleに一致する行ごとにSET句内でmgr_numberが指定されているためです。各job_titleに一致するすべての行に、同じmgr_numberをSETできます。したがって、エラー・メッセージは表示されません。

9.9 CURRENT OF句の疑似実行について

DELETE文またはUPDATE文でCURRENT OF cursor句を使用すると、カーソルから最後にフェッチされた行を参照できます。ただし、CURRENT OF句とホスト配列の併用はできません。かわりに各行のROWIDを取得し、更新または削除するときにその値を使用して現在行を識別してください。次に例を示します。

EXEC SQL BEGIN DECLARE SECTION;
 emp_name (25) CHARACTER(20);
 job_title (25) CHARACTER(15);
 old_title (25) CHARACTER(15);
 row_id (25) CHARACTER(18);
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
 SELECT ENAME, JOB, ROWID FROM EMP;
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
...
LOOP
 EXEC SQL FETCH emp_cursor
 INTO :emp_name, :job_title, :row_id;
 ...
 EXEC SQL DELETE FROM EMP
 WHERE JOB = :old_title AND ROWID = :row_id;
 EXEC SQL COMMIT WORK;
ENDLOOP;

ただし、フェッチされた行は、FOR UPDATE OF句が使用されていないため、ロックされません。したがって、読み取った行を削除する前に、別のユーザーがその行を変更すると、結果が一貫性のないものになる可能性があります。

9.10 SQLERRD(3)の使用方法について

INSERT文、UPDATE文、DELETE文およびSELECT INTO文では、SQLERRD(3)は処理された行数を記録します。FETCH文では、処理された行の累積合計を記録します。

ホスト配列をFETCH文で使用するとき、最後の反復で戻された行数は、SQLERRD(3)の前の値(別の変数に格納)から現在の値を引けばわかります。次の例では、最後のフェッチで戻された行数を確認します。

EXEC SQL BEGIN DECLARE SECTION;
 emp_number (100) INTEGER;
 emp_name (100) CHARACTER(20);
EXEC SQL END DECLARE SECTION;
...
 rows_to fetch INTEGER;
 rows_before INTEGER;
 rows_this_time INTEGER;
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
 SELECT EMPNO, ENAME
 FROM EMP
 WHERE DEPTNO = 30;
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
...
-- initialize loop variables
set rows_to_fetch = 20; -- number of rows in each "batch"
set rows_before = 0; -- previous value of sqlerrd(3)
set rows_this_time = 20;
WHILE rows_this_time = rows_to_fetch
 LOOP
 EXEC SQL FOR :rows_to_fetch
 FETCH emp_cursor
 INTO :emp_number, :emp_name;
 set rows_this_time = sqlca.sqlerrd(3) - rows_before;
 set rows_before = sqlca.sqlerrd(3);
 ENDLOOP;
ENDWHILE;

SQLERRD(3)は、配列の操作中にエラーが発生した場合にも便利です。処理はエラーを引き起こした行で停止するため、SQLERRD(3)を調べることによって正常に処理された行数がわかります。