MySql - What are stored procedures and stored functions

What Are Stored Procedures and Stored Functions in MySQL? 

Introduction

In modern database systems, especially in MySQL, stored procedures and stored functions are essential tools for encapsulating complex logic and operations inside the database itself. They help improve performance, maintainability, and security by moving business logic closer to the data. This article provides an in-depth explanation of what stored procedures and stored functions are, their differences, how to create and use them in MySQL, and best practices for their usage.

Overview of Stored Procedures and Stored Functions

What is a Stored Procedure?

A stored procedure is a named collection of SQL statements and control-flow logic that perform a specific task or a group of tasks in the database. Stored procedures are stored and executed directly by the MySQL server. They can accept input parameters, execute SQL queries, manipulate data, and optionally return multiple result sets or output parameters.

Stored procedures help modularize complex operations, allow reusability of code, reduce client-server communication overhead, and provide an additional layer of security by restricting direct access to underlying tables.

What is a Stored Function?

A stored function is similar to a stored procedure but is designed to return a single value. It behaves like a built-in function in SQL, and can be used in queries, expressions, and other SQL statements. Stored functions must return exactly one value using the RETURN statement.

Stored functions are useful for encapsulating frequently used computations, calculations, or data transformations within the database.

Key Differences Between Stored Procedures and Stored Functions

Creating Stored Procedures in MySQL

Basic Syntax

DELIMITER //
CREATE PROCEDURE procedure_name ([parameters])
BEGIN
  -- SQL statements
END //
DELIMITER ;

- DELIMITER is changed from the default semicolon ; to // (or any other) temporarily to allow the procedure body to contain semicolons.
- procedure_name is the name of the stored procedure.
- parameters are optional and specify inputs and outputs.
- The BEGIN ... END block encloses the SQL statements executed by the procedure.

Parameter Modes

• IN - Input parameter, passed to the procedure.
• OUT - Output parameter, used to return data to the caller.
• INOUT - Parameter used as both input and output.

Example: Creating a Simple Procedure

Create a procedure to retrieve employees by department:

DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
  SELECT employee_id, first_name, last_name, salary
  FROM employees
  WHERE department = dept_name;
END //
DELIMITER ;

Calling a Stored Procedure

CALL GetEmployeesByDept('Sales');

Example with OUT Parameter

Procedure to count employees in a department and return the count:

DELIMITER //
CREATE PROCEDURE CountEmployeesByDept(IN dept_name VARCHAR(50), OUT emp_count INT)
BEGIN
  SELECT COUNT(*) INTO emp_count
  FROM employees
  WHERE department = dept_name;
END //
DELIMITER ;

Calling the procedure:

CALL CountEmployeesByDept('Marketing', @count);
SELECT @count;

Creating Stored Functions in MySQL

Basic Syntax

DELIMITER //
CREATE FUNCTION function_name ([parameters]) RETURNS return_datatype
BEGIN
  -- SQL statements
  RETURN value;
END //
DELIMITER ;

- The function must have a return type specified by RETURNS.
- The function body contains the logic and must end with a RETURN statement returning a value of the specified type.

Example: Creating a Function

A function that calculates the yearly salary given monthly salary:

DELIMITER //
CREATE FUNCTION YearlySalary(monthly_salary DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
  RETURN monthly_salary * 12;
END //
DELIMITER ;

Using Stored Functions

Functions can be used inside SQL statements:

SELECT employee_id, first_name, last_name, YearlySalary(salary) AS yearly_salary
FROM employees;

Advantages of Stored Procedures and Functions

• Performance: Reduce network traffic by executing multiple SQL statements in one call.
• Maintainability: Centralize and reuse SQL logic.
• Security: Restrict direct access to tables by granting execute rights on procedures/functions.
• Encapsulation: Hide complex SQL code from application layers.
• Portability: Logic resides in database, easing application migration.

Limitations and Considerations

Deterministic vs Non-Deterministic

Functions should ideally be deterministic (same input, same output). Non-deterministic functions (e.g., involving random numbers, time) can cause optimization issues.

Side Effects in Functions

Although MySQL allows data modification statements inside functions, it is considered bad practice due to unpredictability and potential locking issues.

Transaction Control

Stored procedures can include transaction control statements (COMMIT, ROLLBACK), but they need careful management to avoid leaving transactions open.

Security Context

By default, procedures and functions run with definer's privileges. Be careful with security implications to avoid privilege escalation.

Error Handling in Stored Procedures and Functions

DECLARE ... HANDLER

MySQL supports declaring handlers to manage exceptions or warnings in procedures/functions.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  -- error handling code
END;

Example: Handling Divide By Zero

DELIMITER //
CREATE FUNCTION SafeDivide(a INT, b INT) RETURNS DECIMAL(10,2)
BEGIN
  DECLARE result DECIMAL(10,2);
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  SET result = NULL;
  
  IF b = 0 THEN
    RETURN NULL;
  ELSE
    SET result = a / b;
  END IF;
  RETURN result;
END //
DELIMITER ;

Debugging and Testing Stored Procedures and Functions

Testing Procedures

Use the CALL statement with appropriate parameters and verify results using SELECT queries.

Debugging Techniques

• Use SELECT statements inside procedures/functions to output intermediate values.
• Use logging tables to record debug information.
• Check MySQL error messages carefully.

Managing Stored Procedures and Functions

Viewing Existing Procedures and Functions

Use INFORMATION_SCHEMA.ROUTINES table to list all stored routines:

SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database_name';

Altering Procedures and Functions

MySQL does not support ALTER PROCEDURE or ALTER FUNCTION. You must drop and recreate them:

DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name ...

Dropping Procedures and Functions

DROP PROCEDURE IF EXISTS procedure_name;
DROP FUNCTION IF EXISTS function_name;

Stored procedures and stored functions are powerful tools in MySQL to encapsulate and modularize database logic, improve performance, and increase security. While stored procedures are versatile and can perform complex operations with input and output parameters, stored functions are designed to return a single value and are usable within SQL expressions. Understanding their syntax, usage, differences, and limitations helps database developers and administrators design robust and maintainable applications.