Detailed Notes
1. Declare variables 2. Select into and returning a value; 3. Examples
delimiter // create or replace procedure tutorial.count_rows() BEGIN DECLARE num_rows INT DEFAULT 0;
END; // delimiter ;
delimiter // create or replace procedure tutorial.count_rows() BEGIN DECLARE num_rows INT DEFAULT 5;
select num_rows; END; // delimiter ;
delimiter // create or replace procedure tutorial.count_rows() BEGIN DECLARE num_rows INT DEFAULT 0;
select count(*) into num_rows from address;
select num_rows; END; // delimiter ;
delimiter // create or replace procedure tutorial.count_rows() BEGIN DECLARE num_rows INT DEFAULT 0; DECLARE a1,a2,a3,a4 INT DEFAULT 0;
select count(*) into a1 from address; select count(*) into a2 from app_user; select count(*) into a3 from lkp_type; select count(*) into a4 from all_dates;
SET num_rows = a1 + a2 + a3 + a4; select num_rows,a1,a2,a3,a4; END; // delimiter ;
delimiter // create or replace procedure tutorial.count_rows() BEGIN DECLARE num_rows INT DEFAULT 0; DECLARE a1,a2,a3,a4 INT DEFAULT 0;
select count(*) into a1 from address; select a1; select count(*) into a2 from app_user; select a2; select count(*) into a3 from lkp_type; select a3; select count(*) into a4 from all_dates; select a4;
SET num_rows = a1 + a2 + a3 + a4;
select num_rows; END; // delimiter ;
delimiter // create or replace procedure tutorial.cs_test(id int) BEGIN DECLARE c,s varchar(50) DEFAULT 'undefined';
select city,state into c,s from address where t_one_id = id;
select c,s; END; // delimiter ;
Transcript Text
[Music] well hello and welcome back we are continuing our my sequel mostly sequel but my sequel maria db focused tutorial series and we are continuing looking at stored procedures today now last time we went through and we did a uh back on day 11 we created our nice little uh tutorial get address where we could send a parameter in and pull a record back also did a very simple one where we showed some tables we did some things where we did some selects now we're going to get into something a little different we're going to talk about variables now i'll flip over here and i'm going to dive into this one so i do my normal delimiter let's blow this up a little more just because so i do my delimiter and i'm gonna go ahead and create a replace procedure i'm going to call this one count rows and in this one i'm going to declare num rows is an integer and you see this much like you would most of the declarations in the database so here i'm giving it a name which is num rows i'm getting it a type which is int and then a default value which is zero and then end so if i do this it's not going to actually do anything terribly useful so if i do call get rows we're gonna see basically nothing oops i'm sorry i get rows count rows i should probably change my delimiter back okay so nothing happens because it wasn't doing anything interesting all it was doing was declaring a variable now what i can do here same thing so i'm going to declare it and now i'm going to select rows let's go ahead and give it a default value of like five and now actually let's do this just so we can keep track of these guys that way you've got some good examples okay so now we're going to create a replace this time we're going to set our value to five and we're going to do a select number oops we're going to do that just to be clean i don't think it likes it otherwise and now if we come in and we do our count rows wherever that went is that right uh count rows now we see num rows is five so we're getting a value back that means that we can come in with our variables and we can do some interesting stuff like let's say num rows is 4 but instead and we're going to still return gnome rows but instead we're going to come in here and we're going to do select account star from let's say address up and we're going to do select into num rows count star from address i'm sorry wait it's count start into let's forget the order of that so now let's see what that does ah there we go so now uh where'd it go call call count rows now it's three and if you look at select star from address you see three rows so now we're actually able to do something here now let's do this let's just show tables and let's do well i just gotta copy all of those so here's what we're gonna do is now we're gonna get a little more interesting let's do this so we're gonna take a few tables uh let's see we're going to call it ah we'll keep it the same name but now we're going to do address uh let's do amp user lookup type and all dates and now we're going to change this up a little bit so in each of these we're selecting account now here we can do num rows but we're going to do now we're gonna do a multiple so we're gonna do a one a two a three and a four for our four counts so we can declare multiple variables and we're still declaring the same type and the same default value for each of these so now instead of num rows i do a one a2 a3 oh a3 when i get there a4 so now we've got all these so instead of select num rows what we want to do is now we want rows equals a1 plus a2 plus a3 plus a4 and with this we do a set that's just how they do it so you can't do a straight up num rose equals you do a set now we're going to select num rows and let's see how this works out i have no idea how many rows it's going to be but it's probably like a double digit number so now we call oh count rows we get 13. so we're getting each of those now we could come in and we could do select a1 we could select as we go so we can actually get all of these as we go and see how it's going three and a four so let's do this with sort of a debug and i'm just going to do what was that count rose it's probably faster to do it that way at least if i do it like this and so now we can see that our a1 came through we got 3 for that 4 for a2 3 for a3 and 3 for a4 which is 3 plus 4 is 7 plus 3 is 10 plus 3 is 13 and boom we have our total number of rows so if we've been able to declare variables and do something with them which you could normally do you can do some of this we haven't gotten into it but you can do it with your normal script type of information we're just doing this with now within a sort procedure so we can actually do something we can actually hold some records and do something with them we can actually do some manipulation now within it so we could do things like uh let's see what would be something interesting i have no idea of something nervous to do so we're gonna skip that part about the interesting part but okay sorry back to this so we're able to when we do a select without an n2 as we see here in the a1 a2 a3 a4 and then the final one we can see that we're going to see this we see this output this stuff is actually sent out as part of the result now this doesn't it's not how it occurs in all databases this is what saying sort of important is that some you can have multiple result sets come back some will only allow one so you're going to have to look at your specific database if you're getting away from these two but if i get rid of my selects and let me go back to that then all of this other stuff if i get rid of the selects that aren't an into then i don't see the declare don't see that one i don't see any of these selects i don't see setting the gnome rows i don't see it until i do select num rows now i could do num rows comma a1 comma a2 comma a3 comma a4 [Music] so i can do that and give myself a little something different from my result set and now we see num rows plus i see each of the variables so you can see where i specifically set them otherwise the and this becomes important when you get into the development side of it because this is a single result set the time we did it before what we did before was multiple result sets it was one two three four five result sets so you're gonna have to depending on what your your connection is and how it handles multiple result sets you're gonna have to address those differently than you would this one where this is simply row one column one column two come three comma four column five there's different ways that you're going to address such a thing now you can do uh let's do a quick let's do like let's select star from address just to see what that looks like again so let's do city state uh yeah let's do city state and we can do let's jump down here we're just going to call it cs test and in this case we don't need to declare well we will declare this but now um we're just going to make these march our 50s i can't remember how big our city was but we can do city and state and now we can select city state into c comma s from address and let's see what this looks like when we get done and we do select cs uh let's default those can't default to that let's default it to undefined and let's see what it does for us oh darn it i want to get that and okay now it looks good so now let's see what happens when i do cs tests result consists of more than one row now this is where i'm getting multiple because i just i just did this into that from address well since it's in the c and s from address c is a single string s is a single string but address is going to select if i actually do select city state from address remember we're going to get multiple records back so i have to do something to in this case if i want just one of those then it would be something like uh let's see what was the address t1id then i could come back and i can do int [Music] id where what did i call that uh t1id equals id and now i'm gonna have to give it what uh i do replace that uh let's see so into id let's look at that again let's make sure i've got this and i didn't miss anything weird did i oh i'm sorry it's the opposite direction i forgot it is i do that wrong half the time it's not its name and then type okay so now if i do call what is that cs test and i give it a value that's very that's valid for one of those three ids let me get a record if i give it one that's not then i'm going to get nulls back because that now i see that default value come in it did not get a record back for this so it didn't assign those in and so instead it just went with their default values so [Music] we can play around with this quite a bit and are going to continue to do so we'll look at some things like actually building with our result sets and some things like that so we're going to get a little more complicated but i want to get into just some basics this time around of declaring variables and assigning things to them you can either do a select into you know select some value into our variable or we can do a set we can set our value equal to another variable or about like a number because we could always do set num rows equals 15. we can do the same thing there we just did it as a mathematical expression before so we've got a couple different ways to play around with this already you should start seeing some of the power of a stored procedure particularly if you're having to do some complex calculations and things like that where you may want to like hold a max or some calculation or sum from one table and then utilize that into another and like i said we're going to continue looking at that but for now it gets a good start into our introduction to variables in sql that being said we'll wrap this one 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 my sequel
mostly sequel but my sequel maria db
focused tutorial series and we are
continuing looking at stored procedures
today now last time we went through and
we did a uh
back on day 11 we created our nice
little
uh tutorial get address where we could
send a parameter in
and pull a record back
also did a very simple one where we
showed some tables we did some things
where we did some selects
now we're going to get into something a
little different we're going to talk
about variables
now
i'll flip over here and i'm going to
dive into this one
so i do my normal
delimiter
let's blow this up a little more just
because
so i do my delimiter
and i'm gonna go ahead and create a
replace procedure i'm going to call this
one count rows
and in this one i'm going to declare num
rows
is an integer and you see this much like
you would most of the declarations in
the database so here i'm giving it a
name which is num rows
i'm getting it a type which is int
and then a default value which is zero
and then end so if i do this it's not
going to actually do
anything terribly useful
so if i do call get rows we're gonna see
basically
nothing oops
i'm sorry i get rows count rows
i should probably change my delimiter
back okay so nothing happens
because it wasn't doing anything
interesting all it was doing was
declaring a variable now what i can do
here
same thing so i'm going to declare it
and now i'm going to select
rows
let's go ahead and give it a default
value of like five
and now
actually let's do this
just so we can keep track of these guys
that way you've got some good examples
okay so
now we're going to create a replace this
time we're going to set our value to
five and we're going to do a select
number oops we're going to do that just
to be clean
i don't think it likes it otherwise
and now if we come in
and we do our count rows wherever that
went
is that right
uh count rows now
we see num rows
is five so we're getting a value back
that means that we can come in with our
variables and we can do some interesting
stuff like
let's say num rows is 4 but instead and
we're going to still return gnome rows
but instead
we're going to come in here and we're
going to do
select
account star from
let's say address
up and we're going to do select into
num rows count star from address
i'm sorry wait it's count start into
let's forget the order of that
so now
let's see what that does
ah
there we go
so now
uh where'd it go call call count rows
now it's three and if you look at select
star from
address
you see three rows
so now we're actually able to do
something here now let's do this let's
just show tables
and let's do
well i just gotta copy all of those so
here's what we're gonna do
is
now we're gonna get a little more
interesting let's do this
so we're gonna take a few tables
uh let's see we're going to call it ah
we'll keep it the same name
but now
we're going to do address
uh let's do
amp user
lookup type
and
all dates
and now
we're going to change this up a little
bit
so in each of these we're selecting
account
now here
we can do num rows
but we're going to do
now we're gonna do a multiple so we're
gonna do
a one a two a three and a four for our
four counts
so we can declare multiple variables
and we're still declaring the same type
and the same default value for each of
these so now instead of num rows
i do a
one a2
a3 oh
a3
when i get there
a4
so now we've got all these so instead of
select num rows what we want to do is
now we want
rows equals
a1 plus a2 plus a3 plus a4
and with this we do a set
that's just how they do it so you can't
do a straight up num rose equals you do
a set
now we're going to select num rows
and let's see how this works out
i have no idea how many rows it's going
to be but it's probably like a double
digit number
so now we call
oh
count rows
we get 13.
so we're getting each of those now we
could come in and we could do
select a1
we could select as we go so we can
actually get all of these as we go and
see how it's going
three and a four
so let's do this with sort of a debug
and i'm just going to do what was that
count
rose
it's probably faster to do it that way
at least if i do it like this
and so now we can see
that our a1 came through we got 3 for
that 4 for a2 3 for a3 and 3 for a4
which is 3 plus 4 is 7 plus 3 is 10 plus
3 is 13 and boom we have our total
number of rows
so if we've been able to
declare variables
and
do something with them which you could
normally do you can do some of this
we haven't gotten into it but you can do
it with your normal script type of
information
we're just doing this with now within a
sort procedure so we can actually do
something we can actually hold some
records and do
something with them
we can actually
do some manipulation now within it so we
could do things like
uh let's see
what would be something interesting i
have no idea of something nervous to do
so we're gonna skip that part about the
interesting part but okay sorry back to
this
so we're able to
when we do a select without an n2
as we see here in the a1 a2 a3 a4
and then the final one
we can see that we're going to see this
we see this output
this stuff is actually sent out as part
of the result
now this doesn't it's not how it occurs
in all
databases this is what saying sort of
important is that some you can have
multiple result sets come back some will
only allow one
so you're going to have to look at your
specific database if you're getting away
from these two
but if i get rid of my selects
and let me go back to that
then all of this other stuff
if i get rid of the selects that aren't
an into
then
i don't see the declare don't see that
one i don't see any of these selects i
don't see setting the gnome rows i don't
see it until i do select num rows
now i could do num rows
comma a1 comma a2 comma a3 comma a4
[Music]
so i can do that
and give myself a little something
different from my result set
and now we see
num rows plus i see each of the
variables
so you can see where i specifically set
them
otherwise the and this becomes
important when you get into the
development side of it because this is a
single result set
the time we did it before what we did
before was multiple result sets it was
one two three four five result sets
so you're gonna have to depending on
what your
your connection is and how it handles
multiple result sets you're gonna have
to address those differently than you
would this one where this is simply row
one column one column two come three
comma four column five
there's different ways that you're going
to address such a thing
now
you can do
uh let's do a quick let's do like let's
select star from
address just to see what that looks like
again so let's do city state uh yeah
let's do city state
and we can do
let's jump down here
we're just going to call it cs test
and in this case we don't need to
declare well we will declare this
but now
um
we're just going to make these march our
50s
i can't remember how big our
city was
but we can do city and state
and now
we can select city
state
into c comma s from address
and let's see what this looks like
when we get done and we do
select cs uh let's default those can't
default to that let's default it to
undefined
and let's see what it does for us
oh
darn it
i want to get that
and okay now it looks good
so now let's see what happens when i do
cs tests
result consists of more than one row now
this is where
i'm getting multiple because i just
i just did this into that from address
well since it's in the c and s
from address
c is a single
string s is a single string but address
is going to select if i actually do
select city state
from address remember we're going to get
multiple
records back
so i have to do something to
in this case if i want just one of those
then it would be something like uh
let's see what was the address t1id then
i could come back
and i can do int
[Music]
id
where what did i call that uh
t1id
equals id
and now
i'm gonna have to give it
what uh i do
replace that
uh
let's see
so into id
let's look at that again
let's make sure i've got this
and
i didn't miss anything weird did i oh
i'm sorry it's the opposite direction
i forgot it is
i do that wrong half the time it's not
its name and then type
okay so now if i do call what is that cs
test
and i give it a value that's very that's
valid
for one of those three ids let me get a
record if i give it one that's not
then i'm going to get nulls back because
that now i see that default value come
in it did not get a record back for this
so it didn't assign those in
and so instead it just went with their
default values
so
[Music]
we can play around with this quite a bit
and
are going to continue to do so we'll
look at some things like
actually building with our result sets
and some things like that so we're going
to get a little more complicated but i
want to get into just some basics this
time around
of declaring variables
and assigning things to them you can
either do a select into
you know select some value
into our variable or we can do a set we
can set our value equal to
another variable or about like a number
because we could always do
set num rows
equals 15. we can do the same thing
there
we just did it as a mathematical
expression before so we've got a couple
different ways to
play around with this already you should
start seeing some of the power of a
stored procedure
particularly if you're having to do some
complex calculations and things like
that where you may want to
like hold a max or some calculation or
sum from one table and then utilize that
into another
and like i said we're going to continue
looking at that but for now it gets a
good start into our introduction to
variables in sql
that being said we'll wrap this one up
so go out there and have yourself a
great day a great week and we will talk
to you
next time
you