MySQL Stored Procedure 7 - Intro to Stored Procedures

welcome back everybody this video we are going to talk about stored routines there are two classifications of stored routines their stored procedures and stored functions we are going to be discussing both of these in this series but we're going to start with stored procedures they have a different purpose but the way they work is very similar so once you get stored routines learning stored functions is really not that big of an issue eventually we're going to do a video comparing the differences between the two so you have a clear distinction of when you need to use a stored procedure and when you should use a store function instead what is a stored procedure a stored procedure is just a section of code that we can call we put all of this code in here and then later we can run the call command and we don't have to put all of that code in line and we talked about all these things in the first video and I think a little bit with variables in the second and third video but essentially we want to avoid putting things in line as much as it's necessary so if we can take a lot of code and put it in its own stored procedure we can then call all these different stored procedures as needed so if we need to do one thing with our database we could call this one or we could call this one or we could call this one and sometimes these take what's known as arguments and parameters and what that is is basically passing data into these stored procedures so we could pass in like a number that's needed and it's going to do something with that number starting off though you can make stored procedures without parameters for those of you with more programming experience you can think of stored procedures as creating an API to your database and if you have no idea what I'm talking about don't worry about it we're gonna discuss it in a future video but just keep that in the back of your mind if you guys know what an API is there's a couple of different ways you can work with the database as you get more experience with MySQL you begin to do more things when it comes to interacting with the database just starting out you usually just open a client type in some basic select commands and get results but after doing it over and over again you're not using your time optimally as you get more experienced you start to think about ways you can save time one way is saving SQL scripts an SQL script is just a file that you can run and it's going to give back your result so we could make an in-depth select statement and then save it as an SQL script and any single time we need to run this command again all we got to do is find that file and run it and we get the results that we need this is useful but it's not optimal in all situations because now we have a bunch of SQL scripts we have to organize them put them on our file server and then open them and they don't take parameters that means we can't just pass data into them we might have to open the script edit and then run it to match that certain situation that we need this is different than a stored procedure because with a stored procedure you can just find it pass in data as an argument and you get the result you need for that situation so I would vote that stored procedures are better than SQL scripts I ain't saying that you should never use SQL scripts but it's always better to lean on stored procedures that's because they are actually connected with your database so let's say this box here is your database and you can have all kinds of tables in your database and all kinds of goodies like that well one thing you can put in here are stored procedures SQL scripts are a separate file with stored procedures when you open your database in your database management system you can find all of your stored procedures and call those from your code and that's this really super useful SQL scripts are great for some situations specifically when you're setting up a database you can just save the way you set it up and reference that later but they're not always the best in every single situation you can also think of stored procedures as extending the MySQL language that's because once you have your stored procedures made you can call those say the procedure that you want to run and it will do something so you can basically make these SQL commands that do in-depth things they couldn't do by default with just MySQL you're using MySQL to do this but you end up with commands that are much more useful to your database so in your SQL when you actually want to create your stored procedure you are going to have to worry about something called a delimiter a delimiter is a fancy name for the character you use to tell MySQL that a statement is over so right now by default it's a semicolon and that's kind of like the standard when it comes to programming there are tons of programming languages that end all of their commands are their statements with a semicolon we are going to have to change the default delimiter when it comes to stored procedures we could do that with the delimiter command and then put for example slash slash and now when we go through here and make a bunch of commands MySQL sees this as being part of your stored procedure it's kind of weird it kind of makes you think they probably could have made a way where you don't need to do that but that's just how it is you're gonna have to remember to put delimiter and then whatever you want a lot of people also use dollar signs but it doesn't really matter and then when you're done you can put the end keywords whatever your delimiter is and then say delimiter again and then follow with the semicolon that that'll tell it to go back to the normal standard semicolon to be the delimiter that's a long going to discuss in this video that'll give you a rough overview of everything you need to know to begin working with stored procedures in the upcoming videos we'll discuss the API thing I was talking about and also show the code for creating a stored procedure and then eventually we'll be getting on the computer typing up some commands and showing the results see you guys then be sure to subscribe and I'm hoping you're enjoying the series if you have any suggestions leave a comment below we'll see what we can do thanks

Channel: Caleb Curry

Views: 49,941

Keywords: mysql, stored, procedures, functions, and, mysql 7, introduction to stored procedures, methods, delimiter, delimiter //, delimiter $$, Go, END, character

Id: 6PMDXuFUFb0

Length: 6min 25sec (385 seconds)

Published: Thu Jun 16 2016