Create and view stored procedures on an RDS for MySQL instance - ApsaraDB RDS

This guide shows you how to create a stored procedure in ApsaraDB RDS for MySQL and view stored procedures using system tables or the SHOW STATUS statement.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for MySQL instance
Access to Data Management (DMS) or a MySQL client. For connection instructions, see Connect to an ApsaraDB RDS for MySQL instance

Create a stored procedure

Log on to your RDS instance using DMS or a MySQL client, then run the following SQL statements.

The DELIMITER command changes the statement terminator from ; to //. This lets you use ; inside the procedure body without MySQL ending the statement prematurely.

Create the procedure:

DROP PROCEDURE IF EXISTS TEST_PROC;
DELIMITER //
CREATE PROCEDURE TEST_PROC(IN ID int,OUT NAME VARCHAR(50))
BEGIN
IF(ID = 1) THEN SET NAME = 'test1';
END IF;
IF(ID = 2) THEN SET NAME = 'test2';
END IF;
SELECT version();
END //;

Note

If you need to create both a table and a stored procedure in the same session, create the table first, then create and call the stored procedure.

Using DMS

If you log on through DMS, navigate to Programmable Object Permissions > Edit (stored procedures) to manage stored procedures.

View a stored procedure

Choose one of the following methods to list or inspect stored procedures.

Use SHOW statements

Run SHOW PROCEDURE STATUS to list all stored procedures, or SHOW CREATE PROCEDURE to view the definition of a specific one:

SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE \G;

show procedure

Use system tables

Query mysql.proc to list stored procedures in a specific database:

SELECT * FROM mysql.proc WHERE db='' AND type='procedure' order by name;