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
SET SERVEROUTPUT ON;
CURSOR cur_RebellionRider IS
SELECT first_name, last_name FROM employees
WHERE employee_id >200;
FOR L_IDX IN cur_RebellionRider
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;
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.
- 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