Do you know you can make custom functions for use in your MySQL queries? If No, Let’s get some basic idea of it.User defined Functions is known as UDF, good feature in MySQL that you can use in a query.
What is an User Defined Function?
User Defined Function is the code that extends the functionality of MySQL server by adding external code can work same as inbuilt functions like concat(), find_in_set() in MySQL.User-defined functions are compiled as object files which can be added with statement CREATE FUNCTION and can be removed from the server with statement DROP FUNCTION dynamically.
User Defined functions are useful when you want to extend the functionalities of your MySQL server.
Some Important Points:
- User-defined functions take zero or more input parameters, and return a single value such as an string, integer, or real values.
- You can define simple function that operate on a single row at a time or an aggregate functions that operate on groups of rows.
- You can indicate that a function returns NULL or that an error occurred.
User defined function syntax is very similar to stored procedures in MySQL.Here I have created simple user-defined functions which are to calculate available credits in the user account.
Example:
1 2 3 4 5 6 7 8 9 10 | DROP FUNCTION CalculateAmount// CREATE FUNCTION CalculateAmount(userid INT) RETURNS float(10,2) BEGIN DECLARE totalCredits FLOAT; SELECT SUM(amount) INTO totalAmount FROM credit_user WHERE id =userid; RETURN totalAmount; END |
NOTE: Do not take parameter variable in function same as column field of database if you use it with database query otherwise its conflict.
Above Function is to calculate the total available amount of the user of specified userid.DROP FUNCTION is to drop the function if its already exists in the database.CREATE FUNCTION is to create a user-defined function like here by the name of ‘CalculateAmount’.
The function CalculateAmount will take in an integer userid that will be a call value and return out a float value because the return type is RETURNS float(10,2).You can define any variable in a function with statement DECLARE.
SYNTAX of DECLARE:
1 2 3 | DECLARE variablename datatype |
Use that variable into a function like here I have used in a query to get total amount.
How to call UDF in MySQL?
SYNTAX
1 2 3 | SELECT function_name(parameters); |
Example
1 2 3 | SELECT CalculateAmount(1); |
Here we checked simple user defined function which will be work like any other MySQL function.You can create the functions as simple or complex as you need.
So there you have it. Now go and create your own functions to use into MySQL. 🙂
Further Reading:
To learn MySQL PACK_KEYS with example
To pass limits with store procedure in MySQL
Mysql String Function FIND_IN_SET( )
I hope you have enjoyed this tutorial. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.
Comments (5)