Back to Top

How to create MySQL User Defined Functions

how-to-create-mysql-user-defined-functions

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:

  1. User-defined functions take zero or more input parameters, and return a single value such as an string, integer, or real values.
  2. You can define simple function that operate on a single row at a time or an aggregate functions that operate on groups of rows.
  3. 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:

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:

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

Example

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)

  1. This will only work with small databases. Once your databases get to a couple of GB in size, this probably be EXTREMELY slow or not work at all.

  2. Awesome Post! Great template for other advanced data manipulation operations too! Thanks

  3. You should use requests from INFORMATION_SCHEMA database insted of using SHOW TABLES, this is more accurate and light :
    SELECT TABLE_NAME FROM INFORMATION_TABLE.TABLES WHERE TABLE_SCHEMA LIKE YourDbName

    More than that, you can easily adapt the above request to get only table names you want to backup.

  4. Yes, we can use INFORMATION_SCHEMA.! Thanks for the reply Laurent.

  5. Thanks a Lot for thid Post….this worked

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

How to use jQuery Lazy Load Plugin?

Posted on 3 years ago

Bhumi

How to manage Session in WordPress

Posted on 5 years ago

Bhumi