In this tutorial, we will learn what is a stored function, its syntax, how it works, why do we need it and at the last, we will see a practical example to understand it better. If you know about MySQL stored procedures, then it is easier to understand the stored function. However, it is not necessary to have a knowledge of stored procedure and you can still understand the MySQL stored function without any prior understanding.
Also read: Create MySQL Stored Procedure [With Examples]
What is a Stored Function?
A MySQL stored function is a set of SQL instructions/statements that perform a certain task and return a single value. You can think of a stored function as any regular function in programming languages such as C, C++ or Java.
Stored Functions are somewhat similar to stored functions. Such as both are used to execute a block of code/statements. However, there are multiple differences between them which are as follows-
- Stored functions can return only a single value.
- We can call a stored function from the SQL statement.
- Stored functions only have one type of parameter which is the IN. However, it is set to default and you can’t / don’t specify the input type.
- Stored functions can not produce results set as stored procedures do
When Do We Need a Stored Function?
Stored functions are very useful when you want to write complex business logic and return a single value. You can call the stored function from a SQL statement.
For example- SELECT colName, FunName(Parameter) FROM table_name;
Syntax of MySQL Stored Function
The syntax of the MySQL stored function is very identical to the stored procedure. There are only a few differences. See below syntax to define a stored function.
DELIMITER //
CREATE FUNCTION function_name(
[ param1 type,
param2 type,… ]
)
RETURNS datatype
[[NOT] DETERMINISTIC]
BEGIN
-- statements
END $$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)Where,
- DELIMITER – It changes the default operator which indicates the end of the statement.
- CREATE FUNCTION – It creates a stored function.
- function_name – You can give any name to the stored function which follows the naming rules in MySQL.
- param1, param2 – The names of parameters along with their datatypes.
- RETURNS datatype – It specifies the datatype of the return value.
- [NOT] DETERMINISTIC – You can specify if the function is deterministic or non-deterministic. The deterministic function always returns the same output when the same input is provided. On the other hand, the non-deterministic function always returns a different output when the same input is provided. By default, the non-deterministic is set if nothing is specified.
- BEGIN…END – You can write one or more valid SQL statements inside the BEGIN…END block. Inside the BEGIN…END block, you must specify at least one RETURN statement that returns a value to the calling program. As soon as the RETURN statement is executed, the function terminates immediately.
Example of a MySQL Stored Function
Now we will take an example to show how a stored function is created. For that, we will use a “accounts” table of the following description and data.
Now, we will create a stored function that will return the status of the balance. Such as, if the balance is below Rs100 then we should get “too low” status. Let’s do it!
CREATE FUNCTION getStatus(balance FLOAT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE status VARCHAR(10);
IF balance < 100 THEN
SET status="Too Low";
ELSEIF balance < 500 THEN
SET status="Low";
ELSEIF balance < 1000 THEN
SET status="Good";
ELSEIF balance > 1000 THEN
SET status="Ideal";
END IF;
RETURN (status);
END //Code language: SQL (Structured Query Language) (sql)
Here, we have specified the parameter “balance” which will be sent from the calling program.
Then we have used the IF-THEN-ELSE statement to get the status of the account balance. We are storing the status of the balance in a variable called “status”. Finally, we return the value of the status variable.
Let’s now test the stored function by calling it from the SQL statement.
SELECT *, getStatus(balance) as status FROM accounts;Code language: SQL (Structured Query Language) (sql)Here, we display the table data using the SELECT * statement and call the function with the parameter of the balance column value. The output table will create an extra column to show the status of the account balance.
As you can see, we have received the proper output depending on the balance of the user account.
Conclusion
In this tutorial, we learned what is a stored function and its syntax. We went through a practical example where we learned how to call a stored function from a SQL statement. We highly recommend you to practice it as much as you can because this is a very helpful feature given by MySQL and you will need it often in your projects.