Docs

Database Workshop

Database management

Stored procedure management

This topic describes how to visually create, edit, and delete stored procedures on the DBW data interaction platform, implementing data lifecycle management of databases.

Prerequisites

You have created an instance and created an account.

tip

The account you create must be granted the required permissions, for example, for querying and modifying the specified database.

Precautions

We recommend that you do not create, delete, or edit stored procedures in the system database.

The SQL statement in Stored procedure definition cannot contain the return keyword.

No custom separators are required in Stored procedure definition.

The output parameters of a stored procedure cannot be the same as table field names.

No default parameter values are specified in the stored procedures of RDS for MySQL. Therefore, when calling a RDS for MySQL stored procedure, make sure that all parameters are specified and some can be set to NULL. For example, when you create the stored procedure p(in a int, out b int), where a and b are parameters, you can run the call p(1,@b) command to call the stored procedure.

Deletion of stored procedures is irreversible. Proceed with caution.

Actions such as creating or deleting objects may impact online business performance. It is recommended to perform these during off-peak hours.

Create a stored procedure

Log on to the data interaction platform for RDS for MySQL.

On the top menu bar of the Data interaction platform page, click the icon next to the database and select the target database.

In the visual area of the Data interaction platform, expand the Stored procedure, right-click the target view, and select Create stored procedure.

On the Create stored procedure@{datebase name} tab, set the following parameters.

Parameter	Description
Name	Specify a custom name for the stored procedure according to the following naming rules: The stored procedure name must be unique in the database. The name must be 1 to 64 characters in length, and cannot be empty. The name must start with a letter and end with a letter or a digit. The name can consist of lowercase letters, digits, underscores (_), or hyphens (-).
Input parameter	Optional. Click Add field and set the input parameters.
Output parameter	Optional. Click Add field and set the output parameters. tip The output parameters cannot be the same as the table field names.
Security	Specify the security context of the stored procedure. Valid values: Default: When you select this option, the security context is DEFINER by default. DEFINER: The stored procedure is executed with the permissions of the user specified by the DEFINER. When you select DEFINER, the user specified by the DEFINER must exist in the database and have the required permissions. Otherwise, the execution will fail. INVOKER: The stored procedure is executed with the permissions of the user invoke this view. When you select INVOKER, the execution can succeed provided that the executor has permission to execute it.
Data access	Select a data access method for the stored procedure. Valid values: Default: When you select Default, the data access method is ContainsSQL by default. NoSQL: The subroutine contains no SQL statements. ContainsSQL: The subroutine does not contain statements that read or write data. ReadsSQLData: The subroutine contains statements that read data, but not statements that write data. ModifiesSQLData: The subroutine contains statements that write data.
Comment	Optional. Describe the stored procedure to help identify it.

Click Next step.

On the Stored procedure definition tab, configure a command statement and click Submit.

tip

The SQL statement in Stored procedure definition cannot contain the return keyword.

No custom separators are required in Stored procedure definition.

In the Execute SQL dialog box, make sure that the statement is correct and click Execute.

Execute a stored procedure

Log on to the data interaction platform for RDS for MySQL.

On the top menu bar of the Data interaction platform page, click the icon next to the database and select the target database.

In the visual area of the Data interaction platform, expand the Stored procedure, right-click the target view, and select Execute stored procedure.

In the Execute stored procedure dialog box, specify Parameter value and click Execute.

In the Execute SQL dialog box, make sure that the statement is correct and click Execute.

After the execution is completed, in the Execute stored procedure dialog box, you can go to the Execution result area and click View result to view the execution results.

Edit a stored procedure

Log on to the data interaction platform for RDS for MySQL.

On the top menu bar of the Data interaction platform page, click the icon next to the database and select the target database.

In the visual area of the Data interaction platform, expand the Stored procedure, right-click the target view, and select Edit stored procedure.

On the Edit stored procedure@{database name} tab, go to the Stored procedure definition area and edit the statement of the stored procedure.

tip

The SQL statement in Stored procedure definition cannot contain the return keyword.

No custom separators are required in Stored procedure definition.

Click Submit.

In the Execute SQL dialog box, make sure that the statement is correct and click Execute.

Delete a stored procedure

warning

Deletion of stored procedures is irreversible. Proceed with caution.

Log on to the data interaction platform for RDS for MySQL.

On the top menu bar of the Data interaction platform page, click the icon next to the database and select the target database.

In the visual area of the Data interaction platform, expand the Stored procedure, right-click the target view, and select Delete stored procedure.

In the Delete stored procedure dialog box, make sure that the statement is correct and click Execute.

Related APIs

API	Description
DataExecCommands	Call the DataExecCommands interface to execute an SQL command.
DataSessionKeepAlive	Call the DataSessionKeepAlive interface to keep an SQL session alive.
DataCloseSession	Call the DataCloseSession interface to close an SQL session.
DataCancelExec	Call the DataCancelExec interface to cancel the execution of an SQL command.

Last updated: 2025.11.10 20:19:02