MySQL - Stored Functions

Hello, aspiring database enthusiasts! Today, we're going to dive into the fascinating world of MySQL Stored Functions. As your friendly neighborhood computer teacher, I'm excited to guide you through this journey, even if you've never written a line of code before. So, grab a cup of your favorite beverage, and let's get started!

What are MySQL Stored Functions?

Imagine you have a magical recipe book that can instantly whip up your favorite dish whenever you need it. That's kind of what MySQL Stored Functions are for databases! They're like pre-written recipes (or functions) that you can call upon whenever you need to perform a specific task in your database.

Why Use Stored Functions?

Reusability: Write once, use many times!
Consistency: Ensure the same logic is applied everywhere.
Security: Control who can access and modify your functions.
Performance: They can be faster than writing the same code repeatedly.

Now, let's roll up our sleeves and create our first stored function!

Creating Your First Stored Function

Here's a simple example to get us started:

DELIMITER //

CREATE FUNCTION HelloWorld() 
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    RETURN 'Hello, World!';
END //

DELIMITER ;

Let's break this down:

DELIMITER //: This tells MySQL to use // as the statement delimiter instead of the default semicolon (;). We do this because our function contains semicolons, and we don't want MySQL to get confused.
CREATE FUNCTION HelloWorld(): This line declares that we're creating a function named "HelloWorld".
RETURNS VARCHAR(50): This specifies that our function will return a string (VARCHAR) of up to 50 characters.
DETERMINISTIC: This keyword tells MySQL that for the same input, the function will always return the same output.
BEGIN and END: These keywords wrap the body of our function.
RETURN 'Hello, World!';: This is the actual code of our function. It simply returns the string "Hello, World!".
DELIMITER ;: This sets the delimiter back to the default semicolon.

Now, let's call our function:

SELECT HelloWorld();

And voila! You should see "Hello, World!" as the result. Congratulations, you've just created and called your first stored function!

Parameters and Variables in Stored Functions

Let's make things a bit more interesting by adding parameters and variables to our functions.

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE greeting VARCHAR(100);
    SET greeting = CONCAT('Hello, ', name, '! Welcome to MySQL functions!');
    RETURN greeting;
END //

DELIMITER ;

In this example:

We've added a parameter name VARCHAR(50) to our function.
We've declared a variable greeting using the DECLARE keyword.
We use the SET keyword to assign a value to our variable.
We've used the CONCAT function to combine strings.

Let's call our new function:

SELECT Greet('Alice');

This should return: "Hello, Alice! Welcome to MySQL functions!"

Using Stored Functions with Database Data

Now, let's create a function that actually interacts with our database. Imagine we have a table called employees with columns id, first_name, last_name, and salary.

DELIMITER //

CREATE FUNCTION CalculateBonus(employee_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE employee_salary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);

    SELECT salary INTO employee_salary
    FROM employees
    WHERE id = employee_id;

    SET bonus = employee_salary * 0.1;

    RETURN bonus;
END //

DELIMITER ;

This function:

Takes an employee_id as input.
Declares variables for employee_salary and bonus.
Retrieves the employee's salary from the employees table.
Calculates a 10% bonus based on the salary.
Returns the calculated bonus.

To use this function:

SELECT first_name, last_name, CalculateBonus(id) AS bonus
FROM employees
WHERE id = 1;

This will return the first name, last name, and calculated bonus for the employee with id 1.

Control Flow in Stored Functions

Let's enhance our bonus calculation with some control flow:

DELIMITER //

CREATE FUNCTION CalculateBonusTier(employee_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
    DECLARE employee_salary DECIMAL(10,2);
    DECLARE bonus_tier VARCHAR(20);

    SELECT salary INTO employee_salary
    FROM employees
    WHERE id = employee_id;

    IF employee_salary < 30000 THEN
        SET bonus_tier = 'Bronze';
    ELSEIF employee_salary < 50000 THEN
        SET bonus_tier = 'Silver';
    ELSE
        SET bonus_tier = 'Gold';
    END IF;

    RETURN bonus_tier;
END //

DELIMITER ;

This function uses IF, ELSEIF, and ELSE to determine a bonus tier based on the employee's salary.

Calling Stored Function From Stored Procedure

Finally, let's see how we can use our stored functions within a stored procedure:

DELIMITER //

CREATE PROCEDURE EmployeeBonusReport(IN emp_id INT)
BEGIN
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_bonus DECIMAL(10,2);
    DECLARE emp_bonus_tier VARCHAR(20);

    SELECT CONCAT(first_name, ' ', last_name) INTO emp_name
    FROM employees
    WHERE id = emp_id;

    SET emp_bonus = CalculateBonus(emp_id);
    SET emp_bonus_tier = CalculateBonusTier(emp_id);

    SELECT emp_name AS 'Employee Name',
           emp_bonus AS 'Bonus Amount',
           emp_bonus_tier AS 'Bonus Tier';
END //

DELIMITER ;

This stored procedure:

Takes an employee ID as input.
Retrieves the employee's full name.
Calls our CalculateBonus function to get the bonus amount.
Calls our CalculateBonusTier function to get the bonus tier.
Returns a report with the employee's name, bonus amount, and bonus tier.

To call this procedure:

CALL EmployeeBonusReport(1);

And there you have it! We've covered the basics of MySQL Stored Functions, from creation to usage within stored procedures. Remember, practice makes perfect, so don't be afraid to experiment with your own functions. Happy coding!

MySQL Function Methods
CREATE FUNCTION
RETURNS
DETERMINISTIC
BEGIN...END
DECLARE
SET
IF...ELSEIF...ELSE
SELECT...INTO
RETURN

Credits: Image by storyset