Detailed Notes
1. Cursors
DELIMITER // CREATE PROCEDURE cursorExample ( ) BEGIN DECLARE done INTEGER DEFAULT 0; DECLARE theName varchar(100) DEFAULT "";
-- declare cursor DEClARE curName CURSOR FOR SELECT username FROM app_user;
-- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN curName; myLoop: LOOP FETCH curName INTO theName; IF finished = 1 THEN LEAVE myLoop; END IF;
-- provide output select theName; END LOOP myLoop;
-- Clean up when we are done CLOSE curName;
END// DELIMITER ;
DELIMITER // CREATE OR REPLACE PROCEDURE cursorExample ( ) BEGIN DECLARE done INTEGER DEFAULT 0; DECLARE theName varchar(100) DEFAULT ""; DECLARE output varchar(1000) DEFAULT "";
-- declare cursor DECLARE myCursor CURSOR FOR SELECT username FROM app_user;
-- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN myCursor; myLoop: LOOP FETCH myCursor INTO theName; IF done = 1 THEN LEAVE myLoop; END IF;
-- provide output set theName = concat(theName,", "); set output = concat(output,theName); END LOOP myLoop;
-- Clean up when we are done CLOSE myCursor; select output;
END// DELIMITER ;
DELIMITER // CREATE OR REPLACE PROCEDURE cursorExample ( ) BEGIN DECLARE done INTEGER DEFAULT 0; DECLARE theName varchar(100) DEFAULT ""; DECLARE theLogin varchar(100) DEFAULT ""; DECLARE output varchar(1000) DEFAULT "";
-- declare cursor DECLARE myCursor CURSOR FOR SELECT userName,userLogin FROM app_user;
-- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN myCursor; myLoop: LOOP FETCH myCursor INTO theName,theLogin; IF done = 1 THEN LEAVE myLoop; END IF;
-- provide output set theLogin = concat(" [",theName,"]"); set theName = concat(theName,theLogin," | "); set output = concat(output,theName); END LOOP myLoop;
-- Clean up when we are done CLOSE myCursor; select output;
END// DELIMITER ;
Transcript Text
[Music] one well hello and welcome back we are continuing our sql tutorials and most of the focus on mysql and mariadb is our test environment and today we're continuing looking at storm procedures and some of the things we can do there and we're going to get into cursors now for this one if i can find my document here um i'll blow this up a little bigger we're going to and what a cursor is is a a pointer for a set if you think about a uh like a number of items and it you know we've seen cursors in many ways but so let's say you've got like item one two three four five these are our records cursor is basically like the current row selector for that so when the curs if there's a cursor for these five rows if the cursor's pointing to this row then you're going to that's sort of your current context and then you can move the cursor to the next row and the next row on the next or the next row which is really the point and the the use with our context with what we're talking about what we're going to want to do is we're going to get a selection we're going to get a record set and then we're going to use a cursor to walk through that and do some sort of processing in our case we're going to keep it sourced simple so we've got this little stored procedure that we're just going to call cursor example we're not going to send any parameters or anything and we're going to start out with a couple variables we declare one is done and it's going to start at you know zero it's gonna be basically our boolean so if it's a one then it's done one and done zero it's not done and then we're gonna have a storage variable called the name that we're gonna keep up with now to get it for uh declaring cursor is pretty straightforward as you just declare um i'm sure i did miss my typing on it and you give it a name so i'm just going to call it so this is just cursor name or actually let me call it my cursor just to make it and it's and you declare it as a cursor for which means what is it cursoring what's the record set in this case i'm just going to select username from app user i could do something more complicated but for now we're going to keep it really simple we're going to have a not found handler which is basically says hey if i don't find something so when i try to do something with the cursor as in if i go to next record and it's not found then i'm going to set done equal to 1 here so that just means that hey if i get to the end of the the record set i'm done and then in order to execute it basically we call an open so i'm going to do an open on my cursor i just want to make sure i cut up my other thing and i'm going to kind of i'm going to have a thing i'm just going to call it my loop in this loop i am going to fetch uh this is my cursor into the name which is my little temporary variable and uh if i'm finished i'm sorry if done so if i'm done which means if i get a you know right here so to continue it doesn't exit i could just blast out of here if i don't find it what i'm going to be you know do a nice little clean uh leave of this store procedure so if i'm done if i've run off the end of my list my record set then i'm just going to leave my loop otherwise i'm going to select the name and uh here's the end of my loop and then i'm just go i've got a we did an open up here for the cursor and then when we're done we want to close it so we start open our connection and then we close the connection so if i take that and let's go here let's make sure hopefully i got everything right and i can do source i got sequel let's see what it does up uh undefined cursor her name uh where did i do oh i did not change it here all right cursor let's go over here oh i didn't get that let's do it this way my cursor i accidentally told it to save which i didn't want to do and let's try that again okay so now if i call cursor example then what i'm doing here is i'm going to see user1234 and let's let me do that select for you so i have user1234 my users and it's going to come through each time i'm just doing a select here now i can do something more interesting so i could do um let's move him outside of the cursor uh let's do that oh except for i don't think it's gonna like that concatenation but we're gonna find out in a second i always forget that piece so now oh do i create a replace uh nope so i need to do or replace and now if i come back here and source it okay now it's going to complain because that doesn't uh that's not how we concatenate i do want to do um i believe it's this oh i guess i'm doing it into the name so let's call this i'm gonna change this a little bit because now we're gonna have to call this output and we'll make it a larger like i don't know 1000 we'll make a nice long line and then output equals concatenate output comma the name kind of that i think i can do it that way and if i do select output see how that looks still didn't like it because it's not that concatenate oh sorry it's not concatenate i always forget it is concat and let's take that and flip him over to oh wait let's go here put that in here just so we've got it in our notes uh let's see oh and we're probably i don't think he likes it doing it with three so first we're gonna do that um let's do this equals get that we're gonna concatenate the name there and then we're gonna do that and let's see if that is a little more friendly nope still doesn't like it because let's see what did we miss here oh i forgot to do our sets so we do that and now we run it then we're good if we try to do that call somewhere back here now what we're doing uh let me blow this uh let me do two things okay take this and replace it and let me put it over here so we can see it a little better so now what i did is i declared this output and i should probably call it something else but we'll do that for now um and then instead of doing the select each time like i did this time what i do is each time i come through i concatenate a comma to the name and then concatenate that to the output and then i get done i do one select and i have my user list here so i can do something more complicated so i can do let's do this and let's do so what i want to do here let's play around with this a little bit let's do that guys let's do oh let's do uh username and user login i'm gonna use your login well this should be a comma name the login and then uh that's all good i'm going to search that into name comma log in and then let's do this let's get really fancy so the name equal let's see let's see if i can do this i think i can do it this way oh and then i want that to be in let's just do a pipe just because oh so let's play with that let's see how that does for us whoops that's not what i want this is what i want so now when we call it we've so i'm sorry i flew right through that so what i'm doing now so now what i'm going to see is for each row i'm seeing the username and the uh the user log in in brackets there beside it so what i did and i could probably break these out make them even you know do a one-liner here but now um actually let's flip it over here oh and let's go ahead and tack it to the end here so it's in our notes so now what we're doing and we can do this with any number of fields but what we're gonna do is now say okay i've got two different values so i've got two columns i'm selecting for my app user and then all i'm going to do is i just fetch from my cursor into whatever fit you know as many variables as i need to match those fields that i'm pulling in the select and so in this case all i'm doing is coming in is saying all right i'm going to come in and set up my um instead of my little string here of wrapping the log in with a couple brackets and then appending that to the name so that we get username and then log in and then just put some brackets to do that now i could do that i could do selects i could do all kinds of different things that i wanted to as far as wheeling and dealing with that i could also take that and turn around and use that as something you know grab one of those values and then try to grab a value from a different table or or could do that for example and for each um each record like in the app user i could run one you know i could go through and with each one call a stored procedure on that particularly like if we do an update or something like that there's some things that we can do that is your normal uh working with loops and collections or you know result sets so cursors become pretty powerful if you want to start really wheeling and dealing with your data within your stored procedure and there's a lot of things that we can do with them that help us as far as like performance is concerned and some other things because we can we could do and we don't this can have a where clause this can be any our cursor can be any select so we can get very complicated in our cursor and how we pick our data that we're going to be working with so that gives us so an introduction to cursors and working with them having a couple fields how do we make that work and how do we integrate that into stored procedures and that covers what i wanted to cover for today for this uh session so we will wrap this one up and uh we're just gonna keep continuing working our way through the various things that are out there playing around with some of these um i don't know how far we are into this as i said we've still got i know a few more than a few more topics so just keep coming back we're gonna keep throwing these things out there and uh i have changed it up uh initially it was day one day two day three and that's still sort of in my notes but i'm trying to put the now i'm switching it over so you should be able to see sort of the topic or a primary topic as part of the uh the name of the session if you're out on youtube and hopefully that'll help when you're doing it using this as like a reference or something like that 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]
one well hello and welcome back we are
continuing our sql tutorials and most of
the focus on mysql and mariadb is our
test environment
and today we're continuing looking at
storm procedures and some of the things
we can do there
and we're going to get into cursors
now for this one
if i can find my document here
um i'll blow this up a little bigger
we're going to and what a cursor is
is a
a pointer for a set if you think about a
uh like a number of items and it you
know we've seen cursors in many ways but
so let's say you've got like item one
two three four five these are our
records
cursor is basically like the current row
selector for that so when the curs if
there's a cursor for these five rows
if the cursor's pointing to this row
then you're going to that's sort of your
current context and then you can move
the cursor to the next row and the next
row on the next or the next row
which is really the point and the the
use with
our context with what we're talking
about what we're going to want to do is
we're going to get a selection we're
going to get a record set and then we're
going to use a cursor to walk through
that
and do some sort of processing
in our case
we're going to keep it sourced simple
so we've got this little stored
procedure that we're just going to call
cursor example we're not going to send
any parameters or anything
and we're going to start out with a
couple variables we declare one is done
and it's going to start at you know zero
it's gonna be basically our boolean so
if it's a one
then it's done one and done zero it's
not done
and then we're gonna have a storage
variable called the name that we're
gonna keep up with now to get it for uh
declaring cursor is pretty
straightforward as you just declare
um
i'm sure i did
miss my typing on it and you give it a
name so i'm just going to call it so
this is just cursor name
or actually let me call it
my cursor
just to make it
and it's and you declare it as a cursor
for
which means what is it cursoring what's
the record set in this case i'm just
going to select username from app user
i could do
something more complicated but for now
we're going to keep it really simple
we're going to have a not found
handler which is basically says hey if i
don't find something so when i try to do
something with the cursor
as in if i go to next record
and it's not found then i'm going to set
done equal to 1 here so that just means
that hey if i get to the end of the the
record set i'm done
and then in order to
execute it basically we call an open
so i'm going to do an open on my cursor
i just want to make sure i cut up my
other thing and i'm going to kind of i'm
going to have a thing i'm just going to
call it my loop
in this loop
i am going to
fetch
uh
this is
my cursor
into the name which is my little
temporary variable
and uh if i'm finished
i'm sorry if done
so if i'm done which means if i get a
you know right here so to continue it
doesn't exit i could just blast out of
here if i don't find it what i'm going
to be you know do a nice little
clean
uh leave of this store procedure
so if i'm done if i've run off the end
of my list
my record set then i'm just going to
leave my loop
otherwise i'm going to select the name
and uh here's the end of my loop and
then i'm just go i've got a
we did an open up here for the cursor
and then when we're done we want to
close it
so we start open our connection and then
we close the connection
so if i take that
and let's go here
let's make sure hopefully i got
everything right
and i can do source
i got sequel let's see what it does up
uh undefined cursor her name
uh
where did i do oh
i did not change it here
all right cursor
let's go over here
oh i didn't get that
let's do it this way my cursor
i accidentally told it to save which i
didn't want to do
and let's try that again okay so now
if i call
cursor example
then what i'm doing here is i'm going to
see user1234 and let's let me
do that select for you
so i have
user1234 my users and it's going to come
through each time i'm just doing a
select here
now i can do something more
interesting
so i could do um
let's move him
outside of
the cursor
uh
let's do that
oh
except for i don't think it's gonna like
that concatenation but we're gonna find
out in a second i always forget that
piece
so now oh do i create a replace
uh nope
so i need to do or replace
and now if i come back here and source
it
okay now it's going to complain because
that doesn't
uh that's not how we concatenate i do
want to do
um
i believe it's this
oh i guess i'm doing it into the name
so let's call this
i'm gonna change this a little bit
because now we're gonna have to call
this output
and we'll make it a
larger like i don't know 1000 we'll make
a nice long line
and then output
equals concatenate
output comma the name kind of that
i think i can do it that way and if i do
select output
see how that looks
still didn't like it because it's not
that concatenate
oh sorry it's not concatenate i always
forget it is concat
and let's take that
and flip him over to
oh wait let's go here
put that in here just so we've got it in
our notes
uh let's see
oh and we're probably i don't think he
likes it doing it with three so first
we're gonna do that um
let's do this
equals
get that we're gonna concatenate the
name
there
and then we're gonna do that
and let's see if that is a little more
friendly nope still doesn't like it
because let's see what did we miss here
oh i forgot to do our sets
so we do that
and now we run it then we're good if we
try to do that call somewhere back here
now
what we're doing uh let me
blow this uh let me do two things okay
take this
and
replace it
and let me put it over here
so we can see it a little better
so now what i did is i declared this
output and i should probably call it
something else but we'll do that for now
um
and then instead of doing the select
each time like i did this time what i do
is each time i come through
i concatenate a comma to the name
and then concatenate that to the output
and then i get done i do one
select and i have my user list here
so i can do something more complicated
so i can do
let's do this
and let's do
so what i want to do here let's play
around with this a little bit
let's do that guys let's do
oh let's do uh username and user login
i'm gonna use your login
well this should be a comma
name
the login
and then
uh
that's all good i'm going to search that
into name comma
log in
and then let's do this
let's get really fancy
so the name equal
let's see
let's see if i can do this i think i can
do it this way
oh
and then i want that to be
in
let's just do a pipe just because
oh
so let's play with that let's see how
that does for us
whoops that's not what i want this is
what i want
so now when we call it
we've so
i'm sorry i flew right through that so
what i'm doing now so now what i'm going
to see is for each row i'm seeing the
username
and the uh the user log in in brackets
there beside it so what i did and i
could
probably break these out make them even
you know do a one-liner here but now um
actually let's flip it over here
oh and let's go ahead and tack it to the
end here so it's in our notes
so now what we're doing
and we can do this with any number of
fields
but what we're gonna do is now say okay
i've got two different values so i've
got two columns i'm selecting for my app
user
and then all i'm going to do is i just
fetch
from my cursor into
whatever fit you know as many variables
as i need to match those
fields that i'm pulling in the select
and so in this case all i'm doing is
coming in is saying all right
i'm going to come in and set up my um
instead of my little string here of
wrapping the log in with a couple
brackets and then
appending that to the name so that we
get username
and then log in
and then just put some brackets to do
that
now i could do that i could do selects i
could do all kinds of different things
that i wanted to as far as wheeling and
dealing with that
i could also take that and turn around
and use that as something you know grab
one of those values and then try to grab
a value from a different table or
or
could do that
for example and
for each um each record like in the app
user i could run one you know i could go
through and with each one
call
a
stored procedure
on that
particularly like if we do an update or
something like that
there's some things that we can do that
is your normal
uh working with loops and collections or
you know result sets
so
cursors become pretty powerful if you
want to
start really wheeling and dealing with
your data within your stored procedure
and
there's a lot of things that we can do
with them that help us
as far as like performance is concerned
and some other things
because we can we could do
and we don't this can have a where
clause this can be any our cursor can be
any select so we can get very
complicated
in our cursor and how we
pick our
data that we're going to be working with
so that gives us so an introduction to
cursors and working with them having a
couple fields how do we make that work
and how do we
integrate that into stored procedures
and that covers what i wanted to cover
for today for this uh session so
we will wrap this one up
and uh we're just gonna keep continuing
working our way through the various
things that are out there playing around
with some of these
um i don't know how far we are into this
as i said we've still got i know a few
more than a few more topics so
just keep coming back we're gonna keep
throwing these things out there and uh i
have changed it up
uh initially it was day one day two day
three and that's still sort of in my
notes but
i'm trying to put the now i'm switching
it over so you should be able to see
sort of the topic or a primary topic as
part of the
uh the name of the session if you're out
on youtube and hopefully that'll help
when you're doing it using this as like
a reference or something like that
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