Stored Procedures

Hello everybody, in this lesson, we're going to be taking a look at stored procedures. Stored procedures are a way to save your SQL code that you can reuse over and over again. When you save it, you can call that stored procedure, and it's going to execute all the code that you wrote within your stored procedure. It's really helpful for storing complex queries, simplifying repetitive code, and just enhancing performance overall. So let's take a look at how we can create a stored procedure. Now we're going to start by just creating a really simple query. We'll make it a little bit more advanced as we go along and take a look at the different things within stored procedures that you can do. Now let's change this query. Let's say where the salary is greater than, and let's do 50,000. Let's actually do greater than or equal to 50,000. We want to include Tom and Jerry as well. So let's go ahead and run this. Now, what we want to do is save this really complex code within a stored procedure. Let's come right down here. And we can create a super, super, super simple stored procedure by just saying CREATE PROCEDURE and pasting that. Now we just have to name it. So we have CREATE PROCEDURE, and we'll call this large_salaries. And then we do a closed parentheses. Now, this is as simple as it can possibly be. It does not get any simpler than this. So let's go ahead and run this. And if we go down, we pull this up, you can see that it says CREATE PROCEDURE, 0 rows affected. So it looks like it worked. And if we come over here to this refresh button, you should see now that under stored procedures, it drops down and we have our large_salaries. That's exactly what should have happened. We wanted to save that into our parks and recreation. Now if you wanted to be careful, you could say USE parks_and_recreation. This is not a bad idea, but you don't have to, but you can specify what database within your actual editor window. Sometimes that is helpful. But now we've created it, now let's see how we can call it. All we have to do is say CALL. We're going to copy this entire thing including the parentheses. And let's end it with that's right, a semicolon. Let's go ahead and run this. And as you can see, it worked because we've got the exact output. So we actually called this stored procedure, and this code ran, so it's just a SELECT statement, so it worked perfectly. Now you can also come over here to large_salaries and there's this little tiny little button here that looks like a lightning bolt. And if you click it, it's going to open up a different window and we'll say CALL parks_and_recreation.large_salaries. So you can do it that way as well, but we're not going to be doing it that way. Now what we've written right here is not best practice by any means, and I'm going to copy this down here, because there's a lot of different things that you need to take into account when you're creating a stored procedure. For example, this right here is most likely not what you're going to be putting into a stored procedure. This is super, super simple. Typically, you'll be having multiple queries. And let's see what happens if I try to put another query in here. And let's get rid of this. So we're going to select everything where the salary is greater than 50,000, then we'll select everything where it's greater than 10,000, which is everybody. Let's call this large_salaries2. So we have two different statements in here, and we want them all to be under this large_salaries2. Let's select everything, and let's run this. And we're getting an output, which is already not a good sign. But we created the stored procedure, and then we selected everything. So, what's actually happening here? Pull this back down. What's happening is, is this is creating the stored procedure, and this is just some other, you know, random query. But that's not what we want. What we want is everything or both of these queries within one stored procedure. The best practice is to use something called a delimiter. Now, this right here is a delimiter, this semicolon. So, this semicolon separates our queries from one another. It tells MySQL, "Hey, you know, this is a different query. Don't be mixing these and cause errors, you know." That's essentially what a delimiter does. And we can change the delimiter by coming up here and saying DELIMITER, and we can change it to almost anything we want. Now in my actual job, I've seen it done many different ways. I've seen these forward slashes, I've also seen dollar signs. This is probably the one that I've seen the most when I've worked with data engineers, data scientists, database developers. Yeah, this one I see a lot. And then you'll come into the code, and you'll say BEGIN, and let's go over here, and let's tab all of this, and then we'll say END. Now when we end, we're going to end it with this dollar sign. So here's what's happening. We're changing the delimiter right here to dollar sign. We're creating our stored procedure, and within it, we are keeping all of this. So all of this code is going to go into this one stored procedure. Then at the end, we are saying this is the end right here of this stored procedure. These semicolons no longer are the delimiter that's telling us when it is the end of the stored procedure. That's what the delimiter does. Now, it is best practice at the end to change it back, right? Let me spell it right. Because if you don't, then you're going to have to start using these dollar signs for everything. And how do you spell delimiter? Oh, man. There we go. Now we've changed it back to a semicolon afterwards. So then we can go and write other queries and it'll act appropriately. Let's go down. So this is getting closer to best practice. Let's go ahead and run this entire thing. And if we pull this up, we're not getting an output. That's a good sign. If we pull this up, it's saying we already created number two. Let's change that to three, my apologies. Let's go down here. Now we've created this stored procedure 3. Now let's go over here. We're going to right-click on this. We're going to say Alter Stored Procedure. And now you can see that we have both of these queries within this stored procedure. Let's get rid of this, and we're going to go and call this. So let's copy this large_salaries3. Bring this all the way down. And let's say CALL that stored procedure. If we run it, you'll notice we get two outputs. We have 6 and 7. This result 6 is where it's greater than 50,000 or 50,000 or greater. This one is where it's greater than 10,000, which is essentially the entire table. Now so far, we've done everything just by writing it all out, and that's fantastic, but you can also come over here to Stored Procedures, you can right-click and say Create Stored Procedure. Now, let's actually copy this, so we're just going to create the exact same thing, but we'll create stored procedure, and we can just paste this in here. And let's go ahead and do that. There we go. And sure, we'll call it new_procedure, why not? And if we say Apply, you'll notice that it generates this script right here. And we can apply it, and we can create it, and we will in just a second. But let's take a look at it. So we're going to USE parks_and_recreation, that's what I was mentioning before. We're then going to say DROP PROCEDURE IF EXISTS. Now this is something that I was going to show you later, well, I'll just show it to you now. Sometimes it is really beneficial to write something like this before you create it, in case you've already created a stored procedure with that name that you're wanting to replace. So it's checking if it's there, and if that new procedure is already there, it's just going to drop it. Then it comes down, and let me see if I can zoom in on this. And then it's going to create our delimiter, which it uses dollar signs, so MySQL is even, you know, validating what I was saying earlier. We're going to USE parks_and_recreation again. And now again, we have to use, instead of a semicolon, we're using dollar signs. Then we're creating the procedure, which is new_procedure, we're saying BEGIN, END, and then it's even changing the delimiter back. So basically everything that I said, this is kind of doing it for you automatically. Now when I click Apply, it went ahead and executed that SQL statement, and our new one is ready. So we can go ahead and alter that stored procedure, and it looks exactly the same as this one out here, which was large_salaries number three. So it looks exactly the same. Now let's go ahead and get rid of this, get rid of this, and let's go down below. The next thing that I want to take a look at is something called a parameter. Now before I actually get into this, I'm going to copy all this down here because I don't want to rewrite all of it, if I'm being honest. So let's paste this in here. Now parameters are variables that are passed as an input into a stored procedure, and they allow the stored procedure to accept an input value and place it into your code. Let's take a look at what that actually means. Now before I do anything, I'm just going to change this to number four so I don't forget. So let's get rid of all of this. We're going to keep it somewhat simple because we're looking at something new. Now when I say we're passing through a parameter, I'm talking about when we're calling it. So let's say we've already created this one, I'm not going to, you know, run this yet, but let's say we've created it, let's say I want to pass in an employee_id. I want to pass in a specific person, and I want to retrieve their salary. I know their employee IDs, I just want it to pull up their salary for us. So what we're going to do is we'll get rid of this. And when we're calling it, put this down, when we're calling it, I'm going to pass through a value like 1, that's Leslie Knope, and then I want the salary to be the output. So I'm going to select the salary. So we're selecting salary from the employee_salary, but how do we know that this 1 is the person we're looking for? Well, when we're actually creating this parameter, we create it right in here. That's what tells the stored procedure to accept an input value when we're calling it down below. We're going to call this employee_id. Now after we call it, after we name our parameter, we need to then give it a data type, so I'll call this an INT. So we're telling the stored procedure when somebody calls this stored procedure, they have to pass through an integer. It can't be a string or it can't be a date, it has to be an integer. Now what we're going to go do is right down here, we'll say WHERE the employee_id, that's from this column in the actual table, we'll say is equal to the employee_id, which is our parameter right here. Now you might be thinking, that's really confusing, they're named the exact same thing, can I change it? The answer is yes, I actually encourage it. So there are some naming conventions that are out there that I think are helpful, ones that I personally use. But remember, this is just kind of a variable parameter name, you can kind of call it whatever you want. So if I wanted to say huggy_muffin, I could, and this could be huggy_muffin. So let's try it with huggy_muffin. I just came up with that off the top of my head, so don't judge me. But we're going to create this stored procedure, and then when we call it later, we want it to return the salary where the employee_id, right here, is equal to whatever was passed through that parameter, that input parameter. And we're going to keep it as 1, so it should return 75,000. Let's go ahead, we're going to create this. And now let's go right down here, and we're going to run it. And we can see that that is the salary, and it worked perfectly. Now, like I was saying, that is not what I would actually name it. There are some naming conventions like _param at the end, so you kind of want to keep it, at least I recommend you try to keep it similar to what you're actually looking for. And you can either end it in _param, or there's another way that you can do it, which is come right over here and do p_. And these are just ways that you can tell the code or you can just be able to visually see the difference in the code. So this is just what I recommend. Then you put it right down here. So you say WHERE the employee_id is equal to p_employee_id, saying this is the parameter that's being passed through and put into our actual query. So that is all we're going to take a look at in this lesson. In the next lesson, we're going to be taking a look at triggers and events.