Mysql Procedures PL SQL

Stored Procedures :
Andrew Gilfrin, www.mysqldevelopment.com

Introduction.
MySQL was first released back in November of 1996 and has grown ever since. It has become the
database of choice for web develoment d!e to ease of !se and s!ort by many IS"s and web
hosting comanies. #$ the comany who control and develo MySQL claim it is the worlds most
o!lar oen so!rce database.
%owever one thing it has had diffic!lty in the ast is convincing &serio!s& database develoers that it
has the caacity to challenge the database market leaders' (racle' Microsoft&s SQL Server and I$M&s
)$*. +here are a n!mber of reasons for this re!tation' some ,!stified b!t others not so.
(ne area of f!nctionality that the big layers all s!ort is stored roced!res within the database'
this is something MySQL has lacked. -ntil now' with the release of MySQL ../.1 s!ort has been
added for stored roced!res within the database. #t this early stage it&s still limited in caability and
has some imortant arts missing b!t #$ seem to be aroaching the s!b,ect by getting the basics
right first before increasing the f!nctionality.
0or the average web develoer the introd!ction of stored roced!res may seem a little bit of an anti
clima1' they may on the s!rface seem s!rl!s to re2!irements with most of the sol!tions they
rovide being relatively easy to relicate in "%" or some other web lang!age. $!t s!ort for stored
roced!res will be seen as a big ste forward in the database comm!nity where data is king' rather
than a means to maintain data within a web alication.
So What Are They?
Stored roced!res are sections of code which are stored in the database and e1ec!table within the
database. +hey can erform simle changes to single val!es or !ndertake large rocessing tasks.
+hey fall in to two categories' f!nctions and roced!res. 3e will look at the difference between the
two later in the t!torials.
It is in fact ossible to !se and create f!nctions within MySQL !sing 4 b!t that can be a little
comlicated and has limited f!nctionality. +here are also a n!mber of f!nctions available within
MySQL that yo! may have !sed before' s!ch as 567#+7S+89 or 4(N4#+89.
Getting Going
-nfort!nately the set! and installation of MySQL is a big s!b,ect and something we don&t have room
to go into detail abo!t here' b!t there are lenty of web sites which can give yo! all the information
yo! need.
+o rogram with stored roced!res in MySQL we need to !se version ../.1 or above' we recommend
yo! !se as high a release as ossible' this can be downloaded from the MySQL web site for free. In
addition to MySQL we will be !sing a basic te1t editor' this is not necessary b!t will make o!r life a
little easier.
(ne of the advantages of !sing stored roced!res is that they are r!n from within the database so
any commands we !se on one oerating system will be available on another. +he t!torials have been
r!n !nder 3indows :" b!t they sho!ld r!n e1actly the same on any other oerating system yo! may
be !sing. (ne final word' if the only coy of MySQL yo! have is a lower version than ../.1 then
!nfort!nately yo! won&t be able to try the e1amles yo!rself' b!t yo! can still read and learn all
abo!t stored roced!res from the t!torials.
Conventions
0inally a word on the conventions we will be !sing in the t!torials. #ny code that yo! can tye in will
be dislayed in one of two ways
//This is code seen for the first time.
//Or code you have already seen but is being reference later
4hanges to rograms revio!sly created d!ring the t!torial will be shown in bold
//This is old code here
//This is the line we want you to add
;o! can download a <ied coy of all the so!rces !sed in each chater !sing the men!. $!t after
each section of code will be a link to the act!al so!rce code file that we !sed. 3e s!ggest yo! try
tying in each section of code yo!rself as this can often hel yo! !nderstand what is act!ally going
on' b!t if yo! can&t get a artic!lar rogram to work feel free to !se o!r code. +he so!rce will
aear as follows.
so!rce.ms
So lets get straight in by having a go at creating a simle roced!re' yo! can click on the %ello3orld
link bellow to go to the relevant age or !se the men! at the to of this and every age. #t any time
yo! can move on to the ne1t section !sing the link marked = or back to the revio!s section !sing
the link marked with >.
Hello World
3hat better lace to start a rogramming than with the !bi2!ito!s %ello3orld. In this
introd!ction we wont worry too m!ch abo!t the why or the how we will ,!st get straight into the
code.
$efore we get into writing some code we will set ! some data we will be !sing later in the
t!torials. 3e are !sing a te1t editor to create scrits which we r!n !sing the source command in
MySQL. %ere is the contents of the scrit we have !sed to set ! the test data.
drop database if exists pers
\g
create database pers
\g
use pers
\g
create table emps(emp_id int NOT NULL,
emp_name varchar(30),
dept_id int,
salary decimal(5,2),
primary key(emp_id))
\g
insert into emps (emp_id,emp_name,dept_id,salary)
values (1,'Roger',1,2000.00),(2,'John',2,2500.00),
(3,'Alan',1,2100.00)
\g
select * from emps
\g
create table dept (dept_id int NOT NULL,
description varchar(30),
primary key(dept_id))
\g
insert into dept (dept_id,description)
values (1,'Information Technology'),(2,'Sales')
\g
select * from dept
\g
set!.my
If yo! haven&t !sed source before its a way of writing an SQL scrit in file and then r!nning it in
MySQL. +o !se so!rce call it in the following way.
source c:/mysql/source/setup.myp
(f co!rse e1change the ath for the one yo! will be !sing. (ne word of warning if yo!r !sing
MySQL on windows' yo! need to !se ? rather than @ as MySQL can interret @ as a call to a secial
f!nction s!ch as @h which wo!ld show the MySQL hel.
0inally before we begin we need to do one more thing. 3hen creating stored roced!res within
MySQL we need to !se the ; character to terminate a line. %owever MySQL interrets this as a
delimiter character so we need to tell MySQL to !se another delimiter when r!nning other code.
3e will be !sing //.
So lets set that now
delimiter //
Lets check this works with a simle SQL statement.
select 'hello' //
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
(A so lets get on to o!r first rogram which will be a f!nction. If yo! have any e1erience of
rogramming it is likely yo! will have seen %ello3orld or a rogram like it before. It won&t act!ally
do a great deal b!t it will work and it will be easy. So lets give it a go.
create function helloworld() returns varchar(20)
return "Hello World";
//
Query OK, 0 rows affected (0.00 sec)
helloworld1.my
%oef!lly yo! will see the Q!ery (A message which tells yo! that the f!nction has been
s!ccessf!lly created.
Now we want to call the f!nction so we can see the res!lt. 3e will talk more abo!t the difference
between a f!nction and a roced!re in a later section' b!t for now all we need to mention on the
s!b,ect is that we call them in different ways. +o call a f!nction we do the following.
select helloworld() //
+--------------+
| helloworld() |
+--------------+
| Hello World |
+--------------+
So as yo! can see calling a f!nction is retty simle and the res!lts are ret!rned in the same was
as a res!lt set from an SQL call.
3ell done that&s yo!r first f!nction comleted. If yo! haven&t managed to get it working check the
synta1 for small selling errors or the odd character missing.
In the ne1t lesson we will look at what we have ,!st written and e1lain what each section means.
Hello World !"lained
Now we have written o!r first f!nction and been able to get some o!t!t lets look at what we
act!ally did. Lets look at it word by word and see what each art means.
create (A so first ! its create' which as yo! can imagine is telling the comiler that we want to
create something. 3e wanted to create a f!nction so we !sed the function keyword ne1t. +his tells
MySQL we are going to be creating a f!nction rather than another MySQL ob,ect s!ch as a table. 3e
then give that f!nction a name helloworld in o!r case' after the name we !t a set of brackets we
won&t worry too m!ch abo!t those ,!st yet e1cet to mention that they are there and in fact need to
be there. #ll retty simle so far' ne1t ! is returns ' which might not be so obvio!s. 6et!rns is !sed
to tell MySQL which sort of data tye we will be ret!rning from the f!nction. 3e are ret!rning a
VARCHAR which is a variable length character string. 3e !t the 8*/9 in so MySQL knows that the
string will be at most */ characters.
So far we have simly told SQL we want to create a f!nction called helloworld which will ret!rn a
varchar of */ characters in length' that&s all there is to it. 3e now have 1 of * otions' o!r first is to
create a single line rogram or o!r second otion to create a rogram with more than one line. In o!r
case it was ,!st a single line so all we had to do was write that line. If yo!r creating a f!nction yo!
m!st ret!rn a val!e' that&s done !sing the return keyword. 3e know its going to ret!rn a varchar so
we ,!st o!t !t a simle string #Hello World#.
# few moments ago I mentioned we had * otions' the second otion is to create a f!nction with
more than one line' while it is ossible to create f!nctions with only a single line they are not
artic!larly !sef!l. If we want to write more than one line we need to !t the lines between a begin
and an end. 3e can do this even with a single line f!nction so lets do this now with a new version of
hello$orld.
$efore we do that we need to talk abo!t drop function. )ro f!nction is !sed to remove the
f!nction from the database so that when we load o!r new version it doesn&t ca!se an error. 3e can
dro in either of the following two ways.
drop function helloworld \\
or
drop function if exists helloworld \\
+he difference between the two is that the first will rod!ce an error message if the f!nction we are
trying to dro doesn&t e1ist. ;o! can !se either b!t i refer the second as its a little more tidy. So lets
make that change to o!r helloworld f!nction.
drop function if exists helloworld
//
begin
return "Hello World 2";
end
//
helloworld*.my
;o! may have sotted that we also changed the ret!rned string to &%ello 3orld *& this was simly to
show that we were act!ally looking at the new f!nction rather than the old one. +his time we will
have seen this o!t!t.
+---------------+
| helloworld() |
+---------------+
| Hello World 2 |
+---------------+
3e can see that the addition of begin and end doesn&t effect the f!nctionality. Lets move on to look
at the difference between f!nctions and roced!res. the second tye of rogram !nit we can create.
%unctions or Procedures
(!r first rogram' HelloWorld was a f!nction. In MySQL we have the otion of creating both
f!nctions and roced!res. +his lesson will look at the differences between f!nctions and roced!res
and looks at when yo! might !se one and not the other. Lets start with f!nctions as we are already
familiar with how one is constr!cted and might work.
%unctions
0!nctions are rograms which when called ret!rn a single val!e' they m!st always ret!rn a val!e and
will always ret!rn ,!st one val!e. +hey can be relatively simle or a little more comle1 incl!ding
m!ltile lines of code or ,!st a single line. (n advantage of f!nctions is that we can call them from
within an SQL statement. +ake for e1amle the C&'CAT f!nction which is already available in MySQL.
select concat(emp_name,' ',dept_id) from emps
-> //
+------------------------------+
| concat(emp_name,' ',dept_id) |
+------------------------------+
| Roger 1 |
| John 2 |
| Alan 1 |
+------------------------------+
3 rows in set (0.00 sec)
#s yo! can see here we can !se f!nctions within the SQL statement to erform transformations or
calc!lations on data within the database tables. 3e can also do this with o!r own f!nctions as we
will see in later sections.
+here really isn&t a great deal more to say abo!t f!nctions' of co!rse they can do a lot more than ,!st
ret!rn strings as we have done so far and we will cover more of this in f!t!re sections.
Procedures
"roced!res can do a little more than f!nctions' they do not need to ret!rn anything b!t can if
needed ret!rn many things. "roced!res cannot be r!n from within SQL statements like f!nctions' b!t
m!st be called. Lets create a simle roced!re and see how we call it.
create procedure helloprocedure()
select 'Hello Procedure' ;
//
helloroced!re1.my
#s yo! can see creating a roced!re is very similar to the creating a f!nction. #s a general r!le we
sho!ld be able to do the same things in roced!res and f!nctions' there is c!rrently one e1cetion to
this which we will disc!ss in a later section. ;o! can also see that I didn&t incl!de a begin or end' this
was simly to show that it was ossible. Lets r!n the roced!re and see what we get.
call helloprocedure() //
+-----------------+
| Hello Procedure |
+-----------------+
| Hello Procedure |
+-----------------+
;o! may also have noticed that we r!n a roced!re differently from a f!nction' this was done !sing
the call command. 0or a little more ractice lets add the begin and end statements. 3e will be
calling drop procedure' in ,!st the same way we !sed drop function' to remove the roced!re
before adding the changes.
drop procedure if exists helloprocedure
//
create procedure helloprocedure()
begin
select 'Hello Procedure 2' ;
end
//
call helloprocedure() //
+-------------------+
| Hello Procedure 2 |
+-------------------+
| Hello Procedure 2 |
+-------------------+
helloroced!re*.my
3e will see more differences between f!nctions and roced!res as we contin!e' so we will now carry
on and e1and on o!r hello$orld and hello"rocedure rograms.
(sing )aria*les
;o! may already be aware of !ser variables within MySQL. # !ser variable can be !sed to store
information for later !se in yo! session. # variable in broader terms can be viewed as a container for
information. In MySQL f!nctions and roced!res we can create variables to hold information d!ring
o!r rocessing.
)aria*le +ata Ty"es
3hen we create a variable we need to give it a data tye. # data tye is the format and tye of data
we want to store in the variable' this co!ld be a string' n!meric or date and time. +here are a
n!mber of different data tyes within MySQL and all are avaliable for !se in o!r rograms. 0or the
moment we will concentrate on a few basic tyes b!t if yo! wish to see the f!ll set available take a
look here.
httB??dev.mys2l.com?doc?mys2l?en?4ol!mnCtyes.html
3e won&t concern o!rselves with all the different tyes for now so lets get straight in and see how we
define a variable' what we can do with it and how we can change the val!e stored in it.
Lets !se o!r HelloWorld f!nction again to work with a variable. 6ather than simly assing o!t a
string we will ass o!t the contents of a variable.
+o create a variable we !se the following synta1.
declare var_name[,...] tpe ,+%A(-T value.
+he declare tells the comiler we are abo!t to define the variable. 3e then give it a !ni2!e name'
at this stage MySQL have yet to secify naming conventions and restrictions for variable names b!t its
a given that yo! sho!ld avoid !sing reserved words and try to kee them short b!t descritive. ;o!
can define more than one variable of the same tye !sing one declare statement as we shall see in a
moment. 3e then secify the data tye we wo!ld like to assign to the variable' as mentioned before
this can be any of the standard MySQL data tyes. 0inally we can assign the variable a defa!lt val!e'
we will look at this in more detail in a few min!tes.
+his may be a little bit too m!ch to take in so lets try and define a variable so we can see one in
action.
//
begin
declare l_hello varchar(20) default 'Hello World 3';
return l_hello;
end
//
+---------------+
| helloworld() |
+---------------+
| Hello World 3 |
+---------------+
helloworldD.my
So we have now changed o!r helloworld f!nction to !se a variable. 3e added the declare keyword to
tell the comiler that we were !sing a variable' named it lChello and gave it a defa!lt val!e of &%ello
3orld *&. 3e then relaced the string we had !sed revio!sly with the variable. Now when we r!n
the f!nction we can see that the o!t!t has changed.
3e mentioned that we co!ld declare more than one variable on a single line' lets now try and
declare more than on variable and also see how we can set a val!e for them.
//
begin
declare l_hello, l_world, l_string varchar(20);
set l_hello = 'Hello';
set l_world = 'World';
set l_string = concat(l_hello,' ',l_world,' 4');
return l_string;
end
//
+---------------+
| helloworld() |
+---------------+
| Hello World 4 |
+---------------+
helloworldE.my
3e are now starting to see o!r f!nction look a little more s!bstantial. 3e have introd!ced a few new
concets here which are 2!ite imortant.
0irstly we can see that we have declared D variables !sing one declare statement. 3e co!ld have
!sed the default statement also b!t this wo!ld have been ointless as all three wo!ld have had the
same val!e.
Ne1t we !sed the set statement to assign a val!e to each of o!r variables. In the case of the first two
this was a simle case of !se a character string. $!t for the third we !sed the C&'CAT f!nction to
,oin the other * strings together. +his demostrates the fact that we can !se f!nctions within other
f!nctions' this goes for the standard f!nctions s!ch as C&'CAT and also for ones we define o!rselves.
Ho$ever... while trying to rove that we can call o!r own rograms from within other f!nctions the
server ket crashing. - to this oint we had been r!nning the so!rce !sing version ../.1alha' +his
is a good time to ress home that release . is not a f!ll release as yet and is not stable and sho!ld be
!sed as s!ch. +here will be things that we can&t do at this early stage. $!t we installed the latest
version ../.*alha and the roblem seemed to be fi1ed.
create function testhelloworld() returns varchar(20)
return helloworld()
//
select testhelloworld();
//
+------------------+
| testhelloworld() |
+------------------+
| Hello World 4 |
+------------------+
testhelloworld.my
#ll this disc!ssion of calling f!nctions has taken !s off the ath a little so back to the matter in hand'
variables. So far we have declared' set and !sed a string f!nction for o!t!t' b!t we can !se many
other tye of variables. %ere&s a roced!re which !ses a date tye variable and ret!rns the c!rrent
date.
drop function if exists showdate
//
create function showdate() returns varchar(20)
begin
declare l_date date;
set l_date = CURDATE();
return l_date;
end
//
select showdate()
+------------+
| showdate() |
+------------+
| 2005-01-23 |
+------------+
showdate.my
#s yo! can see in the f!nction we call C(/+AT which in effect does e1actly the same thing as o!r
f!nction b!t it does demonstrate that we can define and !se date data tyes.
+his ne1t one ret!rns a n!mberF
drop function if exists getsalary
//
create function getsalary() returns numeric
begin
declare l_salary numeric(13,2);
set l_salary = 1000000.00;
return l_salary;
end
//
getsalary.my
+hese small e1amles will have shown yo! what variables are and how to create them. 3e will leave
variables now as we will be !sing them in f!t!re rograms and it is there that we will begin to see
more clearly how and when we might !se them more f!lly
Passing Para0eters
So far we have been getting o!t!t from f!nctions !sing ret!rn and from roced!res !sing a select
statement. $!t we can also ass information into o!r rograms and in the case of roced!res o!t
also. So far o!r rograms have simly been a way to ret!rn static information' we know what we are
getting back beca!se its the same each time. +o ass information in and o!t we !se arameters. In
the case of f!nctions we can only ass in information so lets try that first. 3e can !se yet another
version of helloworld' b!t this time ass in a character string.
//
create function helloworld(param1 varchar(100)) returns varchar(100)
begin
return CONCAT('Hello World and ',param1) ;
end
//
select helloworld('Alan') //
+----------------------+
| helloworld('Alan') |
+----------------------+
| Hello World and Alan |
+----------------------+
helloworld..my
Lets e1lain what we did here' yo! can see that the f!nction itself is very similar to ones we have
written in the ast. +he first difference is that in the brackets following helloworld we have
incl!ded the word param! and a varchar assignment. param! is the name of o!r arameter' if we
were creating a roced!re we wo!ld have incl!ded a keyword before this to show which tye of
arameter it is b!t as f!nctions can only accet I' arameters this is not incl!ded. +he arameter
we will be acceting will be a character string so we assign it to a varchar of 1// characters in
length. #ll we then need to do is reference the arameter within the f!nction which we do in the
ret!rn val!e. It is ossible to change the val!e of the arameter in the f!nction' its !nlikely yo!
wo!ld need to do so b!t it is ossible.
+he ne1t change is the way in which we call the f!nction. "revio!sly we simly !sed helloworld"# b!t
we now need to ass the val!e into the f!nction' this is done simly by lacing the val!e we wish to
ass in between the brackets' as we will see in a moment if yo! have more than one arameter yo!
sho!ld ass them in !sing the same order as they are defined in the f!nction. In some lang!ages it&s
ossible to ass arameters either by osition or by e1licitly naming them' the MySQL
doc!mentation doesn&t say if assing in by naming them is ossible' we did try to do this b!t co!ldn&t
seem to do so' for now simly ass them in by osition.
1ore Than &ne
3e can ass in as many arameters as we need to and as mentioned above we simly do this by
adding more between the brackets like so.
//
create function helloworld(param1 varchar(100),param2 varchar(100)) returns
varchar(100)
begin
return CONCAT(param1,' ',param2) ;
end
//
select helloworld('Hello','World') //
+-----------------------------+
| helloworld('Hello','World') |
+-----------------------------+
| Hello World |
+-----------------------------+
helloworld6.my
Passing Para0eters $ith Procedures
3e can ass val!es both in and o!t of roced!res. +his is done in a similar way to f!nctions e1cet
that we need to !se the $% or &'( keywords to tell the comiler what to do with the arameters. So
far we have !sed a select statement in a roced!re to rod!ce o!t !t' lets now !se an o!t
arameter in the roced!re to get the val!e.
//
create procedure helloprocedure(OUT param1 VARCHAR(100))
begin
set param1 = 'Hello World';
end
//
call helloprocedure(@a)
//
helloroced!reD.my
3hat haened thereG 3e create the roced!re called it b!t didn&t get any o!t!t. 0irst thing to
mention is that when we call the roced!re we !sed 2a as the arameter. -sing 2a created a !ser
variable' this is where the val!e is now stored. +o access it we need to !se a select statement to see
the val!e.
select @a //
+-------------+
| @a |
+-------------+
| Hello World |
+-------------+
So there we have the val!e. 3ith all the e1citment of losing o!r o!t!t we skied over a co!le of
oints. 0irstly yo! can see the &'( keyword needed in a roced!re to tell the comiler what tye of
arameter it is. Secondly yo! can see all we needed to do in the roced!re was !se the )*(
statement to assign a val!e to aram1.
I'3 &(T Sha4e It All A*out
#s with f!nctions we can also accet arameters into a roced!re. 3e said earlier that with
roced!res that yo! need to secify their tye' this isn&t strictly tr!e. +his is beca!se a arameter in
a roced!re is give an $% tye by defa!lt therefore it is ossible to not secify the tye if it&s going to
be an $% tye' however its is always recommended to be as descritive as ossible when coding. 3e
also said that there are only two tyes of arameters' this is also !ntr!e as with roced!res yo! can
define a arameter that is both an $% and &'( arameter. Lets have a look at !sing a arameter
witho!t assigning a tye and one that is an $%&'( tye.
//
create procedure helloprocedure(param1 VARCHAR(100),INOUT param2
VARCHAR(100))
begin
set param2 = concat(param1,' ',param2);
end
//
helloroced!reE.my
So we have created the roced!re' now we need to call it. -nlike the f!nction we will be ret!rning a
val!e from the arameter therefore we can&t simly !se a literal string val!e. 3e need to create a
!ser variable' again !nlike revio!s e1amles we need to create this first as we need to reo!late
it. +his is done !sing the )*( statement in the same way we assign val!es to variables. Lets do that
now and then call the roced!re.
set @a = 'World'
//
call helloprocedure('Hello',@a)
//
select @a //
+-------------+
| @a |
+-------------+
| Hello World |
+-------------+
3e created the !ser variable 2a and set its val!e' this was then assed into the roced!re along
with another arameter' these were combined and laced into o!r second arameter' this was then
assed o!t of the roced!re and we were able to see its val!e !sing a select statement.
3e looked revio!sly at calling roced!res and f!nctions from within other f!nctions and roced!res.
#nything we can do from the command line when calling rogram !nits we can also do when calling
them from within other rogram !nits' lets test it o!t. 0irst we will create a f!nction which accets
a string arameter and ret!rns that string reversed' then call this from within a roced!re and ret!rn
the res!lt !sing an o!t arameter.
drop function if exists reverseit
//
create function reverseit(param1 varchar(100)) returns varchar(100)
begin
return reverse(param1);
end
//
drop procedure if exists callreversefunc
//
create procedure callreversefunc(INOUT param1 VARCHAR(100))
begin
set param1 = reverseit(param1);
end
//
set @a = 'Hello World'
//
call callreversefunc(@a)
//
+-------------+
| @a |
+-------------+
| dlroW olleH |
+-------------+
arametertest.my
(ne thing to note' in some lang!ages the comiler will not allow yo! to create f!nctions or
roced!res if any rograms yo! call within them are not resent' the MySQL comiler doesn&t seem to
have this restriction at resent' b!t the scrit was written so that the f!nction being called by o!r
main roced!re was created first.
3e have seen in this section how to create arameters' ass val!es into rograms !sing them and
also ass val!es o!t. 3e will !se arameters in later rograms to allow !s to make o!r rograms
more dynamic.
Characteristics
3hen creating stored ro!tines we can secify a n!mber of characteristics which allow !s to tell
MySQL some imortant information with regards to how the roced!re will f!nction. 4!rrently a most
of these characteristics are for f!t!re s!ort only. +he fo!r we can c!rrently secify are as follows
L#N5-#57 SQL
HN(+I )7+76MINIS+I4
SQL S74-6I+; J)70IN76 K INL(A76M
4(MM7N+ &string&
-anguage
In the f!t!re it will be ossible to write stored ro!tines not ,!st in #NSI SQL b!t also in a n!mber of
other different rogramming lang!ages. MySQL have highlighted "%" in artic!lar and (racles "L?SQL
has been mentioned on a n!mber of websites. +his will oen stored roced!re rogramming to
develoers who have skills in other rogramming lang!ages. $!t for now we are limited to #NSI SQL
therefore we can only !se the S5- characteristic like so.
drop function helloworld
//
create function helloworld() returns varchar(20) language SQL
//
characteristic1.my
#s yo! can sell all that needs to be done is to secify the characteristic after the initial create
f!nction command. 4!rrently SQL is the only s!orted val!e for the lang!age characteristic' yo! do
not need to secify this as it is the defa!lt val!e and is likely to be when new lang!ages become
s!orted.
+eter0inistic
+he deterministic characteristic is !sed only within f!nctions. # f!nction is deterministic if it ret!rns
the same val!e each time for the same set of arameters. +his will allow the otimi<er to make
decisions on how to handle the f!nction and make imrovements in seed based on this information.
3e can secifiy this characteristic like so.
//
create function helloworld() returns varchar(20) deterministic
//
characteristic*.my
+here is little oint in !sing this characteristic at resent however as the otimi<er c!rrently ignores
it and is only incl!ded for f!t!re comatibility.
S5- Security
+his is ossibly the most imortant of the characteristics at resent. +he SQL Sec!rity characteristic
can be set to either de6iner or invo4er. +his means that when the roced!re is r!n any SQL
statments will r!n against the sec!rity ermissions of either the definer 8the !ser who created the
stored ro!tine9 or the invoker 8the !ser calling the stored ro!tine9. +his is set like so.
//
create function helloworld() returns varchar(20) sql security definer
//
characteristicD.my
C&11'T
+he final characteristic is co00ent. #s the name s!ggests the comment charactersitic is !sed to add
a comment to the f!nction. +his comment is in the form of a string enclosed in 2!ote marks. +his is
done like so.
//
create function helloworld() returns varchar(20) comment 'This is a function
to return the string helloworld'
//
characteristicE.my
+he comment can seen when !sing the show command as we will see in the ne1t section. In addition
to adding a ro!tine level comment yo! can also added inline comments. +hese can be either a single
line comment or m!ltile lines. +o !se a single line comment we !se two N characters and to !se a
m!ltile line comment we lace the comment between ?O and O?. +his is done like so
//
create function helloworld() returns varchar(20) comment 'This is a function
to return the string helloworld'
begin
-- this is a single line comment
/* this is a multiple
line comment
*/
end
//
1etadata
3ith release of version ../.* MySQL now has addition methods for retrieving information abo!t stored
ro!tines. ;o! can !se SH&W as yo! have been able to with tables b!t also the new information schema
tables
SH&W
If yo! have revio!s e1erience of !sing MySQL yo! may already have !sed )H&+ to dislay information
abo!t the MySQL set! or tables within the database. ;o! can !se )H&+ to get information abo!t
f!nctions and roced!res yo! have created.
+he first way to !se it is to show the create statement' this is good if yo! have lost the so!rce file yo!
!sed to create the rogram or if yo! created it directly in MySQL. +he synta1 to !se sho$ create is as
follows.
SHOW CREATE {function / procedure} sp_name
3e can try this on one of the f!nctions we have created.
show create function helloworld //
+------------+----------
+------------------------------------------------------------------------------
| Function | sql_mode | Create Function
+------------+----------
+------------------------------------------------------------------------------
| helloworld | | CREATE FUNCTION `pers`.`helloworld`(param1
varchar(100),param2 varchar(100))
| | | RETURNS varchar(100)
| | | begin
| | |
| | | return CONCAT(param1,' ',param2) ;
| | |
| | | end
+------------+----------
+------------------------------------------------------------------------------
#nd the same for a roced!re.
show create procedure helloprocedure //
+----------------+----------
+---------------------------------------------------------------------------
| Procedure | sql_mode | Create Procedure
+----------------+----------
+---------------------------------------------------------------------------
| helloprocedure | | CREATE PROCEDURE `pers`.`helloprocedure`
| | | (param1 VARCHAR(100),INOUT param2
VARCHAR(100))
| | | begin
| | |
| | | set param2 = concat(param1,' ',param2);
| | |
| | | end
+----------------+----------
+---------------------------------------------------------------------------
Sho$ Status
+he second !se of )H&+ with roced!res and f!nctions is )H&+ )(A('). +his dislays vario!s
characteristics of the rogram s!ch as its name' tye and creation and modification dates.+he synta1 is as
follows.
SHOW {function / procedure} STATUS [LIKE 'pattern']
Ho$ever when we tried to get this working the server ket crashing. +his is another reminder that were
working with very new technologies here. %oef!lly this will be fi1ed in a f!t!re release' the MySQL
doc!mentation does show !s what we sho!ld e1ect to see.
("date I tried this show synta1 on Mac (S : and it erformed as doc!mented' the crash we reorted was
on the 3indows :" version of MySQL. So if yo!r !sing anything other than :" lease try the synta1 and let
!s know how yo! get on.
SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
Db: test
Name: hello
Type: FUNCTION
Definer: testuser@localhost
Modified: 2004-08-03 15:29:37
Created: 2004-08-03 15:29:37
Security_type: DEFINER
Comment:
In6or0ation Sche0a
+he information schema is available in version ../.* and ! of MySQL. +he schema allows !s to view
Metadata abo!t ob,ects within MySQL s!ch as +ables' col!mns' rivileges and of more relevance to !s
store ro!tines. Metadata is commonly referred to data abo!t data' if yo! can 2!ery tables within MySQL
then yo! will be able to !se the Information Schema to give yo! information on yo!r stored ro!tines.
3e will limit o!rselves to talk abo!t stored ro!tines b!t yo! may want to look at the MySQL
doc!mentation for details on what&s available for other database ob,ects.
+here are a n!mber of col!mns we can select from the information schema' these are as follows.
SPECIFIC_NAME
ROUTINE_CATALOG
ROUTINE_SCHEMA
ROUTINE_NAME
ROUTINE_TYPE
DTD_IDENTIFIER
ROUTINE_BODY
ROUTINE_DEFINITION
EXTERNAL_NAME
EXTERNAL_LANGUAGE
PARAMETER_STYLE
IS_DETERMINISTIC
SQL_DATA_ACCESS
SQL_PATH
SECURITY_TYPE
CREATED
LAST_ALTERED
SQL_MODE
ROUTINE_COMMENT
DEFINER
;o! can !se any combination of these col!mns to e1tract information abo!t a secific ro!tine' a gro! of
ro!tines or all ro!tines within the database !sing standard SQL statements.
0or e1amle if we wanted to see a list of all the ro!tines in the system we co!ld !se the following SQL.
select routine_name from information_schema.routines //
+--------------+
| routine_name |
+--------------+
| calcsalary |
| calcsalary |
| getage |
| isemployee |
| ismember |
+--------------+
+his lists all of the ro!tines in the system' however we can see two ro!tines with the same name. It might
be better if we can see the database the ro!tines belongs too and also which tye of ro!tine it is as there
is no distinction in this list between roced!res and f!nctions.
select routine_name, routine_schema, routine_type from
information_schema.routines//
+--------------+----------------+--------------+
| routine_name | routine_schema | routine_type |
+--------------+----------------+--------------+
| calcsalary | payroll | PROCEDURE |
| calcsalary | pers | PROCEDURE |
| getage | pers | PROCEDURE |
| isemployee | pers | FUNCTION |
| ismember | pers | PROCEDURE |
+--------------+----------------+--------------+
+his time we can see the distinct between the different ro!tines. 3e might want to see only the ro!tines
for the database we have c!rrently selected. +o do this we can !se a where statement and incl!de a call
to the data*ase78 f!nction like so.
select routine_name, routine_schema, routine_type from
information_schema.routines where routine_schema = database() //
+--------------+----------------+--------------+
| routine_name | routine_schema | routine_type |
+--------------+----------------+--------------+
+--------------+----------------+--------------+
+his time we can see ,!st the ro!tines for the c!rrently selected database.
3e can incl!de as many col!mns as needed and !se standard SQL where cla!se synta1 to select only the
secific rows we need. 3e will leave it ! to yo! to discover what the informationCschema.ro!tines table
can offer yo! as f!rther disc!ssion will likely become reetitive b!t this short introd!ction does give an
indication of the !sef!lness.
0ys9l."roc
+he final way to retrieve M7+#)#+# for stored roced!res is to !se the mys2l.roc table. +his table
f!nctions in the same way as the informationCschema b!t contains slightly less information. +he col!mn
names for mys2l.roc are also different to those !sed in the information schema.
db
name
type
specific_name
language
sql_data_access
is_deterministic
security_type
param_list
returns
body
definer
created
modified
sql_mode
comment
)ata is selected from mys2l.roc in the same way as informationCschema and has the same fle1ibility with
regards to standard SQL synta1.
select name, db, type from mysql.proc //
+------------+---------+-----------+
| name | db | type |
+------------+---------+-----------+
| calcsalary | payroll | PROCEDURE |
+------------+---------+-----------+
3hich method to !se is at yo!r discretion' informationCschema is #NSI standard and yo! may have !sed it
with other databases which s!ort it s!ch as SQL Server.
So far we have looked at the basics of how to constr!ct stored roced!res within MySQL' ne1t we will be
looking at some more comle1 concets within the lang!age which wo!ld ideally be laced at the end of
o!r t!torials b!t they are imortant to !nderstand for a very !sef!l art of database rogramming.
It may be worth reviewing what we have learnt so far before going on to the ne1t section. "erhas !sing
the knowledge yo! have gained so far yo! co!ld create some of yo!r own f!nctions and roced!res.
Handlers
So far the things we have looked at have been relatively simle. +he f!nctions and roced!res we
have written have been retty !seless really. +o make them more !sef!l it wo!ld be good if we co!ld
interact with the database more. 3e will be looking at how to do this soon b!t before we do this we
need to look at handlers. +he reason for this is that one of the main methods !se to interact with
the database is c!rsors and they !se handlers in their rocessing.
So what are handlers' as the name s!ggests they are methods of handling conditions which need to
be dealt with. +hese conditions are slit into the following gro!s.
S5-STAT
conditionCname
S5-WA/'I'G
'&T %&('+
S5-:CPTI&'
mys2lCerrorCcode
+hese gro!s all relate to sit!ations where' for one reason or another' MySQL has decided to give !s
feedback abo!t the rocessing of a f!nction' roced!re or SQL statement. Its likely yo! will have
enco!ntered them before witho!t noticing.
$efore we look at handling lets have a go at trying to rod!ce some errors. 3e can !se the tables we
set! at the start of the t!torials.
insert into emps values(1,'Dave',1);
ERROR 1062 (23000): Duplicate entry '1' for key 1
So here we can see MySQL rod!ced an error' in this case 766(6 1/6*. +he n!mber between the
brackets is the SQLS+#+7 which can be the same for a n!mber of errors. +his can be seen here.
insert into emps values(NULL,'Dave',1);
ERROR 1048 (23000): Column 'emp_id' cannot be null
+his time we have a different error n!mber 81/EP in this case9 b!t the same SQLS+#+7. 3hen these
errors occ!r in o!r roced!res and f!nctions they will terminate o!r rograms' what we wo!ld
rather haen is for the rogram to deal with the error and contin!e rocessing or end more
gracef!lly.
So lets look at how we create a handler to deal with these conditions. +he synta1 is as follows.
+C-A/ handler_tpe HA'+-/ %&/ condition_value[,...] sp_statement
0irst we !se ,*C-AR* as we did with variable to tell the comiler that we are creating a handler'
ne1t is the handler tye this can be one of the following C&%($%'*' *.$( or '%,&. +his is what the
rogram will do when the condition is met. So if we !se C&%($%'* the rogram will carry on
rocess after the handler has been called' if we !se *.$( the rogram will end immediately. +he
final tye '%,& is c!rrently not s!orted b!t this will be !sed on transactional tables to rollback
work carried o!t ! to that oint. HA%,-*R /&R is simly telling the comiler that we are declaring
a handler. +he condition_value will be one of the tyes we mentioned earlier' SQLS+#+7'
SQL3#6NIN5 etc. +his is !sed so that the handler only fires when a secific condition is met. 3e can
define more than one condition at the same time if we want to handle the condition in the same
way. 0inally we have sp_statement this is a short section of code which will r!n when the handler is
fired. +his wo!ld normally be a )*( statement.
So lets constr!ct a simle e1amle and see how it works. 3e can try and deal with o!r d!licate
entry roblem. 0irst lets create a roced!re that doesn&t handle the error and see what haens.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
insert into emps VALUES (1,'Dave',1,10);
set p_end := 'The End';
end;
//
handlerroc1.my
(A so we will be e1ecting the roced!re to r!n the insert statement and then assing o!t a
arameter with the words &+he 7nd&. Lets call it and see what haens.
call handlerproc(@a) //
ERROR 1062 (23000): Duplicate entry '1' for key 1
select @a
//
+------+
| @a |
+------+
| NULL |
+------+
3e got the error message b!t o!r arameter is emty. +his is beca!se the error stoed o!r
roced!re dead. In most cases we wo!ldn&t want that to haen' so we need to !se a handler to
deal with the error. Lets add in a handler to the roced!re and see what haens then.
//
begin
declare continue handler for sqlstate '23000' SET @b = 'With Errors';
insert into emps VALUES (1,'Dave',1,10) ;
set p_end := 'The End';
end;
//
call handlerproc(@a)
//
select @a
//
+---------+
| @a |
+---------+
| The End |
+---------+
handlerroc*.my
3e can see that this time we didn&t get the error message and o!r arameter assed o!t the val!e.
+hats beca!se when the error occ!rred the handler took over dealt with the roblem and contin!ed
rocessing the roced!re. ;o! may have noticed that we also had an Qb variable' this was S7+ to
&3ith 7rrors& when the handler was fired b!t we did nothing with it. 3e co!ld have added this to the
arameter to show that while the rogram got to the end there was an error. Lets try that with o!r
rogram.
//
begin
declare continue handler for sqlstate '23000' SET @b = '- With Errors';
set p_end := concat('The End ',@b);
end;
//
call handlerproc(@a)
//
select @a
//
+-----------------------+
| @a |
+-----------------------+
| The End - With Errors |
+-----------------------+
handlerrocD.my
+his time the rogram handled the error and we o!t!t the res!lt of the SQL that was r!n when the
handler was called.
So we have been !sing a handler to deal with SQLS+#+7. +his will deal with a set of different errors
b!t we might have a sit!ation where we wo!ld want to deal with different conditions which are
gro!ed !nder the same SQL+#+7 in a different way. +ake for e1amle the sit!ation we looked at
earlier' we had * errors which had the same SQLS+#+7 b!t different error n!mbers. 3e can add *
handlers to the roced!re to deal with both roblems.
//
begin
declare continue handler for 1062 SET @b = '- With Error 1062';
end;
//
handlerrocE.my
Lets see what the o!t!t rod!ced by this roced!re.
call handlerproc(@out) //
select @out //
+---------------------------+
| @out |
+---------------------------+
| The End - With Error 1062 |
+---------------------------+
3e can easily show what wo!ld haen if we changed the roced!re so that it rod!ced the other
error.
//
begin
insert into emps VALUES (NULL,'Dave',1,10) ;
end;
//
call handlerproc(@out) //
select @out //
+---------------------------+
| @out |
+---------------------------+
| The End - With Error 1048 |
+---------------------------+
handlerroc..my
So we have looked at SQLS+#+7 and secific error messages' lets now look at the remaining tyes of
conditions that we can handle.
3e can !se SQL3#6NIN5 to handle all SQLS+#+7 codes that begin with /1 or N(+ 0(-N) is for all
SQLS+#+7 codes that begin with /*. SQL7:47"+I(N will handle all SQLS+#+7 codes not ca!ght by
SQL3#6NIN5 or N(+ 0(-N).
# list of error messages can be fo!nd here.
+he final tye of condition we can handle are !ser defined conditions. +hese are conditions that we
can define o!rselves. 3e will be looking at those in the ne1t section.
("date
# recent 2!estion in the MySQL develoer for!ms romted me to think what wo!ld haen if we
declared a n!mber of handlers to deal with errors at the SQLS+#+7 level and also at the error
message level. +ake for e1amle if we had a roced!re which co!ld res!lt in a n!mber of error&s
being raised' we might want to deal with one in a artic!lar way b!t ,!st deal with the others !nder
a general SQLS+#+7 handler. I create a test roced!re to see what haens.
//
begin
declare continue handler for sqlstate '23000' SET @b = 'With SQLSTATE';
insert into emps VALUES (1,'Dave',1,10);
set p_end := concat('The End',@b);
end;
//
call handlerproc(@a) //
select @a //
+--------------------------+
| @a |
+--------------------------+
| The End- With Error 1062 |
+--------------------------+
handlerroc6.my
%ere we can see that we have declared two handlers to deal with a secific error 1/6* and
SQLS+#+7 *D///. ;o! may be aware that error 1/6* is contained within SQLS+#+7 *D//' however we
can see from the res!lting SQL that in this case the handler to fire was the one declared to deal with
the error code. I swaed the osition of the handlers to test if it was d!e to the osition of the
handlers b!t it seems that this irrelevant.
ConditionsWe saw in the last section how we can handle various conditions that may appear
in our processing. We looked at how these conditions are grouped and how to handle
those groups.
In addition to the standard gro!s MySQL allows !s to define o!r own named conditions. %owever
these conditions may only be linked to SQLS+#+7 val!es or mys2lCerrorCcodes. +his makes
conditions at the resent time rather limited and in fact rather ointless. +he latest MySQL
doc!mentation has very little to say on the matter and doesn&t give any indication if conditions will
be e1anded in the f!t!re.
+he synta1 for creating a condition is as follows..
+C-A/ condition_name C&'+ITI&' %&/ condition_value
#s mentioned before the doc!mentation on conditions is c!rrently very limited so we m!st make a
few ass!mtions abo!t what we can and can&t do. condition_name for e1amle sho!ld conform to
the standard ob,ect naming r!les within MySQL' it&s best to stick to alhan!meric characters and
kee it short b!t reasonably descritive. conditon_value on the other hand is strictly controlled
and sho!ld be either S5-STAT followed by the aroriate SQLS+#+7 code or a mys2l error code
Lets have a look at how we create a condition and how we !se it within a handler.
drop procedure if exists conditionproc
//
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';
end;
//
conditionroc1.my
%ere we have added a line to declare o!r condition' in o!r case we are going to handle any
conditions which res!lt from SQLS+#+7 *D///. 3e have given it a name' not;null which allows !s to
identify it in the handler. Lets try and r!n it and see what we get.
call conditionproc(@a) //
select @a //
+-------------------------------+
| @a |
+-------------------------------+
| The End - With not_null Error |
+-------------------------------+
3e co!ld alternatively !se an error code like so.
drop procedure if exists conditionproc
//
create procedure conditionproc(OUT p_end VARCHAR(10))
begin
declare not_null condition for 1048;
declare continue handler for not_null SET @b = '- With not_null Error';
end;
//
call conditionproc(@a) //
select @a //
+-------------------------------+
| @a |
+-------------------------------+
| The End - With not_null Error |
+-------------------------------+
conditionroc*.my
#s yo! can see declaring and !sing conditions is fairly easy' however c!rrently there isn&t really a
great deal to be gained by doing so. +he only reason I can see yo! might want to !se them is so that
the code is a little easier to read for somebody who&s not familiar with it. 0or e1amle they might
not know that error code 1/EP is &4ol!mn 1111 cannot be n!ll& so !sing a condition we can make it a
little clearer what the handler is act!ally dealing with. %aving said that yo! co!ld ,!st as easily add
a comment before the handler.
3e have taken a fairly s!bstantial deto!r over the last * sections to look at something fairly
comle1' b!t it is imortant to know abo!t %andlers at a basic level when looking at one of o!r
f!t!re sections. Lets move on to how we can !se stored roced!res to access the database tables
and ret!rn information to !s.
Select Into
So far o!r rograms have done little more than ret!rn static strings. 3e have seen that we can call
s2l statements from within roced!res which dislays the res!lts on the command line' b!t what
wo!ld be more !sef!l wo!ld be if we co!ld select data from tables within the database and !se them
within the roced!re.
So far we haven&t really !se the tables we have set ! b!t in this section we will be !sing them more
and more' so if haven&t set them ! yet now wo!ld be a good time.
set!.my
;o! may remember we looked at variables in an earlier section' at the time we ,!st laced static
information in them b!t we can also select val!es from a table into them. 3e do this !sing the select
into synta1. Lets create a simle roced!re to do ,!st that.
drop procedure if exists selectintoproc
//
create procedure selectintoproc(OUT p_out VARCHAR(10))
begin
declare l_emp_name varchar(30);
select emp_name into l_emp_name from emps;
set p_out := l_emp_name;
end;
//
selectintoroc1.my
Lets r!n this and see what we get.
call selectintoproc(@a) //
ERROR 1172 (42000): Result consisted of more than one row
(os what haened thereG 3e got an error message beca!se we tried to lace the entire contents
of the ems table into a single variable. ;o! may have noticed that the ems table has D records in it
and I&m s!re yo! know that D into 1 ,!st won&t go.
+here are a few ways we can get ro!nd this' we co!ld aly what we learnt in the revio!s sections
and create a handler to deal with this b!t in that case the roced!re wo!ld be !seless as we wo!ldn&t
get o!r variable o!lated. 3e co!ld !se a where cla!se to limit the amo!nt of rows ret!rned and
this wo!ld be the best method' however we will wait !ntil the ne1t section to look at this. 0or now
we will !se the li0it keyword. +his will r!n the SQL statement b!t then ret!rn only the n!mber of
rows we secify' in o!r case 1. Lets try that now.
//
begin
declare l_emp_name varchar(30);
select emp_name into l_emp_name from emps limit 1;
set p_out := l_emp_name;
end;
//
select @a //
+-------+
| @a |
+-------+
| Roger |
+-------+
selectintoroc*.my
So as yo! can see o!r roced!re now comletes s!ccessf!lly and we can see the res!lt of the select.
3e can select any n!mber of col!mns from a table or in fact tables at once.
//
begin
declare l_emp_name, l_dept_id varchar(30);
select emp_name, dept_id into l_emp_name, l_dept_id from emps limit 1;
set p_out := concat(l_emp_name,' ',l_dept_id);
end;
//
select @a //
+---------+
| @a |
+---------+
| Roger 1 |
+---------+
selectintorocD.my
//
begin
declare l_emp_name, l_dept_name varchar(30);
select emp_name, description into l_emp_name, l_dept_name
from emps, dept
where emps.dept_id = dept.dept_id
limit 1;
set p_out := concat(l_emp_name,' ',l_dept_name);
end;
//
selectintorocE.my
;o! can see from these * e1amles how easy it is to select data from tables and lace them in
variables !sing select into. (ne thing to be aware of is that we need to try and make s!re that the
variable is the same tye as the val!e in the col!mn we want to o!late it with. In some cases
MySQL will convert the val!e into the same data tye as the variable b!t this will some times ca!se
the roced!re to ret!rn incorrect information. 0or e1amle if yo! try and lace a alha character
into a n!mber variable.
It may be that yo!r roced!re is simle eno!gh that it doesn&t need to !se a variable. In this case we
can assign the val!e directly to an o!t arameter.
//
begin
select emp_name into p_out from emps limit 1;
end;
//
select @a //
+-------+
| @a |
+-------+
| Roger |
+-------+
selectintoroc..my
3e have now started to see how we can interact with the data within MySQL !sing roced!res' b!t
what wo!ld be more !sef!l is if we co!ld alter the select statement on the fly to give !s different
res!lts deending on a arameter we have assed in. 3e will be doing ,!st that in o!r ne1t
section....
Where Clause
In o!r last section Select Into we had a sit!ation where the roced!re failed beca!se we tried to fit
the entire res!lt set into a single variable. 3e said that there were a few ways to deal with this and
one of those was to !se a where cla!se. ;o! may already be familiar with how to !se a where cla!se
in an SQL statement and it is done in e1actly the same way within a roced!re.
drop procedure if exists whereclauseproc
//
create procedure whereclauseproc(OUT p_out VARCHAR(30))
begin
select emp_name into p_out from emps where emp_id = 1;
end
//
whereroc1.my
%owever what we can do within a roced!re is change the where cla!se dynamically based on the
val!e of a arameter we ass in. Lets say we wanted to be able to ass in the emCid and get the
emloyees name.
drop procedure if exists whereclauseproc
//
create procedure whereclauseproc(IN p_in INT, OUT p_out VARCHAR(30))
begin
select emp_name into p_out from emps where emp_id = p_in;
end
//
whereroc*.my
+his time we added an I' arameter to accet the emCid we wanted to !se. +his was then !sed in
the where cla!se instead of a hard coded val!e. +o call the roced!re we need to first set a !ser
variable and ass that into the roced!re.
set @a = 2 //
call whereclauseproc(@a,@b) //
select @b //
+------+
| @b |
+------+
| John |
+------+
0irstly we set the !ser variable 2a to *' the emCid we wanted to find the name for. 3e then called
the roced!re !sing 2a and 2* 8o!r o!t arameter9 this ret!rned the res!lt into Qb which we then
selected to see the val!e. +he ne1t thing to do wo!ld be to try and r!n the roced!re !sing a
different emCid to make s!re the roced!re really does ret!rn a different val!e based on the in
arameter.
set @a = 3 //
call whereclauseproc(@a,@b) //
select @b //
+------+
| @b |
+------+
| Alan |
+------+
So we can see that !sing a different in arameter ret!rns different rest!lts. #t this stage we won&t
get into a disc!ssion of sec!rity iss!es b!t this does give yo! some ideas abo!t how yo! can make
yo!r MySQL data more sec!re' at the moment yo! may giving select access to !sers to sto them
changing data' !sing roced!res more normally f!nctions we can add another layer of sec!rity. 3e
can limit !sers to secific col!mns or even s!b sets of col!mns by only giving them access to the
roced!res rather than the !nderlying tables. %owever this method of sec!rity has been addressed
within version . of MySQL with the introd!ction of views.
3e have digressed again so lets get back to rogramming stored roced!res. +here really isn&t a great
deal more to say abo!t where cla!ses at resent' we have introd!ced the concet and hoef!lly yo!
can see how yo! might !se them to filter data. 3e sho!ld oint o!t we have been !sing arameters
directly in o!r e1amles b!t yo! co!ld ,!st as well !se variables in the where cla!se.
Cursors
If yo! have rogrammed !sing stored roced!res in other database technologies yo! may be familiar
with c!rsors. %owever c!rsors within MySQL are fairly simle and don&t have the f!ll f!nctionality that
other lang!ages offer. %owever thats not to say they are of no !se as we shall see.
0or those of haven&t !sed c!rsors before a brief introd!ction is necessary. 4!rsors are essentially a
named SQL statement which we can define in o!r roced!res. 3e can then easily look at the contents
of the c!rsor by fetching the records from it. Its a way for !s to get information from o!r tables in
one big ch!nk and then work on it. #s always there is no better way to find o!t abo!t them than
act!ally doing one b!t beca!se c!rsors re2!ire a little more code than things we have looked at
revio!sly we will need to look at the vario!s stages before writing a roced!re which !ses c!rsors.
+eclaring Cursors
#s with variables' conditions and handlers we need to declare a c!rsor before we can !se it. +he
synta1 for this is as follows.
+C-A/ cursor_name C(/S&/ %&/ select_statementF
+he first thing we need to do is give the c!rsor a name' this is how we will refer to it later in the
roced!re. 3e can have more than one c!rsor in a single roced!re so its imortant to give it a name
that will in some way tell !s what its doing. 3e then need to secify the select statement we want to
associate with the c!rsor. +he SQL statement can be any valid SQL statement and it is ossible to !se
a dynamic where cla!se !sing variable or arameters as we have seen revio!sly.
&"en3 %etch and Close
(nce the c!rsor has been declared we can then !se it. +he first thing we need to do is oen the
c!rsor. +he synta1 to do so is very simle.
&P' cursor_name;
+he c!rsor name is simly the name of the c!rsor that yo! defined earlier. (nce oened we can then
fetch val!es from the c!rsor. +o do this we need to have set ! variables to hold the val!es' we need
one variable er col!mn defined in o!r c!rsor select statement' we will look at this in more detail in
few moments. +he synta1 for fetch is as follows.
%TCH cursor_name I'T& var_name [, var_name] ...
#gain cursor_name is the name of the c!rsor we want to fetch from' var_name is the name of the
variable we have defined to accet the val!e. (nce we have fetched the rows we need we then need
to close the c!rsor. +he synta1 for this is as simle as oening the c!rsor.
C-&S cursor_name
#gain cursor_name being the name of the c!rsor we wish to close.
So we are now at the oint where we can define a simle c!rsor' so lets do ,!st that.
drop procedure if exists cursorproc
//
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;
//
c!rsorroc1.my
%ere we have created a very basic c!rsor. 3e declared the c!rsor and gave it a name of c!rC1. 3e
then defined the SQL statement we wanted to !se' in this case we !sed a where cla!se !sing an in
arameter. 3e then oened the c!rsor fetched the single col!mn val!e into a variable then closed
the c!rsor. 0inally we assed the val!e back o!t. Lets see it in action.
set @a = 1 //
call cursorproc(@a,@b) //
select @b //
+-------+
| @b |
+-------+
| Roger |
+-------+
(!r c!rsor ret!rned a val!e in the same way a select into wo!ld. Its !nlikely that yo! wo!ld !se a
c!rsor like this. 4!rsors are !sef!l for rocessing m!ltile rows rather than single val!es. +o do this
we need to add some more code to o!r roced!re to handle the rocessing of the c!rsor.
//
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;
//
c!rsorroc*.my
(!r roced!re has become a lot more comlicated and needs a lot of e1lanation. ;o! will be able to
see a n!mber of line of code which we haven&t come across before. 3e won&t dwell on those too m!ch
for now as we will be looking at them in detail in later sessions.
+he first thing we have done is declare D variables' the first lClooCend is in effect a boolean variable
8tr!e or false9. +his is imlemented in MySQL !sing an I'T variable where / is false and 1 is tr!e' this
variable will hel !s find o!t when we come to the end of the c!rsor. 3e then declare * decimal
val!es and these will be !sed to accet the val!e from the c!rsor d!ring the fetch and to kee a
r!nning total. 3e then declare the c!rsor in m!ch the same way as we did before the main difference
being we are selecting the salary val!e and that we don&t have a where cla!se so we will get the f!ll
table. Ne1t we declare a handler' this is why we looked at handlers and conditions before we dealt
with c!rsors. +he handler will be dealing with the S5-STAT /*/// which is raised when there is no
f!rther data to be fetched' this will allow !s to catch the event and handle it in a controlled manner.
3e then oen the c!rsor' set the val!e in lCtotal to / and then !se the re"eat keyword. 3e will be
looking at re"eat in a later session b!t for now it&s eno!gh to know that it simly reeats the code
between reeat and end reeat !ntil an e1it condition is fo!nd. 3e then fetch the val!e from the
c!rsor into the variable' its at this stage that we may enco!nter SQLS+#+7 /*//' if we have the
SQLS+#+7 associated with the handler it will fire and lClooCend will be set to 1 8tr!e9. 3e then !se
an i6 statement. I6 allows !s to aly conditional logic to the rogram' again we will be looking at this
in more detail' for the moment we ,!st need to know that if lClooCend is false 8SQLS+#+7 /*// hasn&t
been raised9 we will add lCsalary to the r!nning total. 3e then get to until' as we mentioned earlier
this is linked to the reeat' at this stage it checks to see if lClooCend is tr!e' if it is then the reeat
will be stoed if not it will reeat the code again.
In o!r e1amle we wo!ld e1ect the reeat to be erformed D times as we have D rows in o!r ems
table. +his will res!lt in the lCtotal variable adding the salary from the D rows together. Lets r!n the
roced!re and see what we get.
call cursorproc(@a) //
mysql> select @a //
+------+
| @a |
+------+
| 6600 |
+------+
+his is another case where we co!ld have simly done this !sing an SQL statement in the database'
b!t as yo! have seen c!rsors are a little comlicated and this was a simle e1amle so that yo! co!ld
get the basic synta1 and f!nctionality witho!t being too overwhelmed.
MySQL&s imlementation of c!rsors is a little limited at resent and does not offer the e1tended
f!nctionality of lang!ages s!ch as +NSQL and "L?SQL' b!t they are an imortant art of stored
roced!re develoment and hoef!lly in f!t!re releases #$ will e1tend the f!nctionality of c!rsors. #
few additional oints to note abo!t c!rsors within MySQL' they are nonNscrolling' read only and
asensitive. NonNscrolling means that we can only go thro!gh a c!rsor from to to bottom' we can&t go
to a artic!lar row or go back a row. #sensitive means that the server may or may not make coy of
the res!lts table.
3e will move on now as f!rther talk of c!rsors may become conf!sing witho!t knowing a little more
abo!t conditional logic. 3e will ret!rn to c!rsors a little later.
I6 State0ents
So far we have taken a rather straight forward aroach to the roced!res' with the e1cetion of
the more comlicated c!rsor roced!re o!r rograms have simly started at the to' e1ec!ted all
of the lines of code and then finished at the bottom. Its also tr!e that so far most if not all of o!r
rograms co!ld have easily been written as straight SQL calls.
"retty m!ch all rogramming lang!ages !se $/ statements to control the flow of the rogram or to
allow the develoer to !se different sections of code based on a certain criteria. Lets create the
most simle 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
//
iff!nction1.my
+his simle f!nction shows !s how we can !se a basic $/ statement. 0irst we ass in a arameter'
declare a varchar variable' then we !se i6 to determine if the arameter we assed is e2!al to #' if
it is e2!al to # the code between the $/ and *%, $/ will be called' if it isn&t e2!al to # the rogram
ignores this code. Lets r!n the f!nction a co!le of times and see what haens.
select iffunction('A') //
+-----------------+
| iffunction('A') |
+-----------------+
| This was A |
+-----------------+
select iffunction('B') //
+-----------------+
| iffunction('B') |
+-----------------+
| Not A |
+-----------------+
So we can see we get different res!lts deending on which arameter we ass. "assing # res!lts in
the additional code r!nning and therefore the variable is changed. 3hen we called it with $ the
code was not called and the variable was not changed.
0or an I% to r!n the eval!ation condition 8the code between I0 and +%7N9 m!st eval!ate to tr!e.
+his eval!ation can be as comlicated or as simle as yo! re2!ire so long as it eval!ates to tr!e or
false.
I0 1R1 +%7N
I0 8lCval!e = 1/9 #N) 8lCerror >= &//1&9 +%7N
I0 lCboolean +%7N
#re all of these e1amle are valid 8so long as lCboolean is a boolean val!e9.
7lse
In o!r first e1amle rogram it was simly a case of if the arameter is # r!n the code' we didn&t
have the ability to r!n something else if it wasn&t. 3e co!ld simly add two if statements to handle
this like so.
set l_value = 'This is A';
end if;
if p_inparam <> 'A' then
set l_value = 'Not A';
end if;
+his wo!ld solve o!r roblem b!t its not a artic!larly elegant or for that matter efficient. # better
sol!tion is to !se *-)*. +his allows !s to do one thing if the condition is met b!t a second if it is not
met. Lets try a simle e1amle to see how this works.
drop function if exists iffunction
//
begin
declare l_value VARCHAR(10);
else
set l_value := 'Not A';
end if;
return l_value;
end
//
select iffunction('A') //
+-----------------+
| iffunction('A') |
+-----------------+
| This was A |
+-----------------+
select iffunction('B') //
+-----------------+
| iffunction('B') |
+-----------------+
| Not A |
+-----------------+
iff!nction*.my
So yo! can see that all we have to do is add the else after the first statement so that the rogram
can deal with one or the other condition. $!t what if we wanted to eval!ate more than one
condition' lets say two' in this case we can !se an elseif. Lets see it in action.
//
begin
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
//
iff!nctionD.my
+his time we can see that we have an elseif condition between the if and else. 3e can add as many
of these as we need. %owever there is a another way to achieve the same f!nctionality and this is
the CA)* statement which we will be looking at in the ne1t section.
1utually !clusive Conditions
It&s not essential b!t in ractice all of yo!r if conditions sho!ld be m!t!ally e1cl!sive. If the val!e
or val!es yo! are eval!ating in the I0 and 7LS7I0 statements co!ld meet more than one of the
conditions then they are not m!t!ally e1cl!sive. 3hen rocessing the if statement the rogram will
sto 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 e1amle.
//
create function iffunction(p_inparam INT) returns VARCHAR(10)
begin
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 |
+-----------------+
iff!nctionE.my
In this block we can see that the arameter we ass will meet both the first and second condition.
$!t when it meets the first MySQL stos rocessing the rest of the code !ntil it meets the relevant
end i6.
'ested I%<s
+here may be sit!ations where we need to check a n!mber of conditions with some related
elements. In this case we co!ld write a n!mber of -SI% statements to meet all of the
re2!irements 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;
3hile this is erfectly accetable to the comiler and will work a m!ch more elegant sol!tion is to
!se nested I0 statements. #ll we need to do is call additional I0 statements from inside the original
I0 statements. So the above e1amle wo!ld become.
//
create function iffunction(p_comp VARCHAR(10),p_divi VARCHAR(10)) returns
VARCHAR(10)
begin
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
//
iff!nction..my
%ere we first check the val!e of Ccom and then once that has been determined we check the
val!e of Cdivi. In o!r small e1amle there are only a small n!mber of conditions b!t if we had a
more comle1 e1amle we wo!ld be saving val!able rocessing time doing it this way as we wo!ld
only need to check the minim!m amo!nt of conditions to get to o!r answer. 0or e1amle if we had
D val!es to check and 1/ otions for each we wo!ld have 1/// different ossibilities ' !sing the first
method there wo!ld be a ossibility that the rogram wo!ld need to check all 1/// before
eval!ating to +6-7. $!t !sing nested I0&s we red!ce that to a ma1im!m of D/. In addition to any
erformance gain it also makes it easier to !nderstand and add additional code later.
(ne additional thing yo! may have noticed in this last e1amle is we didn&t incl!de an 7LS7
statement. Its worth ointing o!t that yo! do not need to incl!de an 7LS7 if yo! do not re2!ire one'
its erfectly valid to check one' two or many conditions witho!t having to catch anything that
doesn&t meet one of those re2!irements.
In this section we have looked at a n!mber of different technics with regard to !sing I0 statements.
3e looked at basic I0 statements' e1tended them with 7LS7 and 7LS7I0 and finally looked at how
we can nest I0 statements to make o!r rograms more efficient and readable. 3e mentioned that
in many cases when !sing m!ltile 7LS7I0 statements we can !se 4#S7 instead so lets move on to
that now.
Case
3e saw in the last section how we can !se 7LS7I0 to link a n!mber of conditions together. 3hile
7LS7I0 is a erfectly accetable method we can also !se case. 4ase is very similar to an I0
statement in that it can be !sed to control the flow of the rogram deending on different
conditions. +here are two ways in which we can !se case.
CAS case_value
+he first method is to secify the so!rce of the comarison ! front. 3e already know how to !se
7LS7I0 so lets take that as a starting oint. Lets say we want to eval!ate 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;
#s yo! can see we are checking the same variable each time. S!rely it wo!ld be m!ch better if ,!st
told MySQL which val!e we wanted to check ! front and then ,!st give it what we want to
eval!ate each time. +his is what we can do with the CA)* statement. Lets look at how we might
convert to the above I0 7LS7 constr!ct into the e2!ivalent 4#S7.
drop function if exists casefunction
//
create function casefunction(p_comp VARCHAR(10)) returns VARCHAR(10)
begin
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
//
casef!nction1.my
So we can see here how to !se CAS. +he first thing is to !se the 4#S7 keyword then immediately
the val!e we want to eval!ate. 3e then iss!e a series of +H*% statements which check the val!e
secified against a criteria. If the condition is met the code after that when statement will be
e1ec!ted. #s with an I0 statement MySQL will sto comaring the val!es as soon as a match is
fo!nd.
+here a co!le of limitations to this style of 4#S7. 0irstly it limits yo! to a simle comarison
condition' yo! can only really comare one val!e with another and it does not allow comle1 and
m!ltile conditions. +his may not be a roblem and it sho!ld be noted that its a better sol!tion
than the m!ltile if statements we have seen revio!sly.It sho!ld be noted however than any
additional code can be incl!ded after the when statement. It wo!ld be ossible for e1amle to do
this simle comarison and then !se more comle1 I0 statements. Secondly it also means that the
comarison m!st contain the same val!e each time' we cannot mi1 and match as wo!ld be able to
!sing I0 and 7LS7I0.
CAS ...
+he second tye of case allows more comle1 conditions to be !sed. +his time we do not secify
anything ! front. 3e iss!e when statements and add the condition there. +his is done as follows.
drop function if exists casefunction
//
create function casefunction(p_comp VARCHAR(10),p_divi VARCHAR(10))
returns VARCHAR(10)
begin
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
//
casef!nction*.my
%ere we can see that each when statements has its own conditional statement. +his is more like
the I0 7LS7I0 style and erforms in the same manner. 3hich yo! choose 4#S7 8with or witho!t the
case_value assigned ! front9 or I0 7LS7I0 is ! to yo!. It wo!ld be recommended to !se I0 for
simle conditional rocessing as it is standard across rogramming environments b!t 4#S7 can be a
little easier to read.
-oo"
+here are three methods within MySQL to erform loos' the most simle of which is loo.
Warning B make s!re yo! tye any code in this session as acc!rately as ossible. It co!ld be
ossible to send yo!r rogram into an infinite loo which will e1ec!te forever. ;o! can of co!rse
terminate the MySQL server or reboot the machine b!t if yo!r doing this via yo!r web host they
might not be so hay abo!t it.
-sing loos we can e1ec!te a section of code a m!ltile n!mber of times. Loo is very simle to
!se and only re2!ires that yo! !se -&&P to secify the start of the loo and '+ -&&P to
terminate it. +he MySQL doc!mentation says that yo! can otionally incl!de a label for the loo'
b!t d!ring testing we fo!nd that it was imossible to create !sef!l loos witho!t them.
-A)
3e warned earlier that its ossible to send yo! rogram into an infinite loo' this is when the
rogram kees e1ec!ting over and over. +his haens beca!se when !sing loo we need to tell it
when to sto looing' in the case of infinite loos this e1it has been left o!t. +o terminate a loo
we !se -A)' +his wo!ld normally be within some sort of I% statement as we wo!ld only want to
leave the loo !nder certain conditions. +his is the most likely lace an infinite loo can occ!r' the
sit!ation where we do in fact secify a -A) statement b!t the criteria is never met.
It may be a little conf!sing talking abo!t L7#L7 when we haven&t created a loo yet b!t its
e1tremely imortant we !nderstand the conse2!ence of not !sing a L7#L7 statement. So lets
create a very basic loo and see what it does and how to e1it 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
//
loof!nction1.my
3e start off in normal fashion creating the f!nction witho!t any arameters. 3e declare an IN+
variable' this will be !sed in the loo to kee track of how many times we have gone aro!nd the
loo. 3e then name o!r loo' as we said revio!sly its ossible to write a loo witho!t naming it
however in this case we can&t then !se L7#L7 and as we have disc!ssed this leads to an infinite
loo. So we give it the name loo1 then add a colon to tell the comiler this is a name. 3e then
!se the L((" statement to signify the start of the loo. 3e then add 1 to o!r variable and then
check what the val!e is in an I0 statement. +he first time ro!nd its one so the I0 statement
eval!ates to false and the code between I0 and 7N) I0 is ignored. +he ne1t statement we get to is
7N) L((". MySQL knows that if it has got to this stage witho!t calling the L7#L7 statement it
needs to go back to the L((" statement and r!n the code again. +he second time ro!nd we add
another to the variable and check again' the same res!lt this time so its on to the 7N) L((" and
therefore back to the L(("' this cycle goes on !ntil the val!e of lCloo reaches 1/. #t this oint
the code in the I0 statement is called' in this case its L7#L7. (nce L7#L7 has been called the loo
terminates and no f!rther rocessing takes lace in the loo. Lets r!n the loo to see what
haens' b!t check yo!r code caref!lly we don&t want to die waiting for it to finish.
select loopfunction() //
+--------------------+
| loopfunction() |
+--------------------+
| We looped 10 times |
+--------------------+
3e can see from this that the loo e1ec!ted 1/ times. 3e can make a simle ad,!stment to the
code to show that when the L7#L7 statement is met any f!rther rocessing is stoed.
//
begin
declare l_loop, l_loop2 int default 0;
loop1: loop
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
//
+--------------------------------------------+
| loopfunction() |
+--------------------------------------------+
| We looped 10 times but loop2 only got to 9 |
+--------------------------------------------+
loof!nction*.my
So yo! can see that while code before the L7#L7 statement was e1ec!ted 1/ times the code after
was e1ec!ted only 9 times. +here is nothing wrong with doing it either way so long as yo! know
which way yo!r doing it.
IT/AT
In the first two e1amles we let MySQL make the decision of when to do the loo again. $!t we
can force it to do the loo o!rselves. +o do this we !se IT/AT. 3e !se it in m!ch the same way
as L7#L7 in that we ,!st !se the word I+76#+7 and give it the loo name. Lets create another
version of o!r loo !sing I+76#+7 so we can see how it f!nctions.
//
begin
loop1: loop
if l_loop < 11 then
iterate loop1;
end if;
leave loop1;
end loop loop1;
return concat('We looped ',l_loop,' times.');
end
//
+---------------------+
| loopfunction() |
+---------------------+
| We looped 11 times. |
+---------------------+
loof!nctionD.my
3e can see that the res!lts are very similar to the first two e1amles. ;o!r free to choose which
method yo! refer as they are both e2!ally valid ways to !se loos. If we wanted to we co!ld
incl!de m!ltile L7#L7 and I+76#+7 statments d!ring the loo to e1it or loo again based on more
than one criteria.
+here isn&t a lot more that can be said of loos' b!t there are other methods we can !se to
erform similar looing which may be more aroriate in different circ!mstances.
6eeat
3e have looked at simle loos which allow !s to erform a section of code a n!mber of times.
-sing simle loos we need to define the e1it condition as a searate section of code. In addition
to simle loos we can !se reeat to carry o!t looing in MySQL stored roced!res. 3hen !sing a
reeat loo the code is rocessed !ntil a condition is met' this is the same as !sing leave within a
simle statement' b!t in a reeat loo the leave statement is secified as art of the end reeat
synta1. +he synta1 for reeat is as follows.
,begin_label0. /PAT
state0ent;list
('TI- search;condition
'+ /PAT ,end_label.
#s with loos we have the otion of giving the reeat a label' b!t !nlike a simle loo its act!ally
ractically ossible to do so as the leave condition for the loo is art of the end reeat synta1.
(nce we have labeled the reeat we then add the /PAT keyword. 3e can then add one or
more lines of code we wish to reeat' its imortant as with simle loos for one of these lines to
be something that will change d!ring the rocessing of the loo to avoid sending the rogram
into an infinite loo. (nce we have added all of the statements within the reeat loo we !se
('TI- to tell MySQL if it is time to sto rocessing the loo. +his is done by lacing a condition
after the !ntil which will eval!ate to tr!e when we want to end the loo. +his can be a simle
comarison s!ch as variable_name = >? or something more comlicated s!ch as
7variable_name = @?8 and 7l;is;end;o6;cursor8 ' all the time the condition is false the reeat
will contin!e to loo. 3e then' witho!t !sing a semi colon' add '+ /PAT and the reeat label
if we have !sed one.
+he following code shows how to create a simle reeat loo.
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
//
reeatf!nction1.my
#s yo! can see the reeat is fairly simle' the first thing we do is declare an int variable'
l;re"eat;count this is so we can kee a co!nt of how many times we have been aro!nd the loo
and also to give !s a way to e1it o!r loo. 3e then simly added re"eat to tell MySQL that we
wish to start the loo. +he line of code is called which adds 1 to the variable we declared. +his
variable is then checked !sing until' in o!r case we are checking to see if it e2!als 1/' if it does
the reeat wo!ld end. In o!r case it only e2!als 1 so the rogram goes back to the to of the
reeat and erforms the code again' 1 is added to the total then it is checked again. +his looing
contin!es !ntil s!ch time as the condition between until and end re"eat eval!ated to tr!e. Lets
r!n the code and see what we get.
select repeatfunction() //
+------------------+
| repeatfunction() |
+------------------+
| 10 |
+------------------+
#s yo! can see the f!nction ret!rns 1/' this is beca!se the loo e1ec!ted 1/ times.
When to /e"eat and $hen to -oo"
;o! may have noticed that re"eat and loo" act in a similar way' so it&s worth looking at why we
wo!ld !se one over the other. It&s tr!e that reeat is essentially red!ndant from a f!nctionality
oint of view' there is nothing that we can do with reeat that cannot be done with a simle loo
constr!ct. It comes down to ersonal choice and a decision on which yo! think is a more
descritive method' I think that reeat is a little easier to !nderstand by virt!e of the fact the
e1it condition is tightly linked to the end of the reeat. +he single difference between a simle
loo and a reeat is that yo! can erform f!rther statements after the conditional check in a
simle loo' this may or not be a restriction deending on what yo!r !sing the loo to do.
-A) and IT/AT
#s with simle loos in addition to !sing the ('TI- keyword to define the e1it condition for a
loo its also ossible to !se the -A) keyword to e1it the reeat loo. +here may be times
when rocessing code that there are more than 1 criteria for an e1it' in this case it may also be
ossible that the criteria will not be met at the same time. Lets look at a very simle and
contrived e1amle of this' its !nlikely we wo!ld ever do s!ch a thing b!t it demonstrates the fact
we can !se -A) in a reeat loo.
//
create function repeatfunction() returns int
begin
declare l_repeat_count int default 0;
rep1: repeat
if l_repeat_count = 5 then
leave rep1;
end if;
until l_repeat_count = 10 end repeat rep1;
return l_repeat_count;
end
//
+------------------+
+------------------+
| 5 |
+------------------+
reeatf!nction*.my
%ere we have created a rather artificial e1amle to rove the oint b!t it does demonstrate the
oint rather well. +he first thing we have changed is that we have added a label for the reeat
loo' this allows !s to !se the -A) keyword. 3e then added an I% statement after we had
incremented o!r variable to check it&s val!e' if the I% condition eval!ates to +6-7 the -A)
statement is !sed and the reeat loo terminates.
It&s !nlikely that yo! will !se this method as it really negates the oint of !sing a reeat loo in
the first lace. It wo!ld be m!ch more logical to code the loo as a simle loo' b!t its worth
mentioning none the less. 3hat is more likely is that we wo!ld want to only rocess some of the
code d!ring the loo and not other times. 3e can do this !sing the IT/AT keyword. IT/AT
allows !s to sto rocessing the code at some oint between the /PAT and '+ /PAT b!t
not sto looing comletely.
//
create function repeatfunction() returns varchar(50)
begin
declare l_repeat_count, l_count_2 int default 0;
rep1: repeat
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
//
+----------------------------------------------+
+----------------------------------------------+
| We looped 10 times, but we only counted to 7 |
+----------------------------------------------+
reeatf!nctionD.my
3e can see from this e1amle that the reeat was erformed 1/ times b!t the variable lCco!ntC*
was only incremented S times.
Warning : it&s really imortant when !sing iterate that yo! make s!re that the rogram won&t go
into an infinite loo. 3hile testing to see if the !ntil is eval!ated when the iterate takes lace
the code was sent into an infinite loo' my "4 became !n!sable to the oint ressing control'
alt' delete didn&t work. I managed to get to the services control anel and tried to sh!t down the
MySQL service b!t it was imossible. +he only sol!tion was to switch the "4 off at the ower
oint. +hat was fine on my home "4 b!t if I had been connecting to a server or web host the
sol!tion wo!ldn&t have been so easy. It&s very easy to send the rogram into an infinite loo'
esecially when !sing iterate so check yo! code caref!lly before r!nning it.
C(/S&/S
;o! may have noticed in one of the earlier sections that we have already !sed the reeat loo.
3hen !sing c!rsors we need some way to loo thro!gh the record set that is rod!ced' we co!ld
!se any of the loo techni2!es in MySQL b!t reeat is a good choice. 3hen writing code it&s
imortant to remember that other eole or yo!rself may be !sing it in the f!t!re' making it
easy to read is a big art of good rogramming. If yo!r an 7nglish seaker its clear what the word
reeat imlies' therefore !sing it in code gives hints to what the code is doing. 3hen we wrote
the original c!rsor code we only skimmed the s!rface on what the reeat was doing. Lets go back
over the c!rsor code and look at the reeat in more detail now that we have seen it&s !se in more
detail.
//
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;
//
c!rsorroc*.my
+he !rose of the reeat in a c!rsor rocessing block is to loo aro!nd the record set. 3e
simly start by !sing the /PAT keyword to tell MySQL that we want to start looing' we then
fetch records from the c!rsor into a variable. +he rogram then comletes a series of statements
and we reach the ('TI- keyword. 3e have seen in o!r disc!ssion on reeat that we need to !se
a condition that will eval!ate to tr!e when !sing ('TI-. In this case we are !sing a variable that
has been set !sing a handler' the handler will be called when there are no records left to fetch
from the c!rsor' this will always haen at some oint when !sing c!rsors. 3hen rogramming we
want the code firstly to be efficient b!t secondly to be easy to read' if we were to e1lain what
we were doing in 7nglish it wo!ld be something like' Twe want to re"eat this section of code
until the c!rsor fetches no more rowsT. Its easy to see this in the way the code is act!ally
written.
While
So far we have seen a simle loo' and a reeat loo the final method available to erform
looing in MySQL is the WHI- loo. 3e have disc!ssed looing in general terms in both the
revio!s sections so we won&t dwell too m!ch on that here. +he synta1 for a while loo is as
follows.
,begin_label:. WHI- search;condition +&
state0ent;list
'+ WHI- ,end_label.
0irstly we can secify a label for the while loo' this is otional in most cases. 3e then !se the
keyword WHI- and immediately add the condition' this can be any condition or set of conditions
which eval!ate to +6-7 or 0#LS7. If +6-7 the loo will be erformed if false it will not. Ne1t we
have the +& keyword' which in effect tells MySQL where the conditions end. 3e can then secify
one or more lines of code. 0inally we see the '+ WHI- keyword' again with an otional label. If
yo! !se a label it m!st be !sed at both the start and end of the loo. Lets create a simle WHI-
loo as we have done with both loo and reeat.
drop function if exists whilefunction
//
create function whilefunction() returns VARCHAR(20)
begin
while l_loop < 10 do
end while;
return concat('We looped ',l_loop,' times');
end
//
select whilefunction() //
+--------------------+
| whilefunction() |
+--------------------+
| We looped 10 times |
+--------------------+
whilef!nction1.my
0irst we declare an integer variable so we can kee co!nt d!ring the loo and also check o!r e1it
condition. 3e then start the loo by !sing the WHI- keyword and then secify o!r e1it condition'
as before we will be looing ten times. 3e then !se +& to tell MySQL we have finished secifying
the e1it condition and to carry o!t the following commands. 3e then increment o!r variable so
that we have a sit!ation where we can e1it the loo at some oint. 0inally we come to the end
while which simly tells MySQL to ret!rn to the start of the loo. +he rocess contin!es !ntil s!ch
time as the condition eval!ates to tr!e.
#s with simle and reeat loos we can !se both IT/AT and -A) to control rocessing within
the loo.
//
begin
declare l_loop, l_count_2 int default 0;
wloop1: while l_loop < 10 do
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
//
+----------------------------------------------+
| whilefunction() |
+----------------------------------------------+
| We looped 10 times, but the count was only 6 |
+----------------------------------------------+
whilef!nction*.my
//
begin
declare l_loop, l_count_2 int default 0;
wloop1: while l_loop < 10 do
if l_loop = 5 then
leave wloop1;
end if;
end while wloop1;
return concat('We looped ',l_loop,' times.');
end
//
+--------------------+
| whilefunction() |
+--------------------+
| We looped 5 times. |
+--------------------+
whilef!nctionD.my
-&&P3 /PAT3 WHI-
Its ossible to erform the same loo !sing all three methods. %owever there is a s!btle different
between the three.
-&&P : -sing loo gives !s the control over when the loo takes lace. -sing iterate and leave we
can erform code when we need to.
/PAT : 3ith a reeat loo we will always erform the loo once 8!nless we !se L7#L7 as the
first statement9. 3e sho!ld !se reeat when we are e1ecting to r!n the loo at least once and
where the conditions which will res!lt in leaving the loo will be determined within it.
WHI- : # while loo may never act!ally be erformed as the e1it condition is secified at the
start of the loo. Its ossible that this e1it condition co!ld be false before we even enter the
loo. -se while loos where there is a ossibility we don&t want to erform the loo.
3hen and where to !se each different tye of loo is really a matter of ersonal reference' b!t
when !sing them its worth at least for a moment to look at how we wo!ld imlement it !sing the
other two methods to check there isn&t a more logical way.
3e haven&t gone into as m!ch detail with while as we have with the other two methods of looing'
b!t this is beca!se they all f!nction in a similar way and it wo!ld be ointless to kee going over
the same f!nctionality.
Procedural Programming in MySQL - Part 2
Andrew Gilfrin, www.mysqldevelopment.com
In the first part of Procedural Programming in MySQL published in Januarys Pipelines newsletter, we looked at
the basics of creating functions and procedures within MySQL !e looked at functions, procedures, parameters,
"ariables and finally at the S#L#$% I&%' synta(
%he first release of Stored Procedures within MySQL set a base le"el of functionality that should suit most basic
programming re)uirements, but thats 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 didnt co"er in the first part !e
wont be looking e(plicitly at how to code these features as that would re)uire much more space and time than
we ha"e here, but at the end you will ha"e an understanding of whats a"ailable to you
andlers
Most programming languages ha"e some type of error handling that allow the programmer to deal with errors or
e(ceptions that are raised within the program MySQL stored procedures of course are no e(ception *e(cuse the
pun+ If you ha"e used MySQL as a database, its likely you will ha"e at some time encountered errors within
SQL statements Its these errors that can be handled within the handlers section of a stored procedure
,n error, warning or e(ception is handled using the following synta(
DECLARE handler_type HANDLER FOR condition_value[,...] p_tate!ent
%he handler type is the action that will happen if the handler is called %his can be either continue to carry on
processing the procedure, e(it to lea"e the procedure and rollback *which as yet is unsupported+ but likely to be
useful when dealing with transactions
#rrors can be handled on a number le"els, which can be categori-ed in the following groups %his is defined in
the condition."alue section %he different categories are as follows
"#L"$A$E
"#L%ARN&N'
NO$ FO(ND
"#LE)CE*$&ON
!y+l_error_code
condition_na!e
#rror numbers within MySQL are grouped by SQLS%,%# so a handler defined with SQLS%,%# will handle all of
the conditions raised under a particular SQLS%,%# code %he ne(t le"els are SQL!,/&I&0, which groups
SQLS%,%# codes beginning with 12 &'%3'4&5 is for errors with an SQLS%,%# beginning with 16 and
SQL#7$#P%I'& which is any SQLS%,%# outside of these 6 groups
Mys)l.error.code is for indi"idual errors and e(ceptions If you wanted to deal with 6 not found errors in a
different way, you could do this using the particular error code in two separate handlers $ondition.name allows
us to define our own names for an SQLS%,%# or error code $urrently it seems the only purpose of this is to
make code more readable but ,8 may ha"e plans for this in the future
%he 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 Its likely you would set a "ariable in this are which could be used later to determine if the
handler has been called
%he following code e(ample shows how you might use a handler to deal with the possibility of inserting a
duplicate key or a null "alue
create procedure handlerproc,O($ p_end -ARCHAR,./00
1e2in
declare continue handler 3or ./45 "E$ 61 7 89 %ith Error ./458:
declare continue handler 3or ./;< "E$ 61 7 89 %ith Error ./;<8:
inert into e!p -AL(E" ,N(LL,8Dave8,.,./0 :
et p_end =7 concat,8$he End 8,610:
end:
In this case the 9#//'/ 21:; *6<111+= $olumn >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 didnt code a handler to deal with this it would stop the
procedure or function and pass the error back to the calling program 4sing 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 e(isting SQLS%,%# or
error code %here isnt much more to them than that %ake for e(ample the following code
create procedure conditionproc,O($ p_end -ARCHAR,./00
1e2in
declare not_null condition 3or "#L"$A$E 85>///8:
declare continue handler 3or not_null "E$ 61 7 89 %ith not_null Error8:
inert into e!p -AL(E" ,N(LL,8Dave8,.,./0 :
et p_end =7 concat,8$he End 8,610:
end:
%he declaration of the condition simply assigns the name not.null to SQLS%,%# 6<111, which then allows us to
use that not.null name in the handler rather then SQLS%,%# 6<11
Cursors
Most database procedural programming languages will make use of cursors $ursors are named result sets
which we can process within procedures !e mention in part 2 the current restriction on functions with regards
to selecting data from tables and this e(tends to cursors 4sing select into we can select a single row of data
from the database into "ariable but with cursors we can select multiple rows which we can the loop through
$ursors 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 %herefore we ha"e to code e"ery single part of the cursor
oursel"es
, basic cursor would look something like this
create procedure curorproc,&N p_in &N$, O($ p_out -ARCHAR,>/00
1e2in
declare l_e!p_na!e -ARCHAR,>/0:
declare cur_. curor 3or elect e!p_na!e 3ro! e!p ?here e!p_id 7 p_in:
open cur_.:
3etch cur_. into l_e!p_na!e:
cloe cur_.:
et p_out 7 l_e!p_na!e:
end:
%he declare statement tells MySQL the name of the cursor ,fter the for we specify the select statement to be
used to gather that data %his can be any "alid SQL statement
%o populate the cursor we use open which gets the data !e can then fetch the information from the cursor into
"ariables we ha"e defined in the procedure %o finish using the cursor we simply issue close !e will look at the
synta( for dealing with multiple row cursors when we look at looping constructs later
!"
,lmost all programming languages will contain I3 statements I3 statements allow us to make decisions within
the stored procedures and functions whether to e(ecute or not e(ecute code based on "alues within the
program
%ake the following code for e(ample
create 3unction i33unction,p_value$oChec@ -ARCHAR,./00 return -ARCHAR,./0
1e2in
declare l_reult -ARCHAR,./0 de3ault 8Not A8:
i3 p_value$oChec@ 7 8A8 then
et l_reult =7 8$hi ?a A8:
end i3:
return l_reult:
end
%he I3 statement e"aluates a conditional statement which will e"aluate to %/4# or 3,LS# %his can be as
simple as the one abo"e *p."alue%o$heck @ A,+ or much more complicated If the condition e"aluates to %/4#
the code between the %B#& and #&5 I3 will be processed if it e"aluates to 3,LS# the code will not be
e(ecuted
In addition to I3 we ha"e #LS# which allows our statement to e(pand to allow us to run code if the condition
e"aluates to false %his can be seen here
1e2in
declare l_reult -ARCHAR,./0:
ele
et l_reult =7 8Not A8:
end i3:
return l_reult:
end
In this e(ample if the first conditional check e"aluates to false the code between else if and end if will fire
!e may wish to e"aluate a second, third or any number of conditions after the original if In this case we can
use else if like so
1e2in
elei3 p_value$oChec@ 7 8AB then
ele et l_reult =7 8Not A or A8:
end i3:
return l_reult:
end
C#S$
MySQL also supports $,S# statements $ase statements are similar to I3C#LS# I3 statements in that they
e"aluate a condition and carry out commands based on the result of that check %here are 6 types of $,S#
statements a"ailable in MySQL
%he first specifies the "alue we wish to e"aluate at the start of the $,S# statement, then at each stage the "alue
is check against a criteria %his is done as follows
create 3unction cae3unction,p_value$oChec@ -ARCHAR,./00 return -ARCHAR,./0
1e2in
cae p_value$oChec@
?hen CAB then
et l_reult =7 C$he -alue ?a AB:
?hen CAB then
et l_reult =7 C$he -alue ?a AB:
?hen CCB then
et l_reult =7 C$he -alue ?a CB:
ele
et l_reult =7 C$he -alue ?a not A,A or C:
return l_reult:
end
!e start by specifying the "alue we wish to check %hen we use when to compare the "alue with another "alue
If this comparison e"aluates to true then the code after that line is run, if false it mo"es to the ne(t comparison
and so on until it finds a match or it gets to the else statement %he else is optional and its also worth
mentioning that if any of the when conditions are met the remaining conditions are not e"aluated
Dou can use this type of $,S# is you will be comparing the same "alue each time If we wish to e"aluated
different criteria at each when statement we can use the second type of $,S# like so
create 3unction cae3unction,p_value$oChec@ -ARCHAR,./00
return -ARCHAR,./0
1e2in
declare l_reult -ARCHAR,>/0:
declare l_econdChec@ -ARCHAR,./0 =7 COtherB:
cae
?hen p_ value$oChec@ 7 8A8 then
et l_ reult =7 8&t ?a A8:
?hen p_ value$oChec@ 7 8A8 then
et l_ reult =7 8&t ?a A8:
?hen l_ econdChec@ 7 8Other8 then
et l_ reult =7 8&t ?a Other8:
end cae:
return l_ reult:
end
%his time we can e"aluate a different condition at each when statement %he same rules apply as with the other
style of $,S# in that MySQL will stop e"aluating the when conditions as soon as it finds a match
L%%PS
%here are three types of loops within MySQL, simple loops, repeat loops and while loops ,ll three operate in the
same way but ha"e a subtle difference in their implementation , word of warning when using any loops in
MySQL E try not to put the procedure into an infinite loop this isnt all that hard and may result in ha"ing to shut
you P$ down to reco"er ,n infinite loop is on that contains no e(it statement or an e(it statement that will ne"er
e"aluated to false
S!MPL$ L%%PS
,s the name suggests, simple loops are relati"ely simple to use %hey contain at a basic le"el loop and end loop
keywords to specify the start and end of the code you wish to loop Bowe"er using Fust these two keywords will
put us into an infinite loop which will run fore"er *or until you pull the plug from the wall+
%o e(it the loop use the lea"e statement %his can be placed within an I3 statement to stop the loop under a set
condition 8ut be sure to check that this I3 statement will e"aluate to false at some stage
create 3unction loop3unction,p_value$oChec@ -ARCHAR,./00
return -ARCHAR,./0
1e2in
declare l_counter int =7/ :
$etLoop= loop
et l_counter =7 l_counter D .:
i3 l_counter 7 ./ then
leave $etLoop:
end i3:
end loop $etLoop:
return l_ reult:
end
%o use a lea"e in a loop we need to gi"e the loop a name, in the abo"e e(ample %estLoop %his lets MySQL
identify the loop you wish to lea"e as its possible to nest loops inside of each other
In addition to lea"e we can use the iterate keyword %his tells the loop to start again without e(ecuting the
remaining code in the loop
create 3unction loop3unction,0 return -ARCHAR,E/0
1e2in
declare l_loop int de3ault /:
loop.= loop
et l_loop =7 l_loop D .:
i3 l_loop F .. then
iterate loop.:
end i3:
leave loop.:
end loop loop.:

return concat,8%e looped 8,l_loop,8 ti!e.80:
end
GG
&$P$#' L%%PS
%he repeat loop effecti"ely has the lea"e statement built in It performs in e(actly the same way as a simple
loop Dou can use both lea"e and iterate in the same way %he difference comes with the last line of the loop
which is as follows
repeat
Ftate!entH
until FconditionH end repeat:
%he loop will keep repeating until the condition contained in the untilCend repeat statement e"aluates to true
W!L$ L%%PS
,gain the while loop functions in the same way as a simple or repeat loop, but this time the e(it condition
e"aluation takes place at the start of the loop
?hile FconditionH do
Ftate!entH
end ?hile
,ll the time that the condition e"aluates to true the loop will take place Its important with both while and repeat
loops to make sure that the state of the condition will change at some point during the e(ecution of the loop
%he decision on which loop to use comes down to how many time the loop is likely to run ,s the repeat loop will
not e"aluate the e(it condition until the last statement the code between loop and end loop will always fire once
!ith a while loop its possible that the loop may ne"er be called !ith a simple loop its up to you to make the
decision on how much code is called before the e(it condition is e"aluated
L%%P!() C*&S%&S
!e mentioned cursors pre"iously but in our e(ample we only selected a single row , much more likely use of
cursors is to use them to process multiple rows of data ,rmed with our information on loops and handlers we
can now look at doing this
create procedure curorproc,O($ p_out DEC&IAL,E,500
1e2in
declare l_loop_end &N$ de3ault /:
declare l_alary, l_total DEC&IAL,E,50:
declare cur_. curor 3or elect alary 3ro! e!p:
declare continue handler 3or +ltate 8/5///8 et l_loop_end 7 .:
open cur_.:
et l_total 7 /:
repeat
3etch cur_. into l_alary:
i3 not l_loop_end then
et l_total 7 l_total D l_alary:
end i3:
until l_loop_end end repeat:
cloe cur_.:
et p_out 7 l_total:
end:
GG
%he cursor is declared as before and we open the cursor in the same way using the open keyword %his time
howe"er we declare a handler, this handler has been assigned to fire when SQLS%,%# 16111 is called, more on
that in a second !e then open a repeat loop , record is then fetched from the cursor %he salary is added to
our "ariable %hen the until condition is e"aluated using the "ariable l.loop.end %his "ariable is set when the
handler we declared fires %he first time around the loop its unlikely to fire so the loop repeats %his will continue
to happen until such time as the fetch from the cursor returns no more rows %hat will result in the Bandler firing
and the l.loop.end "ariable being set to true %he ne(t time the until condition is e"aluated the loop will e(it
Its possible to use a while and simple loop to process a cursor in much the same way but due to the e"aluation
taking place the end in a repeat loop this is the most logical of the three
%hat concludes the second part of this short introduction into Stored Procedure programming in MySQL Stored
procedures are a new feature and its likely that they will change o"er the ne(t few releases of MySQL so check
the MySQL website for updates to the specification and functionality

Mysql Procedures PL SQL

Uploaded by

Copyright:

Available Formats

Mysql Procedures PL SQL

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Mysql Procedures PL SQL

Uploaded by

Copyright:

Available Formats

Stored Procedures :

Andrew Gilfrin, www.mysqldevelopment.com

You might also like