The MySQL syntax for stored procedures

365 Careers
A free video tutorial from 365 Careers
Creating opportunities for Business & Finance students
4.5 instructor rating • 72 courses • 1,456,613 students

Lecture description

In this lecture, we will focus on the SQL syntax allowing us to create a specific type of a stored routine – the MySQL stored procedure. 

Learn more from the full course

SQL - MySQL for Data Analytics and Business Intelligence

SQL that will get you hired – SQL for Business Analysis, Marketing, and Data Management

09:32:46 of on-demand video • Updated March 2021

  • Become an expert in SQL
  • Learn how to code in SQL
  • Boost your resume by learning an in-demand skill
  • Create, design, and operate with SQL databases
  • Start using MySQL – the #1 Database Management System
  • Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
  • Adopt professionally tested SQL best practices
  • Gain theoretical insights about relational databases
  • Work with a sophisticated real-life database throughout the course
  • Get maximum preparation for real-life database management
  • Add data analytical tools to your skillset
  • Develop business intuition while solving tasks with big data
  • Study relational database management theory that you will need in your workplace every day
  • Learn how to create a database from scratch
  • The ability to take control of your dataset – insert, update, and delete records from your database
  • Be confident while working with constraints and relating data tables
  • Become a proficient MySQL Workbench user
  • Acquire top-notch coding techniques and best practices
  • Know how to answer specific business questions by using SQL’s aggregate functions
  • Handle complex SQL joins with ease
  • Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
  • Merge coding skills and business acumen to solve complex analytical problems
  • Become a proficient SQL user by writing flawless and efficient queries
  • Tons of exercises that will solidify your knowledge
  • The freedom to query anything you like from a database
English [Auto] In this lecture, we will focus on the school syntax, allowing us to create stored procedures before we begin, remember that we will apply all procedures and functions to the employees database. Therefore, to avoid any confusion while typing the code in this section, let's write and execute use employees semicolon. Perfect, we are ready to move on. Think of how semicolons are used in school, we said they function as a statement Terminator, but technically they can also be called delimiters. And by typing Delimiter and the dollar symbol two times, you'll be able to use the dollar symbols as your delimiter. The semicolon isn't your delimiter anymore. And why would you do that? Well, think of the long sheets of code you can have in the school editor there. Every query is terminated by a semicolon, right? Now, imagine you are invoking or calling a certain procedure that uses the semicolon as a delimiter. In that case, the rescue engine will run only the first of the statements in your procedure and we'll move on to the next query that is beyond the procedure. It is not going to read the code after the first semicolon. To avoid this problem, you need a temporary delimiter different from the standard semicolon. There are various symbols. You can use a double dollar sign or double forward slash, for instance. It doesn't really matter which one you choose. I will opt for the double dollar symbol. Great. We must then, right, create procedure and attach the name we would like to assign to it. Next to the name, remember that you must always open and close parentheses, they are inherent to the syntax for creating a procedure because within these parentheses you would typically insert parameters. What do parameters do they represent certain values that the procedure will use to complete the calculation it is supposed to execute? This concept is more intuitive than you might think at first to clarify it, in this section, we have prepared a few videos showing how to use procedures with parameters. For the moment, though, please remember that a procedure can be created without parameters to. Nevertheless, the parentheses must always be attached to its name, otherwise my Eskil will display an error message clear. What follows is the body of the procedure. It is always enclosed between the keyword begin, the keyword end and the temporary delimiter, which in our case is a double dollar sign. MySQL workbench will display a black vertical line along the left side of the body of the procedure with a tiny box on top by clicking on the minus or the plus sign located within this box. You can hide. Or expand the code of the body. You see? Fantastic. Please pay attention to the following, the body of the procedure is composed of a query, and this query is the reason you are creating the entire procedure in the first place. It will be placed between the begin and end keywords. More importantly, however, at the end of this query, we'll have the usual delimiter, the semicolon, not the double dollar sign. How come? Well, if we use the delimiter again, the creation of the procedure will stop here and my uncle will show an error. Excellent. Finally, do not forget to reset the delimiter to the classical semicolon symbol, if you forget to do that, you risk making the opposite mistake, not run any of the code, succeeding the line where you are calling the procedure. And from this moment on, the double dollar sign will not act as a delimiter. Once again, the semicolon will have this role. Wonderful. I hope you enjoyed this video. Next, we will learn how to create a procedure and invoke it. Thank you for watching.