MySQL in a Nutshell, 2nd Edition

Name

ALTER PROCEDURE

Synopsis

ALTER PROCEDURE stored_procedure
  [{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
  [SQL SECURITY {DEFINER|INVOKER}]
  [COMMENT 'string']

This statement changes the characteristics of an existing stored procedure. You cannot change the procedure itself with it. To do that, you need to delete the procedure with DROP PROCEDURE and create a new procedure with CREATE PROCEDURE. See the description of CREATE PROCEDURE later in this chapter for an explanation of each characteristic.

There are three types of characteristics that you can set or change with this statement: the types of interaction with the server, the user recognized for SQL security, and a comment. Each type may be given in a space-separated list, in any order. See CREATE PROCEDURE later in this chapter for a discussion of the characteristics. The COMMENT clause replaces any existing comment. To clear a comment without inserting another, give two quotes with nothing between them.

This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a stored procedure, by default.

Here is an example of this statement:

ALTER PROCEDURE students_copy_proc
SQL SECURITY INVOKER
COMMENT 'Copies data from students table to students_backup.
Add a comment with @ref_note.'

If you look at the example for CREATE PROCEDURE later in this chapter, you’ll see that the example here is changing the procedure created in that example. We’re only adding that the user account to be used for executing the procedure will be the invoker, and we’re adding a comment about the procedure—we didn’t include one when we created the procedure.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition