MySQL - DROP 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 load a user defined functions using the CREATE FUNCTION statement.

MySQL DROP FUNCTION Statement

The MySQL DROP FUNCTION Statement is used to drop/delete such user defined (loadable) functions added to the server. To execute this function, you need DELETE privilege.

Syntax

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

DROP FUNCTION [IF EXISTS] function_name

Where, function_name is the name of the loadable function you need to delete.

Example

Assume we have an UDF file with name udf.dll and it contains a function sample and we have loaded it in MYSQL using the CREATE FUNCTION statement as −

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 = 'sample';

Output

Following is the output of the above query −

name	ret	dl	type
sys_exec	2	udf.dll	function

Following query deletes the above created function using the DROP FUNCTION statement −

DROP FUNCTION sample;

Since we have deleted the created function if you execute the above SELECT again, query you will get an empty set. −

select * from mysql.func where name = 'sample';
Empty set (0.06 sec)

Example

Let us create/load 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";

Following set of quires deletes all the above functions −

DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
DROP FUNCTION myfunc_int;
DROP FUNCTION sequence;
DROP FUNCTION avgcost;

If you try to drop a function that doesn’t exist error will be generated as shown below −

DROP FUNCTION demo;
ERROR 1305 (42000): FUNCTION test.demo does not exist

If you use the IF EXISTS clause along with the DROP FUNCTION statement as shown below, the specified function will be dropped and if a function with the given name, doesn’t exist the query will be ignored.

DROP FUNCTION IF EXISTS demo;