What are the methods to view and delete MySQL stored procedures?

1. Check the stored procedure

After the stored procedure is created, the user can check the status of the stored procedure through the SHOW STATUS statement, or through the SHOW CREATE statement to view the definition of the stored procedure. Users can also view stored procedure information by querying the Routines table under the information_schema database. This section will explain in detail how to view the status and definition of stored procedures.

1. SHOW STATUS statement to view stored procedures

In MySQL, you can use the SHOW STATUS statement. Its basic syntax is as follows:

 SHOW PROCEDURE STATUS  [ like ‘pattern'] ;

• The parameter PROCEDURE represents the query stored procedure;

• The parameter LIKE 'pattern' is used to match the storage The name of the process.

show procedure status like 'proc%';

2. Use the SHOW CREATE statement to view the definition of the stored procedure

In MySQL, you can use SHOW CREATE The statement checks the status of the stored procedure. The syntax is as follows:

SHOW CREATE PROCEDURE proc_name ;|\G

• The parameter PROCEDURE indicates querying the stored procedure;

• The parameter proc_name indicates storage The name of the process.

ⅠUse a semicolon to end

show create procedure proc_age;

ⅡUse a backslash to end it (use a backslash to make it clearer) )

3. View the information about stored procedures from the information_schema.Routine table

Routines table in the information_schema database stores information about stored procedures and functions.. Information about stored procedures and functions can be queried by querying the records in this table. Its basic syntax is as follows:

SELECT * FROM information_schema.Routines
    Where ROUTINE_NAME = ‘proc_name';

• The field ROUTINE_NAME is the column name of Routines to store stored procedures and functions;

• The parameter proc_name represents The name of the stored procedure or function.

select * from information_schema.Routines 
    where routine_name = 'proc_age';

Or view the statement of the stored procedure separately

select routine_definition from information_schema.Routines 
    where routine_name = 'proc_age';

2. Deletion of stored procedures

Deleting stored procedures in MySQL is completed through the SQL statement DROP:

DROP PROCEDURE proc_name;

The keyword DROP PROCEDURE is used to indicate the deletion of the stored procedure; the parameter proc_name indicates the name of the stored procedure to be deleted .

For example

Attachment: Stored procedure modification

Although the MySQL database supports modification of stored procedures, we still cannot modify the storage The contents of the procedure cannot be modified, nor can the name of the stored procedure be modified. If you want to modify the content of a stored procedure, you can only delete the original stored procedure and then rewrite a stored procedure; if you want to modify the name of the stored procedure, you can only delete the original stored procedure and then create a new one. Stored procedure, and write the contents of the original stored procedure into the new stored procedure name.

MySQL only supports modifying some characteristics of stored procedures. The SQL example of the modification command is as follows:

alter procedure [stored procedure name] [stored procedure characteristics];

There are mainly 6 types of stored procedure features that can be written:

2, no sql. Indicates that the subroutine does not contain SQL statements.

3. reads sql data. Indicates that the subroutine contains SQL statements for reading data.

4. modifies sql data. Indicates that the subroutine contains SQL statements for writing data.

Re-expression: You can use sql security define or sql security invoke. If it is define, it means that the stored procedure can only be executed by the definer itself. If it is invoked, it means that the caller can execute it.

6. comment [Comment information]. Indicates adding comment information to the stored procedure.

The above is the detailed content of What are the methods to view and delete MySQL stored procedures?. For more information, please follow other related articles on the PHP Chinese website!