Detailed Notes
1. Functions 2. Removing Functions 3. Listing Functions
DELIMITER //
CREATE FUNCTION multiply3( param1 decimal(10,2), param2 decimal(10,2), param3 decimal(10,2)
) RETURNS float DETERMINISTIC BEGIN return param1 * param2 * param3; END //
DELIMITER ;
DELIMITER // CREATE FUNCTION SumStats( param1 integer ) RETURNS integer DETERMINISTIC BEGIN select sum(value2) freom stats limit param1; END //
DELIMITER ;
SHOW FUNCTION STATUS;
DELIMITER //
CREATE FUNCTION add3( param1 decimal(10,2) ) RETURNS float DETERMINISTIC BEGIN return param1 + 3; END //
DELIMITER ;
Transcript Text
[Music] well hello and welcome back we are continuing our series of sql stuff uh focused uh we're using mysql and mariadb for our uh our examples and today we're gonna talk about functions now we have looked at function things like functions with stored procedures so what we're going to see is something that's just a little bit different when you get into the world of functions but it's a couple of key changes so let's just start with this oops let me do this spoiler alert you'll be able to see this okay so we have uh with a function we're going to do start with create function much like create procedure would be and we can do create or replace function much as we did with stored procedures we can give it a name same thing we did we give it some parameters with types this looks should look very much like a stored procedure and then in this case we get uh we've got a couple little different things one we have this returns and returns is a data type so it could be float integer varchar whatever it happens to be it could be decimals whatever you want it to be and that's telling it what this function is going to return and then you have this command it's either deterministic or not space deterministic and that says whether or not the same parameters will return the same value now this becomes pretty important when you talk about performance and caching and things like that this being a good example if i'm gonna call multiply three a thousand times and i have the same you know and it's the parameters are one two three let's just say those are the values and i know that the answer is the same every time then instead of going into this code i can just store that when i call it with those values what the you know what the solution is what the result is and that's going to save a lot of processing time particularly if you're doing this you know a thousand times so deterministic versus non-deterministic is uh important thing to have now by default it's not deterministic so you usually don't have to worry about it except in the cases where you know that the parameters are the same parameters are always going to return the same value and this can be very important particularly as you get into more complicated functions that are doing lookups or things of that nature because you can really allow the database to cache and tune itself so that it you know it effectively runs as fast as possible now then from there it's going to be just as we do with a stored procedure we do a begin and an end we're going to have a bunch of commands now the difference with a function is that you're going to have this return and it could be a value uh it could be like a variable it could be as i did in this case or i just return since just multiply 3 i'm just going to return multiplying those three numbers together and so the the functions are going to look very much like a stored procedure the difference between the two is stored procedures don't necessarily return values and you do your selects or something like that whereas a function the whole point is to call that function return a value therefore if this is a stored procedure you would say call multiply three um and since we're decimals let's just do like 35.8 i don't know 82.4 and four okay that would be if it was a stored procedure we'd call that but it's not because the call is sort of like along the lines of a selector something like that instead we just do it like this there's no call in front of it and so i can do a select so i can do a select and then i have this function and we've seen functions these are custom ones but we've seen them built in when we do like a a min and a max or concat or things like that so those functions are there we're just adding our own so now if i do select then as we normally see i get my you know and i could do uh mult multiply three numbers i can even do it this way so multiply three numbers and i get that and notice i know it's you know this one's a very simple one but notice that initially this was in 0.001 seconds this one's actually faster because after you call it the first time so let's uh let's change this up so i think we'll see that it's such a fast one in general oh this is a big number uh that's going to come in same thing it's going to hit super fast but what's happening is it can store that so it you will typically see uh the first call will be a little slower and then after that it will have a cash value in some cases and it'll go faster in particular for like i said if we're getting into something that it takes a few seconds to build that out so if we wanted to do um let's see what did we call that well let's do it this way so if we do what did we have so if we do like some value stats so let's do we're gonna play around with this a little bit so now we're gonna do we're going to do some stats sum so we'll even do it this way so we're going to do some stats and it's going to be parameter one that's just going to be an integer and it's going to return an integer let's do it that way this will be deterministic and from what we want to do here let's do select some value two from stats limit param let's see if this will work because normally we haven't talked about a limit yet we'll come back to that later but that's normally going to be so let's do that first so if i do select star from stats i can get my eight rows back but i can also do limit five and only get five rows back so now let's take this guy i need to set my delimiter somewhere up here oh and that's in put this in my notes and now we can get rid of that and let's see if that works so do source current uh yeah so it needs a hard number i wonder if it is it let's see if we'll do that nope so i can't do that let's do uh what was stats where we'll do a little different id less than that well uh wait that may be a typo so maybe i can do that i'm not allowed to return a result set from a function oh my about into uh student clear at results integer into that results i need to return that result let's see if that works so i'm running into all right now let's go look back here where i was playing with variables before uh let's make oh listen make him so result there there we go okay and then i can do uh select what was that uh some stats and let's do it seven let's see if it takes a minute okay so it took point zero one let's do the same thing now it took zero so again oh well we're not seeing it unfortunately this is not taking long enough i need a lot more rows however uh it gives you the idea now that we can so we can do this we can you know we can change it up oh this one's given the same value no matter what interesting uh we have a bug that we're id is less than that and let's do create or replace five okay so it didn't yeah it didn't like that it needed a hard number instead of a value variable oh and now we're going to see if we called the first time oh no because it was deterministic and i already called it uh yeah it's too close so i can't i'm not and i was looking at i apologize i'm looking at the how fast it ran and it does annoy it's it's not a big enough one if you've got something that runs like a second or two then i think when you start adding to terministic then you can do it you just got to figure out something that takes a while in this our database we're just not going to have it so you've already seen let me skip ahead so the next thing you can do is you can list functions and let's show stats functions let's see so we have here actually it's a show function status and so we can see this a little bit complicated but we can see here that in this database tutorial we have two functions we have multiply 3 and some stats the type is a function and basically you can see who created it that's my user name and you know some other stats if you wanted when it was created when it was modified um not a whole lot of interesting stuff there but we can do drop function multiply three and now we'll see that doesn't exist anymore then we can do drop function some stats and now we have no functions anymore so we can do these just like for proceed stored procedures that again the key here is that now we can do this um whereas you're going to do a call with a stored procedure so you can't do like select uh let's see i don't know if i have a store proceed but if i do store procedure real quick um so i don't know if i have that cursor example so let's do that real quick let's see if i can cut and paste not done like that it's too long okay so if i put that in here and then i do call what did i call that cursor example i get mine that but i can't do select cursor example so when i'm doing so if i do select star from stats i can't do like selects uh stat id comma cursor example comma value one from stats it's gonna blow up but i can do let's do multiply through let's just create multiply uh let's just we're going to create one real simple but i can do things like that's a very simple one add three that just has one parameter turns a float deterministic return that plus three very simple and if we go in here and now if we do source current dot sql we can come in here and we can do so we can do value one comma add three value one and we can put that there bam and so we can see here we're with each number you know one plus three is four two plus three is five and we can build that into our select so now instead of where stored procedure is gonna do some work a function is going to do usually a modification to some work based on data so we can use those within our data as opposed to a stored procedure that is sort of encapsulating tasks and within data so that gets us to gets us through functions and hopefully now between stored procedures and functions we've got a good list of things that we can work on work with to do some pretty complicated logic within our database and within our queries and that'll wrap this one up we're going to go ahead and call it a lesson on this one but we'll come back we've got plenty more to discuss and as always go out there and have yourself a great day a great week and we will talk to you next time [Music] you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our series of sql stuff uh
focused uh we're using mysql and mariadb
for our
uh our examples
and today we're gonna talk about
functions
now we have
looked at function
things like functions
with stored procedures so what we're
going to see is something that's just a
little bit different
when you get into the world of functions
but it's a couple of key
changes so let's just start with this
oops
let me do this
spoiler alert you'll be able to see this
okay so
we have uh with a function we're going
to do start with create function
much like
create procedure would be and we can do
create or replace function much as we
did with stored procedures
we can give it a name same thing we did
we give it some parameters with types
this looks should look
very much like a stored procedure
and then in this case we get uh we've
got a couple little different things one
we have this returns
and returns is a data type so it could
be float integer varchar whatever it
happens to be it could be decimals
whatever you want it to be
and that's telling it what this function
is going to return
and then you have this command
it's either deterministic or not space
deterministic
and that says whether or not
the same parameters will return the same
value
now this becomes pretty important when
you talk about
performance and caching and things like
that
this being a good example if i'm gonna
call multiply three
a thousand times
and
i have the same you know and it's the
parameters are one two three let's just
say those are the values
and i know that the answer is the same
every time then instead of going into
this code
i can just store that when i call it
with those values what the you know what
the
solution is what the result is
and that's going to save a lot of
processing time particularly if you're
doing this you know a thousand times so
deterministic versus non-deterministic
is
uh important thing to have now by
default it's not deterministic
so
you usually don't have to worry about it
except in the cases where you know that
the parameters are the same parameters
are always going to return the same
value
and this can be very important
particularly as you get into more
complicated functions that are doing
lookups or things of that nature because
you can really
allow the database to
cache and tune
itself so that it you know
it effectively runs as fast as possible
now then from there
it's going to be just as we do with a
stored procedure we do a begin and an
end
we're going to have a bunch of commands
now the difference with a function
is that you're going to have this return
and it could be a value uh it could be
like a variable it could be as i did in
this case or i just return
since just multiply 3 i'm just going to
return multiplying those three numbers
together
and so
the
the functions are going to look
very much like a stored procedure the
difference between the two is stored
procedures
don't necessarily
return values and you do your selects or
something like that whereas a function
the whole point is to call that function
return a value
therefore
if this is a stored procedure you would
say call multiply three
um and since we're decimals let's just
do like 35.8
i don't know 82.4
and four okay
that would be if it was a stored
procedure we'd call that
but it's not
because the call is
sort of like along the lines of a
selector something like that instead
we just do it like this there's no call
in front of it and so i can do a select
so i can do a select and then i have
this function and we've seen functions
these are custom ones but we've seen
them built in when we do like a
a min and a max or concat or things like
that so those functions are there we're
just
adding our own so now if i do select
then as we normally see i get my you
know and i could do uh
mult
multiply
three numbers
i can even do it this way
so multiply three numbers
and i get that
and notice i know it's you know this
one's a very simple one but notice that
initially this was in 0.001 seconds this
one's actually faster because after you
call it the first time so let's uh let's
change this up
so i think we'll see that it's such a
fast one in general
oh this is a big number
uh that's going to come in same thing
it's going to hit super fast but what's
happening is it can store that so it you
will typically see uh the first call
will be a little slower and then after
that it will have a cash value
in some cases and it'll go faster
in particular for like i said if we're
getting into something that it takes
a few seconds to build that out
so if we wanted to do um
let's see what did we call that well
let's do it this way
so if we do what did we have so if we do
like some value
stats
so let's do
we're gonna play around with this a
little bit
so now we're gonna do
we're going to do some stats
sum
so we'll even do it this way so we're
going to do some stats
and it's going to be parameter one
that's just going to be an integer
and it's going to return an integer
let's do it that way
this will be deterministic
and from what we want to do here
let's do select some
value two
from
stats
limit
param let's see if this will work
because normally we haven't talked about
a limit yet we'll come back to that
later but that's normally going to be so
let's do that first
so if i do select star from stats
i can get my eight rows back but i can
also do limit
five
and only get five rows back
so now let's take this guy
i need to set my delimiter somewhere up
here
oh and that's in
put this in my notes
and now we can get rid of that
and let's see if that works
so do source current
uh
yeah so it needs a hard number
i wonder if it is it let's see if we'll
do that
nope
so i can't do
that
let's do
uh what was stats where
we'll do a little different
id
less than that
well uh
wait
that may be a typo so maybe i can do
that
i'm not allowed to return a result set
from a function
oh
my about
into
uh student clear
at results
integer
into
that results
i need to return
that result let's see if that works
so i'm running into all right now let's
go look back here where i was playing
with variables before
uh let's make oh listen make him so
result
there
there we go okay
and then i can do
uh select what was that uh some stats
and let's do it seven let's see if it
takes a minute
okay so it took point zero one let's do
the same thing now it took zero so again
oh well we're not seeing it
unfortunately this is not taking long
enough i need a lot more rows however
uh it gives you the idea now that we can
so we can do this we can
you know we can change it up
oh this one's given the same value no
matter what
interesting
uh
we have a bug that we're
id is less than that and let's do create
or replace
five
okay so it didn't yeah it didn't like
that it needed a hard number instead of
a value variable
oh and now we're going to see
if we called the first time
oh no
because it was deterministic and i
already called it uh
yeah it's too close so i can't i'm not
and i was looking at i apologize i'm
looking at the how fast it ran
and
it does annoy it's it's not a big enough
one if you've got something that runs
like a second or two then i think when
you start adding to terministic then you
can do it you just got to figure out
something that takes a while in this our
database we're just not going to have it
so
you've already seen
let me skip ahead so the
next thing you can do
is you can list functions
and
let's show stats functions
let's see
so we have here actually it's a show
function
status
and so we can see this a little bit
complicated but we can see here that
in this database tutorial
we have two functions we have multiply 3
and some stats
the type is a function
and
basically you can see who created it
that's my user name and you know some
other stats if you wanted when it was
created when it was modified
um not a whole lot of interesting stuff
there
but we can do drop function
multiply three
and now we'll see that doesn't exist
anymore then we can do drop function
some stats
and
now we have no functions anymore
so we can do these just like for proceed
stored procedures that again the key
here is that now we can do this um
whereas
you're going to do a call with a stored
procedure so you can't do like select uh
let's see i don't know if i have a store
proceed but if i do
store procedure real quick um
so
i don't know if i have
that cursor example
so let's do that real quick let's see if
i can cut and paste not done like that
it's too long okay
so if i put that in here
and then i do call
what did i call that cursor example
i get mine that but i can't do
select cursor example
so when i'm doing so if i do select star
from
stats i can't do like selects
uh stat id comma
cursor example
comma
value one
from
stats
it's gonna blow up but i can do
let's do multiply through let's just
create multiply uh let's just we're
going to create one real simple
but i can do things like
that's a very simple one add three that
just has one parameter
turns a float deterministic
return that
plus three
very simple
and if we go in here
and now if we do source
current
dot sql
we can come in here and we can do
so we can do value one
comma add three value one
and we can put that there bam and so we
can see here we're with each number
you know one plus three is four two plus
three is five
and we can build that into our select so
now instead of where stored procedure is
gonna do some work a function is going
to do usually a modification to some
work based on data so we can use those
within our data as opposed to a stored
procedure that is sort of encapsulating
tasks and within data
so that gets us to
gets us through functions and
hopefully now between stored procedures
and functions we've got a good list of
things that we can work on
work with to do some pretty complicated
logic within our database and within our
queries
and that'll wrap this one up we're going
to go ahead and call it a lesson on this
one but we'll come back we've got plenty
more to discuss
and as always go out there and have
yourself a great day a great week and we
will talk to you
next time
[Music]
you