Detailed Notes
1. In, Out, InOut 2. if then else end if 3. case when then else
delimiter // create or replace procedure tutorial.multiplier(in x int, out y int, inout z int) BEGIN select x * z into y; select x * x into z; END; // delimiter ;
set @second = 5; call multiplier(2,@result,@second);
select @result;
create or replace procedure tutorial.multiplier(in x int, out y int, inout z int) BEGIN select x * z into y; select x * x into z; END; // delimiter ; call multiplier(2,4,6);
set @first = 4; call multiplier(2,@first,6); set @second = 6;
call multiplier(2,@first,@second);
delimiter // create or replace procedure tutorial.greeting(in name varchar(20), in flag varchar(1)) BEGIN if flag = "M" then select concat("Hello Mr. ",name); ELSE select concat("Hello Ms. ",name); end if; END; // delimiter ;
delimiter // create or replace procedure tutorial.greeting(in name varchar(20), in flag varchar(1)) BEGIN CASE WHEN flag = "M" then select concat("Hello Mr. ",name); WHEN flag = "F" then select concat("Hello Ms. ",name); ELSE select concat("Hello ",name); end case; END; // delimiter ;
Transcript Text
[Music] well hello and welcome back we are continuing our season where we're looking at sql stuff uh working our way through all kinds of cool little stateful things this episode we're continuing uh focused on mysql and mariadb mostly and we're getting looking at stored procedures sort of the month of stored procedures and this episode we're going to look at a couple different things we're going to review basically go back to parameters before we had end parameters we're going to look at in out and in out parameters and then we're going to take a look at some logical stuff such as if statements and things of that nature so that being said i want to start with a little stored procedure here and we're gonna do this thing called multiplier let me see if i can blow this guy up can i do that okay so we'll go back to here so what we're doing in this one is it's just like we've done before so let's create a replace procedure and it's going to be called multipliers this is a new one we've got three variables we've got n well i'm sorry we've got x which is an n it's coming in and it's an integer we've got y which is an out and it's an integer and we have z which is an in out which is an integer so we've got three integers coming in and what we're going to do with them is we're going to select x times y i'm sorry x times z into the y variable and x times x into the z variable now first let me talk about in out and in out so n variables are values that are going to come in we will not pass them out so you could do like a call my proc and you could give it a number which we've done before like one if it's an n variable if it's an out and i do that it's going to give me an error because it needs to be able to send it back out into something and it's i'm sorry if it's out it's a variable it's basically an empty variable it ignores what's sent in and then if it's in out it can utilize what comes in but it also will rewrite it so it has to be a variable i'll show you what that looks like as we go through these a little bit so so right now we're just doing a call multiplier and we've got three values so let's just do 2 times 4 times 6 and let's see what it tells me now in this case here we go out or in out argument 2 is not a variable so argument 2 has to be a variable so we're going to do set at first equals four and then we're going to do at first let's see what it does and so now we sent a variable in but now the second one is complaining about that because out and in out both have to be a variable so the second is going to be we're going to call it second well we're going to set it to 6 and now when we call it at first a second boom it works so since we didn't display anything we need to take a look at these so now what we can do is we can look at first which is the y parameter and we can see what is its value now oops i have to put semicolon it's 12. which is 2 times 6 is 12. and then if we do second it should be x squared so 4 right yep and it's 4. so it's pretty easy to set up our variables that we just have to remember if they are and if we're trying to keep it simple then you want them all ends and then you're just going to have one value maybe that comes out but you hate may have a bunch of things along the way that you're having to work with maybe row counts and and stuff like that and particularly you may want to overwrite some of that stuff and pass it back through those typically in outs are very confusing for people to work with particularly as a you know the people that are calling those procedures so it's better to do one or the other but that can sometimes be very i can get you some very long signatures that you may not want to do but there are definitely common uses for such things for example if we want to insert a record then maybe we want to insert we give it the values that we want to insert but we have an out variable that is the new record id there's something along those lines or it could be a you know a pass fail or a confirmation value or something like that there's a lot of different things we can do so those are in and out variables next i want to look at is uh let me just take this whole that well let me delete this i'm going to take that whole thing because i'm gonna throw it into our notes and then i'll clean it up later one second while i do this okay so i've got that for now another thing i want to do i want to look at an if then else so let's go build and if then else real quick so let's do and let's keep this simple so i'm going to say i'm going to keep this really simple so i'm going to do in name is a varchar 20. oh let's do this and in gender is varchar is a char one it's gonna just be an m or f if you want something else then that's okay well let's do that we'll take care of that a minute uh so in gender and in name so now we're gonna do an f so we're gonna say if gender gender equals m then select hello mr plus name and let's see what that does oh and then we're gonna do an end if let's just do that right now so this is gonna be greeting so we're gonna do greeting and we've got a name and a gender okay so let's go back over here and whoa okay so it took a little bit uh just basically had some sort of neat little uh surprise character or something like that so let's get back to what we were working on here took me a minute to fix that so i'm gonna do select concat uh hello mister and then i'm gonna get it sorry uh name so let's see if this works now that should work okay so it does so now if i call greeting and they're both ends so the name is going to be uh mr bob uh one's mickey jones and we're gonna make it an m oops and so it's going to say hello mr jones if we give it something else then it gets nothing because there is no else there so let's take care of that so let's say and that would also been if i'd said it was female nope nothing so let's take care of the female case so for that one it's gonna be hello miss and so now oh i need to replace it ah dang it i did that again ah okay oh why do you keep doing this to me okay all right okay let's do this and let's see if this will run a little better all right we're going to change it we're going to do avi current dot sql and so that could be pretty but it's going to work whoops let's get into here okay so that's current now let's see if we do this okay so now let's call it uh let's go back to our call where was that call call call call what a mess so if we call it with an f we get hello mrs jones if we call it with an m again hello mr jones if we call it with something else say other then we get mrs jones still it's not taking that into account so what we can do instead let me see if i can fix it this way instead of this now what we're going to do is we're going to do a case and a case statement looks like basically when then and there's an else at the end so we can sort of make it simple so we're going to say when flag equals that when flag equals f then that and then we're going to do an else and it's just going to say hello jesus to make it simple now if we take that and source it and now we call it if we do an o it's just going to say hello jones if we do a female it's gonna say hello misses if we do a male it'll be hello mister if we do lowercase because it's gonna be case sensitive no it it's not good if i didn't x that's just going to go hello jones if i do empty it's going to be hello jones if i do a null it's going to tell me i need a perimeter so it's not too much different to do a so we can do an if and when to do a case it's just going to be a series of wins sort of like an if-then-else but it's just going to be you know if than that or if then else if that you would normally see and no with your case let's go to something you can't see the speed but note that with a case there's going to be an n case and when you do an if there is going to be an end if so let me take that and copy that over here to make sure we've got it in our little list o things and so there's a couple of ways for us to now get a little more fancy basically with our procedures through store procedures we can use n variables out variables ins and outs and now we've got some logic we can do between ifs and case statements so we are slowly building on this thing and we'll continue to do so in the next in future episodes that being said i think it's time to wrap it up apologies for the confusion today some sort of cut and paste kicked my butt but that happens sometimes we all get bit sometimes by very odd little things and that just leaves us to wrap it up so 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 are
continuing our season where we're
looking at sql stuff uh working our way
through all kinds of cool little
stateful things
this episode we're continuing uh focused
on mysql and mariadb mostly
and we're getting looking at stored
procedures sort of the month of stored
procedures
and this episode we're going to look at
a couple different things
we're going to review
basically go back to parameters
before we had end parameters we're going
to look at in out and in out parameters
and then we're going to take a look at
some logical stuff such as if statements
and things of that nature
so that being said
i want to start with a little stored
procedure here
and we're gonna do this thing called
multiplier let me see if i can blow this
guy up can i do that
okay so we'll go back to here so what
we're doing in this one
is it's just like we've done before so
let's create a replace procedure
and it's going to be called multipliers
this is a new one
we've got three variables we've got n
well i'm sorry we've got x which is an n
it's coming in and it's an integer
we've got y
which is an out
and it's an integer
and we have z which is an in out which
is an integer so we've got three
integers coming in
and what we're going to do with them is
we're going to select
x times y
i'm sorry x times z into the y variable
and x times x into the z variable
now first let me talk about in out and
in out so n variables
are values that are going to come in we
will not pass them out so you could do
like a call
my proc
and you could give it a number which
we've done before like one if it's an n
variable
if it's an out
and i do that it's going to give me an
error because it needs to be able to
send it back out into something
and it's
i'm sorry if it's out
it's a variable it's basically an empty
variable it ignores what's sent in
and then if it's in out it can utilize
what comes in
but it also will rewrite it so it has to
be a variable
i'll show you what that looks like as we
go through these a little bit
so so right now we're just doing a call
multiplier
and
we've got three values so let's just do
2 times 4 times 6 and let's see what it
tells me
now in this case
here we go out or in out argument 2
is not a variable
so argument 2 has to be a variable
so we're going to do
set
at
first
equals four
and then
we're going to do at first
let's see what it does
and so now we sent a variable in but now
the second one is complaining about that
because out and in out both have to be
a variable so the second is going to be
we're going to call it second well
we're going to set it to 6
and now when we call it
at first a second boom it works
so since we didn't display anything we
need to take a look at these so now what
we can do is we can look at first which
is the y parameter
and we can see what is its value now
oops i have to put semicolon it's 12.
which is
2 times 6 is 12. and then if we do
second
it should be x squared so 4 right yep
and it's 4.
so it's pretty easy to
set up our variables that we just have
to remember if they are and if we're
trying to keep it simple then you want
them all ends and then you're just going
to
have one value maybe that comes out but
you hate may have a bunch of things
along the way that you're having to
work with
maybe row counts and and stuff like that
and
particularly you may want to overwrite
some of that stuff and pass it back
through those
typically in outs are very
confusing for people to work with
particularly as a you know the people
that are calling those procedures so
it's better to do one or the other
but that can sometimes be
very i can get you some very long
signatures
that you may not want to do but
there are definitely common uses for
such things for example if we want to
insert a record
then maybe we want to insert we give it
the values that we want to insert but we
have an out variable that is the new
record id there's something along those
lines
or it could be a you know a pass fail or
a confirmation value or something like
that there's a lot of different things
we can do
so
those are in and out variables next i
want to look at is uh
let me just take this whole
that well let me delete this
i'm going to take that whole thing
because i'm gonna throw it into our
notes and then i'll clean it up later
one second while i do this
okay
so i've got that for now
another thing i want to do i want to
look at an if then else so let's go
build and if then else real quick
so let's do and let's keep this simple
so i'm going to say
i'm going to keep this really simple so
i'm going to do
in
name
is a varchar
20.
oh let's do this
and
in
gender
is
varchar is a char one it's gonna just be
an m or f
if you want something else then that's
okay
well let's do that
we'll take care of that a minute
uh so in gender and in name so now we're
gonna do an f so we're gonna say if
gender
gender
equals
m
then
select
hello
mr
plus
name
and let's see what that does oh and then
we're gonna do an end if let's just do
that right now
so this is gonna be greeting
so we're gonna do greeting
and we've got a name and a gender okay
so let's go back over here
and whoa
okay so
it took a little bit uh just basically
had some sort of neat little
uh surprise character or something like
that so let's get back to what we were
working on here
took me a minute to fix that so i'm
gonna do select concat
uh hello mister
and then i'm gonna get it
sorry uh
name
so
let's see if this works now that should
work
okay so it does
so now if i call
greeting
and they're both ends so the name is
going to be
uh mr bob uh one's mickey jones
and we're gonna make it an m
oops
and so it's going to say hello mr jones
if we give it something else
then it gets nothing because
there is no else there so let's take
care of that so let's say
and that would also been
if i'd said it was female nope nothing
so let's take care of the female case
so for that one
it's gonna be hello miss
and so now
oh i need to replace it
ah dang it
i did that again ah
okay
oh why do you keep doing this to me okay
all right
okay let's do this
and let's see if this will run a little
better
all right we're going to change it we're
going to do avi
current dot sql
and
so that could be pretty
but it's going to work
whoops
let's get into here
okay
so that's current now let's see if we do
this
okay so now let's call it
uh let's go back to our call where was
that call call call call
what a mess
so if we call it with
an f we get hello mrs jones
if we call it with an m
again hello mr jones if we call it with
something else say other
then we get
mrs jones still it's not taking that
into account so what we can do instead
let me see if i can fix it this way
instead of this now what we're going to
do is we're going to do a case
and a case statement looks like
basically when
then and there's an else at the end so
we can sort of make it simple so we're
going to say when flag
equals that
when
flag equals
f
then
that
and then we're going to do an else
and it's just going to say hello
jesus to make it simple
now if we take that and source it
and now we call it if we do an o it's
just going to say hello jones if we do a
female
it's gonna say hello misses if we do a
male it'll be hello mister if we do
lowercase because it's gonna be case
sensitive
no it it's not good
if i didn't x that's just going to go
hello jones if i do empty it's going to
be hello jones if i do a null it's going
to tell me i need a perimeter so
it's not
too much different to do a so we can do
an if and when to do a case
it's just going to be a series of wins
sort of like an if-then-else
but it's just going to be you know if
than that or if then else if that you
would normally see and no with your case
let's go to something
you can't see the speed but note that
with a case
there's going to be an n case
and when you do an if
there is going to be
an end if
so let me take that and copy that over
here to make sure we've got it in our
little list o things
and so there's a couple of ways for us
to now get a little more
fancy basically with our procedures
through store procedures we can use n
variables out variables ins and outs
and now we've got some logic we can do
between ifs and case statements
so we are slowly building on this thing
and we'll continue to do so
in the next in future episodes that
being said i think it's time to wrap it
up apologies for the confusion today
some sort of cut and paste kicked my
butt but
that happens sometimes we all get bit
sometimes by very odd little things
and
that just leaves us to wrap it up so go
out there and have yourself a great day
a great week and we will talk to you
next time
you