MySQL User Defined Functions

MySQL User Defined Functions

Did you know you could make custom functions for use in your SQL queries? You could make an entire library of custom functions to work and manipulate your data on your database making them application and language independant. A few possible functions you could make –

• Calculate an Employee’s Accumulated Vacation Time – maybe pass in the Employee ID and return the number of Hours they have in vacation time available.
• Customer Address – Pass in the Customer ID or name and return their mailing address in a standardized format.
• Account Balance – Pass in the Account number and return the Current Balance (assuming you have a table of debits and credits)

So now you would not have to worry about doing the calculations in the application – you can leave it to the database. Pretty Cool.

MySQL’s user defined function syntax is very similar to stored procedures and kinda self explainatory. Below is a simple example.

Example

mysql> CREATE FUNCTION isOdd(value INT) RETURNS TINYINT
    -> RETURN value%2;
Query OK, 0 rows affected (0.19 sec)

This is example of a user defined function simply tells us TRUE(1) or FALSE(0) if a number we pass to it is odd.

Create

mysql> CREATE FUNCTION isOdd(value INT) RETURNS TINYINT

This line states that we are going to create a user defined function by the name of isOdd. The function isOdd will take in an integer that we will call value, and return out a tinyint. Makes sense when we say it like that, but can you point to all the parts? As we said – it is similar to a stored procedure that you feel like you should know it – but syntatically different enough that you can trip and fall a couple of timesif you don’t pay attention.

Return

    -> RETURN value%2;
Query OK, 0 rows affected (0.19 sec)

Here is where we actually calculate what to return and then return that value, just like in a PHP user defined function.

Usage

mysql> select isOdd(13);
+-----------+
| isOdd(13) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select isOdd(14);
+-----------+
| isOdd(14) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

Here we demonstrate how we can now use the newly created user defined function in a standard SQL statement. These SQL statements could have been sent from the developers application just like any other MySQL function. Again we made sure to do some basic testing of the function.

Right about now I guess I should note that this is a very simple example and that all the logical controls and looping functions that are available to stored procedures do work in stored user defined functions as well. You can make the functions as simple or complex as you need.

So there you have it. Now go – and create your own functions to use in MySQL.