1. Syntax for Creating a Stored Procedure
CREATE PROCEDURE procedure_name()
BEGIN
-- SQL statements
END;
procedure_name: The name of the stored procedure.BEGIN ... END: Defines the procedure’s body.Let’s assume we have an employees table and we want to create a procedure that retrieves all employees from a specific department.
CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM employees WHERE department = dept_name;
END;
IN dept_name VARCHAR(50): Takes the department name as input.SELECT * FROM employees WHERE department = dept_name;: Retrieves employees from the given department.CALL GetEmployeesByDept('IT');
MySQL Stored Procedures are a powerful way to streamline database operations. They improve efficiency, enhance security, and allow developers to execute reusable SQL logic efficiently.