BULK COLLECT
BULK COLLECT with a query is a very efficient way to
retrieve the result set. Instead of looping through each row, you store the
results in one or more collections, in a single operation. You can use these
keywords in the SELECT INTO and FETCH INTO statements, and the RETURNING INTO clause.TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
IF names IS NULL OR names.COUNT = 0 THEN -- check if collections are empty
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Results: ');
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Processing all results at once ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE('--- Processing ' || v_limit || ' rows at a time ---');
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('--- Fetching records rather than columns ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
-- Now all the columns from the result set come from a single record
DBMS_OUTPUT.PUT_LINE(' Employee ' || recs(i).last_name || ': $'
|| recs(i).salary);
END LOOP;
END;
/
Example 11-13 shows how you can fetch from a cursor into a collection of records.
Example 11-13 Bulk-Fetching from a Cursor Into a Collection of Records
TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
FROM departments WHERE department_id > 70;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
The optional LIMIT clause, allowed only in bulk FETCH statements, limits the number of rows
fetched from the database. In Example
11-14, with each iteration of the loop, the FETCH statement fetches ten rows (or less) into
index-by table empids. The previous
values are overwritten. Note the use of empids.COUNT to determine when to exit the loop.
Example 11-14 Using LIMIT to Control the Number of Rows In a BULK COLLECT
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80;
empids numtab;
rows PLS_INTEGER := 10;
BEGIN
OPEN c1;
LOOP -- the following statement fetches 10 rows or less in each iteration
FETCH c1 BULK COLLECT INTO empids LIMIT rows;
EXIT WHEN empids.COUNT = 0;
-- EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data
DBMS_OUTPUT.PUT_LINE('------- Results from Each Bulk Fetch --------');
FOR i IN 1..empids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i));
END LOOP;
END LOOP;
CLOSE c1;
END;
/
Retrieving DML Results into a Collection with the RETURNING INTO Clause
You can use the BULK COLLECT clause in the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement:
Example 11-15 Using BULK COLLECT With the RETURNING INTO Clause
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
Using FORALL and BULK COLLECT Together
You
can combine the BULK COLLECT clause with a FORALL statement. The output collections are built
up as the FORALL statement iterates.
In Example
11-16, the employee_id value of each deleted row is stored in the
collection e_ids. The
collection depts has 3 elements, so the FORALL statement iterates 3 times. If each DELETE issued by the FORALL statement deletes 5 rows, then the
collection e_ids, which stores
values from the deleted rows, has 15 elements when the statement completes:
Example 11-16 Using FORALL With BULK COLLECT
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,30);
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
TYPE dept_t IS TABLE OF employees.department_id%TYPE;
e_ids enum_t;
d_ids dept_t;
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp WHERE department_id = depts(j)
RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN e_ids.FIRST .. e_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
END LOOP;
END;
/
The column values returned by each execution are added to the values returned
previously. If you use a FOR loop instead of the FORALL statement, the set of returned values is
overwritten by each DELETE statement.
You cannot use the SELECT ... BULK COLLECT statement in a FORALL statement.
Using Host Arrays with Bulk Binds
Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. This is the most efficient way to pass collections to and from the database server.
Host arrays are declared in a host environment such as an OCI or a Pro*C
program and must be prefixed with a colon to distinguish them from PL/SQL
collections. In the following example, an input host array is used in a DELETE statement. At run time, the anonymous PL/SQL
block is sent to the database server for execution.
...
BEGIN
-- assume that values were assigned to the host array
-- and host variables in the host environment
FORALL i IN :lower..:upper
DELETE FROM employees WHERE department_id = :depts(i);
COMMIT;
END;
/
