Stored Procedures & Functions in MySQL

Stored Procedures & Functions in MySQL

In many applications, we repeatedly execute the same set of SQL statements.
Instead of writing the same queries again and again, MySQL allows us to store them inside the database.

This is done using Stored Procedures and Functions.

They help organize logic inside the database and improve performance, security, and maintainability.

1. What Are Stored Procedures?

A stored procedure is a pre-written group of SQL statements that is saved in the database and can be executed whenever needed.

Think of it as a reusable database program.

Instead of writing multiple queries repeatedly, you define them once and call them when required.

2. Why Use Stored Procedures?

Stored procedures are useful because:

• They reduce repeated code.
• They improve performance (compiled and stored).
• They increase security (users execute the procedure without direct table access).
• They centralize business logic inside the database.

3. Creating a Stored Procedure

Basic syntax:

DELIMITER //

 

CREATE PROCEDURE procedure_name()

BEGIN

    SQL statements;

END //

 

DELIMITER ;

Example:

DELIMITER //

 

CREATE PROCEDURE get_all_students()

BEGIN

    SELECT * FROM students;

END //

 

DELIMITER ;

To execute the procedure:

CALL get_all_students();

4. Parameters in Stored Procedures

Stored procedures can accept parameters.

There are three types:

1. IN Parameter

Used to pass a value into the procedure.

Example:

DELIMITER //

 

CREATE PROCEDURE get_student_by_id(IN student_id INT)

BEGIN

    SELECT * FROM students WHERE id = student_id;

END //

 

DELIMITER ;

Call it:

CALL get_student_by_id(5);

2. OUT Parameter

Used to return a value from the procedure.

Example:

DELIMITER //

 

CREATE PROCEDURE get_total_students(OUT total INT)

BEGIN

    SELECT COUNT(*) INTO total FROM students;

END //

 

DELIMITER ;

3. INOUT Parameter

Used to both send and return a value.

It acts as both input and output.

5. What Are Functions in MySQL?

A function is similar to a stored procedure, but must return a single value.

Functions are commonly used for calculations.

Example:

DELIMITER //

 

CREATE FUNCTION calculate_bonus(salary INT)

RETURNS INT

DETERMINISTIC

BEGIN

    RETURN salary * 0.10;

END //

 

DELIMITER ;

Use it:

SELECT calculate_bonus(50000);

6. Difference Between Procedure and Function

Feature	Stored Procedure	Function
Returns value	Optional	Must return one value
Used in SELECT	No	Yes
Can modify data	Yes	Generally used for calculations
Called using	CALL	Used insidea  query

7. When to Use Stored Procedures

Use stored procedures when:

• You need to perform multiple SQL operations.
• You want to enforce business logic at the database level.
• You need better security control.
• You want reusable database operations.

8. Pros and Cons

Advantages

• Reusability
• Better performance
• Improved security
• Reduced network traffic

Disadvantages

• Harder to debug
• Database dependency
• Can become complex if overused

9. Real-World Example

In an online store:

• Procedure to place an order.
• Procedure to update stock.
• Function to calculate discount.
• Function to calculate tax.

Instead of writing complex queries in application code, they can be stored in the database.

Summary

Stored procedures and functions allow you to store logic inside the MySQL database.

Stored Procedures:

• Can execute multiple SQL statements.
• May or may not return values.

Functions:

• Must return a single value.
• Used mainly for calculations.

They improve code organization, performance, and security in database-driven applications.