Introduction
We are describing User-Defined Functions. User-Defined Functions is code that extends the functionality of MySQL Server. A User-Defined Function can work the same as built-in functions like concat(), find_in_set() in MySQL and concat(), and find_in_set() in string functions. User-Defined functions are compiled as object files that can be added by a CREATE FUNCTION statement and can be removed dynamically from the server with a DROP FUNCTION statement.
This function is very helpfull when you want to extend the functionality in your MySQL Server; the features are:
- User-defined functions can have zero or more input parameters
- Can have aggregate functions that handle groups of rows
- You can indicate that a function returns NULL or that an error occurred
The syntax is similar to Stored Procedures in MySQL. Here I am creating a simple User-Defined function to calculate the available credits in a user account.
Example
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:
You are not use parameter var in function same types column field of database if you use with database query otherwise its conflict.
Syntax
|
DECLARE variablename datatype |
Use that variable in a function, like here I have used it in a query to get the total amount.
Syntax Of User-Defined Function
|
SELECT function_name(parameters); |
You can create the functions as simple or as complex as you need.
Some Important Features
- The function returns the values of string, integer, or real.
-
You will define a single row in this function, or aggregate functions can operate on groups of rows.
-
To provide MySQL arguments and pass them to a function.