Why hello again, it's me. So today I got to explain stored procedures. A stored procedure is prepared SQL code that you can save. It's great if there's a query that you write often. For example, I have this statement.
It's very verbose. There's a lot we have to write. If this statement is something I have to write often, I could save it and reuse it later. This statement will interjoin the transactions table and my customers table, then display all customers that made a transaction.
This statement is very verbose. If I have to write the statement often, I could save it as a stored procedure. I can refer to the nickname that I gave that snippet of code.
The name I gave this procedure was getCustomers. Then I need to call this stored procedure. And that does the same thing, and it's a lot less to write. There's also a few other benefits, which we'll discuss at the end of the video. Let's begin with something simple.
I would like to select all from my customers table. To turn the statement or statements into a stored procedure, I will type create. procedure, then a name for the statement or statements.
I will name this statement get customers. Add a set of parentheses, type the keyword begin, wherever your statement or statements end, type end. Then for clarity, I'm going to tab all of my statements between begin and end. So we have a problem.
We're trying to create this procedure. However, MySQL is stopping at this semicolon here. We end all statements with a semicolon rather than at the end. Our semicolon is known as a delimiter. It's kind of like the period at the end of a sentence.
We're telling MySQL that our statement ends here, but we need our statement to end here after the end keyword. We can actually change the delimiter temporarily. Type delimiter before we create the procedure. Typically, when people change their delimiter temporarily, they'll either use two forward slashes or two dollar signs.
I'll stick with the dollar signs because I think that's cooler. After we create our procedure, let's change our delimiter back to the semicolon. MySQL no longer recognizes our semicolon as the delimiter. After the end keyword, I will use our new delimiter. to end the statement.
That should work now. Let's execute the statement. We have created our procedure.
I'm going to refresh my Navigator window. Underneath stored procedures, we have our stored procedure of getCustomers. To invoke the stored procedure, type call, the name of the procedure, getCustomers, add a set of parentheses, then a semicolon.
That will execute whatever code you stored within your stored procedure. To drop a stored procedure, you would type drop procedure, the name of the procedure, get customers. Let's try another example. This time we will send our procedure a piece of data within the set of parentheses, a customer ID number such as one, two, three, four, so on and so forth. We will get a customer by their customer ID, we will create a procedure.
I will name this procedure findCustomer. Add a set of parentheses. To find a customer by their customer ID, when we invoke the stored procedure, within the set of parentheses, we will need to place a customer ID.
ID. But when we create the stored procedure, we have to set up what is called a parameter. We will type in, then a nickname for that piece of data. We're passing in a transaction ID. I'll give this parameter a nickname of ID.
Then we need the data type of what we're passing in. We're passing in an integer. So we have one parameter set up. Let's use that begin keyword, that end keyword, List our statement or statements between these two keywords.
I will select all from my customers table where my customer ID equals the ID that we pass in, that nickname. We need to change the delimiter because MySQL thinks we're trying to end our statement here, but we need it to end after the end keyword. So let's change the delimiter to double dollar signs, then change it back. All right, we have created that stored procedure. Let's invoke it.
call find customer within the set of parentheses we need to pass in a customer ID number let's pass in one that would give us Fred fish to his Larry lobster 3 is Bubble Bass, 5 is Pearl Crabs. When you invoke a stored procedure, you can pass in some data, depending on what you need exactly for your stored procedure. Let's drop this procedure, then do one last example.
Drop procedure find customer This time we will send two arguments, two pieces of data, a first name and a last name. create procedure find customer Within our set of parentheses, the first piece of data we'll pass in will be a first name. But first is already a keyword, maybe fname, meaning first name. Then the data type.
Let's see. For customers, the data type of first and last names is varchar50. varchar50.
For your next piece of data, you would separate that with a comma. In. Our nickname will be LNAME for last name.
Data type is VARCHAR50. Then I'm just going to put that on a new line for clarity. Type the begin keyword.
and write your statements between begin and end. We will select all from our customers table where first name equals the name of the data that we pass in and last name equals the other piece of data, the second piece, the last name. Again, we need to change the delimiter to create this procedure.
Double dollar signs. Then change it back. We have created our stored procedure.
Let's invoke it. Call find customer. Within the set of parentheses, we will list a first name and a last name.
Larry, comma, lobster. Get Larry Lobster's information. Let's find him.
And there he is. Customer ID 2. First name Larry. Last name Lobster.
Referral ID of 1. Email is llobster at gmail.com. Alright everybody, that is a stored procedure. It's prepared. SQL code that you can save. It's great if there's a query that you write often.
A few of the benefits is that it reduces network traffic, it increases performance, and is more secure. An administrator can grant permission to a user or an application to use a stored procedure. One of the downsides though is that it increases memory usage of every connection. And well everybody those are stored procedures in MySQL.