MySQL - CREATE FUNCTION Statement for User-Defined Functions

A function is a block of organized, reusable code that is used to perform a single, related action. Functions provide better modularity for your application and a high degree of code reusing.

MySQL provides a set of built-in function which performs particular tasks for example the CURDATE() function returns the current date.

In addition to stored functions, you can create and load a user defined functions into MySQL server using the CREATE FUNCTION statement. To execute this function, you need INSERT privilege.

Syntax

Following is the syntax the CREATE FUNCTION statement for User-Defined Functions −

CREATE [AGGREGATE] FUNCTION function_name
   RETURNS {STRING|INTEGER|REAL|DECIMAL}
   SONAME shared_library_name

Where, function_name is the name of the function you need to create and shared_library_name is the name of the library file containing the code of the desired user-defined function.

Example

Assume we have an UDF file with name udf.dll and it contains a function sample. Following query adds the UDF function to MySQL −

CREATE FUNCTION sample RETURNS INTEGER soname 'udf.dll';

You can verify the whether the UDF is installed using the following query −

select * from mysql.func where name = 'sys_exec';

Output

Following is the output of the above mysql query −

If you are creating/loading a aggregate function you can specify the AGGREGATE clause. Based on the return value of the function you hare loading you can specify various return types such as STRING, INTEGER, REAL, DECIMAL.

Following are more quires that loads user defined functions in to MYSQL −

CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.dll";
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.dll";
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.dll";
CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.dll";
CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.dll";