Cursor For Loop With Simple Explicit Cursor In Oracle Database

By Manish Sharma

Cursor For Loop In Oracle Database

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.


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.

Syntax of Cursor For Loop.

 FOR loop_index IN cursor_name

Example 1: Cursor For Loop With Simple Explicit Cursor

  CURSOR cur_RebellionRider IS
  SELECT first_name, last_name FROM employees
  WHERE employee_id >200;
  FOR L_IDX IN cur_RebellionRider
   DBMS_OUTPUT.PUT_LINE(L_IDX.first_name||' '||L_IDX.last_name);
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:

  FOR L_IDX IN (SELECT first_name, last_name FROM employees
  WHERE employee_id >200)
   DBMS_OUTPUT.PUT_LINE (L_IDX.first_name||' '||L_IDX.last_name);

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:

  • Directly write the SELECT statement without specifying the cursor name into the loop statement.
  • Enclose the SELECT statement into parenthesis.
  • 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 or click here to instantly share it on your 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!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn

         View Manish Sharma's profile on LinkedIn