Hi, in this lesson, we understand how to deal with looping structures in PL SQL. loop is a means to repeatedly execute a set of statements. There are many occasions in which we need to use a loop. We have three different looping structures provided by PL SQL. Loop. While loop and for loop.

Loop is the first and foremost. loop is a means to execute statements repeatedly and indefinitely by default. While is executing statements as long as given condition is true. For is repeatedly executing statements from the given value to the given value, eight ranges A value from given lower range to operate. Let us see how to use loop the first loop. This is repeatedly executing statements indefinitely.

That's how it is designed, but no loop should run indefinitely. So, sooner or later you need to stop it. For that you need to use exit statement. Exit is used to terminate Allu it can take a condition or it can be used on its own. This is an example where we start with the loop execute these three statements as part of the loop. The first statement is printing I which starts with one then we increment i by one.

But then comes the most important statement. Exit, you have to exit the loop when I exit Stan because the program is to print numbers one to 10. And when I exceeds 10 we need to stop this loop. So we start with Taiwan, we print it, we increment it by one and when I exceeds 10 the loop is terminated because we use exit terminating the loop means coming out of this loop and end loop and we resume execution after the loop. In this case, there is nothing after the loop, but if you have any that will be executed. So this is the first looping structure available.

Let us see how to run it. This is our program. As usual. Make sure you set server output on and then execute the code. And this is the result of the program, we get numbers from one to 10. And this is where we stop.

If you change this to five, you're going to stop after five numbers are printed, you can go and run it like this, it stops after five. Well it is very flexible loop. You can even start from 10 and move down if you want. All that you need to do is subtract one from I and say, when is less than one, you want to stop it. Well in this case, the same program is going to print numbers from 10 to one like this. So this is how you can run the program to get 10 to one.

So this is the first of the looping structures. Now, let us see the next looping structure, which is while loop. But before that, let's also talk about exit. And one more very important statement called continue. Exit is used to terminate the loop. Whenever you say exit the loop is terminated, but you can execute exit conditionally, I mean, you can give a condition only when the condition is true, then exit is executed.

That's exactly what I'm doing here. I want to exit the loop when total is greater than 1000. The other statement is continue. Where we give continue followed by again condition. If continue is executed, it is going to take you back to the beginning of the loop ignoring the statements that follow this. So, whatever statements that are present after continue they are ignored and next iteration of the loop begins.

It means, when this condition is true, I mean amount is less than zero, we are not interested in the statements given here, we want to go back to next iteration and start again. So, continue does not terminate the loop, it just terminates the current iteration starts next iteration immediately. These are two statements which are used inside a loop depending on whether you want to exit or you want to just stop the current iteration and start the next iteration. Now, the second looping structure is a while loop. This is where we give a condition. When the condition is satisfied the statements are executed.

When condition fails, the loop is terminated, so we no longer execute statements. Let's look at an example. Here it is, when i is less than or equal entertain, we execute these two statements because this is the body of the loop. So we need to give the statements in between loop an end loop and condition just after a while and make sure your condition is false sooner or later. If condition is never false, you never come out of the loop. That makes it another infinite loop.

So this loop is to print again numbers one to 10 but this time We are not using exit because while loop itself is providing a condition and terminates the loop when condition is false. Well, the next looping structure is for loop which is going to set the counter to lower range and it keeps on incrementing gate are decrementing it depending on whether we are using reverse, it keeps on incrementing gate from lower range to upper range. So, this is counter this is lower range, this is upper range and we need to give two dots in between these two can be either constants or even expressions and it will repeatedly execute these statements as long as the counter does not exceed upper range Let's look at an example. The first example is printing numbers one to 10 because it is setting the counter I do one and it will take it up to 10.

And every time it is incrementing it by one. So, we are going to print numbers one to 10 but the same loop can be returned using reverse. And one very important point you should not change this you just have to give reverse if you want to start with the 10 and go up to one. So, when you say reverts, it starts at 10. It goes up to one. So, the same loop is now going to print numbers from 10 to one, then nine and so on.

And another very important point you don't have to declare the variable i because PL SQL automatically creates the loop variable. If you don't create it, well if you want you can declare it as well but doesn't matter if you don't declare PL SQL automatically declares it. Here are some other examples using for loop. I want to find out how many employees joined in each year. I want to find out what is the minimum year this is going to find out the minimum year out of all the years. First we take the year from hire date and find out the minimum value and also we are finding out the maximum ear and we want to copy those two values into start ear and ear and we want to go from start ear to end ear.

So I is going from this ear to that They say some 2000 and if this is 2015, it will go from 2000 to 2015 of course, inclusive of 2000 and 2015. And for each year I want to know how many implies joined. So, this is going to find out how many employees joined in each year. And this is where we are displaying the count of the employees for each year. So, the program is going to print how many employees joined in each year. Let us see how this program works.

So, here is the program we are talking about going from the minimum ear to maximum ear. So, said I do start ear and then keep incrementing it until it crosses ending here And find out the number of employees joined in each year. So let's go and run this. When you run this, it is going to display output like this. So the first year in which anyone joined is 2001, according to the data we have, and the last year is 2008. So it displays the number of employees joined in each year from 2001 to 2008.

Another interesting program is this, where we want to know what are the missing employee IDs, we want to display the employee IDs that are missing. For example, if you have one, not one, one or two, but then one, not four, it means one or three is missing. So I would like to find out again, the least imply ID and then the other Just employ ID, those two are already there, so we don't have to check for them. So, I start with minimum plus one and stop at maximum minus one. And this is my loop. And for every employ ID, I want to know whether there is any employ.

So we can find out how many employees are there with the given employee ID. And if the count is zero, it means that ID is missing. So we can find out the missing employee IDs in this way. But you might not get anything from this because now employ is really missing out data is such we are not going to see any missing employee ID. But you can test it by deleting some employees from employees table. But be careful some employees cannot be deleted because they have depended Then draws in job history.

But even if you delete employees who do not have dependent roles, make sure you roll back the change later. For example, in order to try this, you can come here and delete an employee. So DELETE FROM employees where employee ID is equal to 120. Let's try and this one be committed immediately. So we can go ahead and do that. And there it is saying the child record found.

That means I can't delete 120. We understood you cannot delete the parent rows when child row is present. So we try to delete an another employee 121. And then 121 is also having a child record. Not just in a job history, even in employers, we have the child rows Do remember employ is having manager. So manager is the parent employ is the child.

So, you may have some employees who are managers for other employees, even then you can't delete the employee you can try some other numbers obviously, there are some employees who are not having job history and then who are not having any employees reporting to them, such implies can be deleted. Well, if you try to delete imply 128 you will succeed because employ 128 is not having any dependent rows. Now, we deleted 128 now go ahead and run the program and the program should give you the result which is 128. That means it's saying this is the missing employer ID now, Don't forget to rollback the change. So, rollback whatever change you made, so that the deletion is undone. Now, let's go run you see nothing that means all employees are intact.

So, you can delete some rows from employees table to test it, you can write a lot of such programs using for loop, you know how to use looping structures, how to use control statements like if statement and then case statement. So with the help of this, you can do a lot. So in this lesson, we understood how to deal with different kinds of loops like loop while loop for loop. We also understood how to deal with exit and continue and where we can use these statements. We understood how to use reverse loop and normal loop when When it comes to for loop, and for loop, automatically declared the counter variable. That's all for now in looping structures.

We'll be using loops all through this course. And we'll discuss more about how to apply them in different contexts in the coming lessons. See you in the next lesson.