MySQL Stored Procedures

MySQL Stored Procedures

A stored procedure in MySQL is a reusable set of SQL statements stored in the database. It allows you to encapsulate logic, reduce redundancy, and improve performance by minimizing client-server communication.

Benefits of Stored Procedures

1. Reusability: Code can be reused across multiple applications.
2. Performance: Reduces network traffic by processing on the server.
3. Security: Users can execute stored procedures without direct table access.
4. Maintainability: Centralizes business logic for easier updates.

Basic Syntax

DELIMITER //

CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name datatype, ...)
BEGIN
    SQL statements;
END;
//

DELIMITER ;

Parameters

1. IN: The parameter is input-only (default).
2. OUT: The parameter is output-only.
3. INOUT: The parameter can both accept input and return output.

Examples

1. Simple Stored Procedure

A procedure to display all rows from a table:

DELIMITER //

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

DELIMITER ;

Call the procedure:

CALL GetAllEmployees();

2. Stored Procedure with Input Parameter

A procedure to find an employee by ID:

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END;
//

DELIMITER ;

Call the procedure:

CALL GetEmployeeById(1);

3. Stored Procedure with Output Parameter

A procedure to get the total number of employees:

DELIMITER //

CREATE PROCEDURE GetEmployeeCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM employees;
END;
//

DELIMITER ;

Call the procedure:

CALL GetEmployeeCount(@count);
SELECT @count;

4. Stored Procedure with INOUT Parameter

A procedure that increments a number:

DELIMITER //

CREATE PROCEDURE IncrementNumber(INOUT number INT)
BEGIN
    SET number = number + 1;
END;
//

DELIMITER ;

Call the procedure:

SET @num = 5;
CALL IncrementNumber(@num);
SELECT @num; -- Output: 6

Using Control Flow in Procedures

IF Statement

DELIMITER //

CREATE PROCEDURE CheckSalary(IN emp_id INT, OUT result VARCHAR(50))
BEGIN
    DECLARE emp_salary INT;
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;

    IF emp_salary > 5000 THEN
        SET result = 'High Salary';
    ELSE
        SET result = 'Low Salary';
    END IF;
END;
//

DELIMITER ;

LOOP Statement

DELIMITER //

CREATE PROCEDURE PrintNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;

    numbers_loop: LOOP
        IF counter > 5 THEN
            LEAVE numbers_loop;
        END IF;

        SELECT counter;
        SET counter = counter + 1;
    END LOOP;
END;
//

DELIMITER ;

Modifying a Stored Procedure

MySQL does not support direct modification of stored procedures. To modify one, you must:

1. Drop the existing procedure.
2. Create it again with the desired changes.

Dropping a Stored Procedure

DROP PROCEDURE IF EXISTS procedure_name;

Viewing Stored Procedures

List all stored procedures:

SHOW PROCEDURE STATUS WHERE Db = 'database_name';

View the code for a procedure:

SHOW CREATE PROCEDURE procedure_name;

Best Practices

1. Error Handling: Use the DECLARE and HANDLER statements to manage errors gracefully.
2. Avoid Overuse: Use stored procedures judiciously for tasks requiring server-side logic.
3. Use Parameters Wisely: Clearly define input, output, and in-out parameters.
4. Optimize Queries: Ensure SQL statements inside procedures are optimized for performance.

Let me know if you need help with advanced examples or troubleshooting!