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
