Explains stored procedures in MySQL, walking through an example and running it
- [Instructor] Now, unlike functions, stored procedures tend to do something. You'd use one if you wanted to edit or delete some data that can perform actions rather than just returning a value. We're going to look at some that do just return values, though, because I don't want to cover editing and deleting data in this course. Now let's start by looking at this query. Select everything from the inventory table for a particular film and for store number two. And when we run that we see it gives us three rows back, so there are three items of stock, all with the same film and all assigned to the same store.
Now what if we wanted to know which of these films were not currently in stock? For that we could use the film not in stock stored procedure. And if I click on it, you see the substance of the query there, which we'll look at it in a moment in Atom. But the main thing in this screen I want to show you are these parameters. So this stored procedure has three parameters, two in parameters and then one out parameter. The in parameters are film ID and store ID, and the out parameter is film count.
And all three of those are integers. So let's have a look at what they do. Now, once again, this is specific to MySQL. So we have begin and end, which contain our query. And the query says select the inventory ID from the inventory table, where the film ID is equal to our first input variable, the store ID is equal to our second input variable, and it is not the case that the inventory is in stock.
Now, inventory in stock is a function name, and this is an important thing to know about stored procedures. You can use functions within stored procedures, but you can't do the opposite. So you can't have a stored procedure within a function. So we're saying, and the item is not in stock, and then it says select all the rows that have been found into our output variable, which is P film count. So when you're running a stored procedure in MySQL, we use the call keyword.
Call film not in stock, and then we say, parameter one is two, next one is also two and then for the final output parameter we give a variable name. And that's so, if we wanted, we could then write another query down here and we could make use of that at count like so. We're not going to do that, but that's why a variable name is specified there, to hold the value in case you need it.
And when we hit go, we return just the number nine, which tells us that although there are three copies of this film in store number two, only one of them is currently not in stock. And there's another way of checking that because we've got a stored procedure called film in stock. And so if we were to run that, we would see that the films that are in stock, for film number two and store number two, are 10 and 11.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries