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 • 71 courses • 1,378,541 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 Cuil syntax allowing us to create stored procedures before we began. 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 semi-colon perfect. We are ready to move on. Think of how semi-colons are used in Eske. Well 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 semi-colon isn't a delimiter any. And why would you do that. Well think of the long sheets of code you can have in the rescue. Editor there every query is terminated by a semi-colon right. Now imagine you are invoking or calling a certain procedure that uses the semi-colon as a delimiter. In that case the Cuil engine will run only the first of the statements in your procedure and will move on to the next query. That is beyond the procedure. It is not going to read the code after the first semi-colon. To avoid this problem you need a temporary delimiter different from the standard semi-colon. 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 write 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 as you well will display an error message clear. What follows is the body of the procedure. It is always in close between the keyword begin the keyword end and the temporary delimiter which in our case is a double dollar sign. Maia's. Well 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 will have the usual delimiter the semi-colon not the double dollar sign. How come. Well if we use the delimiter again the creation of the procedure will stop here. In my house Cuil will show an error excellent. Finally do not forget to reset the delimiter to the classical semi-colon symbol. If you forget to do that you risk making the opposite mistake not run any of the codes exceeding 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 semi-colon 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.