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.

Skills Graph

Write 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, let's create a simple table to work with. Open your terminal and connect to the MySQL server using the following command:

mysql -u root -p

You will be prompted for the root password. Enter the password and press Enter.

Now, create a database named testdb:

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

Next, create a table named employees with the following structure:

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255)
);

Now that we have a table, let's create a stored procedure to insert data into it. 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, you can copy and paste it directly into your MySQL terminal.

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

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.

First, ensure you are connected to the MySQL server and using the testdb database. If you are not already connected, use the following commands:

mysql -u root -p

Enter the password when prompted.

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:

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. Connect to MySQL if you aren't already:

mysql -u root -p

Enter the password when prompted.

USE testdb;

Drop the existing procedure:

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) VALUES (employee_name, employee_department);
    -- Add salary update after insert
    UPDATE employees SET salary = employee_salary WHERE name = employee_name AND department = employee_department;
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 added a new column salary to the employees table. You need to add this column manually using the following SQL statement:

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• We added an UPDATE statement to update the salary of the newly inserted employee. Since the INSERT statement doesn't directly support setting the salary, we insert the employee first, then update the salary based on the name and department.

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:

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.

First, ensure you are connected to the MySQL server and using the testdb database. If you are not already connected, use the following commands:

mysql -u root -p

Enter the password when prompted.

USE testdb;

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:

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. 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.