User Defined Functions (UDFs) in SQL
๐ง 1. What is a User Defined Function (UDF)?
A User Defined Function (UDF) is a custom function created by users.
๐ Accepts input parameters
๐ Performs calculations or logic
๐ Returns a single value
Think like this ๐
โ Built-in Function โ SUM(), AVG(), COUNT()
โ User Defined Function โ Created by YOU
โก 2. Why Use UDFs?
โ Reuse business logic
โ Reduce code repetition
โ Improve readability
โ Easier maintenance
๐ฅ 3. Basic Function Example
๐ Function to Calculate Bonus
DELIMITER //
CREATE FUNCTION CalculateBonus(
salary DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN salary ** 0.10;
END //
DELIMITER ;
โถ๏ธ 4. Execute Function
SELECT CalculateBonus(50000);
Output: 5000
๐ฅ 5. Using Function in Query
SELECT
name,
salary,
CalculateBonus(salary) AS bonus
FROM employees;
โก 6. Function with Multiple Parameters
DELIMITER //
CREATE FUNCTION TotalIncome(
salary DECIMAL(10,2),
bonus DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN salary + bonus;
END //
DELIMITER ;
โถ๏ธ Execute
SELECT TotalIncome(50000, 5000);
Output: 55000
๐ฅ 7. Difference Between Function & Procedure
Feature : Function : Procedure
Must return value : Yes : May or may not return
Used inside SELECT : Yes : Called using CALL
Focus : Calculations : Actions
๐ฏ 8. Practice Tasks
1. Create function to calculate tax
2. Create function to calculate annual salary
3. Create function to calculate total income
4. Use function inside SELECT query
5. Compare function vs procedure
โก Mini Challenge ๐ฅ
๐ Create a function: EmployeeGrade(salary)
Rules:
โข salary โฅ 70000 โ 'A'
โข salary โฅ 50000 โ 'B'
โข otherwise โ 'C'
Then use it for all employees.
๐ฅ Pro Tip (Interview Gold)
Most asked interview question:
๐ When should you use Function instead of Procedure?
โ Use Function when:
โข A value must be returned
โข Logic is reusable inside SELECT statements
โข Calculations are needed frequently
Double Tap โค๏ธ For More