Detailed Notes
1. Signal 2. Resignal
DELIMITER //
CREATE OR REPLACE PROCEDURE RaiseSignal(in value int ) BEGIN IF(value != 1) THEN SIGNAL SQLSTATE '45123' SET MESSAGE_TEXT = 'Oops, that is not a 1'; END IF;
select 'End Of Program'; END//
DELIMITER ;
DELIMITER //
CREATE PROCEDURE resignalExample(IN numerator INT, IN denominator INT) BEGIN DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
DECLARE CONTINUE HANDLER FOR division_by_zero RESIGNAL SET MESSAGE_TEXT = 'Denominator cannot be zero'; IF denominator = 0 THEN SIGNAL division_by_zero; ELSE SET result := numerator / denominator; select result; END IF; END//
DELIMITER ;
Transcript Text
[Music] well hello and welcome back we're continuing our tutorial series with mysql our for focus really on sql although we're you know using mysql and mariadb is our examples today we are going to look at signal and resignal we're sort of continuing our exceptions and error handling portion of this and we are getting into a couple other ways that we can we can work with them the first thing we're going to work with is we're going to walk through is called signal now the syntax for signal is actually pretty straightforward and it is you're going to do signal sql state and you're going to give it a number and then most likely you're going to want to give it message text as well otherwise it's not very useful so you're going to give some sort error and this is where you want to raise some error that you you want to throw out to the user so actually let me change this what i'm going to do here is in this procedure let me just clean this up a little bit so it's a little bit let's put that in the same row and what we're going to do is create this thing called raise signal and we're going to give it a value and right now we're just going to make it really simple if the value is not equal to 1 then it's going to say that does not exist but instead of that we're going to say oops that is not a one and we're gonna go with that oh i hate when i do that so i'm gonna take this and throw it over my current because it so i can do that and then if i jump into here and do source current not sql oh or exists because i need to do create or replace and let me fix that here just so you can see it because i was playing around with this earlier so that's already there so now we come here now if we source okay he's good so if i call raise signal whoops if i spell it right with the one then nothing happens if caught with a zero oops that's not a one and it gives me that error code and so if we're looking here and looking you know basically it's a if so there's nothing there so if the value is not equal to one then it's going to give me this message otherwise it does nothing let's just do this let's do uh select uh end of program just to show you how this goes oop then we'll do that but i do want to do this oh let's go over here just to update my current and now if i source it now if i call it with a zero oops that is not one if i call it the one i get in a program so you can see here that in this case what happens is when i do that raise i'm sorry that signal when i signal it does not give me in the program it just says oops that's not a one so signal bails out it essentially raises an exception which is this number and i can do let's call it one two three and let's go where was my current let's change that to like one two three and let's uh source signal with those zero one still works fine if i sing with zero then we can see that's my return code now the error is it's a specific error because we've raised this is something that sql is going to give you but because it gives you that id oops so here it's a little bigger to see because of the state it's going to return that number so if you want to have a custom error code and a message then you can use signal in order to do so now there are situations where that could be a problem you don't want to signal and have something happen while you're dealing with raising your exception that gives you that's where resignal comes in so in the resignal example for this one we're going to have a numerator and a denominator we're going to do a division and what we're going to do is we're going to say if we see division by 0 then that's for if we hit this sql state and we're going to declare our handler which we've seen before so this is going to be continue handler and we're going to set uh here within this re-signal we're going to set our message and the problem here is is that when i come in here if denominator equals zero and i do a signal division by zero then if i want to do anything within it i have to essentially send it back up otherwise it's gonna it caused some issues and that's why here i'm going to get a signal but i also want to continue it so i'm the flow is it's going to catch an error and within that error there is an exception but i'm basically capturing that exception and so i need to resign which means i need to pass that back up the chain so rather than just bailing out completely because this is a continue and because i need to i want to catch that exception if you think of like a try catch i want to catch that exception and then pass it up the chain then i'm going to use a re-signal instead so in this case let's see what oh i need to take this let's make sure i get the whole thing and throw that into my current and then run that uh results oops what did i do oh i'm sorry in line four what did i do here uh oh declare results and this is just going to be an ant okay i can change that my mistake missed one in there oh i need to group my creator replace there we go so now i want to call it as a resignal example with two numbers so call resignal example and let's just do it uh two and one how is that let's see numerator denominator okay so this should give us two over one results two if we swap those two should give us a half uh oh it's giving us an int out my mistake that should be a float now if we run current there we go and let's go back to the one we call it two one and it's two so we're okay there so we can you know if we want to do i don't know some number comma some smaller number okay so we're gonna get our results so that's working however let's put let's start with a zero on the numerator and that's zero that's fine but now if we do three comma zero bam now we're getting the denominator cannot be zero so we have uh in this oops so i need to look at it so what we did is we came in here we said hey i want to change the text if otherwise let's see if i get rid of that i can't do it here i get rid of that here and get rid of my signal when i run it uh set current then it's getting caught it's basically getting just lost because i'm not passing it back up and so uh this continue just says nope i'm not going to do anything with it now if we do uh let's do this uh well let's do let's see what happens if we do select result regardless in this case we do source so we get denominator it cannot be zero it bails out if we do it with a one then it's going to come through and give us our result so we're still getting uh here because it's a signal it was bailing out before um so with this signal we are rewriting the text if we don't if we don't do it see remember if we don't do it because this is a continue handler it just eats that example or that there we go oh sorry re it eats that exception so it's going to come right through because it's a continue handler now i could say if i just wanted to say signal then i'm going to get here i get that right oh then it's going to give me an issue because oh that's okay because it's expecting that signal that signal is always being triggered and so it doesn't like that so it's basically we're now just running into a syntax error if we re-signal then we're okay and this is something we can play around with a little bit as far as raising our exceptions and dealing with them but it's it's really and we'll see some more of this as we get deeper into uh some rather complex stored procedures but this gives us a start so that we can you know now between our prior uh conversation when we talked about exceptions in general and some of the handlers whether it was a continue handler or an exit handler now we have the ability to also signal specific errors which goes back to here [Music] let's see so we can do exit handlers for specific um or continue handlers for specific errors well i can bring that up and so actually i'll bring it over here what's easier to read where is that there you go here then we can create our own um and we can set our signal state to a specific spot we can start playing around with that as well so we can utilize and [Music] basically uh enhance or add to or even i mean just i guess generally modify the exception flow we can use what's built in but we can also change the messages we can capture one do some work and then continue on or capture it do some work and then bail out so it gives us with these this in the prior episode we now have some tools to do very full-featured exception handling which again maybe it's not that big a deal with the initial couple of store procedures we've seen but fairly quickly you'll see when you get into building out stored procedures that there are going to be situations where you're going to want to bail out where you're going to say hey i'm i'm working on records that don't exist or i'm spinning through a list of ids and i'm looking things up based on that and if one doesn't exist somewhere then there's going to be an issue it's actually could be for example a way to encode do some sort of a foreign key relationship as opposed to doing it within using the indexes and the keys not sure why you would do that but there are situations i think where you would have something that is depending on how tricky you want to get it where you you're wanting to do some sort of logic based on whether an id exists or not and it's not really a foreign key because you're not forcing it to exist but you do want the ability to handle that you want to have you know maybe the case for example maybe you have a case where if the foreign key doesn't exist then you create that record uh you go and create it link it up and then go from there so there's yeah i'm i'm sort of just throwing ideas out at this point because once you get into exceptions you're usually outside of you're dealing with outliers anyways even though they may be somewhat common i guess outliers which would make a little different but you're basically saying hey here's a couple of things that can happen i want to be able to handle those conditions as opposed to just you know crashing or quietly doing nothing and then the user has to figure out what's going on the caller of that stored procedure has to figure out what's going on this was a little short but we've gone along the last couple times so i figure we'll we'll do one and we will come back next time we're just going to continue cranking our way through sql and learning how to use it better but go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we're
continuing our tutorial series with
mysql our
for focus really on sql although we're
you know using mysql and mariadb is our
examples
today
we are going to
look at signal and resignal we're sort
of continuing our
exceptions and error handling portion of
this
and we are
getting into a couple other ways that we
can we can work with them
the first thing we're going to work with
is we're going to walk through is
called signal
now the
syntax for signal
is actually pretty straightforward
and it is you're going to do signal sql
state
and you're going to give it a number
and then
most likely you're going to want to give
it message text as well otherwise it's
not very useful
so you're going to give some sort error
and this is where you want to raise
some error that you
you want to throw out to the user so
actually let me change this what i'm
going to do here
is in this procedure let me just clean
this up a little bit so it's a little
bit let's put that in the same row and
what we're going to do
is create this thing called raise signal
and we're going to give it a value and
right now we're just going to make it
really simple if the value is not equal
to 1 then it's going to say that does
not exist but instead of that we're
going to say
oops
that is not a one
and we're gonna go with that
oh
i hate when i do that
so i'm gonna take this
and throw it over my current because it
so i can do that
and then
if i jump into here and do source
current not sql
oh or exists because i need to do create
or replace
and let me fix that
here just so you can see it
because i was playing around with this
earlier so that's already there
so now we come here now if we source
okay he's good
so if i call raise signal
whoops if i spell it right
with the
one
then
nothing happens
if caught with a zero
oops that's not a one and it gives me
that error code
and so if we're looking here
and looking you know basically it's a if
so there's nothing there so if the value
is not equal to one
then it's going to give me this message
otherwise it does nothing
let's just do this let's do uh select
uh end of
program just to show you how this goes
oop then we'll do that but i do want to
do this
oh
let's go over here
just to
update my current
and now
if i source it
now if i call it with a zero
oops that is not one if i call it the
one
i get in a program so you can see here
that in this case
what happens is when i do that raise
i'm sorry that signal
when i signal
it does not give me
in the program it just says oops that's
not a one so signal bails out it
essentially raises an exception which is
this number and i can do let's call it
one two three
and let's go
where was my current let's change that
to like one two three
and let's uh source
signal with those zero one still works
fine if i sing with zero
then we can see that's
my return code
now the error
is it's a specific error because we've
raised this is something that sql is
going to give you but
because it gives you that id oops so
here it's a little bigger to see
because of the state it's going to
return that number so if you want to
have a custom error code
and
a message
then you can use signal
in order to do so
now
there are situations where
that could be a problem you don't want
to signal
and have something happen while you're
dealing with raising your exception
that gives you
that's where
resignal comes in
so in the resignal example
for this one we're going to have a
numerator and a denominator we're going
to do a division
and what we're going to do is we're
going to say if we see division by 0
then that's for if we hit this sql state
and we're going to declare our handler
which we've seen before so this is going
to be continue handler
and we're going to set
uh here
within this re-signal
we're going to set our
message
and the problem here is
is that when i come in here
if denominator equals zero and i do a
signal division by zero
then if i want to do anything within it
i have to essentially send it back up
otherwise
it's gonna it caused some
issues and that's why here i'm going to
get a signal but i also want to continue
it
so i'm
the flow is it's going to catch an error
and within that error
there is an exception but i'm basically
capturing that exception and so i need
to resign which means i need to pass
that back up the chain so rather than
just bailing out completely
because this is a continue
and because
i need to i want to
catch that exception if you think of
like a try catch i want to catch that
exception and then pass it up the chain
then i'm going to use a re-signal
instead
so in this case
let's see what oh i need to take this
let's make sure i get the whole thing
and throw that into my current
and then run that
uh results oops what did i do
oh i'm sorry in line
four
what did i do here uh
oh
declare results and
this is just going to be an ant
okay
i can change that my mistake missed one
in there
oh i need to group my creator replace
there we go so now i want to call it as
a resignal example with two numbers so
call resignal
example
and let's just do it uh two and one
how is that let's see numerator
denominator okay
so this should give us two over one
results two if we swap those two
should give us a half
uh oh it's giving us an int out my
mistake that should be a
float
now if we run current
there we go
and let's go back to the one we call it
two one and it's two so we're okay there
so we can
you know if we want to do i don't know
some number comma some smaller number
okay so we're gonna get our results so
that's working however
let's put let's start with a zero
on the numerator
and that's zero that's fine
but now if we do three comma zero
bam now we're getting the denominator
cannot be zero
so we have
uh in this oops so i need to look at it
so what we did is we came in here we
said hey i want to change the text
if otherwise let's see if i
get rid of that
i can't do it here
i get rid of that here
and get rid of my signal
when i run it uh set current
then
it's getting caught it's basically
getting just lost because i'm not
passing it back up
and so
uh this continue just says nope i'm not
going to do anything with it now if we
do uh let's do this
uh well
let's do
let's see what happens if we do select
result
regardless
in this case we do source
so we get denominator it cannot be zero
it bails out if we do it with a one
then it's going to come through and give
us our result
so we're still getting uh here because
it's a signal it was bailing out
before
um
so with this signal
we are rewriting the text if we don't
if we don't do it see remember if we
don't do it because this is a continue
handler
it just eats that example or that
there we go oh sorry re it eats that
exception
so it's going to come right through
because it's a continue handler now i
could say
if i just wanted to say
signal
then i'm going to get
here
i get that right
oh
then it's going to give me an issue
because
oh that's okay
because it's expecting that
signal that signal is always being
triggered and so it doesn't like that
so it's basically we're now just running
into a
syntax error
if we re-signal
then we're okay
and this is something we can play around
with a little bit as far as raising our
exceptions and dealing with them but
it's it's really and we'll see some more
of this as we get deeper into
uh some rather complex stored procedures
but this gives us a start so that we can
you know now between our prior
uh conversation when we talked about
exceptions in general and some of the
handlers whether it was a continue
handler or an exit
handler now we have the ability to also
signal specific errors which goes back
to here
[Music]
let's see
so we can do
exit handlers for specific
um or continue handlers for specific
errors
well
i can bring that up
and so actually i'll bring it over here
what's easier to read
where is that there you go
here then we can create our own
um
and we can set our signal state to a
specific spot we can start playing
around with that as well so we can
utilize
and
[Music]
basically
uh enhance or add to or even i mean just
i guess generally modify the
exception flow
we can use what's built in but we can
also change the messages we can
capture one do some work and then
continue on or capture it do some work
and then bail out
so it gives us
with these
this in the prior episode
we now have some tools
to do
very full-featured exception handling
which again
maybe it's not that big a deal
with the initial couple of store
procedures we've seen but fairly quickly
you'll see when you get into building
out stored procedures that there are
going to be situations where you're
going to want to bail out where you're
going to say hey i'm
i'm working on records that don't exist
or i'm spinning through a list of ids
and i'm looking things up based on that
and if one doesn't exist somewhere then
there's going to be an issue
it's actually could be for example a way
to
encode do
some sort of a foreign key relationship
as opposed to
doing it within
using the indexes and the keys
not sure why you would do that
but there are situations i think where
you would have something that is
depending on how tricky you want to get
it where you you're wanting to do some
sort of logic
based on
whether an id exists or not and it's not
really a foreign key because you're not
forcing it to exist but
you do want the ability to handle that
you want to have you know maybe the case
for example maybe you have a case where
if the foreign key doesn't exist
then you create that record uh you go
and create it link it up and then go
from there so there's
yeah i'm i'm sort of just throwing ideas
out at this point because once you get
into exceptions you're usually outside
of you're dealing with outliers anyways
even though they may be somewhat common
i guess outliers which
would make a little different but
you're basically saying hey here's a
couple of things that can happen i want
to be able to handle those conditions as
opposed to just
you know crashing
or
quietly doing nothing and then the user
has to figure out what's going on the
caller of that stored procedure has to
figure out what's going on this was a
little short but we've gone along the
last couple times so i figure we'll
we'll do one
and we will come back next time we're
just going to continue cranking our way
through
sql and learning how to use it better
but
go out there and have yourself a great
day
a great week and we will talk to you
next time
you