Is that cost money?

If you appreciate this education, please think about going to Eli the computer guy dot com and offering a one time or monthly recurring donation.

Welcome back.

As you know I am.

He like the computer guy, and in today's clients, we're going to be talking about stored functions within my sequel of database servers.

So it's toward functions allow you to do is they essentially allow you to manipulate data within your tables within your my sequel databases to the example that I'm going to be showing you today is, Let's imagine we're trying to figure out with the retail price four.

Our inventory items should be so we have a parts table that I've created a number of classes ago.

Within that part's table.

We have a number of different records for parts, and then we have the price for those parts.

Now, let's imagine the price for those parts is the price that we pay for those parts, and so we want to figure out how much the parts should be.

If we're going be selling them, tow our customers.

I'm going to say in this event that we're just simply putting a 25% markup on all the parts.

So if we purchased it for $10 then we will sell it for $12.50.

We purchased it for $1.

We'll sell it for a dollar and 25 cents.

And so what we're going to do is we're going to create a stored function that will go through, and it will do the math of based off of that 25% markup.

So we'll actually be able to go through, basically, do something along the lines of a select all statement.

And then what will happen is we'll see our price.

It will see the original price on one side, and then we'll see what the new retail price should be on the other.

So we're talking about functions.

This is the type of thing that we're that we're looking at with.

Functions is a lot of times this is going to be mathematical manipulation of data within your tables to provide you some type of results.

So again, trying to figure out, let's say, sale item.

So let's say you have the prices for your inventory.

What it normally is.

And now you want to do a 10% off sale for the day of the week, or whatever you can guess.

Input that 10% off within a store function, and then it will give you the price that you should be charging your customers.

Now, I will say, when you start getting into store functions like many things like you've probably heard this before, like many things in the mice Equal world.

Wow, You go really deep on this stuff.

You can go into a rabbit hole as far stored functions are concerned.

One of the problems that I run into as somebody trying to teach you about things like store functions is how to teach you enough.

So you get the idea of what's going on with trying to teach you so much that again you give up and go be a florist.

So if you like what you're seeing here and you want to learn more about store functions, we will doom or classes about this in the future.

But obviously, you know, as with everything in here, go do a Google search.

Take a look at what's stored functions will offer you because store functions if you need them.

Like if your particular environment needs sword functions.

Store functions could be absolutely amazing that they can provide you a lot of solutions to the problems that you're having.

But it's one of those things.

If it gets very Mathey very quick, I know a lot of that's one things like with I t people.

A lot of people out there surprisingly don't like math, something they don't like.

My hand is really bad, man.

And so when the problems you get into a storage functions is basically this is math or is basically like a lot of equation e type stuff.

And so one of the problems you can get into is when you're trying to do very complex functions.

If you're not used to math, and if you're not really sure what you're trying to accomplish, you can get to the point of getting frustrated very quickly and like I say, giving up.

So I'm going to show you a very basic example today, just to give you an idea of how the stored functions work.

If you need to know more about it, please go out there and do some more research.

This the rabbit hole of this goes really, really, really deep.

There's a lot of interesting things that you could d'oh, but to be clear, we're just glossing over the very, very top of this particular set of information just so that you don't get too confused when you're trying to learn about horning horning, Will Robinson, horny and Warning should probably come up with another phrase to say, Who knows who knows all the copyright infringement rules and laws out there that hate to get hate to get hit by saying the warning warning.

Will Robinson is that trademarked?

I don't know.

But anyways, there is a bit of a warning, a warning.

Will Robinson with this particular class, something that a lot of new people or people that might not be used to programming and databases may get confused about is Remember, whenever you're dealing with databases and whenever you're dealing with programming in general, there is what is cold.

There is permanent data and impermanent, so there's permanency and impermanent.

Why, that's important with what we're doing.

Only dealing with today is what we're going to be doing is we're going to be creating impermanent values.

So when we come up with the retail price, that will be showing you in these tables.

So again we're gonna have a parts table within that part's table.

We have the prices that we paid for the item, and then we're going to do a store function to tell us what we should be selling those items for.

So when you're looking, a table will say, like the part I d the part name the park Price, the vendor i D.

And then at the end, it will say what the retail price should be.

The important thing to understand here is that retail price at the end, when you're looking at that view, that is impermanent data that is not actually at this time being stored, and a lot of people could get confused about this.

So it's so that data that's permanent with what we're dealing with today is the data that's in the table.

So the part i d.

Number is permanent.

It is there.

If we shut off the server and turn the server back on that data is there.

The part name is permanent.

It's there.

The part price, the price that we're paying is there on the vendor.

I d we're not worried about that today, but that is there.

That is all data that is permanent.

And you can you can actually work against it in the normal flow of a sequel statement so you can don't go, you know, select.

You know, whatever part name where part price is over such and such, right?

So with permanent data within within the database, you can actually interact with that that data relatively easily that they retail price that we're creating today.

When we're doing this function, we're creating what's called impermanent data.

Basically, that data only exists when you call the function right, So we have the table and the table is there, right?

