MySQL Procedure

The MySQL stored procedure is a set of SQL statements that can be created and stored in the database for later execution. Stored procedures allow you to encapsulate a series of SQL statements into a single reusable unit. They are particularly useful for complex tasks, data manipulation, and reducing redundancy in your database operations.

Here’s how to create and use a stored procedure in MySQL:

Creating a Stored Procedure:

You can create a stored procedure using the CREATE PROCEDURE statement. The basic syntax is as follows:

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements go here
END;
//

DELIMITER ;

• DELIMITER //: Changes the delimiter temporarily to // to allow the use of semicolons within the procedure definition.
• CREATE PROCEDURE procedure_name(): Defines the name of the procedure and any parameters (if needed).
• BEGIN and END: Enclose the body of the procedure, where you can write your SQL statements.
• DELIMITER ;: Resets the delimiter back to the default semicolon.

Here’s an example of a simple stored procedure that selects all employees from a hypothetical employees table:

DELIMITER //

CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END;
//

DELIMITER ;

Calling a Stored Procedure:

Once you’ve created a stored procedure, you can call it using the CALL statement:

CALL procedure_name();

For the example above, you would call the GetAllEmployees procedure like this:

CALL GetAllEmployees();

Passing Parameters:

You can also create stored procedures that accept parameters, allowing you to pass values to the procedure. Here’s an example of a stored procedure that accepts a department name and returns employees in that department:

DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(IN department_name VARCHAR(255))
BEGIN
    SELECT * FROM employees WHERE department = department_name;
END;
//

DELIMITER ;

You would call this procedure with a specific department name:

CALL GetEmployeesByDepartment('Sales');

Modifying and Dropping Stored Procedures:

You can modify an existing stored procedure using the ALTER PROCEDURE statement, and you can drop (delete) a stored procedure using the DROP PROCEDURE statement.

Stored procedures can include conditionals, loops, and transaction control statements, making them versatile tools for working with your MySQL database. They are especially valuable for complex tasks and for encapsulating business logic within the database.