Chapter 10: Variables and Stored Routines

 

Congratulations on making it this far! We’ve covered most of the fundamental concepts in SQL.

 

In this chapter, we’ll cover something more advanced. Specifically, we’ll talk about stored routines.

 

However, before we do that, let’s first discuss variables. We need to understand variables before we can fully appreciate stored routines.

 

Variables

 

So what is a variable?

 

A variable is a name given to data that we need to store and use in our SQL statements.

 

For instance, suppose we want to get all information related to employee 1 from the employees and mentorships tables. We can do that as follows:

 

SELECT * FROM employees WHERE id = 1;

SELECT * FROM mentorships WHERE mentor_id = 1;

SELECT * FROM mentorships WHERE mentee_id = 1;

 

However, if we realize that we made a mistake and want the information of employee 2 instead, we’ll have to change all the three SQL statements above. This is relatively easy for three statements, but can be a lot of trouble if there are hundreds of statements. A better way is to use variables.

 

To do that, we can first declare and initialize a variable using the statement below:

 

SET @em_id = 1;
 

Here, we declare a variable called @em_id. User defined variables in MySQL have to be prefixed with the @ symbol.

 

...

Stored Routines

 

Next, let’s move on to stored routines.

 

A stored routine is a set of SQL statements that are grouped, named and stored together in the server. Do not worry if this does not make much sense to you at the moment. We’ll discuss it in greater depth later.

 

There are two types of stored routines - stored procedures and stored functions.

 

Stored Procedures

 

Let’s first look at stored procedures.

 

We can create a stored procedure using the syntax below:

 

DELIMITER $$

 

CREATE PROCEDURE name_of_procedure([parameters, if any])

BEGIN

-- SQL Statements

END $$

 

DELIMITER ;

 

Most of the syntax is pretty similar to the syntax for creating a trigger.

 

The main difference is, instead of using CREATE TRIGGER, we use CREATE PROCEDURE to create the stored procedure.

 

In addition, we have a pair of parentheses after the CREATE PROCEDURE keywords.

 

These parentheses...

Deleting Stored Routines

 

Finally, before we end this chapter, let’s talk about deleting stored routines.

 

While MySQL allows us to alter our stored routines (using the ALTER keyword), we can only make very limited modifications to it.

 

Hence, if we need to edit our stored routines, the easier way is actually to delete and recreate them.

 

To delete a stored procedure, we write

 

DROP PROCEDURE [IF EXISTS] name_of_procedure;

 

To delete a stored function, we write

 

DROP FUNCTION [IF EXISTS] name_of_function;

 

For instance, to delete the calculateBonus function, we write

 

DROP FUNCTION IF EXISTS calculateBonus;