多数の行で構成される結果セットを返すSQLステートメントは、結果セット全体を転送するためにデータベースサーバーとクライアントの間で常にコンテキストを切り替える必要があるため、可能な限り効率的に動作していない可能性があります。 コレクションを使用して結果セット全体をメモリに収集し、クライアントがアクセスできるようにすることで、非効率性を軽減できます。 BULK COLLECT句は、結果セットのコレクションへの集計を指定するために使用されます。
BULK COLLECT句は、SELECT INTO、FETCH INTO、およびEXECUTEIMMEDIATEステートメント、およびDELETE、INSERT、およびUPDATEステートメントのRETURNING INTO句で使用できます。 これらのそれぞれは、次のトピックで説明されています。
セレクトバルクコレクション
BULK COLLECT句は、次のようにSELECT INTOステートメントとともに使用できます。
SELECT select_expressions BULK COLLECT INTOコレクション
[, ...] から... ; 単一のコレクションが指定されている場合、コレクションは単一のフィールドのコレクションである場合もあれば、レコードタイプのコレクションである場合もあります。 複数のコレクションを指定する場合、各コレクションは単一のフィールドで構成されている必要があります。 select_expressionは、ターゲットコレクションのすべてのフィールドの数、順序、および型互換性が一致する必要があります。
次の例は、ターゲットコレクションが単一のフィールドで構成される連想配列であるBULKCOLLECT句の使用を示しています。
DECLARE
タイプempno_tblはempのテーブルです。empno % TYPE INDEX BY BINARY_INTEGER;
TYPE ename_tbl IS TABLE OF emp.ename % TYPE INDEX BY BINARY_INTEGER;
TYPE job_tbl IS TABLE OF emp.job % TYPE INDEX BY BINARY_INTEGER;
TYPE hiredate_tbl IS TABLE OF emp.hiredate % TYPE INDEX BY BINARY_INTEGER;
TYPE sal_tblはBINARY_INTEGERによるemp.sal % TYPE INDEXのテーブルです。
TYPE comm_tblはテーブルO F emp.com m % TYPE INDEX BY BINARY_INTEGER;
TYPE deptno_tblはBINARY_INTEGERによるemp.de ptno % TYPE INDEXのテーブルです。
t_empno EMPNO_TBL;
t_ename ENAME_TBL;
t_job JOB_TBL;
t_hiredate HIREDATE_TBL;
t_sal SAL_TBL;
t_comm COMM_TBL;
t_deptno DEPTNO_TBL;
開始
SELECT empno, ename, job, hiredate, sal, comm, deptno BULK COLLECT
INTO t_empno、t_ename、t_job、t_hiredate、t_sal、t_comm、t_deptno
empから;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE '| |
'SAL '| | 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1 .. t_empno.COUNTループ
DBMS_OUTPUT.PUT_LINE(t_empno(i) | | '| |
RPAD(t_ename(i),8) | | '' | |
RPAD(t_job(i),10) | | '' | |
TO_CHAR(t_hiredate(i),'DD-MON-YY ') | |' | |
TO_CHAR(t_sal(i),'99,999.99 ') | |' | |
TO_CHAR(NVL(t_comm(i),0),'99,999.99 ') | |' | | '|
t_deptno(i);
エンドループ;
エンド;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- --------- -------------------------------------------------
7369スミスクレーク17-DEC-80 800.00 .00 20
7499アレンSALESMAN 20-FEB-81 1,600.00 300.00 30
7521ワードSALESMAN 22-FEB-81 1,250.00 500.00 30
7566ジョーンズマネージャー02-APR-81 2,975.00 .00 20
7654 MARTIN SALESMAN 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 01-5月-81 2,850.00 .00 30
7782クラークマネージャー09-JUN-81 2,450.00 .00 10
7788スコットアナリスト19-APR-87 3,000.00 .00 20
7839王大統領17-NOV-81 5,000.00。00 10
7844ターナーセールスマン08-SEP-81 1,500.00 .00 30
7876アダムスCLERK 23-5月-87 1,100.00。00 20
7900ジェームズCLERK 03-DEC-81 950.00 .00 30
7902フォードアナリスト03-DEC-81 3,000.00 .00 20
7934ミラーCLERK 23-1月-82 1,300.00 .00 10 次の例では、同じ結果を生成しますが、% ROWTYPE属性で定義されたレコード型で連想配列を使用します。
DECLARE
TYPE emp_tblはBINARY_INTEGERによるemp % ROWTYPEインデックスのテーブルです。
t_emp EMP_TBL;
開始
SELECT * BULK COLLECT INTO emp FROM emp;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE '| |
'SAL '| | 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1 .. t_emp.COUNTループ
DBMS_OUTPUT.PUT_LINE(t_emp(i).empno | | '|
RPAD(t_emp(i).ename,8) | | '' | | |
RPAD(t_emp(i).job,10) | | '' | |
TO_CHAR(t_emp(i).hiredate,'DD-MON-YY ') | |' | |
TO_CHAR(t_emp(i).sal,'99,999.99 ') | |' | |
TO_CHAR(NVL(t_emp(i).comm,0),'99,999.99 ') | |' | |
t_emp(i).deptno);
エンドループ;
エンド;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- --------- -------------------------------------------------
7369スミスクレーク17-DEC-80 800.00 .00 20
7499アレンSALESMAN 20-FEB-81 1,600.00 300.00 30
7521ワードSALESMAN 22-FEB-81 1,250.00 500.00 30
7566ジョーンズマネージャー02-APR-81 2,975.00 .00 20
7654 MARTIN SALESMAN 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 01-5月-81 2,850.00 .00 30
7782クラークマネージャー09-JUN-81 2,450.00 .00 10
7788スコットアナリスト19-APR-87 3,000.00 .00 20
7839王大統領17-NOV-81 5,000.00。00 10
7844ターナーセールスマン08-SEP-81 1,500.00 .00 30
7876アダムスCLERK 23-5月-87 1,100.00。00 20
7900ジェームズCLERK 03-DEC-81 950.00 .00 30
7902フォードアナリスト03-DEC-81 3,000.00 .00 20
7934ミラーCLERK 23-1月-82 1,300.00 .00 10 FETCHバルクコレクション
BULK COLLECT句は、FETCHステートメントで使用できます。 FETCH BULK COLLECTは、LIMIT句で制限されていない限り、結果セットから一度に1行ずつ返す代わりに、すべての行を結果セットから指定されたコレクションに返します。
FETCH名BULK COLLECT INTO collection [, ...] [LIMIT n ];単一のコレクションが指定されている場合、コレクションは単一のフィールドのコレクションである場合もあれば、レコードタイプのコレクションである場合もあります。 複数のコレクションを指定する場合、各コレクションは単一のフィールドで構成されている必要があります。 名前で識別されるカーソルのSELECTリストの式は、ターゲットコレクションのすべてのフィールドの数、順序、およびタイプ互換性が一致している必要があります。 LIMIT nを指定した場合、各FETCHのコレクションに返される行数はnを超えません。
次の例では、FETCH BULKCOLLECTステートメントを使用して、行を連想配列に取得します。
DECLARE
TYPE emp_tblはBINARY_INTEGERによるemp % ROWTYPEインデックスのテーブルです。
t_emp EMP_TBL;
CURSOR emp_cur IS SELECT * FROM emp;
開始
OPEN emp_cur;
FETCH emp_cur BULK COLLECT INTO t_emp;
CLOSE emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE '| |
'SAL '| | 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1 .. t_emp.COUNTループ
DBMS_OUTPUT.PUT_LINE(t_emp(i).empno | | '|
RPAD(t_emp(i).ename,8) | | '' | | |
RPAD(t_emp(i).job,10) | | '' | |
TO_CHAR(t_emp(i).hiredate,'DD-MON-YY ') | |' | |
TO_CHAR(t_emp(i).sal,'99,999.99 ') | |' | |
TO_CHAR(NVL(t_emp(i).comm,0),'99,999.99 ') | |' | |
t_emp(i).deptno);
エンドループ;
エンド;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- --------- -------------------------------------------------
7369スミスクレーク17-DEC-80 800.00 .00 20
7499アレンSALESMAN 20-FEB-81 1,600.00 300.00 30
7521ワードSALESMAN 22-FEB-81 1,250.00 500.00 30
7566ジョーンズマネージャー02-APR-81 2,975.00 .00 20
7654 MARTIN SALESMAN 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 01-5月-81 2,850.00 .00 30
7782クラークマネージャー09-JUN-81 2,450.00 .00 10
7788スコットアナリスト19-APR-87 3,000.00 .00 20
7839王大統領17-NOV-81 5,000.00。00 10
7844ターナーセールスマン08-SEP-81 1,500.00 .00 30
7876 ADAMS CLERK 23-5月-87 1,100.00。00 20
7900ジェームズCLERK 03-DEC-81 950.00 .00 30
7902フォードアナリスト03-DEC-81 3,000.00 .00 20
7934ミラーCLERK 23-1月-82 1,300.00 .00 10 即時バルクコレクションを実行
BULK COLLECT句は、EXECUTE IMMEDIATEステートメントとともに使用して、返された行を受け取るコレクションを指定できます。
EXECUTE IMMEDIATE 'sqL_expression;'
バルクコレクションに [,...]
[USING {[bind_type] bind_argument} [, ...]}] ; コレクションは、コレクションの名前を指定します。
bind_typeは、bind_argumentのパラメーターmodeを指定します。
- INのbind_typeは、bind_argumentにsql_expressionに渡される値が含まれることを指定します。
- OUTのbind_typeは、bind_argumentがsql_expressionから値を受け取ることを指定します。
- IN OUTのbind_typeは、bind_argumentがsql_expressionに渡されることを指定し、sql_expressionによって返された値を格納します。
bind_argumentは、sql_expressionに渡される値 (INのbind_typeで指定) 、またはsql_expressionから値を受け取る値 (OUTのbind_typeで指定) 、または両方の値 (INOUTのbind_typeで指定) を含むパラメーターを指定します。
単一のコレクションが指定されている場合、コレクションは単一のフィールドのコレクションまたはレコードタイプのコレクションになります。 複数のコレクションを指定する場合、各コレクションは単一のフィールドで構成されている必要があります。
返品バルクコレクション
BULK COLLECT句は、DELETE、INSERT、またはUPDATEステートメントのRETURNING INTO句に追加できます。
{ insert | 更新 | 削除}
戻り {* | expr_1 [, expr_2 ] ...}
バルクコレクションに [, ...] ; insert、update、およびdeleteは、それぞれINSERT、UPDATE、およびDELETEで説明されているINSERT、UPDATE、およびDELETEステートメントです。 単一のコレクションが指定されている場合、コレクションは単一のフィールドのコレクションである場合もあれば、レコードタイプのコレクションである場合もあります。 複数のコレクションを指定する場合、各コレクションは単一のフィールドで構成されている必要があります。 RETURNINGキーワードに続く式は、ターゲットコレクションのすべてのフィールドの数、順序、および型互換性が一致する必要があります。 * を指定した場合、影響を受けるテーブルのすべての列が返されます。 (* の使用はPolarDB for PostgreSQL (Compatible with Oracle) の拡張であり、Oracleデータベースと互換性がないことに注意してください。)
このトピックの残りの例では、empテーブルをコピーして作成されたclerkempテーブルを使用しています。
SELECTとしてTABLE clerkempを作成します * emp WHEREジョブ='CLERK 'から;
SELECT * clerkempから;
empno | ename | job | mgr | hiredate | sal | comm | deptno
------ -------- -------- --------- -------------------- -----------------------------------
7369 | スミス | クレーク | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7876 | ADAMS | CLERK | 7788 | 5月23日-87 00:00:00 | 1100.00 | | 20
7900 | ジェームズ | クレーク | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7934 | ミラー | クレーク | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
(4行) 次の例では、全員の給与を1.5増やし、従業員の番号、名前、および新しい給与を3つの連想配列に格納し、最後にこれらの配列の内容を表示します。
DECLARE
タイプempno_tblはempのテーブルです。empno % TYPE INDEX BY BINARY_INTEGER;
TYPE ename_tbl IS TABLE OF emp.ename % TYPE INDEX BY BINARY_INTEGER;
TYPE sal_tblはBINARY_INTEGERによるemp.sal % TYPE INDEXのテーブルです。
t_empno EMPNO_TBL;
t_ename ENAME_TBL;
t_sal SAL_TBL;
開始
UPDATE clerkemp SET sal = sal * 1.5 RETURNING empno、ename、sal
BULK COLLECT INTO t_empno、t_ename、t_sal;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME SAL ');
DBMS_OUTPUT.PUT_LINE('----------');
FOR i IN 1 .. t_empno.COUNTループ
DBMS_OUTPUT.PUT_LINE(t_empno(i) | | '' | | | RPAD(t_ename(i),8) | | |
'| | TO_CHAR(t_sal(i),'99,999.99');
エンドループ;
エンド;
EMPNO ENAME SAL
----- ------ ----------
7369 SMITH 1,200.00
7876 ADAMS 1,650.00
7900 JAMES 1,425.00
7934ミラー1,950.00 次の例では、前の例と同じ機能を実行しますが、レコードタイプで定義された単一のコレクションを使用して、従業員の番号、名前、および新しい給与を格納します。
DECLARE
TYPE emp_recは記録です (
empno emp.empno % TYPE,
ename emp.ename % TYPE、
sal emp.sal % タイプ
);
TYPE emp_tblはBINARY_INTEGERによるemp_recインデックスのテーブルです。
t_emp EMP_TBL;
開始
UPDATE clerkemp SET sal = sal * 1.5 RETURNING empno、ename、sal
バルクはtempに集まります。
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME SAL ');
DBMS_OUTPUT.PUT_LINE('----------');
FOR i IN 1 .. t_emp.COUNTループ
DBMS_OUTPUT.PUT_LINE(t_emp(i).empno | | '|
RPAD(t_emp(i).ename,8) | | '' | | |
TO_CHAR(t_emp(i).sal,'99,999.99 ');
エンドループ;
エンド;
EMPNO ENAME SAL
----- ------ ----------
7369 SMITH 1,200.00
7876 ADAMS 1,650.00
7900 JAMES 1,425.00
7934ミラー1,950.00 次の例では、clerkempテーブルからすべての行を削除し、削除された行に関する情報を連想配列に返し、それを表示します。
DECLARE
TYPE emp_recは記録です (
empno emp.empno % TYPE,
ename emp.ename % TYPE、
ジョブemp.job % TYPE、
hiredate emp.hiredate % TYPE、
sal emp.sal % タイプ、
com m emp.com m % タイプ、
deptno emp.de ptno % タイプ
);
TYPE emp_tblはBINARY_INTEGERによるemp_recインデックスのテーブルです。
r_emp EMP_TBL;
開始
leteからclerkemp returningempno、ename、仕事、hiredate、sal、
comm、deptnoバルクCOLLECT INTO r_emp;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE '| |
'SAL '| | 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1 .. r_emp.COUNTループ
DBMS_OUTPUT.PUT_LINE(r_emp(i).empno | | '| |
RPAD(r_emp(i).ename,8) | | '' | | |
RPAD(r_emp(i).job,10) | | '' | |
TO_CHAR(r_emp(i).hiredate,'DD-MON-YY ') | |' | |
TO_CHAR(r_emp(i).sal,'99,999.99 ') | |' | |
TO_CHAR(NVL(r_emp(i).comm,0),'99,999.99 ') | |' | |
r_emp(i).deptno);
エンドループ;
エンド;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- --------- -------------------------------------------------
7369スミスクレーク17-DEC-80 1,200.00 .00 20
7876アダムスCLERK 23-5月-87 1,650.00。00 20
7900ジェームズCLERK 03-DEC-81 1,425.00 .00 30
7934ミラーCLERK 23-1月-82 1,950.00 .00 10