Detailed Notes
1. Exceptions
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, work rolled back, procedure terminated'; END;
DECLARE CONTINUE HANDLER FOR NOT FOUND SELECT 'Row Not Found';
create table some_values( id INT NOT NULL AUTO_INCREMENT, value1 VARCHAR(10) NOT NULL, value2 VARCHAR(10) NOT NULL, PRIMARY KEY ( id ) ); DELIMITER //
CREATE OR REPLACE PROCEDURE simpleException ( in _value varchar(10) ) sp: BEGIN DECLARE idCount INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, work rolled back, procedure terminated' as 'Message'; END;
select 'Inserting Record...' as 'Message'; insert into some_values (value1,value2) values ('first',_value); END//
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE simpleContinue ( in _id int ) BEGIN
DECLARE CONTINUE HANDLER FOR 1364 SELECT 'Unable to insert row' as 'Message';
insert into app_user(t_parent_id) values (_id); SELECT 'Complete' as 'Message'; END//
DELIMITER ; insert into app_user(t_parent_id,email,userLogin) values (1,'',''); DELIMITER //
Transcript Text
[Music] well hello and welcome back we're continuing looking at our going through our sql tutorials and focus mostly on mysql and mariadb today we're continuing uh it's more or less focused in the world of stored procedures right now as we have been the last few sessions but we're going to look at exceptions and error handling which may show up in general scripts as well now let's start with exceptions so with an exception you basically have two types of handlers that you're working with it's either going to be exit which means if i get an exception i am going to bail out of this or continue which means if i get this exception i am going to continue moving forward i'm going to continue with my script so let me go back to let's take a look at one of these uh let's see let's pick one from yesterday [Music] let's do this one and we will modify this guy a little bit so we're gonna take this leave me procedure that we did and we're gonna do create or replace and this time uh instead of leave we're just gonna do um let's do an exit handler and so we're gonna take that so this is where if we given an id and it doesn't exist then we want to do something special so here we're going to do it and this is gonna be now what we're gonna need here is an actual exception so let's do um let's change this around a little bit so in this case instead of the if what we're going to do is we're going to do this count actually we don't even need to do the count uh let me do we're going to call this leave me 2. and let's just change the whole thing up so we're going to call this a simple exception and we're going to give an id and instead what we're going to do here is we don't even need to do that what we're going to do is we're going to clear this and we're going to say errors occurred and then we're going to just update app user and we're going to set a value let's see let's look at um let me jump into my database here and let's do select star from app user let's update uh we're going to update the user login let's just set it well let's just start here okay let's do that one so we're going to set start year equal to uh 2020 we're that so what we're going to do is we're going to end up generating an exception here because if i do this and it is not uh let's do parent id equals zero then oh interesting it does not throw that it does not give me so we're gonna have to change that up a little bit uh describe all right i may have to create a new table here real quick yeah because all mine are way too easy so here let's do this uh let's go back to i think i can steal some great tables so what i'm gonna do here is [Music] somewhere back here i've got here we go let's do this so what we're going to do is we're going to come in and we're going to before that okay going to create table some values and it's going to have an id and it's going to have let's just do value 1 cannot be null value 2 no primary key is going to be id okay so we're going to take that and we create this so now we can't insert so if we go into uh into some values value 1 values first no it should generate there we go okay so now it's going to get that so let's take let's make sure we got those guys here in our apologies we'll do this let's take that insert i'll take that guy and that's going to be in our procedure here okay so we're coming here that's all good we've got him created so let's take a simple exception there we go so i'm going to put him there let's copy that here first was that what i want oh well i've just got all kinds of random stuff in here so let's just do it like that and now okay so now i have simple exception and if i give it oh i guess it doesn't really matter what i can give it any number it doesn't matter at this point and so here i get that there was an exception there now it's going to try that but if i do so let's start from some values i see that it didn't come in and instead i get this and notice that this is you know the text is also the column so what i could do is uh and this will roll back here and so that rollback doesn't really matter too much but if i do let's call him message well i don't want to say that there but i do want to do it here like that now if i go back to my call uh there we go now i'm gonna see where okay so i can do it just like anything else i'm gonna list that out here's the message and i can display this out and this can occur when there's an exception if i say this if i do value to values first comma underscore value and change this up is that right oop let me get that from here ah darn it come here drag and drop it's not always awesome so now if i do it and i do call simple exception if i call it with my value then it's going to work if i send it a null then it's going to say no can't do it because that's a null and i can do other checks around this i could um well this is a sql exception so here i'm not really doing necessarily some issues with this or i'm not i have to have this i have to have an exception actually get raised now same thing is for so this is an exit handler uh is it go oh let me do this let's try this select uh as message inserting record one two three okay so let's do that oh darn it i want to save that there i do want to do that let's go in here and let's do like a later version of that okay so now oh i don't want to do that okay so now if i call simplexception and i call with a null then i see that there's oh it does come through and do that and then it throws that exception oh i'm sorry insert tid because it's not going to generate the exception until i try to do the insert my mistake so let's take that and do it here did i just do that there okay let's try it again oh and can i just call that again call call call where'd he go oh i guess not it's easier to do it this way so now if i call it with a null i just get that that error if i call with a value then it does come through and says hey i answered the record because it comes through here now if i do a continue which is why i did all of this which i can go up and so if i go up here and instead of an exit handler i do a contender continue handler and let me actually do it for that one so now let's say let's change this around a little bit um let's do uh let's take that whole thing and now i'm going to do a simple continue and this one instead what i'm going to do is let's get back to that one that i had before so i'm going to take an id which is an ant he comes in and then let's see if i can find that code that i had from earlier let's do this actually i can just do that update now so i can do uh where did i just put that simple continue here we go okay so here i'm gonna do update app user set uh what did i call that so now i should be able to see row not found uh let's see start here start here equals 2010 where t parent p equals underscore id okay up give it my close my line i'm going to declare a continue handler which will make this really easy and let's see someone continue do that so now i should get a row not found uh i don't need an id count i don't actually need that label simple continue blah blah blah blah blah blah blah blah let's see if this works oops my delimiters um and let's just do select here as message uh let's do that let's change it up just a little bit uh let's see delimiter delimiter let's make sure i've got it in my little samples here and let's load it up okay so now i can do i can call simple continue if i call it with a 4 then i get there because i think 4 is there okay and it got set if i call it with a 2 up and it's still like it because it does not generate the row not found so i've got to get a a not found here so we have to figure out how to do that where it's going to generate it okay i'm going to have to change this around a little bit so enter into app user uh let's see here ct parent one let's just do this t parent id values id and let's just do this um let's do this first so if i do one it should generate okay so he's going to generate 1364. let's just do it from here so for 13.64 as message and we're just going to unable to insert row and uh and we can do it this way so let's just change this here i apologize because we are not going to get to error handling today we're just going to deal with exceptions i thought i would get a little further i did not oops did you don't need you okay so if i call let's just call it simple continue then oh i need to give it an argument and so i'm going to get that unable to insert row but it continues in anyways it's still going to try to do something oh let me put that message is that the one i just did nope that's not the one i did okay wait wait wait okay let's get rid of you let's get down here to where was my here we go this guy oops so this even though we're going to blow up here we're going to do this and we're going to come in we're going to run this guy and then if we do that call call it with that now we're gonna see that's complete even though we got the unable to insert row because we have this continue handler and so we have two different types of handlers that we can work with and all that stuff yet that we can work with that we have talked about today that allow us to either exit based on an error or you know like here we did for just a general sql exception but we can also do it for here this is an error code which we picked up when we tried to do that sierra code right there so if i take this same thing and i could do it for multiples so i could do something different depending on what i did so if i do uh what did that say so it needed to be email and let's just give it this then what it's going to give me is something different so now it's gonna oh okay that's still a 1364 because it's user login but if i go here at some point it's going to give me a different one i'm assuming of course watch this could take me a while to get to the error i want it's still a 13 okay that's just a bunch of pressure default but the point is you'll get different error codes and you can check for those and you could actually have different error handlers depending on what your error code is so if i if i have a different one that is like uh um maybe like walking to some of the other things like divide by zero or something like that you can generate different errors and you can trap for those essentially through your declares and that will allow you to do some there's some basic exception handling right a little long so next time around we're continuing this and we're going to look at our error handling instead of our exceptions and where did that go here we go over here somewhere there we go so instead of air handling we're going to take care of that next time around and i think you'll see that it's be it'll continue um some rather interesting things that we can do within our store procedures that being said sorry if we ran a little bit long playing around with these uh sometimes these examples get a little complex and sometimes they haven't all been run through beforehand so my apologies but that being said go out there and have yourself a great day a great week and we will talk to you next time [Music] you
Transcript Segments
[Music]
well hello and welcome back we're
continuing looking at our going through
our sql tutorials and focus mostly on
mysql and mariadb
today we're continuing uh it's more or
less
focused in the world of stored
procedures right now as we have been the
last few sessions but we're going to
look at exceptions and error handling
which may show up in general scripts as
well
now let's start with
exceptions
so with an exception
you basically have two types of handlers
that you're working with it's either
going to be exit which means if i get an
exception i am going to bail out of this
or continue which means if i get this
exception i am going to continue moving
forward i'm going to continue with my
script so let me go back to
let's take a look at one of these
uh let's see
let's pick one from yesterday
[Music]
let's do this one
and we will modify this guy a little bit
so we're gonna take this leave me
procedure that we did and we're gonna do
create
or replace
and this time
uh instead of leave
we're just gonna do
um
let's do an exit handler
and so we're gonna take that
so this is where if we given an id and
it doesn't exist
then we want to do something special so
here
we're going to do it and
this is gonna be now what we're gonna
need here is an actual exception so
let's do
um
let's change this around a little bit
so in this case instead of the if
what we're going to do is we're going to
do this count actually we don't even
need to do the count
uh let me do we're going to call this
leave me 2.
and let's just
change the whole thing up so we're going
to call this a
simple exception
and we're going to give an id and
instead what we're going to do here is
we don't even need to do that
what we're going to do is we're going to
clear this and we're going to say errors
occurred and then we're going to just
update
app user
and we're going to set a value let's see
let's look at
um
let me jump into my database here
and let's do select star from
app user
let's update
uh we're going to update the user login
let's just set it well let's just start
here okay let's do that one so we're
going to set start
year equal to uh 2020
we're
that so what we're going to do is we're
going to end up generating an exception
here because if i do this
and it is not
uh let's do parent id equals zero then
oh interesting it does not throw that
it does not give me so we're gonna have
to change that up a little bit uh
describe
all right i may have to create a new
table here real quick
yeah because all mine are way too easy
so here let's do this
uh let's go back to i think i can steal
some great tables so what i'm gonna do
here
is
[Music]
somewhere back here i've got here we go
let's do this
so what we're going to do is we're going
to come in and we're going to
before that okay
going to create table
some values
and it's going to have an id
and
it's going to have let's just do
value 1
cannot be null
value 2
no primary key is going to be id
okay so we're going to take that
and we create this so now we can't
insert so if we go into uh
into
some values
value
1
values
first
no it should generate there we go okay
so now it's going to get that so let's
take
let's make sure we got those guys
here in our
apologies we'll do this let's take that
insert
i'll take that guy
and that's going to be in our procedure
here
okay so we're coming here
that's all good we've got him created so
let's take
a simple exception
there we go so i'm going to put him
there let's copy that here first
was that what i want oh well i've just
got all kinds of random stuff in here so
let's just do it like that
and now
okay so now i have simple exception
and if i give it
oh i guess it doesn't really matter what
i can give it any number it doesn't
matter at this point
and so here
i get that there was an exception there
now it's going to try that
but if i do so let's start from
some values
i see that it didn't come in
and instead i get this and notice that
this is
you know the text is also the column so
what i could do is
uh and this will roll back here and so
that rollback doesn't really matter too
much but if i do
let's call him message
well i don't want to say that there but
i do want to do it here
like that
now if i go back to my call
uh
there we go
now i'm gonna see where okay so i can do
it just like anything else i'm gonna
list that out here's the message
and i can display this out and this can
occur when there's an exception if i say
this if i do value
to
values first
comma underscore value
and change this
up
is that right oop let me get that from
here
ah darn it come here
drag and drop it's not always awesome
so now if i do it and i do call simple
exception
if i call it with
my value
then it's going to work
if i send it a null
then it's going to say no can't do it
because that's a null and i can do other
checks around this i could
um well this is a sql exception so
here i'm not really doing necessarily
some
issues with this or i'm not i have to
have this
i have to have an exception actually get
raised
now
same thing is for so this is an exit
handler
uh is it go oh let me do this
let's try this select
uh
as
message
inserting record
one two three okay so let's do that oh
darn it i want to
save that there i do want to do that
let's go in here
and let's do like a later version of
that
okay so
now oh
i don't want to do that okay
so now if i call
simplexception and i call with a null
then i see that there's oh it does come
through and do that and then it throws
that exception oh
i'm sorry
insert tid
because it's not going to generate the
exception until
i try to do the insert my mistake so
let's take that
and do it here
did i just do that
there okay let's try it again oh
and can i just call that again call call
call where'd he go
oh i guess not
it's easier to do it this way so now if
i call it with a null
i just get that that error
if i call with a value
then it does come through and says hey i
answered the record because it comes
through here now if i do a continue
which is why i did all of this
which i can go up
and so if i go up here
and instead of an exit handler i do a
contender continue handler and
let me actually do it for that one so
now let's say
let's change this around a little bit um
let's do
uh
let's take that whole thing
and now i'm going to do a simple
continue
and this one instead what i'm going to
do
is let's get back to that one that i had
before so i'm going to take an id
which is an ant
he comes in and then let's see if i can
find that code that i had from earlier
let's do this
actually i can just do that update now
so i can do
uh where did i just put that simple
continue here we go okay
so here
i'm gonna do
update
app user
set
uh what did i call that
so now i should be able to see row not
found
uh let's see
start here
start here
equals 2010
where t parent
p
equals
underscore id
okay
up give it my close my line
i'm going to declare a continue handler
which will make this really easy
and
let's see someone continue do that so
now
i should get a row not found
uh i don't need an id count i don't
actually need that label
simple continue blah blah blah blah blah
blah blah blah let's see if this works
oops
my delimiters
um
and let's just do
select
here
as
message
uh let's do that
let's change it up just a little bit uh
let's see delimiter delimiter
let's make sure i've got it in my little
samples here
and
let's load it up okay so now i can do i
can call simple continue
if i call it with a 4
then i get there because i think
4 is there okay and it got set if i call
it with a 2
up and it's still like it because it
does not generate the row not found so
i've got to get a
a not found here
so we have to figure out how to do that
where it's going to generate it
okay i'm going to have to change this
around a little bit so enter into
app user
uh let's see
here
ct parent one let's just do this
t parent
id
values
id
and let's just do this um
let's do this first so if i do one
it should generate okay so he's going to
generate
1364. let's just do it from here
so for 13.64
as
message
and we're just going to unable to insert
row
and
uh
and we can do it this way so let's just
change this here
i apologize because we are not going to
get to error handling today we're just
going to deal with exceptions
i thought i would get a little further i
did not oops
did you
don't need you
okay
so if i call
let's just call it simple continue
then
oh i need to give it an argument
and so i'm going to get that unable to
insert row but it continues in anyways
it's still going to try to do something
oh let me put that message
is that the one i just did
nope that's not the one i did
okay wait wait wait
okay let's get rid of you let's get down
here to
where was my
here we go this guy
oops
so this
even though we're going to blow up here
we're going to do this
and we're going to come in we're going
to run this guy and then if we do that
call
call it with that
now we're gonna see that's complete even
though we got the unable to insert row
because
we have this continue handler
and so we have two different types of
handlers
that we can work with and all that stuff
yet
that we can work with that we have
talked about today
that allow us to
either exit based on an error or
you know like here we did for just a
general sql exception but we can also do
it for here this is an error code which
we picked up
when we tried to do
that sierra code right there so if i
take this same thing
and i could do it for multiples so i
could do something different depending
on what i did so if i do uh what did
that say so it needed to be email
and let's just give it this then what
it's going to give me is something
different
so now it's gonna oh okay that's still a
1364 because it's user login but
if i go here at some point it's going to
give me a different one i'm assuming
of course watch this could take me a
while to get to the error i want
it's still a 13 okay that's just a bunch
of pressure default but the point is
you'll get different error codes
and you can check for those and you
could actually have different error
handlers depending on what your error
code is so if i if i have a different
one that is like uh
um
maybe like walking to some of the other
things like divide by zero or something
like that you can generate different
errors and you can trap for those
essentially through your declares
and
that will allow you to do some there's
some basic exception handling
right a little long so next time around
we're continuing this and we're going to
look at our error
handling instead of our exceptions
and
where did that go here we go
over here somewhere there we go
so instead of air handling we're going
to take care of that next time around
and i think you'll see that it's be
it'll continue um some rather
interesting things that we can do within
our store procedures that being said
sorry if we ran a little bit long
playing around with these uh sometimes
these examples get a little complex and
sometimes
they haven't all been run through
beforehand so
my apologies but that being said go out
there and have yourself a great day a
great week and we will talk to you
next time
[Music]
you