ApsaraDB RDS:How do I create and view a stored procedure in an ApsaraDB RDS for MySQL instance?

Create a stored procedure

1. Use Data Management (DMS) or a MySQL client to log on to your RDS instance. For more information, see Connect to an ApsaraDB RDS for MySQL instance.

2. Execute the required SQL statement to create a stored procedure. Sample code:

   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 both the SQL statement to create a table and the SQL statement to create a stored procedure need to be executed, we recommend that you first execute the SQL statement to create a table and then create and call a stored procedure.

   • If you use DMS to log on to your RDS instance, you can choose Programmable Object Permissions > Edit (stored procedures) to perform the required operations.

View a stored procedure

You can use one of the following methods to view a stored procedure:

• Use system tables

  Log on to the RDS instance and execute the following statement:

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

• Use the SHOW STATUS statement

  Log on to the RDS instance and execute the following statement:

  SHOW PROCEDURE status;
  SHOW CREATE PROCEDURE \G;