MySQL - DROP FUNCTION Statement

MySQL DROP FUNCTION Statement

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.

You can DELETE a function using the DROP FUNCTION statement.

Syntax

Following is the syntax the DELETE FUNCTION statement −

DROP FUNCTION function_name

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

Example

Suppose we have created a table named Emp using the following CREATE statement −

CREATE TABLE Emp(
   Name VARCHAR(255), 
   DOB DATE, 
   Location VARCHAR(255)
);

Now, let us insert some records into the Emp table −

INSERT INTO Emp VALUES 
('Amit', DATE('1970-01-08'), 'Hyderabad'),
('Sumith', DATE('1990-11-02'), 'Vishakhapatnam'),
('Sudha', DATE('1980-11-06'), 'Vijayawada');

Let us create a getDob() function which accepts the name of the employee, retrieves and returns the value of DOB column.

DELIMITER //
CREATE FUNCTION test.getDob(emp_name VARCHAR(50))
   RETURNS DATE
   DETERMINISTIC
   BEGIN
      declare dateOfBirth DATE;
      select DOB into dateOfBirth from test.emp where Name = emp_name;
      return dateOfBirth;
   END//
DELIMITER ;

In the same way if we have created another table shown below −

CREATE TABLE student (
   Name VARCHAR(100), 
   Math INT, 
   English INT, 
   Science INT, 
   History INT
);

Now let us insert four records into student table −

INSERT INTO student values
('Raman', 95, 89, 85, 81),
('Rahul' , 90, 87, 86, 81),
('Mohit', 90, 85, 86, 81),
('Saurabh', NULL, NULL, NULL, NULL );

Following function updates the above create table −

DELIMITER //
Create Function test.tbl_Update(S_name Varchar(50), 
   M1 INT, M2 INT, M3 INT, M4 INT)
   RETURNS INT
   DETERMINISTIC
   BEGIN
   UPDATE student SET Math = M1, English = M2, 
   Science = M3, History = M4 WHERE Name = S_name;
   RETURN 1;
   END //
DELIMITER ;

You can verify the list of functions in a database using the SHOW FUNCTION STATUS statement as shown below −

SHOW FUNCTION STATUS WHERE db = 'test'\G;

Output

The above query produces the following output −

************ 1. row ************
                  Db: test
                Name: getDob
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2023-12-05 15:03:56
             Created: 2023-12-05 15:03:56
       Security_type: DEFINER
             Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
************ 2. row ************
                  Db: test
                Name: tbl_Update
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2023-12-05 15:06:48
             Created: 2023-12-05 15:06:48
       Security_type: DEFINER
             Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

Following queries deletes/drops the above created functions −

DROP FUNCTION getDob;

DROP FUNCTION tbl_update;

Verification

Since we have deleted both the functions. If you verify the list of function again you will get an empty set −

SHOW FUNCTION STATUS WHERE db = 'test';
Empty set (0.00 sec)

The IF EXISTS clause

If you try to drop a function that doesnt 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, doesnt exist the query will be ignored.

DROP FUNCTION IF EXISTS demo;