Fetching rows from a cursor (PL/SQL)
The FETCH statement that is required to fetch rows from a PL/SQL cursor is supported by the data server in PL/SQL contexts.
Syntax
Description
- cursor-name
- Name of a static cursor or cursor variable.
- record
- Identifier for a previously-defined record. This can be a user-defined record or a record definition that is derived from a table using the %ROWTYPE attribute.
- variable
- A PL/SQL variable that will hold the field data from the fetched
row. One or more variables can be defined, but they must match in
order and number the fields that are returned in the select list of
the query that was specified in the cursor declaration. The data types
of the fields in the select list must match or be implicitly convertible
to the data types of the fields in the record or the data types of
the variables.
The variable data types can be defined explicitly or by using the %TYPE attribute.
- 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 result row of the cursor 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 result row of the cursor 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 result row of the cursor.
- The data type of each column in the result row of the cursor must be assignable to the array element data type of the corresponding array-variable.
- If exactly one array-variable is specified:
- LIMIT integer-constant
- Identifies a limit for the number of rows stored in the target array. The cursor position is moved forward integer-constant rows or to the end of the result set.
Example
The following example shows a procedure
that contains a FETCH statement.
CREATE OR REPLACE PROCEDURE cursor_example
IS
v_empno NUMBER(4);
v_ename VARCHAR2(10);
CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10
ORDER BY empno;
BEGIN
OPEN emp_cur_3;
FETCH emp_cur_3 INTO v_empno, v_ename;
...
END;
If the %TYPE attribute is used to define the data
type of a target variable, the target variable declaration in a PL/SQL
application program does not need to change if the data type of the
database column changes. The following example shows a procedure with
variables that are defined using the %TYPE attribute.
CREATE OR REPLACE PROCEDURE cursor_example
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10
ORDER BY empno;
BEGIN
OPEN emp_cur_3;
FETCH emp_cur_3 INTO v_empno, v_ename;
...
END;
If all of the columns in a table are retrieved
in the order in which they are defined, the %ROWTYPE attribute can
be used to define a record into which the FETCH statement will place
the retrieved data. Each field within the record can then be accessed
using dot notation. The following example shows a procedure with a
record definition that uses %ROWTYPE. This record is used as the target
of the FETCH statement.
CREATE OR REPLACE PROCEDURE cursor_example
IS
v_emp_rec emp%ROWTYPE;
CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
OPEN emp_cur_1;
FETCH emp_cur_1 INTO v_emp_rec;
DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_emp_rec.ename);
END;