EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL Cursor Loop
 

PL/SQL Cursor Loop

Updated April 6, 2023

PL/SQL Cursor Loop

 

 

Introduction to PL/SQL Cursor Loop

PL/SQL cursor loop statement is used for fetching and processing each and every record one by one which is referred to by a cursor. The FOR LOOP is used by the cursor for carrying out the repetitive task of processing the records retrieved from the cursor reference. This type of cursor for loop is a great extension of the traditional type of numeric for loop in which an integer was used to store a value that will be incremented or decremented on each loop traversal.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Further, the manipulation and modification of the integer was also the extra work added. That is why, the use of cursor for loop is very efficient as none of the value needs to be maintained for looping out and all the execution cycle containing the opening, closing, and fetching of the index or cursor that was in traditional integer for loops is maintained internally automatically by cursor loop. In this article, we will have a look at the syntax, working and implementation of the cursor loops in PL/ SQL along with the help of certain examples.

Functions of Cursor Loop

Each cursor is associated with a certain query and for all the records retrieved from that query, the cursor loop does the execution of the body of the loop once for each record. An implicit creation of the loop index is being done while using cursor for loop which helps to retrieve each and every row from the cursor and the best part is that there is no need to manage the cycle of execution involving open, fetch and close steps.

The loop index has the datatype of ROWTYPE which internally returns the cursor and also opens the same later. While looping around the resultset using the cursor, each of the iterations has its associated record fetched inside the loop index. In case if none of the record is left for the iteration the cursor automatically closes its loop. In other situations, like the use of GOTO, EXIT, and raise an exception the cursor is automatically closed and gets out of the loop execution which transfers the control of execution flow outside the loop.

Syntax:

The syntax of the cursor loop also called as cursor for loop is as shown below:

FOR record value IN name_of_cursor
LOOP
Processing for each of the individual record of cursor;
END LOOP;

The terms used in the syntax are explained below:

  • recordValue – The name of the index used by cursor and having the ROWTYPE datatype which acts as the record variable for cursor type. Note that the scope of this variable is local scope hence, it can be only used inside the cursor loop and not outside it. After all, the execution of iterations of the cloop are completed the value of the record variable becomes undefined.
  • Name_of_cursor – As the name suggest it is the cursor name that is declared explicitly and remains close till loop starts. We can also make the alternative usage of SELECT statement in place of the name of cursor which retrieves a number of records. When SELECT function is used, the DBMS creates an implicit cursor which opens, fetches, and closes internally. One of the disadvantages, in this case, is that we cannot refer that cursor further in our program as it is maintained internally while using select query.

The optimization of the cursor for loop is done by the oracle DBMS that makes it work in a similar manner as that of bulk collect. Hence, even though it might look like the record is being fetched one by one it does not happen so. The Oracle DBMS retrieves and fetches all the records at once and the processing is done one by one.

Example

Let us now look at how we can make the use of cursor for loop in our code with the help of certain examples.

Example #1

Let us have a look at the example that demonstrates the usage of how the cursor

can be used in PL/ SQL to retrieve the information about a particular entry in the table. Consider that we have a table called customer details which stores the details of the customers. To check the contents of the table, we can fire the following query in SQL –

SELECT * FROM [customers_details];

The output of execution of above query statement is as shown below showing the contents of the table customer details –

SQL Cursor Loop 1

To retrieve the details of the table in such a way that each of the customer’s first name is retrieved and the contact details showing its mobile number is retrieved, we can create a procedure in PL/ SQL. In this procedure, we will make use of the cursor named sample cursor which will point out to all the resultset of the customer details table having its f_name and mobile number fields.

Further, we will loop through the result by rotating it in the loop where we will take each of the individual record of the cursor table contents one by one in a variable named sample variable. Then we will keep on displaying the result in such a way the DBMS output will contain the name and its corresponding mobile number with an in-between string attached as “having the mobile number”.

DECLARE
CURSOR sampleCursor
IS
SELECT
f_name, mobile_number
FROM
customers_details
BEGIN
FOR sampleVariable IN sampleCursor
LOOP
dbms_output.put_line( sampleVariable.f_name || ' has the contact number ' || sampleVariable.mobile_number );
END LOOP;
END;

The output of above code is as displayed below:

SQL Cursor Loop 2

Example #2

Consider one more example where we have a table named Employees which has the following contents in it when retrieved by using the query statement:

SELECT * FROM [Employees]

The execution of above instruction gives following output:

SQL Cursor Loop 3

Now, in case if we want to retrieve all the data of the Employees table in such a way that it should display the first name and then the birth date in the format – firstname has his birthday on birthdate. For that thing, we can make the use of following procedure –

DECLARE
CURSOR sampleCursor
IS
SELECT
FirstName, BirthDate
FROM
Employees
BEGIN
FOR sampleVariable IN sampleCursor
LOOP
dbms_output.put_line( sampleVariable.FirstName || ' has his birthday on ' || sampleVariable.BirthDate );
END LOOP;
END;

The output of above procedure is as shown below:

output 1

Conclusion

We can make the use of cursor loop in PL/ SQL to loop around a particular cursor or the records retrieved from select query in order to perform certain task again and again for each of the record from the result set.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL Cursor Loop” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL Collections
  2. Triggers in PL/SQL
  3. PL/SQL Data Types
  4. What is PL/SQL?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW