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 Eli in the computer Guy, and in today's class, we're going to be going over stored procedures within my sequel databases.

So essentially all a store procedure is a way of referencing a pre written sequel statement.

So as we've gone on in these classes are sequel statements and become more and more complex.

So in the beginning, you start with, you know, select all from whatever table and you get the results.

Then you start doing select, you know, name something else.

Some other column from table any.

Then you get the results, and then you do select blah, blah blah from a table inner.

Join on you know where order by the whole nine yards and essentially at the end of the day, you can get very, very, very complex sticks, equal statements and become very difficult to type out every time you want a certain results.

So wouldn't it be nice?

Won't it be nice.

If essentially you could write that big old nasty sequel statement once and then be able to call that as a procedure in the future.

So instead of having to type out, you know, select blah, blah blah from where order by the whole nine yards.

Essentially, all you could do is, you'd say, call nasty procedure and then calling That nasty procedure will then actually send that entire sequel statement that is referencing to You're my sequel database, and then you will get the results out.

So we're talking about store procedures.

That's all we're doing.

Essentially, what we're doing is we're writing a script in the my sequel World.

So again, if you're thinking about Oh, if you're using Windows, if you're using Lennox again, you're using any other kind of scripting, scripting language.

Basically, you can write out a whole long script and then be able to call that script using a simple name.

That's what you're able to do with a store procedure with any my sequel database.

What you're able to do is you're able to write out a long, nasty sequel statement, and then you're able to call that sequel statement using simply the name of that store procedure.

So we're gonna be going over today is stored procedures.

They are very, very useful to use on.

Surprisingly easy to set up.

Now.

We do have to do a little bit of a warning.

A warning?

Will Robinson or need a warning.

There is one thing that I'm going to show you today That could really screw you up for your my sequel databases.

Um, most of what I'm going to show you today.

You know, if you fat finger, if you do something stupid, it will either error out or the waist.

Worst case scenario, you just get a really nasty procedure that you have to delete.

But there is one thing here.

There is one thing here.

If you're not paying attention, it could really make your life a nightmare.

Trying to use your my sequel database server going into the future, and that is setting the the limiter.

So what?

I mean by setting the limiter.

So currently you haven't a limiter of a semicolon, right?

So whenever you do, you know, select all from table.

In order to get that statement to run, you have to use the the limiter of a semicolon.

So the default a limiter within my sequel is the semicolon.

See, right out the statement you had semi Colon and then that statement runs on.

So that could be a real pain in the butt if you don't realize that the 11 there is required where you can write out a statement.

And if you don't use a semicolon if you hit inner, all you get is that weird little arrow thing, and nothing actually runs.

Nothing, actually, really happens s so you have to put that little semi colon in there as the delimit er That will then have this sequel statement run one of the interesting things when you're creating storm procedures, when you create the store procedure, you have to type in the semi colon and hit enter to go to the next line without what you created.

Actually running while you're creating it becomes a bit of a mess.

Basically, what happens with my Sequels whenever it sees that delivers?

It was a semi colon.

As soon as it sees it and you hit enter, it will try to run whatever it is that you've given it, even if you're trying to create a store procedure with basically creates a mess.

So what we're going to dio is you're able to use a command, the limiter.

So you use a command.

The limiter and I used the commanded limiter.

You can change the delimit er that is being you.

So we're going to do is we're going to change the limiter.

Circus adle emitter space, a dollar sign, dollar sign.

So what that will dio is that what changes the limiter from being a semicolon to a dollar sign dollar sign?

So basically, whatever we type in will not run until we type in dollar sign, dollar sign, and then hit Enter.

So why that's important is we're going to create the procedure Within the procedure that we're creating.

There will be semi Coghlan's But before we've created procedure, we will change the delimit er $2 sign dollar sign.

So when When my sequel sees a semi colon and reads the inner while the delimit er is still dollar sign dollar sign, nothing bad will happen.

So basically we're and he was about to deliver a dollar sign dollar sign.

You're going to create our procedure with our semi colons in there.

But it won't be any big deal because basically the my sequel, but it will not be processing when it C semi colons because of the limiter, will now be dollar sign dollar side.

We'll get to the end when we get to the annual new dollar sign dollar sign, so that will then create the procedure for us.

And then after that, after that, the next thing that you're going to do is do delimit er space semi colon to reset the delimit er back to being a semicolon.

If you set the deal emitter to some weird ass, who the hell knows what?

Remember that you're my sequel.

Database server will not process sequel statements until it sees that delivered her.

So if you can get a limiters to something else on and then you do not can get back to a cynical and then you can't run into problems later, when you go to type in a sequel, statements normally on you will run into issues there.

So that's just the big thing.

Just to keep in mind is we will be changing the delivered her here, so we'll be changing the limiter from semi Colon $2 sign dollar sign in order to create the procedure as soon as we're don't create the procedure, we will set up the limiter back Thio semi colon so that we don't run into problems in the future and so that we can then run the sequel statements as we have been doing in the past, simply using that semi colon as as the end so that let's go over the computer to show you how this works.

