As the name suggests Cursor For Loop is a type of For loop provided by oracle PL/SQL which makes working with cursors in oracle database a lot easier by executing OPEN, FETCH & CLOSE Cursor statements implicitly in the background for you.

Definition:
Cursor for Loop Is an Extension of the Numeric For Loop provided by Oracle PL/SQL which works on specified cursors and performs OPEN, FETCH & CLOSE cursor statements implicitly in the background.

Suggested Reading: Numeric For Loop In Oracle PL/SQL

Syntax of Cursor For Loop.

FOR loop_index IN cursor_name 
	LOOP
		Statements…
	END LOOP;

Example 1: Cursor For Loop With Simple Explicit Cursor

SET SERVEROUTPUT ON;
DECLARE
 CURSOR cur_RebellionRider IS 
 SELECT first_name, last_name FROM employees
 WHERE employee_id >200;
BEGIN
  FOR L_IDX IN cur_RebellionRider
  LOOP
    DBMS_OUTPUT.PUT_LINE(L_IDX.first_name||' '||L_IDX.last_name);
  END LOOP;
END;
/

Please watch the Video Tutorial on YouTube channel for detailed explanation of the above code.

Example 2: Cursor For Loop With Inline Cursor.

You can pass the cursor definition directly into the Cursor For Loop. The code for that is:

SET SERVEROUTPUT ON;
BEGIN
  FOR  L_IDX  IN (SELECT first_name, last_name FROM employees
  WHERE employee_id >200)
  LOOP
     DBMS_OUTPUT.PUT_LINE (L_IDX.first_name||' '||L_IDX.last_name);
  END LOOP;
END;
/

As you can see in the above code, instead of declaring a cursor into a separate declaration section of PL/SQL block we can write the Cursor’s SELECT DML statement right inside the loop statement after IN keyword.

Just remember:

1. Directly write the SELECT statement without specifying the cursor name into the loop statement.
2. Enclose the SELECT statement into parenthesis.
3. Do not terminate the SELECT statement with a semicolon (;)

How many times will Cursor For Loop execute?

Unlike Numeric For Loop with Cursor For Loop we don’t have minimum or maximum range which will decide the number of execution. So how many times will this loop execute?

This loop will execute for each row returned by the specified cursor and it will terminate either when there is no row to return or there is an occurrence of an exception.

That is all on Cursor For Loop with simple explicit cursor in Oracle Database. Hope you learnt something and enjoyed reading. Please do make sure to share this blog on you Facebook & Twitter. From now on you can win a goody bag on sharing the blogs, just make sure to tag me. You can also tell me how you enjoyed reading this blog on my twitter. Thanks & have a great day!