MySQL ALTER FUNCTION Statement

Quiz

MySQL ALTER FUNCTION Statement

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

You can alter/change the characteristics of an existing function using the ALTER FUNCTION statement.

Syntax

Following is the syntax of the ALTER FUNCTION statement −

ALTER FUNCTION function_Name COMMENT 'string', LANGUAGE SQL;

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

Example

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

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

Now let us insert three records into the table using the following query −

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 function named getDob() 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 ;

Following query alters the function getDob, it changes/adds comment to the above created function −

ALTER FUNCTION getDob COMMENT 'This is a sample comment';

Verification

You can verify the altered function using the Show Create Function statement as shown below −

SHOW CREATE FUNCTION getDob\G;

The above query produces the following output −

********** 1. row **********
      Function: getDob
      sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
   Create Function: CREATE DEFINER=`root`@`localhost` 
                    FUNCTION `getDob`(emp_name VARCHAR(50)) RETURNS date
   DETERMINISTIC
   COMMENT 'This is a sample comment'
BEGIN
   declare dateOfBirth DATE;
   select DOB into dateOfBirth from test.emp where Name = emp_name;
   return dateOfBirth;
END
character_set_client: cp850
collation_connection: cp850_general_ci
   Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.08 sec)

Print Page