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

Previous Page Print Page