|
|
|
|
In the first part of Procedural Programming in MySQL published in January’s Pipelines newsletter, we looked at the basics of creating functions and procedures within MySQL. We looked at functions, procedures, parameters, variables and finally at the SELECT INTO syntax.
The first release of Stored Procedures within MySQL set a base level of functionality that should suit most basic programming requirements, but that’s not to say all of the standard features of a programming language are not present. In this second part we will be looking at the features of the language we didn’t cover in the first part. We won’t be looking explicitly at how to code these features as that would require much more space and time than we have here, but at the end you will have an understanding of what’s available to you.
Handlers
Most programming languages have some type of error handling that allow the programmer to deal with errors or exceptions that are raised within the program. MySQL stored procedures of course are no exception (excuse the pun). If you have used MySQL as a database, it’s likely you will have at some time encountered errors within SQL statements. It’s these errors that can be handled within the handlers section of a stored procedure.
An error, warning or exception is handled using the following syntax.
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
The handler type is the action that will happen if the handler is called. This can be either continue to carry on processing the procedure, exit to leave the procedure and rollback (which as yet is unsupported) but likely to be useful when dealing with transactions.
Errors can be handled on a number levels, which can be categorized in the following groups. This is defined in the condition_value section. The different categories are as follows.
SQLSTATE
SQLWARNING
NOT FOUND
SQLEXCEPTION
mysql_error_code
condition_name
Error numbers within MySQL are grouped by SQLSTATE so a handler defined with SQLSTATE will handle all of the conditions raised under a particular SQLSTATE code. The next levels are SQLWARNING, which groups SQLSTATE codes beginning with 01. NOTFOUND is for errors with an SQLSTATE beginning with 02 and SQLEXCEPTION which is any SQLSTATE outside of these 2 groups.
Mysql_error_code is for individual errors and exceptions. If you wanted to deal with 2 not found errors in a different way, you could do this using the particular error code in two separate handlers. Condition_name allows us to define our own names for an SQLSTATE or error code. Currently it seems the only purpose of this is to make code more readable but AB may have plans for this in the future.
The final part of the handler is the set condition which is an area used to define code to be performed if the handler is called. It’s likely you would set a variable in this are which could be used later to determine if the handler has been called.
The following code example shows how you might use a handler to deal with the possibility of inserting a duplicate key or a null value.
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
declare continue handler for 1062 SET @b = '- With Error 1062';
declare continue handler for 1048 SET @b = '- With Error 1048';
insert into emps VALUES (NULL,'Dave',1,10) ;
set p_end := concat('The End ',@b);
end;
In this case the “ERROR 1048 (23000): Column 'emp_id' cannot be null” is raised as the insert statement is trying to insert a null into a not null defined column. If we didn’t code a handler to deal with this it would stop the procedure or function and pass the error back to the calling program. Using handlers is an elegant way to deal with such things.
Conditions
I mentioned conditions earlier and said that they were a way to define a name for an existing SQLSTATE or error code. There isn’t much more to them than that. Take for example the following code.
create procedure conditionproc(OUT p_end VARCHAR(10))
begin
declare not_null condition for SQLSTATE '23000';
declare continue handler for not_null SET @b = '- With not_null Error';
insert into emps VALUES (NULL,'Dave',1,10) ;
set p_end := concat('The End ',@b);
end;
The declaration of the condition simply assigns the name not_null to SQLSTATE 23000, which then allows us to use that not_null name in the handler rather then SQLSTATE 2300.
Cursors
Most database procedural programming languages will make use of cursors. Cursors are named result sets which we can process within procedures. We mention in part 1 the current restriction on functions with regards to selecting data from tables and this extends to cursors. Using select into we can select a single row of data from the database into variable but with cursors we can select multiple rows which we can the loop through.
Cursors in MySQL lack some of the additional features found in other languages such as the number or rows returned or a state containing the status of the cursor. Therefore we have to code every single part of the cursor ourselves.
A basic cursor would look something like this.
create procedure cursorproc(IN p_in INT, OUT p_out VARCHAR(30)) begin declare l_emp_name VARCHAR(30); declare cur_1 cursor for select emp_name from emps where emp_id = p_in; open cur_1; fetch cur_1 into l_emp_name; close cur_1; set p_out = l_emp_name; end;
The declare statement tells MySQL the name of the cursor. After the for we specify the select statement to be used to gather that data. This can be any valid SQL statement.
To populate the cursor we use open which gets the data. We can then fetch the information from the cursor into variables we have defined in the procedure. To finish using the cursor we simply issue close. We will look at the syntax for dealing with multiple row cursors when we look at looping constructs later.
IF
Almost all programming languages will contain IF statements. IF statements allow us to make decisions within the stored procedures and functions whether to execute or not execute code based on values within the program.
Take the following code for example.
create function iffunction(p_valueToCheck VARCHAR(10)) returns VARCHAR(10)
begin
declare l_result VARCHAR(10) default 'Not A';
if p_valueToCheck = 'A' then
set l_result := 'This was A';
end if;
return l_result;
end
The IF statement evaluates a conditional statement which will evaluate to TRUE or FALSE. This can be as simple as the one above (p_valueToCheck = ‘A’) or much more complicated. If the condition evaluates to TRUE the code between the THEN and END IF will be processed if it evaluates to FALSE the code will not be executed.
In addition to IF we have ELSE which allows our statement to expand to allow us to run code if the condition evaluates to false. This can be seen here.
create function iffunction(p_valueToCheck VARCHAR(10)) returns VARCHAR(10)
begin
declare l_result VARCHAR(10);
if p_valueToCheck = 'A' then
set l_result := 'This was A';
else
set l_result := 'Not A';
end if;
return l_result;
end
In this example if the first conditional check evaluates to false the code between else if and end if will fire.
We may wish to evaluate a second, third or any number of conditions after the original if. In this case we can use else if like so.
create function iffunction(p_valueToCheck VARCHAR(10)) returns VARCHAR(10)
begin
declare l_result VARCHAR(10);
if p_valueToCheck = 'A' then
set l_result := 'This was A';
elseif p_valueToCheck = 'B’ then
set l_result := 'This was B';
else set l_result := 'Not A or B';
end if;
return l_result;
end
CASE
MySQL also supports CASE statements. Case statements are similar to IF/ELSE IF statements in that they evaluate a condition and carry out commands based on the result of that check. There are 2 types of CASE statements available in MySQL.
The first specifies the value we wish to evaluate at the start of the CASE statement, then at each stage the value is check against a criteria. This is done as follows.
create function casefunction(p_valueToCheck VARCHAR(10)) returns VARCHAR(10)
begin
declare l_result VARCHAR(10);
case p_valueToCheck
when ‘A’ then
set l_result := ‘The Value was A’;
when ‘B’ then
set l_result := ‘The Value was B’;
when ‘C’ then
set l_result := ‘The Value was C’;
else
set l_result := ‘The Value was not A,B or C;
return l_result;
end
We start by specifying the value we wish to check. Then we use when to compare the value with another value. If this comparison evaluates to true then the code after that line is run, if false it moves to the next comparison and so on until it finds a match or it gets to the else statement. The else is optional and it’s also worth mentioning that if any of the when conditions are met the remaining conditions are not evaluated.
You can use this type of CASE is you will be comparing the same value each time. If we wish to evaluated different criteria at each when statement we can use the second type of CASE like so.
create function casefunction(p_valueToCheck VARCHAR(10))
returns VARCHAR(10)
begin
declare l_result VARCHAR(30);
declare l_secondCheck VARCHAR(10) := ‘Other’;
case
when p_ valueToCheck = 'A' then
set l_ result := 'It was A';
when p_ valueToCheck = 'B' then
set l_ result := 'It was B';
when l_ secondCheck = 'Other' then
set l_ result := 'It was Other';
end case;
return l_ result;
end
This time we can evaluate a different condition at each when statement. The same rules apply as with the other style of CASE in that MySQL will stop evaluating the when conditions as soon as it finds a match.
LOOPS
There are three types of loops within MySQL, simple loops, repeat loops and while loops. All three operate in the same way but have a subtle difference in their implementation. A word of warning when using any loops in MySQL - try not to put the procedure into an infinite loop this isn’t all that hard and may result in having to shut you PC down to recover. An infinite loop is on that contains no exit statement or an exit statement that will never evaluated to false.
SIMPLE LOOPS
As the name suggests, simple loops are relatively simple to use. They contain at a basic level loop and end loop keywords to specify the start and end of the code you wish to loop. However using just these two keywords will put us into an infinite loop which will run forever (or until you pull the plug from the wall).
To exit the loop use the leave statement. This can be placed within an IF statement to stop the loop under a set condition. But be sure to check that this IF statement will evaluate to false at some stage.
create function loopfunction(p_valueToCheck VARCHAR(10))
returns VARCHAR(10)
begin
declare l_counter int :=0 ;
TestLoop: loop
set l_counter := l_counter + 1;
if l_counter = 10 then
leave TestLoop;
end if;
end loop TestLoop;
return l_ result;
end
To use a leave in a loop we need to give the loop a name, in the above example TestLoop. This lets MySQL identify the loop you wish to leave as it’s possible to nest loops inside of each other.
In addition to leave we can use the iterate keyword. This tells the loop to start again without executing the remaining code in the loop.
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
//
REPEAT LOOPS
The repeat loop effectively has the leave statement built in. It performs in exactly the same way as a simple loop. You can use both leave and iterate in the same way. The difference comes with the last line of the loop which is as follows.
repeat
<statements>
until <condition> end repeat;
The loop will keep repeating until the condition contained in the until/end repeat statement evaluates to true.
WHILE LOOPS
Again the while loop functions in the same way as a simple or repeat loop, but this time the exit condition evaluation takes place at the start of the loop.
while <condition> do
<statements>
end while
All the time that the condition evaluates to true the loop will take place. It’s important with both while and repeat loops to make sure that the state of the condition will change at some point during the execution of the loop.
The decision on which loop to use comes down to how many time the loop is likely to run. As the repeat loop will not evaluate the exit condition until the last statement the code between loop and end loop will always fire once. With a while loop its possible that the loop may never be called. With a simple loop it’s up to you to make the decision on how much code is called before the exit condition is evaluated.
LOOPING CURSORS
We mentioned cursors previously but in our example we only selected a single row. A much more likely use of cursors is to use them to process multiple rows of data. Armed with our information on loops and handlers we can now look at doing this.
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;
//
The cursor is declared as before and we open the cursor in the same way using the open keyword. This time however we declare a handler, this handler has been assigned to fire when SQLSTATE 02000 is called, more on that in a second. We then open a repeat loop. A record is then fetched from the cursor. The salary is added to our variable. Then the until condition is evaluated using the variable l_loop_end. This variable is set when the handler we declared fires. The first time around the loop it’s unlikely to fire so the loop repeats. This will continue to happen until such time as the fetch from the cursor returns no more rows. That will result in the Handler firing and the l_loop_end variable being set to true. The next time the until condition is evaluated the loop will exit.
It’s possible to use a while and simple loop to process a cursor in much the same way but due to the evaluation taking place the end in a repeat loop this is the most logical of the three.
That concludes the second part of this short introduction into Stored Procedure programming in MySQL. Stored procedures are a new feature and it’s likely that they will change over the next few releases of MySQL so check the MySQL website for updates to the specification and functionality.