Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I will come back to module force equal programming to we're still in less than one sequel functions.
00:06
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
00:26
make certain ideas easier to digest. In the primary query
00:30
like this name query that we wrote in the previous lesson is pretty
00:36
convoluted to look at,
00:37
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
00:56
in this query and move it into a function that we can then call
00:59
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
01:06
and the, uh I guess that was it? Oh, the replaced function does.
01:11
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
01:26
based on the formatting that is currently in
01:30
the axiomatic a database. So go ahead and make sure you have the axiomatic a database said is your active database
01:37
and we are going to
01:38
start coding our first function. Let's go ahead and get started.
01:45
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.
01:55
So when you're coding functions,
01:57
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.
02:07
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.
02:28
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.
02:37
So drop
02:39
function,
02:42
if exists,
02:46
Parts name. So we're gonna call this function parts name
02:50
and that with this Michael and go out and run that
02:53
we could run it over and over as long as we don't run it while it's still running.
02:59
Let's drop down on then. Say we have to declare another delimit er so we're gonna type in the keyword, the limiter
03:07
like so And we're gonna go ahead and ah,
03:10
use the dollar sign. You might see folks use Florence last four slash
03:16
um, but the dollar sign works well, too.
03:19
And now we're gonna start our create function statement. So we're gonna say, create
03:25
function,
03:30
porous name.
03:32
We're gonna drop down to open some brackets up.
03:37
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.
03:46
That's gonna be a bar char.
03:47
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
03:54
or a text.
03:59
And then our second parameter is going to just be called String,
04:02
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
04:14
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.
04:28
So that is a limitation to be aware off,
04:30
and we're going to return.
04:33
So what do we get back from the function while we get back a bar chart? We get back another string,
04:39
and we'll just make it the same size again. 50
04:42
and ah, here is a new idea. We have to give it a keyword,
04:46
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,
05:01
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
05:17
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.
05:23
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.
05:38
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
05:46
and next is begin. We have to tell it that the definition is beginning.
05:53
De Bure drops in the end statement. That's also required.
05:58
So we're gonna start this off simple. So if
06:01
choice
06:03
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,
06:15
which is what you do in a lot of other languages, too.
06:18
You have to end the F statement, so end. If
06:23
so, pay attention here is that the F statement ends with an end. If semi Colon
06:29
and you also have to have the key word, then
06:30
if you miss either this, then keyword or you forget the semi cooling the function create statement will fail.
06:40
So what are we gonna return then?
06:42
Well, we're going to return
06:44
exactly what we had over
06:47
and
06:48
our previous lessons Query. So we're gonna go ahead and copy this,
06:57
and we are just going to paste it. But that's not gonna work by itself. You have to take this key words string
07:04
and
07:08
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
07:15
notices that we have made a choice of last
07:18
and then horses the string, just like he did in the query
07:23
and returns that the return statement says, Go ahead and make this processing
07:28
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
07:40
fail so that we know that it didn't work for some reason. Maybe spelled last wrong.
07:46
Um, So with the end statement, this is where we have to use our DL imitator.
07:53
And that was the dollar sign. And now we're gonna set back the old delimit er
07:58
e l I am I tr.
08:03
Okay,
08:05
Now let's go ahead and run this.
08:09
Okay? Looks like we have a
08:11
issue.
08:18
Oh, I forgot a semicolon.
08:20
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.
08:30
Oh, I made one other mistake. I forgot. It s This is returns, not return.
08:37
Pops up is a key word as well. Now, if we run it,
08:41
it works correctly,
08:43
so
08:46
let's go ahead
08:48
and see how we would use that.
08:50
So, as you can see, I'm selected from the account where type of e. P. Again
08:56
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,
09:07
and it looks like it worked correctly.
09:11
We see the account name value that's being passed into that function, and we see that the function
09:16
right here that's being a liest as last name is bringing back the last name correctly.
09:22
So we call that function. We pass in the choice with the string,
09:28
and then when we begin, we check if the choices last. If so, we then return the replacement sub string
09:35
with in conjunction with the locate function
09:39
that produces the result.
09:41
And if you want to see your function definition,
09:45
all you have to do is go click on your procedures
09:48
after you refresh, of course,
09:52
and get rid of the filter
09:56
procedures.
09:58
And there's our Parson Am function that we made.
10:01
We can actually take a look at the source and the parameters. The definition
10:07
it's all good information to have,
10:09
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.
10:30
Otherwise, it will fail to create the function as well.
10:33
You can simulate the creative place
10:37
process by dropping the function if exists above your create statement
10:43
and remember that you're using. If statements, not case statements like you did it in sequel code.
10:48
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.
10:56
Hope to see you there. Thank you for your time.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor