How can we alter a MySQL stored function?

If we have ALTER ROUTINE privileges then we can alter MySQL stored function with the help of ALTER FUNCTION query. Its syntax is as follows −

Syntax

ALTER FUNCTION function_name [characteristic ...]
characteristic:
   { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

Here function_name is the name of the function which we want to alter.

The ALTER FUNCTION statement can change the characteristics, defined above in the syntax, of a stored function. We can specify more than one change in an ALTER FUNCTION statement. But we cannot change the parameters or body of the function with this statement. To illustrate it we are adding comments in the function name ‘factorial’ in the following example −

Example

mysql> ALTER FUNCTION factorial COMMENT 'Can accept fraction values';
Query OK, 0 rows affected (0.00 sec)

mysql> Show Create Function factorial\G
*************************** 1. row ***************************
       Function: factorial
       sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `factorial`(n DECIMAL(3,0)) RETURNS decimal(20,0)
DETERMINISTIC
COMMENT 'Can accept fraction values'
BEGIN
DECLARE factorial DECIMAL(20,0) DEFAULT 1;
DECLARE counter DECIMAL(3,0);
SET counter = n;
      factorial_loop: REPEAT
SET factorial = factorial * counter;

SET counter = counter - 1;
   UNTIL counter = 1
END REPEAT;
   RETURN factorial;
END

character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

From the CREAT FUNCTION query, it is clear that Comment has been added to the definition of function ‘factorial’.

Vanithasree

Published on 21-Feb-2018 12:12:18

• Related Questions & Answers
• How can we alter a MySQL stored procedure?
• How can we alter table to add MySQL stored GENERATED COLUMNS?
• How can we delete a MySQL stored function from the database?
• How can we use a MySQL stored function in a database query?
• How can we drop a MySQL stored procedure?
• How can we invoke MySQL stored procedure?
• How can we see the source code of a particular MySQL stored function?
• Can we alter order of columns in MySQL?
• How can we write MySQL handler in a stored procedure?
• How can we alter table to add MySQL virtual GENERATED COLUMNS?
• How can we create a MySQL stored function that uses the dynamic data from a table?
• What is stored procedure and how can we create MySQL stored procedures?
• How can we access tables through MySQL stored procedures?
• How can we perform ROLLBACK transactions inside a MySQL stored procedure?
• How can we handle a result set inside MySQL stored procedure?