Functions in MySQL

 

CREATION AND WORKING OF 

USER-DEFINED FUNCTIONS IN MYSQL

---

1. AIM

To understand Functions in MySQL and implement User-Defined Functions (UDFs) with suitable examples.

---

2. THEORY

✔ What is a Function?

A function in MySQL is a stored program that:

• Accepts parameters

• Performs an operation

• Returns exactly one value

• Can be used inside SQL queries like builtin functions (e.g., SUM, AVG)

Functions are commonly used for:

• Calculations

• Data formatting

• Reusable business logic

• Returning single computed values

---

3. TYPES OF FUNCTIONS IN MYSQL

A. Built-in Functions

MySQL provides many built-in functions:

✔ String Functions

• UPPER(str)

• LOWER(str)

• CONCAT(str1, str2)

• LENGTH(str)

✔ Numeric Functions

• ROUND(num)

• CEIL(num)

• FLOOR(num)

• ABS(num)

✔ Date Functions

• NOW()

• CURDATE()

• DATEDIFF(date1, date2)

• DATE_ADD(date, INTERVAL x DAY)

✔ Aggregate Functions

• SUM()

• AVG()

• COUNT()

• MIN()

• MAX()

---

4. USER-DEFINED FUNCTIONS (UDFs)

MySQL allows users to create their own reusable functions.

---

5. SYNTAX FOR CREATING FUNCTIONS

DELIMITER $$

CREATE FUNCTION function_name (parameters)
RETURNS datatype
DETERMINISTIC
BEGIN
    -- statements
    RETURN value;
END $$

DELIMITER ;

Important Keywords:

• RETURNS datatype → type of returned value

• DETERMINISTIC → always returns same output for same input

• RETURN → value must be returned

---

6. EXAMPLES FOR LAB

---

Example 1: Simple Function to Add Two Numbers

DELIMITER $$

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a + b;
END $$

DELIMITER ;

✔ Call:

SELECT add_numbers(10, 20) AS result;

---

Example 2: Function to Return Employee Annual Salary

Suppose table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

Create Function:

DELIMITER $$

CREATE FUNCTION annual_salary(monthly DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN monthly * 12;
END $$

DELIMITER ;

✔ Call:

SELECT name, annual_salary(salary) AS yearly_salary
FROM employees;

---

Example 3: Function to Check Even or Odd

DELIMITER $$

CREATE FUNCTION check_even(n INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    IF n % 2 = 0 THEN
        RETURN 'Even';
    ELSE
        RETURN 'Odd';
    END IF;
END $$

DELIMITER ;

✔ Call:

SELECT check_even(11);

---

Example 4: Function to Count Total Employees

This uses data from the existing table.

DELIMITER $$

CREATE FUNCTION total_employees()
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM employees;
    RETURN total;
END $$

DELIMITER ;

✔ Call:

SELECT total_employees();

---

Example 5: Function to Compute Grade Based on Marks

DELIMITER $$

CREATE FUNCTION get_grade(marks INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    IF marks >= 90 THEN
        RETURN 'A';
    ELSEIF marks >= 75 THEN
        RETURN 'B';
    ELSEIF marks >= 60 THEN
        RETURN 'C';
    ELSE
        RETURN 'Fail';
    END IF;
END $$

DELIMITER ;

✔ Call:

SELECT get_grade(82);

---

Example 6: Function to Apply Discount

DELIMITER $$

CREATE FUNCTION discount(price DECIMAL(10,2), percent INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price - (price * percent / 100);
END $$

DELIMITER ;

✔ Call:

SELECT discount(1000, 10) AS final_price;

---

7. HOW TO VIEW AND DROP FUNCTIONS

✔ View functions:

SHOW FUNCTION STATUS WHERE Db = 'your_database_name';

✔ View function code:

SHOW CREATE FUNCTION function_name;

✔ Drop a function:

DROP FUNCTION function_name;

---

8. LAB PRACTICE QUESTIONS

1. Create a function to return the square of a number.

2. Create a function to calculate age using date of birth.

3. Create a function to return the highest salary from the employees table.

4. Create a function that returns the reverse of a string.

5. Create a function to calculate simple interest.

6. Create a function to return number of students in a class.

---

9. RESULT

User-defined functions were successfully created, executed, and tested in MySQL.