Detailed Notes
1. Loops 2. While loops 3. Repeat loops 4. Leave statement *** Please note that greater and less than brackets were removed for the YouTube notes.
DELIMITER // CREATE OR REPLACE PROCEDURE looper() BEGIN DECLARE x INT; DECLARE str VARCHAR(255);
SET x = 1; SET str = '';
my_loop: LOOP IF x 10 THEN LEAVE my_loop; END IF;
SET x = x + 1; IF (x mod 2) THEN ITERATE my_loop; ELSE SET str = CONCAT(str,x,','); END IF; END LOOP; SELECT str; END//
DELIMITER ;
call looper();
DELIMITER //
CREATE OR REPLACE PROCEDURE WhileLoop( iterations INT ) BEGIN DECLARE counter INT DEFAULT 1; WHILE counter = iterations DO select counter; SET counter = counter + 1; END WHILE; END//
DELIMITER ;
DELIMITER //
CREATE PROCEDURE RepeatDemo( iterations INT ) BEGIN DECLARE counter INT DEFAULT 1; DECLARE result VARCHAR(100) DEFAULT '';
REPEAT SET result = CONCAT(result,counter,','); SET counter = counter + 1; UNTIL counter = iterations END REPEAT;
SELECT result; END//
DELIMITER ;
DELIMITER //
CREATE PROCEDURE LeaveMe ( _id int ) sp: BEGIN DECLARE idCount INT DEFAULT 0;
-- check if the id exists SELECT COUNT(*) INTO idCount FROM app_user WHERE t_parent_id = _id;
IF idCount = 0 THEN LEAVE sp; END IF;
select * from app_user where t_parent_id = _id; END//
DELIMITER ;
Transcript Text
[Music] [Music] three two one well hello and welcome back we're continuing our series of sequel tutorials uh focused a lot on my sequel maria db and we're continuing at this point looking at uh basically looking at store procedures although the a lot of things that we're learning here these syntax related items could be done within queries as well or within just general scripts they tend to be more useful though or more prevalent at least in a stored procedure concept this time we're going to look at loops a couple different looping mechanisms so jumping into our little database here the first thing i want to do and actually expand that out just a little bit so we see it better so this would be our first um oh i've got an error somewhere in there uh let's see set counter blah blah blah oops i didn't declare that yet so i'm going to jump over first i'm going to do is we're going to look at loops but let me walk about this before i fix that uh what we're going to see is got just a little procedure i'm going to call it while loop and we're going to send it something called iterations so it's just how many times we're going to run it and then we're going to have a counter and then we're gonna basically just count up say well the counter is less than iterations do and it's just gonna select counter and then we're gonna set counter equals counter plus one so let's see let's see what we messed up with here so if we go in here he is iterations he's going to clear that and counter equals counter plus one should be right it's a while so let's try that again let's see what we missed here okay so it looks like dang it i'm back into this final thing so let's go back to here we go so source uh oh let me go to current.com i do create or replace okay so now i've got my loop i'm sorry so i've got my loop and what i'm going to do is um i'm able to what i'm going to do is i'm just going to give it a number of iterations this while loop thing and it's going to be while the counter is less iterations i'm just going to select a number add to it and then do my loop and so if i do call while loop with uh let's just do one then i'm gonna see my counter if i do it five times then i can see here where it does a counter at each of those i could also do a counter at the end of it and within this within our while loop um what i do want to show let's just do this real quick i'll paste this even though it won't like it ah here we go there we'll blow that up a little bit okay that'll be a little easier to read so and actually i'm here three two one on here let's do this let's just bring the whole guy over be a little easier to read so while we're in here notice that it's it doesn't have like a beginner and it's sort of like an f is that you start with your while and then you've got some commands and then in while now in here we can do all sorts of stuff we can do selects we can do updates we can do whatever we want this is just your typical coding syntax structure so we're in a pretty good shape now to start doing some loops if we wanted to but as always we may not want to do it as a while but for example i do want a good wild example would be maybe you want to go through and process records in a table and after each one there may be maybe there's records deleted or there's values that are changed or at least each cycle and you're going to want to basically re-query it and just say while i still have records in here to process then do processing and you easily see this a lot with um some sort of transactional type data where there's like let's say there's a status that's not processed and what you could do is especially depending on the timing of the processing and stuff like that it may take a little while so it could be that the records to be processed have changed since you cycle through now depending on the size of the table it could be costly to have something where you're regularly checking uh you know going back and hitting a a bunch of records but what you could do is have something where you start out and you do like uh you know up here you do something like select records to be processed if i can type that right first you do like select records to be processed then you do your processing loop and then even within this you would have like well let's sit this way wow we have records to process so you grab them all or maybe some limit of it you know like say select next 10 or let's say next 100 records to be processed and then you just keep on going through that and then you know you'd have an n while down here of records to process so basically what you could do is you could say select the next 100 process them and then come through and then select the next 100 so as long as you have some you're going to keep iterating through it you can have a couple of you know nested loops in that sense let me get rid of those for posterity's sake and let me make sure in the notes that i have that and then oh that's not the one i want i want this one so that's our while loop now another one we can do is a repeat loop and that one looks like this oops let me throw this in the current real quick apologies while i'm moving stuff around a little bit because my copy and paste does not seem to like itself right now so in this one with repeat same thing i'm going to give it a number of iterations it's going to feel basically the same i'm going to do something a little different just to mix it up though but i come in and i declare my counter and i'm going to have a result which is a string so you know we don't want to get too long but uh what it's done with each one is it's going to count uh it's gonna come in and it's gonna set the result equal to whatever our number the result is plus the counter and then a comma so it'll be you know one comma two three comma one comma two comma three comma four comma five comma blah blah blah update our counter and we're just gonna repeat until the counter is greater than or equal to the number of iterations and then in repeat and then we're at the end just going to select the result we're just going to print that out so it won't be quite as complicated to see oh so if i just do let's see if i got that right okay so he's there i called him repeat demo so now if i do call repeat demo now let's say i do it and we'll do it five times so he comes through and he does one two three four and then a comma and note uh let's see so i start with one and i'm going to repeat it until the counter is greater than or equal to the number of iterations well this case it's actually um i probably wanted to start this at a zero and change around a little bit because i'm not getting five iterations because i already started at iteration one and so when i come through here he's checking it at two so really what i'd wanna do is say greater than the number of iterations uh and let whoop i don't wanna do that uh i don't want to do create or replace and now if i do it oops i need to put this in this current and now if i do that now let's see one two three four five so you have to be as always you have to be cognizant and intentional about how you do your your numbers and particularly off by ones you know less than equal greater than equal things like that happen regularly so you're going to want to see where your counter you know gets incremented and you would run into this with while loops as well it just happened to be this is a good time to examine it and one of the difference between repeats and whiles is a while does the check at the beginning which means it may not even run at all because you could say while true equals false and it would just skip it because true never equals false repeat until always runs once at least so if you did repeat until true equals false then it will run it once and then it'll be done so that's something to consider as well do you want it to run does your root loop need to execute at least once or not you know is it something that you may not want to execute it at all and so let's look at uh let's see oh wrong one let's look at our next one which is going to be let's do a leave now what you can do here let's bring this over here so we can read it we're going to add labels and this label here we're just going to call it sp for stored procedure and we're going to call this thing leave me and it's just going to go through it's got this id count and what i'm going to do is i'm going to see this is working with our tables again so i'm going to say do i have an id for an app user equal to whatever id i sent in so then if i do or i'm sorry if i don't which means if i can't find one here and so let's look at that so if we do it's like count star from there and uh let's say i make the id equals one oh uh i'm sorry it doesn't like that because of the into our let's change this real quick sorry so let's do count star let's get rid of the n2 and do this and we'll just clean this up a little bit so we're normally going to see so let's just say let's start with one so if we come in here i have one that's got an id of one do i have one with an id of three yes i do do i have one with an id of six no i do not so i'm getting this count i have one id and since this is a primary key it's either one or zero that's what i'm going to run into so for this let me get rid of this what i'm going to do is i'm going to see do i have it if id counts equals 0 which means if i don't then we're going to use this thing called leave and what leave does is it says basically jump out to leave this bracket this labeled bracket in this case it's going to be the store procedure itself so if they give me an id that's not valid i'm not going to do anything otherwise i'm going to select start from app user i'm going to get that record so if i take this uh see he should be all good let's throw him into current real quick uh well let's do that i think that's where he's at where is current let's close a couple of these like random files okay so now oh that one this one and so we're gonna do our source current there we go and what do we call this we call this leave me so if we call leave me with one because we know the idea of one exists then we get that record if we do it with two then two doesn't exist but three does i think there you go three does does four four does does five five does six does not we just check that so six doesn't so you either get the record or not and so you can do a lot of different things like this to basically bail out of a loop which brings us to just a general loop uh let's see where did i put oh here we go so let's look at this general loop and i may actually change this okay so whoop so let's put this where you can read it so very general loop uh we're gonna call this one looper and this one is just gonna loop and it's just account i'm not even giving it i'm just gonna throw a number out there and so here i've got this loop label so now the label is not on the stored procedure but it's on the loop and so what happens is what i'm going to do is i'm going to say hey if x which is this little number that i set to 1. it's greater than 10 then i'm going to leave and i'm going to leave it here which means this thing this structure i'm going to leave it and i'm going to come to the next line which is going to be this select string here i'm going to do set x i'm going to increment my counter if it equals uh and basically if it's even then i'm going to iterate otherwise i'm going to concatenate a string and then i'm going to bail out so it's a you know a different little it's a different little way to approach stuff uh just as a simple sample uh but let me do this actually what i'm gonna do is let's just change this up a little bit and instead of this what we want to do is let's do so this one we're going to come in and we're just going to start building up numbers but if we get beyond 10 then we're going to leave the loop uh let's see so let's go here let's go current and let's make sure i've gotten that one sort of updated because i tweaked that a little bit and now if i do current okay so he's good and i called him looper which has no parameters so if i call it i'm missing something here oh i got rid of my x equals x plus one so that's a problem oh i lost like a bunch of that i did not copy that over properly oh there that's the whole thing okay let me show axe oh i managed to totally screwed that one up let's do create or replace just to be simple instead of doing a drop and we're going to go over here and we'll do the same thing create or replace um oh and i just need to run it again so uh see what i miss here oh i'm sorry and i can iterate on my loop this is to give me a little go to i'm sorry i missed this one too um we have leave i can jump out of my loop but iterate is just going to jump me back to the top of the loop again so it's like a go to let me just see this let me throw this over here just make sure we're good with it i don't lose it and so let's jump back over here we can see a little better i'm sorry i missed this part so in here is i have this iterate and so here i'm going to come through and i can leave it which means go to the next one or i can iterate it which basically says go to that label and so if i do that let's see if i do current and now execute it then it's going to come through and it prints my even numbers as i expected so i think that's we're going to wrap this one up because we've we've covered a couple little things with our looping mechanisms we will return we'll see some a little bit more use of labels and ways to sort of pop around a little bit and we've got some other structures within stored procedures that we can use to wheel and deal with our data that being said that wraps 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]
[Music]
three two
one
well hello and welcome back we're
continuing our series of sequel
tutorials uh focused a lot on my sequel
maria db
and we're continuing at this point
looking at uh basically looking at store
procedures although
the
a lot of things that we're learning here
these syntax related items could be done
within queries as well
or
within just general scripts
they tend to be more useful though
or more prevalent at least in a stored
procedure concept
this time we're going to look at loops a
couple different looping mechanisms
so jumping into our little database here
the first thing i want to do and
actually
expand that out just a little bit so we
see it better
so this would be our first um
oh i've got an error somewhere in there
uh let's see
set counter
blah blah blah
oops i didn't declare that yet so i'm
going to jump over first i'm going to do
is we're going to look at loops but let
me
walk about this before i fix that uh
what we're going to see is got just a
little procedure i'm going to call it
while loop
and we're going to send it something
called iterations so it's just how many
times we're going to run it
and then we're going to have a counter
and then we're gonna basically just
count up say well the counter is less
than iterations do
and it's just gonna select counter
and then we're gonna set counter equals
counter
plus one
so
let's see let's see what we messed up
with here
so if we go in here
he is iterations he's going to clear
that
and counter equals counter plus one
should be right it's a while
so let's try that again let's see what
we missed here
okay so it looks like dang it i'm back
into this final thing so let's go back
to here we go so
source
uh
oh
let me go to current.com i do create
or replace
okay so now i've got my loop i'm sorry
so i've got my loop and what i'm going
to do is um
i'm able to what i'm going to do is i'm
just going to give it a number of
iterations this while loop thing
and it's going to be while the counter
is less iterations i'm just going to
select a number
add to it and then do my loop
and so if i do call while loop
with uh let's just do one
then i'm gonna see my counter if i do it
five times
then i can see here where it does a
counter at each of those i could also do
a counter at the end of it
and within this within our while loop
um what i do want to show
let's just do this real quick i'll paste
this even though it won't like it ah
here we go
there we'll blow that up a little bit
okay that'll be a little easier to read
so
and actually i'm here
three two
one on here let's do this let's just
bring the whole guy over
be a little easier to read
so while we're in here notice that it's
it doesn't have like a beginner and it's
sort of like an f is that you start with
your while
and then you've got
some commands
and then in while now in here we can do
all sorts of stuff we can do selects we
can do updates we can do whatever we
want this is just
your typical coding syntax structure
so we're in
a pretty good shape now to start doing
some loops if we wanted to
but as always
we may not want to do it as a while
but for example
i do want a good wild example would be
maybe you want to go through
and process
records in a table
and after each one
there may be
maybe there's records deleted or there's
values that are changed or at least each
cycle
and
you're going to want to basically
re-query it and just say while i still
have records in here to process then do
processing
and you easily see this a lot with um
some sort of transactional type data
where there's like let's say there's a
status that's
not processed
and what you could do is especially
depending on the
timing of the processing and stuff like
that
it may take a little while so it could
be that the
records to be processed have changed
since you cycle through
now depending on the size of the table
it could be costly to have something
where you're
regularly
checking uh you know going back and
hitting a
a bunch of records but what you could do
is have something where you start out
and you do like uh you know up here you
do something like select
records to be processed
if i can type that right
first you do like select records to be
processed
then you do your processing loop
and then
even within this you would have like
well
let's sit this way
wow
we have records
to process
so you grab them all or maybe some limit
of it you know like say select
next 10
or let's say next 100 records to be
processed
and then
you just keep on going through that and
then you know you'd have an n while down
here
of records to process
so basically what you could do is you
could say select the next 100
process them
and then come through and then select
the next 100 so as long as you have some
you're going to keep iterating through
it you can have a couple of you know
nested loops in that sense
let me get rid of those for posterity's
sake
and let me make sure in the
notes
that i have that
and then
oh that's not the one i want i want this
one
so that's our while loop now another one
we can do is a repeat loop
and that one looks like
this oops let me throw this in the
current real quick apologies while i'm
moving stuff around a little bit
because my copy and paste does not seem
to like itself right now so in this one
with repeat same thing i'm going to give
it a number of iterations it's going to
feel
basically the same i'm going to do
something a little different just to mix
it up though but i come in and i declare
my counter
and
i'm going to have a result which is a
string
so you know we don't want to get too
long but uh what it's done with each one
is it's going to count uh it's gonna
come in and it's gonna set the result
equal to
whatever our
number the result is plus the counter
and then a comma
so it'll be you know one comma two three
comma one comma two comma three comma
four comma five comma blah blah blah
update our counter and we're just gonna
repeat until the counter is greater than
or equal to the number of iterations
and then in repeat
and then we're at the end just going to
select the result we're just going to
print that out so it won't be quite as
complicated
to see
oh so if i just do
let's see if i got that right
okay so he's there i called him repeat
demo
so now if i do call repeat
demo
now let's say i do it and we'll do it
five times
so he comes through and he does one two
three four and then a comma and note
uh let's see so i start with one
and i'm going to repeat it until the
counter is greater than or equal to the
number of iterations well this case it's
actually
um
i probably wanted to start this at a
zero and change around a little bit
because i'm not getting five iterations
because i already started
at iteration one and so when i come
through here
he's checking it at two
so really what i'd wanna do is say
greater than the number of iterations
uh and let whoop
i don't wanna do that uh i don't want to
do create
or replace
and now if i do it oops i need to put
this in this current
and now if i do that
now let's see one two three four five
so you have to be as always you have to
be
cognizant and intentional about how you
do your your numbers and particularly
off by ones you know less than equal
greater than equal things like that
happen
regularly so you're going to want to see
where your counter
you know gets incremented and you would
run into this with while loops as well
it just happened to be this is a good
time to examine it
and one of the difference between
repeats and whiles is
a while does the check at the beginning
which means it may not even run at all
because you could say while true equals
false and it would just skip it because
true never equals false repeat until
always runs once at least
so if you did repeat until true equals
false
then
it will run it once and then it'll be
done
so that's something to consider as well
do you want it to run does your root
loop need to execute at least once
or not you know is it something that you
may not want to execute it at all
and so
let's look at
uh
let's see
oh wrong one let's look at our next one
which is going to be let's do a leave
now what you can do here let's bring
this over here so we can read it
we're going to add
labels
and this label here we're just going to
call it sp for stored procedure
and we're going to call this thing leave
me and it's just going to go through
it's got this id count
and what i'm going to do is i'm going to
see this is working with our tables
again so i'm going to say do i have
an id
for
an app user equal to whatever id i sent
in
so then if i do or i'm sorry
if i don't which means if i can't find
one here
and so let's look at that so if we do
it's like count star from there
and uh let's say i make the id equals
one
oh
uh i'm sorry it doesn't like that
because of the into
our
let's change this real quick
sorry so let's do count star let's get
rid of the n2
and do this and we'll just clean this up
a little bit
so we're normally going to see so let's
just say let's start with one
so if we come in here
i have one that's got an id of one do i
have one with an id of three yes i do do
i have one with an id of six no i do not
so i'm getting this count i have one id
and since this is a primary key
it's either one or zero that's what i'm
going to run into
so
for this
let me get rid of this
what i'm going to do is i'm going to see
do i have it
if id counts equals 0 which means if i
don't
then we're going to use this thing
called leave
and what leave does is it says basically
jump out
to leave this bracket
this labeled bracket in this case it's
going to be the store procedure itself
so
if they give me an id that's not valid
i'm not going to do anything otherwise
i'm going to select start from app user
i'm going to get that record
so if i take this uh see he should be
all good let's throw him into current
real quick
uh well let's do that i think that's
where he's at
where is current let's close a couple of
these like random files
okay so now
oh that one this one
and so we're gonna do our source current
there we go and what do we call this we
call this leave me
so if we call leave me
with one because we know the idea of one
exists
then we get that record if we do it with
two
then
two doesn't exist but three does i think
there you go three does
does four
four does does five
five does six does not we just check
that so six doesn't so you either get
the record or not
and so you can do
a lot of different things like this
to
basically bail out of a loop
which brings us to
just a general loop
uh
let's see where did i put oh here we go
so let's look at this general loop and i
may actually change this
okay
so whoop
so let's put this where you can read it
so very general loop
uh we're gonna call this one looper and
this one
is just gonna loop
and it's just account i'm not even
giving it i'm just gonna throw a number
out there
and so here i've got this loop label so
now the label is not on the stored
procedure but it's on the loop
and so what happens is what i'm going to
do is i'm going to say hey if x which is
this little number that i set to 1. it's
greater than 10 then i'm going to leave
and i'm going to leave it here which
means
this thing this structure
i'm going to leave it and i'm going to
come to the next line which is going to
be this select string
here i'm going to do set x i'm going to
increment my counter
if it equals
uh and basically
if it's even then i'm going to iterate
otherwise i'm going to concatenate a
string
and then i'm going to bail out so it's a
you know a different little
it's a different little way to approach
stuff
uh just as a simple
sample uh but let me do this actually
what i'm gonna do is let's just change
this up
a little bit
and instead of this what we want to do
is
let's do
so this one we're going to come in
and
we're just going to start building up
numbers but if we get beyond 10
then we're going to leave the loop
uh let's see
so let's go here let's go current
and let's make sure i've gotten that one
sort of updated because i tweaked that a
little bit
and now if i do current
okay so he's good and i called him
looper
which has no parameters so if i call it
i'm missing something here
oh i got rid of my x equals x plus one
so
that's a problem
oh i lost like a bunch of that i did not
copy that over properly
oh there that's the whole thing okay
let me show axe oh
i managed to
totally screwed
that one
up
let's do create or replace
just to be simple instead of doing a
drop
and we're going to go over here and
we'll do the same thing create
or
replace
um
oh and i just need to run it again
so
uh
see
what i miss here
oh i'm sorry and i can iterate on
my loop this is to give me a little go
to i'm sorry i missed this one too
um
we have leave
i can jump out of my loop but iterate is
just going to jump me back to
the top of the loop again so it's like a
go to
let me just see this let me throw this
over here just make sure we're good with
it i don't lose it
and so let's jump back over here we can
see a little better i'm sorry i missed
this part
so in here
is i have this iterate and so here i'm
going to come through and i can leave it
which means go to the next one or i can
iterate it which basically says go to
that label
and so if i do that
let's see if i do current
and now execute it
then
it's going to come through and it prints
my even numbers as i expected
so i think that's we're going to wrap
this one up because we've we've covered
a couple little things with our looping
mechanisms we will return we'll see some
a little bit more use of
labels and ways to sort of pop around a
little bit
and we've got some other structures
within stored procedures that we can use
to
wheel and deal with our data
that being said
that wraps 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