RETURNING Clause Using Bulk Collect
1. Overview
The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form.
2. Technologies and Tools Used
The following technologies have been used to achieve this
- Oracle PL/SQL
3. Use Case
You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement.
If your non-query DML statement changes (or might change) more than one row, you will want to add BULK COLLECT to your RETURNING INTO clause and populate an array with information from each changed row.
4. Examples
DECLARE
l_part_numbers DBMS_SQL.number_table;
BEGIN
UPDATE parts SET part_name = part_name || ‘1’ RETURNING part_number BULK COLLECT INTO l_part_numbers;
FOR indx IN 1 .. l_part_numbers.COUNT
LOOP DBMS_OUTPUT.put_line (l_part_numbers (indx));
END LOOP;
END;
Note: the l_part_numbers array consumes PGA or per-session memory. If your update statement happens to change, say, 10 million rows, then this block is likely to exceed PGA memory limits and fail. Whenever working with collections – and especially whenever you populate a collection with BULK COLLECT – keep in mind the possible PGA memory impact.
Populate record in RETURNING with a list of columns
DECLARE
l_part parts%ROWTYPE;
BEGIN
UPDATE parts SET part_number = -1 * part_number, part_name = UPPER (part_name) WHERE part_number = 1 RETURNING part_number, part_name INTO l_part;
DBMS_OUTPUT.put_line (l_part.part_name);
END;
