DB2 Version 10.1 for Linux, UNIX, and Windows

FOR (cursor variant) statement (PL/SQL)

The cursor FOR loop statement opens a previously declared cursor, fetches all rows in the cursor result set, and then closes the cursor.

Use this statement instead of separate SQL statements to open a cursor, define a loop construct to retrieve each row of the result set, test for the end of the result set, and then finally close the cursor.

Invocation

This statement can be invoked within a PL/SQL procedure, function, trigger, or anonymous block.

Authorization

No specific authorization is required to reference a row expression within an SQL statement; however, for successful statement execution, all other authorization requirements for processing a cursor are required.

Syntax


>>-FOR--record--IN--cursor--LOOP--statements--END LOOP---------><

Description

FOR: Introduces the condition that must be true if the FOR loop is to proceed.
record: Specifies an identifier that was assigned to an implicitly declared record with definition cursor%ROWTYPE.
IN cursor: Specifies the name of a previously declared cursor.
LOOP and END LOOP: Starts and ends the loop containing SQL statements that are to be executed during each iteration through the loop.
statements: One or more PL/SQL statements. A minimum of one statement is required.

Example

The following example shows a procedure that contains a cursor FOR loop:

CREATE OR REPLACE PROCEDURE cursor_example
IS
  CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
  DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
  DBMS_OUTPUT.PUT_LINE('-----    -------');
  FOR v_emp_rec IN emp_cur_1 LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
  END LOOP;
END;