What are Loops - Basic Loops

Oracle Master Training • 80,000+ Students Worldwide
A free video tutorial from Oracle Master Training • 80,000+ Students Worldwide
Oracle Architect & Best Selling Instructor
4.5 instructor rating • 7 courses • 84,249 students

Lecture description

In this lecture you will learn the loops terminology and how to use basic loops in PL/SQL..

Learn more from the full course

The Complete PL/SQL Bootcamp : "Beginner to Advanced PL/SQL"

A Full-Real Guide to Make You a Job-Ready PL/SQL Developer &Also covers "Oracle 1Z0-144 and 1Z0-149" PL SQL Exam Topics!

23:51:04 of on-demand video • Updated February 2021

  • Pass the Oracle 1Z0-149 : Program with PL/SQL Certification Exam - on your first try!
  • Pass the Oracle 1Z0-148 : Advanced PL/SQL Certification Exam - on your first try!
  • Anything You Need for Coding PL/SQL From Beginner Level to Advanced Level
  • Every Subject is Explained from the Easiest to the Most Advanced.
  • Real-World Examples for Every Section
  • Some Advanced SQL Techniques
  • Use SQL Developer Efficiently for PL/SQL
  • Learn PL/SQL Architecture Very Well
  • Improve Performance of SQL& PL/SQL Codes
  • Create Your Dynamic SQL & PL/SQL Queries
  • Learn PL/SQL Object Oriented Programming
  • Learn PL/SQL Tuning for Performance
  • Learn & Master PL/SQL by Coding with Hundreds of Practical Examples!
  • Become a Job-Ready Oracle PL/SQL Developer Certified Professional!
English Hi. Let's continue with the LOOPS. While programming with some language, the aim of your code will be simplifying some business. May be the most useful thing is iterating on something. I mean you do the same thing over and over again based on some rule. For example let's say you are calculating the salaries of all employees in your company. Maybe it is different in your company but in my company we always calculate the salaries one day before it is paid. Because there may be permissions, salary cuts or extras whatever. So they are all recalculated in each month. We have more than 10.000 employees and we calculate all of their salaries in minutes. If we do that by hand, imagine how many days it will take by many people. Whatever, I won’t talk about the benefits of programming. But I am talking about the benefits of the LOOPS. As I am trying to explain, we calculate thousands of people with the same rule by using the loops. We just select them from the table and start iterating on a loop. In every turn, we calculate one employee’s salary. There are three types of loops in PL/SQL. In this lesson, I am going to talk about the most basic one, which is the BASIC LOOPS. Basic loops start iteration without any condition and ends when you say it to end. The good part is, you say when to finish. There is no limit. But with this way you can create an infinite loop. The program decides to finish while looping. This is important because sometimes you may not know where to finish looping. For example, you are searching an employee in a list. You need to finish when you find it. You can find in the first iteration, or in tenth, or may be in the last. You don’t know. In other loops, you know where to finish your loop. But basic loops do not know where to finish. So you need to be careful while creating this loop. You need to handle finish conditions. OK. Now let's run into some technical details. Our loop starts with LOOP keyword. We do not put any semicolon or colon in here. After LOOP keyword, we write our code. Then we write our EXIT keyword. We can either write EXIT alone, or write with a condition. If we write it alone, there will be only one iteration. Because when the program faces with exit keyword, since there is no condition, it will immediately end the loop. If we write a condition with WHEN keyword, it will loop until the condition is satisfied. In here we need to be careful. What if this condition is never satisfied? You will get into an infinite loop. An infinite loop is one of the worst thing in programming. If you stack into an infinite loop, you need to kill your session to get rid of this deadlock. But killing your session is a really bad thing, too. All the things you have done will be rolled back. That means all will gone. You may have updated billions of records, may be for hours. It is possible. You may face that kind of things in a real work. So you need to handle this bug. You need to be sure that, your condition will be satisfied in a time. You can either use some conditions like x > 10 or you can use oracle’s pre-defined functions like no_data_found etc, which return true or false values. I will show you these functions. You don’t need to write EXIT statement alone. You can write inside an if statement or somewhere else. Just be careful about infinite loops. Besides, you don’t need to write EXIT statements just before END LOOP keyword. You can write it anywhere between LOOP and END LOOP keywords. But be aware that, the codes written after exit statement will not be executed if the condition is satisfied. You can use more than one exit statements inside a loop, may be for different conditions, but I recommend you to use one exit in a loop, if you don’t really need it. And your loops finishes with END LOOP keyword. Let’s make some examples for basic loops. Let’s write a simple counter example. First declare a counter named v_counter for example. But before that, let's run SET SERVEROUTPUT ON again. Now let's declare our counter named v_counter for example. v_counter It's a number. Let's say number(2). And the initial value is 1. And our begin keyword. Let's start looping in here. Now I will print our counter in each iteration. Let's write our dbms_output.put_line and My count is, let's say, v_counter. Now we need to increase our counter to avoid an infinite loop.. So v_counter, let's copy that.. is equal to v_counter plus one. In PL/SQL we do not have v_counter++ like any other programming languages. We just need to write v_counter = v_counter+1 to increase one by one. Now let’s write a standalone EXIT keyword to see what happens. Just write exit; And I need to finish my loop with writing end loop keyword. And I will finish my BEGIN block with writing END. Now let's run our code. As we can see, our text is written for just one time. We started from loop keyword, then we printed our counter value. Then increased our value with 1. After that, the program faced with exit keyword without any condition. So it immediately finished the loop. But this is not an actual loop. It iterated for only one time. The main idea of a loop is iterating for more than one times. Otherwise why should I use loops? So we need to finish the iteration with a condition. Maybe we can use standalone exit with an if statement. Let's try to make one. For example In here, let's write If our v_counter is equal to 10 then, let me indent my exit, and END IF. Now that's clear and run our code. Very good. This worked. But have an easier way to do this. We could do this with exit when condition. But if we do exit with if statement, that means we intended to do something more. For example, let's write something more in here. If our number is 10, I can say that, for example, "Now I reached the maximum value" and it is let's say v_counter. In here, I printed my value for one more time. Let's clear up here and run it again.. Now I reached the maximum value and it is 10. I iterated nine times but I printed 10 times. We could do something more. when we reached the condition. But if we just want to finish the loop, we can use exit when condition simply. Let’s comment if clause and write our exit when clause. And in here, I write exit when v_counter is equal to 10. Let's run that now.. As we can see, we iterated for 9 times. We can either set when condition to finish when it is equal to 11, or greater than 10, or we can start from 0 and print our variables after increasing. You can write many different algorithms to run that for 10 times. Now let's write greater than 10. and print again for example. It is greater than 10 Yes. Now we printed our variable up to 10. Alright. That's all for basic loops I think. The rest is your business. It’s up to you now. You can write any logic inside your basic loops. The use is very simple and powerful. So this is the end of basic loops lesson, So this is the end of basic loops lesson, See you in our next lessons.