🧮 MySQL Stored Functions – Define, Use, and Reuse Computed Logic

---

🧲 Introduction – Why Use Stored Functions in MySQL?

Stored functions in MySQL are routines that return a single value and can be used within SQL statements just like built-in functions (NOW(), CONCAT(), etc.). They allow you to:

• 🔁 Encapsulate reusable calculations
• 📉 Simplify queries
• 🔒 Centralize business logic
• 🚀 Improve code readability and maintainability

🎯 In this guide, you’ll learn:

• How to create and use stored functions
• Syntax and return types
• Best practices and use cases
• How to manage and remove functions

---

🔧 1. What Is a Stored Function?

A stored function is a named block of SQL code that:

• Accepts parameters (IN)
• Returns a single scalar value
• Can be used in SELECT, WHERE, ORDER BY, and other clauses

---

📐 2. Syntax – CREATE FUNCTION

DELIMITER //

CREATE FUNCTION function_name(param_name datatype)
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
  -- Logic
  RETURN value;
END //

DELIMITER ;

---

🧮 3. Example – Simple Discount Calculator

DELIMITER //

CREATE FUNCTION get_discount(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * 0.90;
END //

DELIMITER ;

🔹 Usage in a Query

SELECT product_name, price, get_discount(price) AS discounted_price
FROM products;

✅ Computes 10% discount for every product on the fly.

---

🔢 4. Parameter Rules

• Only IN parameters are allowed
• You must declare the return type (RETURNS)
• Use RETURN to return a value
• Always end with DELIMITER ;

---

🧪 5. Example – Days Between Dates

DELIMITER //

CREATE FUNCTION days_between(start_date DATE, end_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN DATEDIFF(end_date, start_date);
END //

DELIMITER ;

🔹 Example Usage

SELECT days_between(order_date, delivery_date) AS days_waited
FROM orders;

---

🔍 6. View & Drop Functions

🔹 View All Stored Functions

SHOW FUNCTION STATUS WHERE Db = 'your_database';

🔹 Drop Function

DROP FUNCTION IF EXISTS get_discount;

---

🧠 Stored Function vs Stored Procedure

Feature	Stored Function	Stored Procedure
Returns a value	✅ Yes (with RETURN)	❌ No (uses OUT or result sets)
Used in SQL expressions	✅ Yes	❌ No
Used for	Calculations, lookups	Multi-step processes
Called with	Part of queries	CALL proc_name()

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Mark functions as DETERMINISTIC if they are	Helps with optimizer and replication
Avoid data-changing logic inside functions	Functions should be read-only
Keep functions short and focused	Encourages reuse and clarity
Return consistent data types	Prevents errors in calculations or joins
Use for logic used across multiple queries	Reduces duplication and improves maintainability

---

🚀 Real-World Use Cases

Use Case	Function Name	Description
Apply percentage discount	get_discount(price)	Calculates final price based on a rule
Days until due	days_until_due(date)	Returns days left before a due date
Convert full name to username	generate_username()	Creates lowercase username from names
Validate email domain	is_company_email()	Checks if email ends with @company.com

---

📌 Summary – Recap & Next Steps

MySQL stored functions allow you to embed reusable, calculated logic into your SQL queries. They are perfect for centralizing formulas, custom expressions, and on-the-fly transformations.

🔍 Key Takeaways

• Stored functions return a single value
• Use them inside SQL statements (SELECT, WHERE, etc.)
• Must declare input parameters and return type
• Use DETERMINISTIC where logic is consistent
• Avoid side effects (no INSERT, UPDATE, etc.)

⚙️ Real-World Relevance

Stored functions are ideal for e-commerce discounts, analytics formulas, data formatting, and lookup optimizations in production databases.

---

❓ FAQ – MySQL Stored Functions

---

❓ Can I use INSERT, UPDATE, or DELETE inside a function?

❌ No. Functions must be read-only. Use procedures for data changes.

---

❓ Can I use a function in a WHERE clause?

✅ Yes. For example:

SELECT * FROM orders WHERE days_between(order_date, NOW()) > 30;

---

❓ What’s the difference between DETERMINISTIC and NOT DETERMINISTIC?

• DETERMINISTIC: Always returns the same result for the same inputs
• NOT DETERMINISTIC: May vary due to randomness, time, or data changes

---

❓ How many functions can I create in MySQL?

There is no hard limit; limited only by database performance and organization.

---

❓ Can I pass multiple arguments to a function?

✅ Yes, declare as many IN parameters as needed:

CREATE FUNCTION calc_total(p DECIMAL, tax DECIMAL) RETURNS DECIMAL ...

---

Share Now :