A SELECT INTO statement with the optional BULK COLLECT
keywords preceding the INTO keyword retrieves multiple rows into an
array.
Syntax
.-,--------------.
V |
>>-BULK COLLECT INTO----array-variable-+--+-------------------+-><
'-LIMIT--expression-'
Description
- BULK COLLECT INTO array-variable
- Identifies one or more variables with an array data type. Each
row of the result is assigned to an element in each array in the order
of the result set, with the array index assigned in sequence.
- If exactly one array-variable is specified:
- If the data type of the array-variable element
is not a record type, the SELECT list must have exactly one column,
and the column data type must be assignable to the array element data
type.
- If the data type of the array-variable element
is a record type, the SELECT list must be assignable to the record
type.
- If multiple array variables are specified:
- The data type of the array-variable element
must not be a record type.
- There must be an array-variable for each column
in the SELECT list.
- The data type of each column in the SELECT list must be assignable
to the array element data type of the corresponding array-variable.
If the data type of array-variable is an ordinary
array, the maximum cardinality must be greater than or equal to the
number of rows that are returned by the query.
- LIMIT expression
- Provides an upper bound for how many rows are being fetched. The
expression can be a numeric literal, a variable, or a complex expression,
but it cannot depend on any column from the select statement.
Notes
- Variations of the BULK COLLECT INTO clause are also supported
with the FETCH statement and the EXECUTE IMMEDIATE statement.
Example
The following example shows a procedure
that uses the BULK COLLECT INTO clause to return an array of rows
from the procedure. The procedure and the type for the array are defined
in a package.
CREATE OR REPLACE PACAKGE bci_sample
IS
TYPE emps_array IS VARRAY (30) OF VARCHAR2(6);
PROCEDURE get_dept_empno (
dno IN emp.deptno%TYPE,
emps_dno OUT emps_array
);
END bci_sample;
CREATE OR REPLACE PACKAGE BODY bci_sample
IS
PROCEDURE get_dept_empno (
dno IN emp.deptno%TYPE,
emps_dno OUT emps_array
)
IS
BEGIN
SELECT empno BULK COLLECT INTO emps_dno
FROM emp
WHERE deptno=dno;
END get_dept_empno;
END bci_sample;