

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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’.
- 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?
Advertisements