How to create a simple MySQL function?

You can create a function using create function command. The syntax is as follows −

delimiter //
DROP FUNCTION if exists yourFunctionName;
CREATE FUNCTION yourFunctionName(Parameter1,...N) returns type
BEGIN
# declaring variables;
# MySQL statementns
END //
delimiter ;

First, here we will create a table and add some records in the table. After that, a simple function will be created. The following is the query to create a table −

mysql> create table ViewDemo
   −> (
   −> Id int,
   −> Name varchar(200),
   −> Age int
   −> );
Query OK, 0 rows affected (0.58 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into ViewDemo values(1,'John',23);
Query OK, 1 row affected (0.15 sec)

mysql> insert into ViewDemo values(2,'Sam',24);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ViewDemo;

The following is the output −

+------+------+------+
| Id   | Name | Age  |
+------+------+------+
|    1 | John | 23   |
|    2 | Sam  | 24   |
+------+------+------+
2 rows in set (0.00 sec)

Now we will create a function that takes on integer parameters and returns strings. The purpose of this function is to search records with given id. If the given id matches with table id then it returns the name otherwise it will give an error message like not found.

The function is as follows −

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> drop function if exists searchRecord;
   ->
   -> create function searchRecord(yourId int) returns char(100)
   -> begin
   -> declare Name1 char(100) default "No Name Found For This Id";
   -> select Name into Name1 from ViewDemo where Id =yourId;
   -> return Name1;
   -> end //
Query OK, 0 rows affected (0.21 sec)
Query OK, 0 rows affected (0.33 sec)
mysql> delimiter ;

Now to check the function is working with given id.

Case 1 − When the given id is present.

The query is as follows −

mysql> select searchRecord(2) as Found;

The following is the output −

+-------+
| Found |
+-------+
| Sam   |
+-------+
1 row in set (0.00 sec)

Case 2 − When the given id is not present. 

The query is as follows −

mysql> select searchRecord(100) as Found;

The following is the output displaing the record isn’t there −

+---------------------------+
| Found                     |
+---------------------------+
| No Name Found For This Id |
+---------------------------+
1 row in set (0.00 sec)