Stored Procedures and Functions

Stored procedures and functions are powerful features in MySQL that allow developers to encapsulate frequently used SQL code in a reusable manner. They help in improving performance, enforcing business logic, and reducing the need for repetitive code in applications.

1. Stored Procedures

A stored procedure is a set of SQL statements that can be stored on the MySQL server and executed as a single block. Stored procedures allow for more complex logic, including conditional statements, loops, and error handling, which can be executed without sending multiple queries from the application.

Advantages of Stored Procedures:

• Improved Performance: Reduces the network load by executing multiple SQL statements in one request.
• Centralized Logic: Business logic is placed in the database rather than in the application, making maintenance easier.
• Security: Users can be restricted to execute specific procedures without direct access to the underlying tables.
• Reusability: The same logic can be reused multiple times without rewriting the queries.

Syntax:

DELIMITER //
CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name data_type, ...)
BEGIN
   -- SQL statements go here
END //
DELIMITER ;

• IN: Passes a value to the stored procedure.
• OUT: Returns a value from the stored procedure.
• INOUT: Passes a value and returns a modified value.

Example:

Creating a stored procedure that retrieves an employee’s details based on their ID:

DELIMITER //
CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
   SELECT first_name, last_name, department
   FROM employees
   WHERE employee_id = emp_id;
END //
DELIMITER ;

Calling a Stored Procedure:

CALL GetEmployeeByID(1);

Stored Procedure with INOUT Parameter:

DELIMITER //
CREATE PROCEDURE UpdateSalary(INOUT salary DECIMAL(10,2))
BEGIN
   SET salary = salary * 1.10;  -- Increase salary by 10%
END //
DELIMITER ;

Calling the procedure:

SET @employee_salary = 1000.00;
CALL UpdateSalary(@employee_salary);
SELECT @employee_salary;  -- Will return 1100.00

2. Functions

A function in MySQL is similar to a stored procedure but is designed to return a single value. Functions are often used for calculations, string manipulation, and data transformation, and can be called within SQL statements (such as in SELECT queries).

Advantages of Functions:

• Modular Design: Functions allow you to encapsulate reusable logic and call it whenever needed.
• Used in Queries: Functions can be used directly in SQL queries, making them suitable for transforming or calculating values.
• Return Values: Functions always return a value, unlike stored procedures.

Syntax:

CREATE FUNCTION function_name (parameter_name data_type, ...)
RETURNS return_data_type
BEGIN
   -- SQL statements go here
   RETURN value;
END;

Example:

Creating a function that returns the full name of an employee:

CREATE FUNCTION GetFullName(emp_id INT) RETURNS VARCHAR(255)
BEGIN
   DECLARE full_name VARCHAR(255);
   SELECT CONCAT(first_name, ' ', last_name) INTO full_name
   FROM employees
   WHERE employee_id = emp_id;
   RETURN full_name;
END;

Calling a Function:

SELECT GetFullName(1);

Example: Function for Calculating Tax:

CREATE FUNCTION CalculateTax(salary DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
   RETURN salary * 0.15;  -- Returns 15% of the salary as tax
END;

Key Differences between Stored Procedures and Functions