FlowControl
Contents |
[edit] Flow Control
[edit] If Statements
So far we have taken a rather straight forward approach to the procedures, with the exception of the more complicated cursor procedure our programs have simply started at the top, executed all of the lines of code and then finished at the bottom. Its also true that so far most if not all of our programs could have easily been written as straight SQL calls.
Pretty much all programming languages use IF statements to control the flow of the program or to allow the developer to use different sections of code based on a certain criteria. Lets create the most simple form of an if statement and see how it works within MySQL.
drop procedure if exists iffunction
//
create function iffunction(p_inparam VARCHAR(10)) returns VARCHAR(10)
begin
declare l_value VARCHAR(10) default ´Not A´;
if p_inparam = ´A´ then
set l_value := ´This was A´;
end if;
return l_value;
end
//
Download Iffunction1.myp
This simple function shows us how we can use a basic IF statement. First we pass in a parameter, declare a varchar variable, then we use if' to determine if the parameter we passed is equal to A, if it is equal to A the code between the IF and END IF will be called, if it isn´t equal to A the program ignores this code. Lets run the function a couple of times and see what happens.
select iffunction(´A´) // +-----------------+ | iffunction(´A´) | +-----------------+ | This was A | +-----------------+ 1 row in set (0.01 sec) select iffunction(´B´) // +-----------------+ | iffunction(´B´) | +-----------------+ | Not A | +-----------------+ 1 row in set (0.00 sec)
So we can see we get different results depending on which parameter we pass. Passing A results in the additional code running and therefore the variable is changed. When we called it with B the code was not called and the variable was not changed.
For an IF to run the evaluation condition (the code between IF and THEN) must evaluate to true. This evaluation can be as complicated or as simple as you require so long as it evaluates to true or false.
IF 1=1 THEN IF (l_value > 10) AND (l_error <> ´001´) THEN IF l_boolean THEN
Are all of these example are valid (so long as l_boolean is a boolean value).
[edit] ELSE
In our first example program it was simply a case of if the parameter is A run the code, we didn´t have the ability to run something else if it wasn´t. We could simply add two if statements to handle this like so.
if p_inparam = ´A´ then
set l_value = ´This is A´;
end if;
if p_inparam <> ´A´ then
set l_value = ´Not A´;
end if;
This would solve our problem but its not a particularly elegant or for that matter efficient. A better solution is to use ELSE. This allows us to do one thing if the condition is met but a second if it is not met. Lets try a simple example to see how this works.
drop function if exists iffunction
//
create function iffunction(p_inparam VARCHAR(10)) returns VARCHAR(10)
begin
declare l_value VARCHAR(10);
if p_inparam = ´A´ then
set l_value := ´This was A´;
else
set l_value := ´Not A´;
end if;
return l_value;
end
//
select iffunction(´A´) //
+-----------------+
| iffunction(´A´) |
+-----------------+
| This was A |
+-----------------+
1 row in set (0.00 sec)
select iffunction(´B´) //
+-----------------+
| iffunction(´B´) |
+-----------------+
| Not A |
+-----------------+
1 row in set (0.00 sec)
Download Iffunction2.myp
So you can see that all we have to do is add the else after the first statement so that the program can deal with one or the other condition. But what if we wanted to evaluate more than one condition, lets say two, in this case we can use an elseif. Lets see it in action.
drop function if exists iffunction
//
create function iffunction(p_inparam VARCHAR(10)) returns VARCHAR(10)
begin
declare l_value VARCHAR(10);
if p_inparam = ´A´ then
set l_value := ´This was A´;
elseif p_inparam = ´B´ then
set l_value := ´This was B´;
else
set l_value := ´Not A or B´;
end if;
return l_value;
end
//
Download Iffunction3.myp
This time we can see that we have an elseif condition between the if and else. We can add as many of these as we need. However there is a another way to achieve the same functionality and this is the CASE statement which we will be looking at in the next section.
[edit] MUTUALLY EXCLUSIVE CONDITIONS
It´s not essential but in practice all of your if conditions should be mutually exclusive. If the value or values you are evaluating in the IF and ELSEIF statements could meet more than one of the conditions then they are not mutually exclusive. When processing the if statement the program will stop when it meets one of the conditions, if this is the first it will not even check to see if the rest are met or not. So for example.
drop function if exists iffunction
//
create function iffunction(p_inparam INT) returns VARCHAR(10)
begin
declare l_value VARCHAR(30);
if p_inparam between 1 and 10 then
set l_value := ´First Condition´;
elseif p_inparam between 10 and 20 then
set l_value := ´Second Condition´;
else
set l_value := ´Third Condition´;
end if;
return l_value;
end
//
select iffunction(10) //
+-----------------+
| iffunction(10) |
+-----------------+
| First Condition |
+-----------------+
1 row in set (0.25 sec)
Download Iffunction4.myp
In this block we can see that the parameter we pass will meet both the first and second condition. But when it meets the first MySQL stops processing the rest of the code until it meets the relevant end if.
[edit] NESTED IF'S
There may be situations where we need to check a number of conditions with some related elements. In this case we could write a number of ELSEIF statements to meet all of the requirements like so..
IF l_company = ´ACME´ and l_division = ´SALES´ THEN
...
ELSE IF l_company = ´ACME´ and l_division = ´HR´ THEN
...
ELSE IF l_company = ´ACME´ and l_division = ´IT´ THEN
...
ELSE IF l_company = ´CORP´ and l_division = ´SALES´ THEN
...
ELSE IF l_company = ´CORP´ and l_division = ´HR´ THEN
...
END IF;
While this is perfectly acceptable to the compiler and will work a much more elegant solution is to use nested IF statements. All we need to do is call additional IF statements from inside the original IF statements. So the above example would become.
drop function if exists iffunction
//
create function iffunction(p_comp VARCHAR(10),p_divi VARCHAR(10)) returns VARCHAR(10)
begin
declare l_value VARCHAR(30);
if p_comp = ´ACME´ then
if p_divi = ´SALES´ then
set l_value := ´You entered ACME Sales´;
elseif p_divi = ´HR´ then
set l_value := ´You entered ACME HR´;
elseif p_divi = ´IT ´then
set l_value := ´You entered ACME IT´;
end if;
elseif p_comp = ´CORP´ then
if p_divi = ´SALES´ then
set l_value := ´You entered CORP Sales´;
elseif p_divi = ´HR´ then
set l_value := ´You entered CORP HR´;
elseif p_divi = ´IT´ then
set l_value := ´You entered CORP IT´;
end if;
end if;
return l_value;
end
//
Download Iffunction5.myp
Here we first check the value of p_comp and then once that has been determined we check the value of p_divi. In our small example there are only a small number of conditions but if we had a more complex example we would be saving valuable processing time doing it this way as we would only need to check the minimum amount of conditions to get to our answer. For example if we had 3 values to check and 10 options for each we would have 1000 different possibilities , using the first method there would be a possibility that the program would need to check all 1000 before evaluating to TRUE. But using nested IF´s we reduce that to a maximum of 30. In addition to any performance gain it also makes it easier to understand and add additional code later.
One additional thing you may have noticed in this last example is we didn´t include an ELSE statement. Its worth pointing out that you do not need to include an ELSE if you do not require one, its perfectly valid to check one, two or many conditions without having to catch anything that doesn´t meet one of those requirements.
In this section we have looked at a number of different technics with regard to using IF statements. We looked at basic IF statements, extended them with ELSE and ELSEIF and finally looked at how we can nest IF statements to make our programs more efficient and readable. We mentioned that in many cases when using multiple ELSEIF statements we can use CASE instead so lets move on to that now.
[edit] Case
We saw in the last section how we can use ELSEIF to link a number of conditions together. While ELSEIF is a perfectly acceptable method we can also use case. Case is very similar to an IF statement in that it can be used to control the flow of the program depending on different conditions. There are two ways in which we can use case.
[edit] CASE case_value
The first method is to specify the source of the comparison up front. We already know how to use ELSEIF so lets take that as a starting point. Lets say we want to evaluate the contents of a variable like so.
IF l_comp = ´ACME´ THEN ... ELSEIF l_comp = ´CORP´ THEN ... ELSEIF l_comp = ´INC´ THEN ... ELSEIF l_comp = ´ABC´ THEN ... END IF;
As you can see we are checking the same variable each time. Surely it would be much better if just told MySQL which value we wanted to check up front and then just give it what we want to evaluate each time. This is what we can do with the CASE statement. Lets look at how we might convert to the above IF ELSE construct into the equivalent CASE.
drop function if exists casefunction
//
create function casefunction(p_comp VARCHAR(10)) returns VARCHAR(10)
begin
declare l_value VARCHAR(30);
case p_comp
when ´ACME´ then
set l_value := ´It was ACME´;
when ´CORP´ then
set l_value := ´It was CORP´;
when ´INC´ then
set l_value := ´It was INC´;
when ´ABC´ then
set l_value := ´It was ABC´;
end case;
return l_value;
end
//
Download Casefunction1.myp
So we can see here how to use CASE. The first thing is to use the CASE keyword then immediately the value we want to evaluate. We then issue a series of WHEN statements which check the value specified against a criteria. If the condition is met the code after that when statement will be executed. As with an IF statement MySQL will stop comparing the values as soon as a match is found.
There a couple of limitations to this style of CASE. Firstly it limits you to a simple comparison condition, you can only really compare one value with another and it does not allow complex and multiple conditions. This may not be a problem and it should be noted that its a better solution than the multiple if statements we have seen previously.It should be noted however than any additional code can be included after the when statement. It would be possible for example to do this simple comparison and then use more complex IF statements. Secondly it also means that the comparison must contain the same value each time, we cannot mix and match as would be able to using IF and ELSEIF.
[edit] CASE...
The second type of case allows more complex conditions to be used. This time we do not specify anything up front. We issue when statements and add the condition there. This is done as follows.
drop function if exists casefunction
//
create function casefunction(p_comp VARCHAR(10),p_divi VARCHAR(10)) returns VARCHAR(10)
begin
declare l_value VARCHAR(30);
case
when p_comp = ´ACME´ and p_divi = ´HR´ then
set l_value := ´It was ACME and HR´;
when p_comp = ´ACME´ and p_divi = ´IT´ then
set l_value := ´It was ACME and IT´;
when p_comp = ´CORP´ and p_divi = ´HR´ then
set l_value := ´It was CORP and HR´;
when p_comp = ´CORP´ and p_divi = ´IT´ then
set l_value := ´It was CORP and IT´;
end case;
return l_value;
end
//
Download Casefunction2.myp
Here we can see that each when statements has its own conditional statement. This is more like the IF ELSEIF style and performs in the same manner. Which you choose CASE (with or without the case_value assigned up front) or IF ELSEIF is up to you. It would be recommended to use IF for simple conditional processing as it is standard across programming environments but CASE can be a little easier to read.
[edit] Loop
There are three methods within MySQL to perform loops, the most simple of which is loop.
Warning : make sure you type any code in this session as accurately as possible. It could be possible to send your program into an infinite loop which will execute forever. You can of course terminate the MySQL server or reboot the machine but if your doing this via your web host they might not be so happy about it.
Using loops we can execute a section of code a multiple number of times. Loop is very simple to use and only requires that you use LOOP to specify the start of the loop and END LOOP to terminate it. The MySQL documentation says that you can optionally include a label for the loop, but during testing we found that it was impossible to create useful loops without them.
[edit] LEAVE
We warned earlier that its possible to send you program into an infinite loop, this is when the program keeps executing over and over. This happens because when using loop we need to tell it when to stop looping, in the case of infinite loops this exit has been left out. To terminate a loop we use LEAVE, This would normally be within some sort of IF statement as we would only want to leave the loop under certain conditions. This is the most likely place an infinite loop can occur, the situation where we do in fact specify a LEAVE statement but the criteria is never met.
It may be a little confusing talking about LEAVE when we haven’t created a loop yet but its extremely important we understand the consequence of not using a LEAVE statement. So lets create a very basic loop and see what it does and how to exit it safely.
drop function if exists loopfunction
//
create function loopfunction() returns VARCHAR(20)
begin
declare l_loop int default 0;
loop1: loop
set l_loop := l_loop + 1;
IF l_loop >= 10 THEN
leave loop1;
end if;
end loop loop1;
return concat(’We looped ’,l_loop,’ times’);
end
//
Download Loopfunction1.myp
We start off in normal fashion creating the function without any parameters. We declare an INT variable, this will be used in the loop to keep track of how many times we have gone around the loop. We then name our loop, as we said previously its possible to write a loop without naming it however in this case we can’t then use LEAVE and as we have discussed this leads to an infinite loop. So we give it the name loop1 then add a colon to tell the compiler this is a name. We then use the LOOP statement to signify the start of the loop. We then add 1 to our variable and then check what the value is in an IF statement. The first time round its one so the IF statement evaluates to false and the code between IF and END IF is ignored. The next statement we get to is END LOOP. MySQL knows that if it has got to this stage without calling the LEAVE statement it needs to go back to the LOOP statement and run the code again. The second time round we add another to the variable and check again, the same result this time so its on to the END LOOP and therefore back to the LOOP, this cycle goes on until the value of l_loop reaches 10. At this point the code in the IF statement is called, in this case its LEAVE. Once LEAVE has been called the loop terminates and no further processing takes place in the loop. Lets run the loop to see what happens, but check your code carefully we don’t want to die waiting for it to finish.
select loopfunction() // +--------------------+ | loopfunction() | +--------------------+ | We looped 10 times | +--------------------+ 1 row in set (0.01 sec)
We can see from this that the loop executed 10 times. We can make a simple adjustment to the code to show that when the LEAVE statement is met any further processing is stopped.
drop function if exists loopfunction
//
create function loopfunction() returns VARCHAR(50)
begin
declare l_loop, l_loop2 int default 0;
loop1: loop
set l_loop := l_loop + 1;
IF l_loop >= 10 THEN
leave loop1;
end if;
set l_loop2 := l_loop2 + 1;
end loop loop1;
return concat(’We looped ’,l_loop,’ times but loop2 only got to ’,l_loop2);
end
//
select loopfunction() //
+--------------------------------------------+
| loopfunction() |
+--------------------------------------------+
| We looped 10 times but loop2 only got to 9 |
+--------------------------------------------+
1 row in set (0.00 sec)
Download Loopfunction2.myp
So you can see that while code before the LEAVE statement was executed 10 times the code after was executed only 9 times. There is nothing wrong with doing it either way so long as you know which way your doing it.
[edit] ITERATE
In the first two examples we let MySQL make the decision of when to do the loop again. But we can force it to do the loop ourselves. To do this we use ITERATE. We use it in much the same way as LEAVE in that we just use the word ITERATE and give it the loop name. Lets create another version of our loop using ITERATE so we can see how it functions.
drop function if exists loopfunction
//
create function loopfunction() returns VARCHAR(50)
begin
declare l_loop int default 0;
loop1: loop
set l_loop := l_loop + 1;
if l_loop < 11 then
iterate loop1;
end if;
leave loop1;
end loop loop1;
return concat(’We looped ’,l_loop,’ times.’);
end
//
select loopfunction() //
+---------------------+
| loopfunction() |
+---------------------+
| We looped 11 times. |
+---------------------+
1 row in set (0.00 sec)
Download Loopfunction3.myp
We can see that the results are very similar to the first two examples. Your free to choose which method you prefer as they are both equally valid ways to use loops. If we wanted to we could include multiple LEAVE and ITERATE statments during the loop to exit or loop again based on more than one criteria.
[edit] LEAVING PROCEDURES
This may be a good point to mention that it’s also possible to use leave in the main body of a procedure. To do this all we need to do is name the procedure body and use the leave statement in the same way as when it’s used with loop.
create procedure testleave() testproc:begin leave testproc; select 1; end // Query OK, 0 rows affected (0.06 sec) call testleave()// Query OK, 0 rows affected (0.00 sec)
In this example the procedure never gets to the select statement because we call leave right before it. The LEAVE statement will be useful for terminating the procedure early if user defined errors occur or if you wish to leave a procedure earlier than the last statements.
There isn’t a lot more that can be said of loops, but there are other methods we can use to perform similar looping which may be more appropriate in different circumstances.
[edit] Repeat
We have looked at simple loops which allow us to perform a section of code a number of times. Using simple loops we need to define the exit condition as a separate section of code. In addition to simple loops we can use repeat to carry out looping in MySQL stored procedures. When using a repeat loop the code is processed until a condition is met, this is the same as using leave within a simple statement, but in a repeat loop the leave statement is specified as part of the end repeat syntax. The syntax for repeat is as follows.
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
As with loops we have the option of giving the repeat a label, but unlike a simple loop its actually practically possible to do so as the leave condition for the loop is part of the end repeat syntax. Once we have labeled the repeat we then add the REPEAT keyword. We can then add one or more lines of code we wish to repeat, its important as with simple loops for one of these lines to be something that will change during the processing of the loop to avoid sending the program into an infinite loop. Once we have added all of the statements within the repeat loop we use UNTIL to tell MySQL if it is time to stop processing the loop. This is done by placing a condition after the until which will evaluate to true when we want to end the loop. This can be a simple comparison such as variable_name = 10 or something more complicated such as (variable_name = 20) and (l_is_end_of_cursor), all the time the condition is false the repeat will continue to loop. We then, without using a semi colon, add END REPEAT and the repeat label if we have used one.
The following code shows how to create a simple repeat loop.
drop function if exists repeatfunction
//
create function repeatfunction() returns int
begin
declare l_repeat_count int default 0;
repeat
set l_repeat_count = l_repeat_count + 1;
until l_repeat_count = 10 end repeat;
return l_repeat_count;
end
//
Download Repeatfunction1.myp
As you can see the repeat is fairly simple, the first thing we do is declare an int variable, l_repeat_count this is so we can keep a count of how many times we have been around the loop and also to give us a way to exit our loop. We then simply added repeat to tell MySQL that we wish to start the loop. The line of code is called which adds 1 to the variable we declared. This variable is then checked using until, in our case we are checking to see if it equals 10, if it does the repeat would end. In our case it only equals 1 so the program goes back to the top of the repeat and performs the code again, 1 is added to the total then it is checked again. This looping continues until such time as the condition between until and end repeat evaluated to true. Lets run the code and see what we get.
select repeatfunction() // +------------------+ | repeatfunction() | +------------------+ | 10 | +------------------+ 1 row in set (0.00 sec)
As you can see the function returns 10, this is because the loop executed 10 times.
[edit] When to Repeat and when to Loop
You may have noticed that repeat and loop act in a similar way, so it´s worth looking at why we would use one over the other. It´s true that repeat is essentially redundant from a functionality point of view, there is nothing that we can do with repeat that cannot be done with a simple loop construct. It comes down to personal choice and a decision on which you think is a more descriptive method, I think that repeat is a little easier to understand by virtue of the fact the exit condition is tightly linked to the end of the repeat. The single difference between a simple loop and a repeat is that you can perform further statements after the conditional check in a simple loop, this may or not be a restriction depending on what your using the loop to do.
[edit] LEAVE AND ITERATE
As with simple loops in addition to using the UNTIL keyword to define the exit condition for a loop its also possible to use the LEAVE keyword to exit the repeat loop. There may be times when processing code that there are more than 1 criteria for an exit, in this case it may also be possible that the criteria will not be met at the same time. Lets look at a very simple and contrived example of this, its unlikely we would ever do such a thing but it demonstrates the fact we can use LEAVE in a repeat loop.
drop function if exists repeatfunction
//
create function repeatfunction() returns int
begin
declare l_repeat_count int default 0;
rep1: repeat
set l_repeat_count = l_repeat_count + 1;
if l_repeat_count = 5 then
leave rep1;
end if;
until l_repeat_count = 10 end repeat rep1;
return l_repeat_count;
end
//
select repeatfunction() //
+------------------+
| repeatfunction() |
+------------------+
| 5 |
+------------------+
1 row in set (0.01 sec)
Download Repeatfunction2.myp
Here we have created a rather artificial example to prove the point but it does demonstrate the point rather well. The first thing we have changed is that we have added a label for the repeat loop, this allows us to use the LEAVE keyword. We then added an IF statement after we had incremented our variable to check it´s value, if the IF condition evaluates to TRUE the LEAVE statement is used and the repeat loop terminates.
It´s unlikely that you will use this method as it really negates the point of using a repeat loop in the first place. It would be much more logical to code the loop as a simple loop, but its worth mentioning none the less. What is more likely is that we would want to only process some of the code during the loop and not other times. We can do this using the ITERATE keyword. ITERATE allows us to stop processing the code at some point between the REPEAT and END REPEAT but not stop looping completely.
drop function if exists repeatfunction
//
create function repeatfunction() returns varchar(50)
begin
declare l_repeat_count, l_count_2 int default 0;
rep1: repeat
set l_repeat_count = l_repeat_count + 1;
if l_repeat_count > 5 and l_repeat_count < 9 then
iterate rep1;
end if;
set l_count_2 = l_count_2 + 1;
until l_repeat_count = 10 end repeat rep1;
return concat(´We looped ´,l_repeat_count,´ times, but we only counted to ´,l_count_2);
end
//
select repeatfunction() //
+----------------------------------------------+
| repeatfunction() |
+----------------------------------------------+
| We looped 10 times, but we only counted to 7 |
+----------------------------------------------+
1 row in set (0.00 sec)
Download Repeatfunction3.myp
We can see from this example that the repeat was performed 10 times but the variable l_count_2 was only incremented 7 times.
Warning : it´s really important when using iterate that you make sure that the program won´t go into an infinite loop. While testing to see if the until is evaluated when the iterate takes place the code was sent into an infinite loop, my PC became unusable to the point pressing control, alt, delete didn´t work. I managed to get to the services control panel and tried to shut down the MySQL service but it was impossible. The only solution was to switch the PC off at the power point. That was fine on my home PC but if I had been connecting to a server or web host the solution wouldn´t have been so easy. It´s very easy to send the program into an infinite loop, especially when using iterate so check you code carefully before running it.
[edit] CURSORS
You may have noticed in one of the earlier sections that we have already used the repeat loop. When using cursors we need some way to loop through the record set that is produced, we could use any of the loop techniques in MySQL but repeat is a good choice. When writing code it´s important to remember that other people or yourself may be using it in the future, making it easy to read is a big part of good programming. If your an English speaker its clear what the word repeat implies, therefore using it in code gives hints to what the code is doing. When we wrote the original cursor code we only skimmed the surface on what the repeat was doing. Lets go back over the cursor code and look at the repeat in more detail now that we have seen it´s use in more detail.
drop procedure if exists cursorproc
//
create procedure cursorproc(OUT p_out DECIMAL(5,2))
begin
declare l_loop_end INT default 0;
declare l_salary, l_total DECIMAL(5,2);
declare cur_1 cursor for select salary from emps;
declare continue handler for sqlstate ´02000´ set l_loop_end = 1;
open cur_1;
set l_total = 0;
repeat
fetch cur_1 into l_salary;
if not l_loop_end then
set l_total = l_total + l_salary;
end if;
until l_loop_end end repeat;
close cur_1;
set p_out = l_total;
end;
//
Download Cursorproc2.myp
The purpose of the repeat in a cursor processing block is to loop around the record set. We simply start by using the REPEAT keyword to tell MySQL that we want to start looping, we then fetch records from the cursor into a variable. The program then completes a series of statements and we reach the UNTIL keyword. We have seen in our discussion on repeat that we need to use a condition that will evaluate to true when using UNTIL. In this case we are using a variable that has been set using a handler, the handler will be called when there are no records left to fetch from the cursor, this will always happen at some point when using cursors. When programming we want the code firstly to be efficient but secondly to be easy to read, if we were to explain what we were doing in English it would be something like, "we want to repeat this section of code until the cursor fetches no more rows". Its easy to see this in the way the code is actually written.
[edit] While
So far we have seen a simple loop, and a repeat loop the final method available to perform looping in MySQL is the WHILE loop. We have discussed looping in general terms in both the previous sections so we won´t dwell too much on that here. The syntax for a while loop is as follows.
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Firstly we can specify a label for the while loop, this is optional in most cases. We then use the keyword WHILE and immediately add the condition, this can be any condition or set of conditions which evaluate to TRUE or FALSE. If TRUE the loop will be performed if false it will not. Next we have the DO keyword, which in effect tells MySQL where the conditions end. We can then specify one or more lines of code. Finally we see the END WHILE keyword, again with an optional label. If you use a label it must be used at both the start and end of the loop. Lets create a simple WHILE loop as we have done with both loop and repeat.
drop function if exists whilefunction
//
create function whilefunction() returns VARCHAR(20)
begin
declare l_loop int default 0;
while l_loop < 10 do
set l_loop := l_loop + 1;
end while;
return concat(´We looped ´,l_loop,´ times´);
end
//
select whilefunction() //
+--------------------+
| whilefunction() |
+--------------------+
| We looped 10 times |
+--------------------+
1 row in set (0.00 sec)
Download Whilefunction1.myp
First we declare an integer variable so we can keep count during the loop and also check our exit condition. We then start the loop by using the WHILE keyword and then specify our exit condition, as before we will be looping ten times. We then use DO to tell MySQL we have finished specifying the exit condition and to carry out the following commands. We then increment our variable so that we have a situation where we can exit the loop at some point. Finally we come to the end while which simply tells MySQL to return to the start of the loop. The process continues until such time as the condition evaluates to true.
As with simple and repeat loops we can use both ITERATE and LEAVE to control processing within the loop.
drop function if exists whilefunction
//
create function whilefunction() returns VARCHAR(20)
begin
declare l_loop, l_count_2 int default 0;
wloop1: while l_loop < 10 do
set l_loop := l_loop + 1;
if l_loop < 5 then
iterate wloop1;
end if;
set l_count_2 := l_count_2 + 1;
end while wloop1;
return concat(´We looped ´,l_loop,´ times, but the count was only ´,l_count_2);
end
//
select whilefunction() //
+----------------------------------------------+
| whilefunction() |
+----------------------------------------------+
| We looped 10 times, but the count was only 6 |
+----------------------------------------------+
1 row in set (0.01 sec)
Download Whilefunction2.myp
drop function if exists whilefunction
//
create function whilefunction() returns VARCHAR(20)
begin
declare l_loop, l_count_2 int default 0;
wloop1: while l_loop < 10 do
set l_loop := l_loop + 1;
if l_loop = 5 then
leave wloop1;
end if;
end while wloop1;
return concat(´We looped ´,l_loop,´ times.´);
end
//
select whilefunction() //
+--------------------+
| whilefunction() |
+--------------------+
| We looped 5 times. |
+--------------------+
1 row in set (0.00 sec)
Download Whilefunction3.myp
[edit] LOOP, REPEAT, WHILE
Its possible to perform the same loop using all three methods. However there is a subtle different between the three.
LOOP : Using loop gives us the control over when the loop takes place. Using iterate and leave we can perform code when we need to.
REPEAT : With a repeat loop we will always perform the loop once (unless we use LEAVE as the first statement). We should use repeat when we are expecting to run the loop at least once and where the conditions which will result in leaving the loop will be determined within it.
WHILE :A while loop may never actually be performed as the exit condition is specified at the start of the loop. Its possible that this exit condition could be false before we even enter the loop. Use while loops where there is a possibility we don´t want to perform the loop.
When and where to use each different type of loop is really a matter of personal preference, but when using them its worth at least for a moment to look at how we would implement it using the other two methods to check there isn´t a more logical way.
We haven´t gone into as much detail with while as we have with the other two methods of looping, but this is because they all function in a similar way and it would be pointless to keep going over the same functionality.