Custom Function in MySQL 

How to Create a Custom Function in MySQL?

We have seen that MySQL has some predefined functions to use and get the required values. But what if users want to create their custom function? We Can do the same in MySQL. In this blog, we will see how to create a custom function in MySQL and how we can use the same in our queries.

As we see in the following image we have the Functions option for selected MySQL Schema.

Before creating a function we will check the syntax for function creation which is as below.

CREATE FUNCTION function_name (
  input_value1 DATA_TYPE,
  input_value2 DATA_TYPE,
  ..
  input_valuen DATA_TYPE
)
RETURNS DATA_TYPE
BEGIN
  <--
    .. Function logic
  –>
  RETURN return_value;
END

We can right-click on the Functions and select Create Function Option as shown in the following image

A new window will open in the MySQL Workbench as shown in the image below.

We will write the below code for adding the three numbers we will pass to the function and will get the sum as returned output.

CREATE FUNCTION ‘my_add_function’ (
  V1 INTEGER,
  V2 INTEGER,
  V3 INTEGER
)
RETURNS INTEGER
BEGIN
  DECLARE sum INTEGER;
  SET SUM = V1 + V2 + V3;
  RETURN sum;
END

Click On Apply once done. We will get the following confirmation window with SQL Query created.

Once we Apply, we will get the successful function creation message below.

Click on Finish and refresh the functions. We will be able to see the newly created function in the list as shown below.

Once the function is created we can use that function like the one below.

SELECT my_add_function (1, 2, 3);

We can also give an alias name to the output column like the one below.

If we want to update the function we can click on the icon in front of that function name as shown in the below image.

We will get the following window opened, we can update the changes accordingly and apply the changes.

If we want the SQL Create Query for the function then we can right-click on the function and then select the ‘Copy to Clipboard’ option inside that select the ‘Create Statement’ option as shown below.

Create Query will get copied we can paste it into the notepad or wherever needs to be executed.

Following is the Create query generated for our function created above.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `my_add_function`(
  V1 INTEGER,
  V2 INTEGER,
  V3 INTEGER
) RETURNS int
BEGIN
  DECLARE sum INTEGER;
  SET SUM = V1 + V2 + V3;
  RETURN sum;
END$$
DELIMITER;

-A blog by Shwetali Khambe