How to Create Stored Function in MySQL

In this article, you will learn all about how to create stored functions in MySQL with and without parameters.MySQL provides a CREATE FUNCTION command in order to create stored functions in MySQL.

You can explore more MySQL tutorials by the given link.

👉 Explore other MySQL tutorials

Before going to deep dive into this article, Let’s see what exactly is Stored function in MySQL.

What is the Stored Function in MySQL?

The stored Function in MySQL is a user-defined function with a set of statements that return a single value or scaler value. The stored function in MySQL cab is used with or without parameters.
It is a special kind of tool or program that encapsulates simple formulas or business logic that can be used through SQL statements and programs.

When should we use the stored function?

Let me take an example so that you can understand, where we can implement MySQL Stored Function. Suppose you have an employee table and you want to calculate a bonus value that is 10 of each employee’s salary, then you can create a separate MySQL Stored Function that will take the employee id as a parameter and return a bonus which will be 10 percent of their salary.

This is just a single example, There are many examples that exist, and it all depends on what your requirement is.

MySQL CREATE FUNCTION Syntax

This is the syntax of MySQL CREATE FUNCTION statement.

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body


func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

👉 MySQL CREATE FUNCTION Documentation – Click Here

Sample Table

For a demonstration of this article, I have created two tables called employees and department. As you can see both the tables below.
We will use both employees and department tables throughout this article, As you can see below.

Now let’s see a complete guide to using MySQL Stored Function along with examples.

How to create a stored function in MySQL?

MySQL provides a CREATE FUNCTION statement to create a stored function. Here we are about to see the process of creating a stored function with and without parameters.
You can execute this statement in MySQL shell as well as MySQL workbench application.

Create a stored function in MySQL without a parameter.

In this section, we will see the process of creating MySQL Stored Function without parameters using CREATE FUNCTION statement.

Requirement:- As a requirement, we want to calculate the average value of total employees in an organization.To calculate the average value of all the employees we don’t require any parameter because we don’t want to calculate the average of any particular department within an organization. Let’s see how?

The following CREATE FUNCTION statement creates a function called total_average which returns the total average value of all the employees.

DELIMITER //
CREATE FUNCTION `total_average`()
RETURNS DECIMAL(10, 5)
DETERMINISTIC 
BEGIN
DECLARE average_value DECIMAL(10, 5) DEFAULT 0;
SET average_value = (SELECT AVG(salary) FROM employees);
RETURN average_value;
END 
//
DELIMITER;

Code Explanation:

I have explained all the above codes so that you can get more clarity with the above code. What’s going on with this code?

