MySQL Stored Procedures Basics

Introduction

In this lab, you will learn the basics of MySQL stored procedures. The goal is to understand how to create, call, and modify stored procedures to manage data within a MySQL database.

You will begin by creating a database and a table named employees. Then, you will write a stored procedure named insert_employee to insert data into the employees table. You will learn how to call this procedure using the CALL statement and how to add input parameters to the procedure. Finally, you will learn how to drop the procedure using the DROP PROCEDURE statement.

Note: For this lab, you only need to enter the MySQL shell once at the beginning, and exit at the very end. All SQL commands in the following steps should be executed within the same MySQL session. There is no need to repeatedly connect or disconnect from MySQL between steps.

Create a Procedure to Insert Data

In this step, you will learn how to create a stored procedure in MySQL that inserts data into a table. Stored procedures are precompiled SQL statements stored within the database. They can be executed by name, which can improve performance and security.

First, open your terminal and connect to the MySQL server using the following command:

sudo mysql -u root

This command connects to the MySQL server as the root user. Keep this MySQL session open for all subsequent steps.

Once connected, you will be in the MySQL shell. Now, switch to the testdb database that was created during the setup:

USE testdb;

Now that we are in the correct database, let's create a stored procedure to insert data into the employees table. A stored procedure is created using the CREATE PROCEDURE statement. We'll define a procedure named insert_employee that inserts a new employee record.

Here's the SQL code for the stored procedure:

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

Let's break down this code:

• DELIMITER //: This changes the statement delimiter from ; to //. This is necessary because the procedure itself contains semicolons, and we need to tell MySQL to treat the entire procedure definition as a single statement.
• CREATE PROCEDURE insert_employee: This declares the creation of a stored procedure named insert_employee.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): This defines the input parameters for the procedure. employee_name and employee_department are the names of the parameters, and VARCHAR(255) is their data type. The IN keyword indicates that these are input parameters.
• BEGIN ... END: This block contains the SQL statements that will be executed when the procedure is called.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: This is the SQL statement that inserts a new row into the employees table, using the values passed in as input parameters.
• DELIMITER ;: This resets the statement delimiter back to ;.

To execute this code, copy and paste it directly into your MySQL shell.

After executing the code, you can verify that the procedure has been created by running the following command in the MySQL shell:

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

This command will display information about the insert_employee procedure, including its name, database, and creation date.

You have now successfully created a stored procedure to insert data into the employees table. In the next step, you will learn how to call this procedure.

Call the Procedure with CALL Statement

In the previous step, you created a stored procedure named insert_employee. In this step, you will learn how to call this procedure using the CALL statement.

Reminder: You should still be in the MySQL shell and using the testdb database. If you are not, switch to it with:

USE testdb;

The CALL statement is used to execute a stored procedure. The syntax is as follows:

CALL procedure_name(argument1, argument2, ...);

In our case, the procedure name is insert_employee, and it takes two arguments: the employee's name and the employee's department.

Let's call the insert_employee procedure to insert a new employee named "Alice Smith" in the "Engineering" department:

CALL insert_employee('Alice Smith', 'Engineering');

This statement will execute the insert_employee procedure with the specified arguments.

To verify that the data has been inserted correctly, you can query the employees table in the MySQL shell:

SELECT * FROM employees;

You should see a new row in the table with the name "Alice Smith" and the department "Engineering". The id will be automatically assigned.

Let's insert another employee, "Bob Johnson" in the "Marketing" department:

CALL insert_employee('Bob Johnson', 'Marketing');

Again, verify the insertion by querying the employees table:

SELECT * FROM employees;

You should now see two rows in the table, one for "Alice Smith" and one for "Bob Johnson".

You have now successfully called the stored procedure insert_employee using the CALL statement and verified that the data has been inserted correctly. This demonstrates how stored procedures can be used to encapsulate and reuse SQL logic.

Add an Input Parameter to the Procedure

In the previous steps, you created and called a stored procedure named insert_employee that takes two input parameters: employee_name and employee_department. In this step, you will learn how to add another input parameter to the procedure.

Let's add an employee_salary parameter to the insert_employee procedure. This will allow us to specify the salary of the employee when inserting a new record.

First, you need to drop the existing procedure. If you don't drop it, you will get an error when trying to create a procedure with the same name. In your MySQL shell, run:

DROP PROCEDURE IF EXISTS insert_employee;

Now, let's create the modified stored procedure with the new input parameter.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

Let's break down the changes:

• We added a new input parameter IN employee_salary DECIMAL(10, 2) to the procedure definition. DECIMAL(10, 2) is the data type for the salary, which allows for up to 10 digits with 2 decimal places.
• We modified the INSERT statement to include the salary column and the employee_salary parameter.

Now, let's call the modified insert_employee procedure to insert a new employee named "Charlie Brown" in the "Finance" department with a salary of 60000.00:

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

To verify that the data has been inserted correctly, you can query the employees table in the MySQL shell:

SELECT * FROM employees;

You should see a new row in the table with the name "Charlie Brown", the department "Finance", and the salary 60000.00.

You have now successfully added an input parameter to the stored procedure insert_employee and verified that the data has been inserted correctly. This demonstrates how stored procedures can be modified to accommodate new requirements.

Drop the Procedure

In this final step, you will learn how to drop (delete) a stored procedure from the database. Dropping a procedure removes it from the database, making it no longer available for execution.

Reminder: You should still be in the MySQL shell and using the testdb database.

The DROP PROCEDURE statement is used to remove a stored procedure. The syntax is as follows:

DROP PROCEDURE [IF EXISTS] procedure_name;

The IF EXISTS clause is optional but recommended. It prevents an error from occurring if the procedure does not exist.

In our case, the procedure name is insert_employee. Let's drop the procedure:

DROP PROCEDURE IF EXISTS insert_employee;

This statement will remove the insert_employee procedure from the testdb database.

To verify that the procedure has been dropped, you can try to show the procedure status again in the MySQL shell:

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

This command should return an empty result set, indicating that the procedure no longer exists.

Alternatively, if you try to call the procedure, you will get an error:

CALL insert_employee('Test', 'Test', 1000);

This will result in an error message similar to: ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

You have now successfully dropped the stored procedure insert_employee.

Now you can exit the MySQL shell by typing:

exit

This concludes the lab exercise on creating, calling, modifying, and dropping stored procedures in MySQL.

Summary

In this lab, you learned the basics of MySQL stored procedures, starting with creating a database and a table named employees. You then defined a stored procedure named insert_employee using the CREATE PROCEDURE statement, which inserts data into the employees table. The DELIMITER command was used to handle semicolons within the procedure definition.

The lab also covered how to define input parameters for the stored procedure, specifying their names and data types. This allows you to pass values into the procedure when it is called, making it more flexible and reusable. You practiced calling the stored procedure using the CALL statement and verified the data insertion. Finally, you learned how to drop a stored procedure using the DROP PROCEDURE statement.