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 For Loop in PLSQL
 

For Loop in PLSQL

Updated April 3, 2023

For Loop in PLSQL

 

 

Introduction to For loop in PL-SQL

Since it is very simple to understand the FOR LOOP in every language. So, PL-SQL also has FOR LOOP facility. Basically for loop in PL_SQL are of two types. First is Numerical for loop and the second one is Curser for a loop. SO in this post, we will focus mainly on Numerical for a loop. So, FOR LOOP enable us to iterate any statement or statements in user-specified number of times. In this topic, we are going to learn about For Loop in PLSQL.

Watch our Demo Courses and Videos

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

Syntax and its description for FOR LOOP in PLSQL

FOR lc IN [REVERSE] lower. . . . . . . .upper
LOOP
Statement_1;
Statement _2;
…
Statement_3;
END LOOP;

So the first sentence enables you to initiate the loop counter variable “lc” with a lower and upper limit like every FOP LOOP in other programming languages does. So lower limit is the lower number and the upper limit is the upper limit of iteration. There is EVERSE option available in case we want to iterate the loop in a reverse manner. This keyword is optional for users. Then every bunch of statements will start with “LOOP” word and end with the “END LOOP” word. Basically in your real-time scenario, you have to specify a break statement or exit statement so we have to specify a condition at which point the loop should break so that’s this is how you specify that exit condition you can directly specify exit like this or you can just say exit when well the advantage of exit when the condition is that you can specify a condition here like on which statement it should break well that’s how simple loop works we have your loop command and in loop statement when you have exit condition well those are the two things and the design for the disadvantage of this loop statement is that suppose in case if you forgot to specify exit condition it’s going to run an infinite number of times there’s no stop to it so we have to be very careful while using a loop.

Examples to demonstrate the functioning and use of FOR LOOP in PLSQL

Now let’s try some examples which will help you understand the concepts more clearly.

Input 1:

DECLARE
BEGIN
FOR vc IN 1..7
LOOP
DBMS_OUTPUT.PUT_LINE(vc);
END LOOP;
END;

Output:

For Loop in PLSQL output 1

Explanation:

This is a very simple demonstration of the PL-SQL program. So, here we have DECLARE BEGIN BLOCK. In DECLARE we can declare or/ and initialize any variable with the data type. Here since we are not using any variable except the loop variable so, we did not declare it. Then inside BEGIN block, we have started FOR LOOP. In this, FOR LOOP it is very important to have one variable which will iterate for the entire FOR LOOP cycle. So that loop has two dots with each end upper and lower limit of iteration. So every time variable VC enters the loop it gets incremented by 1 till the upper limit. So each time loop iterates the DBMS OUTPUT prints the output on the console. And lastly do not forget to end loop and put an extra loop to which will serve as a closing bracket for DECLARE block. So After executing the above code, the Value of your counter variable from each iteration is counting from 1 to7.

Input 2:

DECLARE
BEGIN
FOR vc IN REVERSE 1..9
LOOP
DBMS_OUTPUT.PUT_LINE(vc);
END LOOP;
END;

Output:

For Loop in PLSQL output 2

Simply write the keyword REVERSE immediately after the keyword IN. So after executing the above command we get the below result.

Input 3:

Declare
result_var NUMBER;
BEGIN
FOR vc in 1..10
LOOP
result_var:=12*vc;
DBMS_OUTPUT.PUT_LINE(result_var)
END LOOP;
END;

Output:

output 3

Explanation:

Here we wrote a full-fledged PL-SQL code to find the table of 12. So in PL-SQL, every code starts with declare command.

Then inside that, we declare var_res variable as a number to store the result. Then BEGIN statement start and inside that, we create a loop statement which will iterate 10 times multiplying with number table 12.

Input 4:

DECLARE
I number :=1;
BEGIN
LOOP
Dbms_output.put_line(i);
I:=i+1;
EXIT WHEN i>5;
END LOOP;
END;

Output:

output 4

Explanation:

Here We declare I as a number equal to 1 Then our begin statement starts in the begin statement Here we have an exit statement that will exit the execution when at iteration it finds that I am greater than 5. This exit when the statement is also used to restrict the running of the loop to infinite time as we cannot use ctrl +c command to kill the job. So while the condition will be true till I am less than 5 Then the loop will iterate till I am less than 5 fros 0 to 4 and by this way iteration proceeds to increase the I number which was declared d earlier. I will be incremented every time by 1 and Dbms output will display the I each time of iteration. Then loop ends.

DECLARE
I number :=1;
BEGIN
WHILE(i<5)
LOOP
Dbms_output.put_line(i);
I:=i+1;
END LOOP;
END;
/

Output:

output 5

Explanation:

Here We declare I as a number equal to 1 Then our begin statement starts in the begin statement There is a while loop now instead of exit when statement.SO while the condition will be true till I is less than 5 Then the loop will iterate till I is less than 5 from 0 to 4 and by this way, iteration proceeds to increase the I number which was declared earlier.I will be incremented every time by 1 and Dbms output will display the I each time of iteration. Then loop ends.

Conclusion

We have seen the functioning of FOR LOOP with examples. The loop is the first step of coding for any beginner. So in my next post, I will definitely explain the cursor for loop.

Recommended Articles

We hope that this EDUCBA information on “For Loop in PLSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PLSQL Interview Questions
  2. Triggers in PL/SQL
  3. Loops in PL/SQL
  4. PL/SQL Data Types

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
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?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW