| Oracle® Database Oracleプリコンパイラのためのプログラマーズ・ガイド 11g リリース2(11.2) B61344-01 |
|
 前へ |
 次へ |
この章の項目は次のとおりです。
この章では、コーディングの簡略化とプログラムのパフォーマンス改善のために配列を使用する方法について説明します。配列を使用したOracleデータの処理方法、1つのSQL文で配列のすべての要素を処理する方法、処理対象の配列の要素の数を制限する方法について学習します。次の項目について説明します。
ホスト配列
配列を使用する理由
ホスト配列宣言方法
SQL文での配列の使用方法
配列とは、1つの変数名に関連付けられた要素と呼ばれる関連データ項目の集合です。ホスト変数として宣言されたとき、配列はホスト配列と呼ばれます。同様に、配列として宣言されたインジケータ変数はインジケータ配列と呼ばれます。インジケータ配列は、任意のホスト配列に関連付けることができます。
配列を使用すると、プログラミングを簡単にでき、パフォーマンスも改善されます。アプリケーションの作成時には、通常、大量のデータ集合の保存や操作の問題に直面します。配列は、各集合の個々の項目の命名および参照のタスクを簡略化します。
配列を使用すると、アプリケーションのパフォーマンスが向上します。配列により、1つのSQL文でデータ項目の集合全体を操作できます。このため、特にネットワーク環境では、Oracleの通信オーバーヘッドが大幅に軽減されます。たとえば、約300人の従業員に関する情報をEMP表に挿入する必要があるとします。配列がないと、プログラムは300の個々のINSERT(各従業員に1つ)を実行する必要があります。配列を使用すれば、INSERTの実行は1回で済みます。
ホスト配列は、単純なホスト変数と同様に宣言部で宣言します。ホスト配列の次元の設定(サイズの設定)も宣言部で行います。次の例では、3つのホスト配列を宣言するとともに、それぞれの次元を50要素に設定しています。
EXEC SQL BEGIN DECLARE SECTION; emp_name (50) CHARACTER(20); emp_number (50) INTEGER; salary (50) REAL; EXEC SQL END DECLARE SECTION;
Oracleプリコンパイラでは、データ操作文でホスト配列を使用できます。ホスト配列は、INSERT文、UPDATE文およびDELETE文では入力変数として、SELECT文およびFETCH文のINTO句では出力変数として使用できます。
MODE=ANSI14の場合、配列処理はできません。つまり、SQL文のホスト配列を参照できるのは、MODE={ANSI|ANSI13|ORACLE}の場合のみです。
ホスト配列と単純ホスト変数に使用される構文はほとんど同じです。ただし、オプションのFOR句で配列処理が制御できるという点に違いがあります。また、ホスト配列と単純ホスト変数を1つのSQL文で併用するときにも制限があります。
後続の項では、データ操作文でのホスト配列の使用方法を説明します。
ホスト配列は、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行が戻されます。選択条件を満たす行が50行に満たない場合、あるいは取得する行が50行のみの場合は、この方法で十分です。ただし、条件を満たす行が50行を超える場合、この方法ではすべての行を取得できません。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文でどのホスト配列の使用が有効かを示しています。
DBMS=V6の場合、インジケータ配列がないホスト配列にNULLを選択またはフェッチしても、エラーは発生しません。そのため、配列に対する選択およびフェッチを行うときは、必ずインジケータ配列を使用してください。そうすれば、関係付けられた出力ホスト配列でNULLを見つけることができます。(NULLや切り捨てられた値の見つけ方は、「インジケータ変数の使用方法」を参照してください。)
DBMS=V7の場合、インジケータ配列のないホスト配列にNULLを選択またはフェッチすると、Oracleでは処理を停止し、SQLERRD(3)を処理済行数に設定して次のエラー・メッセージを発行します。
ORA-01405: fetched column value is 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文でどのホスト配列の使用が有効かを示しています。
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句の擬似実行」を参照してください。
入力ホスト配列への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);
インジケータ配列の次元は、ホスト配列の次元より大きくする必要があります。
オプションの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句の変数は、処理する配列要素数を指定します。この数は、最小の配列次元を超えないように設定します。また、この数は正数であることが必要です。負数またはゼロを指定すると、行は処理されません。
SELECT文でFOR句を使用すると、次のエラー・メッセージが表示されます。
PCC-E-0056: FOR clause not allowed on SELECT statement at ...
FOR句は意味が不明確であるため、SELECT文では使用できません。「このSELECT文をn回実行する」なのか、「このSELECT文を1回実行し、n行戻す」なのかはっきりしません。前者の問題は、実行するたびに複数行が戻されることです。後者の場合は、次の例のように、カーソルを宣言して、FETCH文でFOR句を使用することをお薦めします。
EXEC SQL FOR :limit FETCH emp_cursor INTO ...
次の例のように、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;
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できます。したがって、エラー・メッセージは表示されません。
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句が使用されていないため、ロックされません。したがって、読み取った行を削除する前に、別のユーザーがその行を変更すると、結果が一貫性のないものになる可能性があります。
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)には正常に処理された行数が記録されます。