• I have replaced the default delimiter semicolon ( ; ) with the // ( Double Slash ) statement using DELIMITER. The delimiter is the special character that is used to signal the end of the MySQL statement. As you can see in the above function, There are various semi-colons have been sued. Without a delimiter, it will be more difficult to parse the whole SQL statement by the compiler, After using a delimiter the MySQL compiler treats the whole SQL statement as a single statement.
• Specify the name of the stored function after CREATE FUNCTION keywords.
• I did not use any parameter in this MySQL stored function.
• Specified the data type of the return value. In my case, it will be decimal. It might be different in your case.
• Specified the DETERMINISTIC characteristic which means it is a characteristic function that will generate the same results for the same input whereas a noncharacteristic function will generate a different result for the same input. MySQL by default uses a non-characteristic function.
• Written all the code or business logic in BEGIN and END blocks, where I declared a variable called average_value with decimal data type after that I calculated the average of total employees of employees table and finally stored into the average_value variable and after that returned the average_value.
• Used ( Double slash ) // as a delimiter to the end of the whole SQL statement.
• Again I have replaced the double slash (//) delimiter with the default one which means semi-colon (;).

Create a stored function in MySQL with parameters.

Parameters are the special kinds of functions that are passed to functions so that functions can utilize those parameter values in order to produce the result.

In this section, we will see how to create a stored function in MySQL with parameters. The parameter will represent the id of the department.

Requirement: As a requirement, I want to calculate the average of all the employees in each department and also show the name of the department.

As you can see in the below code, How I have defined a function named total_average_by_department along with parameter id with the integer data type.

DELIMITER //
CREATE FUNCTION `total_average_by_department`(id integer)
RETURNS DECIMAL(10, 5)
DETERMINISTIC 
BEGIN
DECLARE average_value DECIMAL(10, 5) DEFAULT 0;
SET average_value = (SELECT AVG(salary) FROM employees where dep_id = id);
RETURN average_value;
END
//
DELIMITER;

After executing the above SQL query, Your function will be created successfully.

Calling Stored Function in MySQL:

Let’s see, How can we call the MySQL function with and without parameters.

Calling Stored Function without Parameter:

In the first section, We have seen how to create a stored function without a parameter, To call that function just use the function name followed by a bracket in SQL SELECT statements. As you can see in the below screenshot, I have called the function with their name.

For example. At first, we created a function total_average without a parameter, Then we will call this using the following way.

SELECT total_average();

Calling Stored Function with Parameter:

A function with the parameter will be called in the same ways as a function without a parameter, The only difference is that we will have to pass the parameter inside the function during the calling of the function.

For example, I have created two tables for employees and departments. Now I want to join both tables and also call the function.

Let’s see how can we do that.

SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    d.id,
    TOTAL_AVERAGE_BY_DEPARTMENT(d.id) AS 'average_salary'
FROM
    employees e
        INNER JOIN
    department d ON d.id = e.dep_id;

As you can in the I have joined the department table with employees to get the department name and also passed the department id to the TOTAL_AVERAGE_BY_DEPARTMENT() function to get the average value of employees within a department.

After executing the above code, the output would be:

+------------+------------+--------+----+----------------+
| first_name | last_name  | salary | id | average_salary |
+------------+------------+--------+----+----------------+
| Pankaj     | Kumar      |  25000 |  1 |    33750.00000 |
| Vishvajit  | Rao        |  40000 |  1 |    33750.00000 |
| Harshali   | Gautam     |  20000 |  1 |    33750.00000 |
| Lakshay    | Chaudhari  |  50000 |  1 |    33750.00000 |
| Harshita   | Kumari     |  35000 |  2 |    35000.00000 |
| Jayanti    | Srivastava |  25000 |  2 |    35000.00000 |
| Jay        | Singh      |  45000 |  2 |    35000.00000 |
| Harshita   | Kumari     |  35000 |  2 |    35000.00000 |
| Rahul      | Saini      |  20000 |  3 |    30000.00000 |
| Anand      | Sharma     |  50000 |  3 |    30000.00000 |
| Ishan      | Khattar    |  18000 |  3 |    30000.00000 |
| Vaibhav    | Pathak     |  32000 |  3 |    30000.00000 |
| Vinay      | Sharma     |  25000 |  4 |    32500.00000 |
| Khayali    | Rathor     |  25000 |  4 |    32500.00000 |
| Sandeep    | Singh      |  40000 |  4 |    32500.00000 |
| Diksha     | Rao        |  40000 |  4 |    32500.00000 |
+------------+------------+--------+----+----------------+

You can see, Same code I have executed in the MySQL workbench.

So this is how you can create and call stored functions in MySQL.

How to show function names in MySQL?

There are two ways to show the function names in MySQL. First is using MySQL workbench and second is using MySQL command line tool.
let’s see both ways.

Show function using workbench:

The function names in MySQL workbench shows on the left side inside the Functions section. As you can both functions that we have created throughout this article are shown on the left side inside the Functions tab.

Show function names in MySQL command line tool:

To show the function names in MySQL command line tool. MySQL provides a statement called SHOW FUNCTION STATUS to display the function names along with some other information.

For example, I want to see all the function names which I have created inside the testing database, Then I will use below SQL statement.

show function status where db = 'testing';

As you can see, I have executed the same SQL statement in My MYSQL command line tool to display the function names.

In fact, you can use this statement in MySQL Workbench SQL query editor as well.

How to drop a stored function in MySQL?

Sometimes we want to delete unnecessary function names from the MySQL database.MySQL provides a statement called DROP FUNCTION function_name to delete or drop the function name.

Syntax

DROP FUNCTION [IF EXISTS] function_name

function_name indicates the name of the function which you want to drop or delete.

For example, I want to delete a function called total_average_by_department which I have created in the above section, So my statement would be:

DROP FUNCTION [IF EXISTS] total_average_by_department;

[IF EXISTS] is optional, Therefore you can omit it.

---

Other MySQL Tutorials

---

Conclusion

So, in this entire article, we have covered everything about MySQL Stored Function. The process of explaining this article was easy and informative.
Function in MySQL is one of the most important concepts when you want to perform some operation based on parameters or without parameters and at the end, you want to return some calculated value.

You can create a function in MySQL with or without parameters and call into the function SQL statement to render the result of the created function.

If you found this article helpful, Pls share and keep visiting for further MYSQL tutorials.

Thanks for taking the time to read this article…