And then we're gonna create a function to create the retail price.

Well, the table as it was before, and the table after we leave the function are the same.

We're not actually modifying that table at all.

We're not actually inserting any data into the table.

We're not doing an update.

Operation on the table were signed.

It kind of just kind of like scribbling like think about mentally, as it were, like scribbling notes on the side.

And so that's just something toe think about whenever you're looking at things like views and you're thinking and you're working with these tables is think about what data is permanent and what data is impermanent.

Because as you're going, be building out your database for for whatever project that you're gonna be doing.

If you do things like create functions and then you do not save the data that's created by those functions into a table basically out, put them in some way, then it will be very hard for you to be able to interact with those values going onto the future.

A TTE this point, you may be so new that maybe a little bit confusing.

This might be one of those lessons were like five classes.

From now you go.

Oh, that's what Eli is talking about.

But the warning here is that when we're when we're doing these stored functions, today we're creating in permanent data, more or less, you will not be able to do additional calculations or work based upon the data that we've created, and as soon as you leave the function that data disappears, you don't quite grasp what I'm talking about.

Don't worry, it's okay.

Probably in the future it will start to make a little bit more sense.

But that is just the warning.

Here is just because Because that's one thing like when you're looking at a view when you look at a view of something like a table gonna select all.

It's very easy to get confused between the view and the table itself.

So if you do this function, you get the retail price.

It's very easy to think.

Oh, that retail price is now inside the table and is not inside the table.

It's in the view.

It's not on the table.

I should probably stop now before you get even more confused so that let's go over the computer and I can show you how the story functions work.

Okay, so here we are my computer Again.

Again.

I have a Mac book Pro.

I am running virtual box within an instance, on virtual box, I'm running a button to desktop 18.4 Lt s And then I used something called Task Cell to install the entire lamp stack on to this Lennox desktop.

So I have Apache, my sequel and PHP so more or less.

That's how you get to the lab environment that you're dealing with.

Here, go down here.

You go up to search.

Obviously we type in Terminal two terminal.

Now we have our terminals.

The first thing we're gonna do is we're going to log in.

So get my sequel.

Uh, hyphen you are User name is Bob Hyphen.

P password.

Inner password is 123456 And hey, now we are in my sequel.

From that, I will get control l to clear the screen to make this a little bit easier to see.

Uh, now again, toe, always make sure we know what's going on with our database server.

Uh, we do type show databases, semi colon.

That shows us the databases that we're dealing with on the server.

So we want to use the class DBS.

We're going to use class B semi colon de limiter again when you're dealing with store procedures and store functions, that the limiter becomes very important concept.

So again, currently, we're using to semi colon as a delimit er we're going to go in.

So we're in the class d B now.

We're gonna do show tables just so we know what we're dealing with with the tables.

So we have the tables, we have parts and we have vendors.

We do describe parts, so describe the E S.

C will describe the information basically the schema of the parts table semi colon so we can see we have a field we've apart i d number.

So this is just kind of like the the I.

D.

Number for parts.

It is an auto increments.

So every part has its own number, and it is a primary key.

We don't really worry about that.

That's just use more for again the back and stuff to make sure the database doesn't screw anything up.

Then we have apart hyphen R Apart, underscore name that is a text type.

Then we have part underscore Price.

That's what we're gonna be dealing with today.

That is a float.

So the the data type is going to be important here.

And then we had the vendor underscore I d that we're not going to worry about at all today, and that is going to be an integer.

Then just to see what's going on with this particular table.

Would you select all, uh, from parts.

So this will select all columns, all records from parts table.

And so, as we can see here, we have a cog with a rod.

We have a sprocket nut, a washer and a flan, and we've got prices $11.50 $2.50 $8.50 So on and so forth.

So this is one of the important things, right?

If you're gonna be creating something, a stored function, make sure that you're a table exists makes you your table.

The schema for your table looks, how it's supposed to look on.

Then make sure there's actually some records within the table.

You know, a lot of people go out there and they'll create a function to an empty table or they'll create a function and they'll screw everything up because they don't know what data types or whatever their table allows for.

So the big one that we're gonna be looking at today is park Underscore Price and the data type for that is flock.

And we can see again $11.50 $2.50 $8.50.

So again, when your time out an integer an integer is a data type that is a whole number 1 10 220 that is an integer A float is basically with a decimal 2200.11 11.52 point five, 6.252 point one That is a float.

There's also decimal data types and all kinds of other data times.

But we just use float today and all I need to know is that it is a full s with that let me control clear of screen again.

And so now the first thing that we need to do is we need to set the delimit er just like we did in the store procedures class.

Eso we do is delimit er and then we're going to do dollar sign dollar side.

You could put this theoretically to whatever delimit or you want it to be on dso.

What this means is that my sequel will not try to process what we type in Dill sees dollar sign, dollar sign and then I hit enter.

When I create the function that I'm about to show you, there's gonna be a number of different semi colons in there.

If we did not modify the delimit er at this point in time, as soon as I typed in semi Colon then I hit enter.