So here we are, back at my Mac book pro again.

I have virtual box running within a virtual box.

We have an instance of a bun to desktop 18.4 Lt s within the soup on to desktop.

I use task cell to install the lamp stack.

So this has a packing up my sequel and pH piece.

That's a live environment that we're gonna be dealing with.

And we're going to go down here.

We're going to click on the show applications.

Then you go up to search and for care you type and terminal to get you to the command prompt.

From this point, we're now going to along into our markets.

My Siegel database server.

If you do not have a user account created yet within your my sequel database server, you can use pseudo my sequel.

This will along you in as the root level account, but we created user accounts before, so we're going to my sequel.

Space hyphen, You user name Bob Space Hyphen?

Peed asked the password.

We put a password of 123456 Now we hit inner and we're now in our my sequel database from here said that of a server from here we could do show data basis.

This will show us the databases on this particular server, and we can see we have the normal databases and then we have the class database that is the one that we're concerned about.

So we will do use Class D B that will get us into the class database, and we will use these semicolon So as I talked about before, the semi Colon is currently the delimit er when you types in my colon and then you hit Enter.

That then tells my sequel to run the statement that you put in there.

So we're going to hit that hit enter and our database has changed from here.

What we're going to dio is we're then going to do show tables.

So I want to see the tables within this particular database Semi colon as the DL emitter that we can see.

We have a parts table and we have a vendor's table.

So normally, what you would do is you do, like, select all from parts.

So this is a sequel statement.

So this will select all the columns all the way from the parts table.

We had dinner, and then we can see.

Okay, so we've got part idea partnering in part price vendor I d cog Ride Sprocket.

We can see the price.

We can see all of that information there.

So now what if we wanted to create a store procedure?

So instead of having to do select all from parts we get, simply call a stored procedure we're gonna do is clear the screen.

And so now, in order to create a store procedure, the first thing that we need to do is we need to change into the limiter.

So we're going to do delimit er and they were going to say dollar sign, dollar sign.

So the DL emitter is no longer going to be the semi colon after this.

So I hit enter.

And so now if I do, let's say select all from parts Now, At the end of this, I need to put a dollar sign dollar sign in order for that to run his inner.

It now runs if I simply do select, uh, all from parts, as I did before.

But I do see Michael.

And remember, Semi colon is no longer the delimit er since I can't delimit er up here.

So when I hit in her now, it's just gonna give me that stupid little air a thing until I do dollar sign dollar sign.

Because that's the true delimit er, At that point, it will now actually process the sequel statement.

So that's one of the reasons is very important.

Now when you're done, you put the dilemma ter back to being the semi colon or whatever it is that you want.

So let's clear the screen again.

So the DL emitter is now dollar sign, dollar sign.

So what we're going to do is we're going to create the procedure, so we're going to create procedure and So then we need to give the procedure name.

So it's a test piece.

We just call it test P open parentheses, close parentheses, then we're going to hit her.

And then the first thing that we have to do is we have to type in begin.

So we're going to say we're beginning.

What?

This store procedure is that what has hit?

Enter again?

Just to put this on another line?

Theoretically, all of this could be on one the single align.

I am simply doing this to make it a little bit easier to read.

And then for here, what we're going to do is going to select all from parts and then do the semicolon.

So basically, we're saying create the procedure and they were giving the procedure and name open parentheses, close parentheses, your essay begin.

And then here.

This is where we put in our sequel statement.

However, however long or short and maybe and then we do semi colon.

Now, since the semicolon is no longer the DL emitter, when I enter, there will not be any issues.

Now.

If you had not modified the delimit er as soon as I have that semicolon and as soon as I press inner, then my sequel would try to run what I just typed in.

And then you will get some kind of Frankenstein mess out of it.

So that's why we carry to deliver her.

Now we're going to do is we're going to say end So we're going to end.

So begin and we have whatever whatever is in the middle, and then we're going to end.

And now to say that this is finished, we're going to do a dollar sign.

Dollar sign.

So this is the noodle emitter.

So this says now that says to my sequel process all of this, as soon as I press enter, then I had enter, I get no errors.

The first thing that I dio is I changed my delimit er back to being a semicolon soda limiter, space, semi colon.

And then from here now all I have to dio is call the procedure.

So I do call that I do test p and I do see Michael.

And then now I get the same results, right?

So, basically, instead of having to type out the entire sequel statement like I did before, I could simply type and call test p.

And this sequel statement will run so that let's do something a little bit more complicated.

So now what I'm going to do is I'm going to do a joy.

Right?

So before one of our previous classes I joined, I joined the parts table to the vendor's table based off of vendor I d again.

So that gets a little bit longer, eh?

So let's let's actually try to create that as a procedure just to give you an idea of why you would use these procedures for a longer sequel statements.

So the first thing that we're gonna do is we're going to set the limiter $2 sign dollar sign again, and then from here.

What we're going to do is we're going thio create.

Uh, it's great.

Proceed here.

Then we're gonna call that nasty.

We're just gonna call it nasty perceived.