Home » PLSQL » PL/SQL Cursor FOR LOOP Examples

PL/SQL Cursor FOR LOOP Examples

In PL/SQL, FOR loop allows for easy iteration over a cursor object's entire set of records. A CURSOR FOR loop is a FOR loop that initiates the opening of a cursor.

The CURSOR FOR loop eliminates the need for the programmer to explicitly define the cursor's OPEN, FETCH, and CLOSE operations, thereby saving time.

Cursor FOR LOOP Example

When dealing with explicit cursors for multiple rows, the CURSOR FOR loop will result in the most compact code. The following snippet of PL/SQL code exemplifies this:

DECLARE
/*Declare an explicit cursor to select employee information*/
   CURSOR CUR_EMP IS
     SELECT ename, sal
     FROM emp;
BEGIN
/*FOR Loop uses the cursor CUR_EMP directly*/
   FOR EMP IN CUR_EMP
   LOOP
   /*Display message*/
     DBMS_OUTPUT.PUT_LINE(EMP.ename||' earns '||EMP.sal||' per month');
   END LOOP;
END;

Output:

KING earns 5000 per month
BLAKE earns 2850 per month
CLARK earns 2450 per month
JONES earns 2975 per month
SCOTT earns 3000 per month

Keep in mind that the block variables needed to capture the cursor columns are not declared when using the CURSOR FOR loop.

The index of a CURSOR FOR loop also serves as a record of that data structure's cursors. The cursor can be opened and closed implicitly within the PL/SQL code.

Cursor FOR LOOP with Parameter Example

The following is the PL/SQL Cursor FOR LOOP with parameter example:

DECLARE
  /*Declare an explicit cursor to select employee information for department number 20*/
  CURSOR CUR_EMP(p_deptno IN NUMBER) IS
SELECT
  ename,
  sal
FROM
  emp
WHERE
  deptno = p_deptno;
BEGIN
  /*FOR Loop uses the cursor CUR_EMP directly*/
  -- pass the deptno as parameter value
  FOR EMP IN CUR_EMP(p_deptno = > 20)
LOOP
  /*Display message*/
  DBMS_OUTPUT.PUT_LINE(EMP.ename || ' earns ' || EMP.sal || ' per month');
END
LOOP
;
END;

Output:

JONES earns 2975 per month
SCOTT earns 3000 per month
FORD earns 3000 per month
SMITH earns 800 per month
ADAMS earns 1100 per month

Read also: