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

bulk-collect-clause

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 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, or greater than or equal to the integer-constant that is specified in the LIMIT clause.

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;