7 hours 36 minutes
I will come back to module force equal programming to we're still in less than one sequel functions.
And in the previous lesson, we dealt with using various functions to meet a couple different objectives, from looking at dates to parsing out the name from a name field. Now, in this lesson, we're actually going to create our own function that serves our own purpose. That helps
make certain ideas easier to digest. In the primary query
like this name query that we wrote in the previous lesson is pretty
convoluted to look at,
and I think that it would be much better to place this kind of stuff in a function. And then we'll call that function and we will just get the data that we want out of the function. Now. We still have to deal with some complicated ah logic in that function because we essentially have to take what we've done
in this query and move it into a function that we can then call
to produce the results. Now we're going to make a function that will work just like the location function does the sub string function does
and the, uh I guess that was it? Oh, the replaced function does.
It's going to do something very specific. We're gonna call this function parse name. And the very specific thing that this function will dio is it will parse a string or text field for either the first name or the last name
based on the formatting that is currently in
the axiomatic a database. So go ahead and make sure you have the axiomatic a database said is your active database
and we are going to
start coding our first function. Let's go ahead and get started.
Okay? So go ahead and, uh, again make sure you have a thematic is the active database, and then it will be ah, sequel editor, New sequel editor.
So when you're coding functions,
the DL imitators and the line Terminators come get in your way pretty early on. And that's because they're handled a little bit differently than they are when you're just writing sequel code.
Um, we actually have to declare a different delimit er when declaring functions so that the the the line terminators don't clash. I'll show you what I mean. So let's get started. The first thing we're gonna do. Unfortunately, the function doesn't have a creator replace statement like the view does.
So what we're going to do is we're just going to go ahead and simulate that by calling the drop function if exists before we create a function.
Parts name. So we're gonna call this function parts name
and that with this Michael and go out and run that
we could run it over and over as long as we don't run it while it's still running.
Let's drop down on then. Say we have to declare another delimit er so we're gonna type in the keyword, the limiter
like so And we're gonna go ahead and ah,
use the dollar sign. You might see folks use Florence last four slash
um, but the dollar sign works well, too.
And now we're gonna start our create function statement. So we're gonna say, create
We're gonna drop down to open some brackets up.
This is where we gotta tell it. What parameters we are accepting and we're going to accept to first is going to be a choice.
That's gonna be a bar char.
Now we have to tell it what kind of parameters coming in. So we have to say our choice is going to be a bar chart, which is also known as a string
or a text.
And then our second parameter is going to just be called String,
which isn't a keyword in sequel, So we can use that word here and some languages. You would not be able to do that because the string would actually be the bar chart, But that's not the case here. So I'm gonna
declare is of Arch are 50. So that means it could be taxed, feel up to 50 characters in length, and that should be fine. The only time this would break is if we try to parse someone who had a name longer than 50 characters.
So that is a limitation to be aware off,
and we're going to return.
So what do we get back from the function while we get back a bar chart? We get back another string,
and we'll just make it the same size again. 50
and ah, here is a new idea. We have to give it a keyword,
uh, deterministic or non deterministic. Now, the terminus tick means that we give the same input, we will get the same results. So if we pass in the same exact name over and over and over my seek were telling my sequel,
then he doesn't have to do anything, is just gonna have to spit out the same answer. And it actually is more efficient that way. So the opposite of that would be non deterministic, which would be we give it the same input. But we might get a different answer now. When would that occur? Because
if you're being a little naive about it, you might be like, Well, that doesn't make sense that I get the same input.
I should get the same answer. Well, it depends what you're doing. If you're trying to create a random number or some kind of random string, maybe a key value that's randomized, then you probably won't get the same answer even if you provide the same input.
So let's go ahead and put in the key work deterministic, which means that if it gets the same input, it will return the same value
and next is begin. We have to tell it that the definition is beginning.
De Bure drops in the end statement. That's also required.
So we're gonna start this off simple. So if
equal last now we're using if statements, not the case. *** it, That we use inquiries. That's something to be aware of is that when we drop into functions, we don't use case statements we use if statements,
which is what you do in a lot of other languages, too.
You have to end the F statement, so end. If
so, pay attention here is that the F statement ends with an end. If semi Colon
and you also have to have the key word, then
if you miss either this, then keyword or you forget the semi cooling the function create statement will fail.
So what are we gonna return then?
Well, we're going to return
exactly what we had over
our previous lessons Query. So we're gonna go ahead and copy this,
and we are just going to paste it. But that's not gonna work by itself. You have to take this key words string
replaced the account name with it because that's what we're sending into the function. So we're supplying the string. The string calms down
notices that we have made a choice of last
and then horses the string, just like he did in the query
and returns that the return statement says, Go ahead and make this processing
and return that as an answer. Now, what happens right now if we don't have a choice last that we put in some other words? Well, we're we're not done with this function, but we're just gonna return
fail so that we know that it didn't work for some reason. Maybe spelled last wrong.
Um, So with the end statement, this is where we have to use our DL imitator.
And that was the dollar sign. And now we're gonna set back the old delimit er
e l I am I tr.
Now let's go ahead and run this.
Okay? Looks like we have a
Oh, I forgot a semicolon.
Let's put it in there. Don't forget your semicolons. After each statement, you have to have one. It will not work. So let's go and run that again.
Oh, I made one other mistake. I forgot. It s This is returns, not return.
Pops up is a key word as well. Now, if we run it,
it works correctly,
let's go ahead
and see how we would use that.
So, as you can see, I'm selected from the account where type of e. P. Again
and I have parsed name the function call. I'm passing in the first parameter of last and then I'm passing in the account name value. Let's go ahead and run that,
and it looks like it worked correctly.
We see the account name value that's being passed into that function, and we see that the function
right here that's being a liest as last name is bringing back the last name correctly.
So we call that function. We pass in the choice with the string,
and then when we begin, we check if the choices last. If so, we then return the replacement sub string
with in conjunction with the locate function
that produces the result.
And if you want to see your function definition,
all you have to do is go click on your procedures
after you refresh, of course,
and get rid of the filter
And there's our Parson Am function that we made.
We can actually take a look at the source and the parameters. The definition
it's all good information to have,
so that completes this lesson. I hope you were able to get your function working to recap when creating a function. There's a little there's some extra things that to make sure you make notice off. First of all, you want to make sure you get your son. Michael is in the right place. You got to remember that you have to do that eliminator that the limiter swap.
Otherwise, it will fail to create the function as well.
You can simulate the creative place
process by dropping the function if exists above your create statement
and remember that you're using. If statements, not case statements like you did it in sequel code.
So that completes this lesson. I hope you were able to get their function working, and the next one we will finish this up and parse out the first name.
Hope to see you there. Thank you for your time.