Create a stored procedure-ApsaraDB for OceanBase (Deprecated)(OceanBase)-阿里云帮助中心

Overview

A stored procedure is a collection of one or more statements that encapsulates complex database operations into a reusable code block. This reduces the workload for database developers.

A stored procedure is a set of SQL statements designed for a specific function. It is compiled, created, and saved in the database. You can call and execute it by specifying its name and required parameters. Using stored procedures can speed up the execution of SQL statements.

You can create a stored procedure to perform a specific function. A stored procedure can contain parameters and return values.

Differences between stored procedures and functions

A function's return value provides a specific result. A stored procedure's return value only indicates whether the execution was successful.
You can call a function directly with a SELECT statement. You must use a CALL statement to call a stored procedure.

Benefits of stored procedures

Improves application universality and portability: After you create a stored procedure, you can call it multiple times in a program without rewriting its SQL statements. You can also modify the stored procedure without affecting the application's source code. This improves program portability.
Increases SQL execution speed: A stored procedure is compiled. If an operation contains many SQL statements or is executed multiple times, using a stored procedure is faster than executing individual SQL statements.
Reduces server load: When you perform database object operations by calling individual statements, many SQL statements must be sent over the network. If you use a stored procedure, you can send a single procedure call command. This reduces the network load.

As shown in the preceding figure, creating a stored procedure involves five steps:

Specify the stored procedure name.
Add parameters.
Confirm the new stored procedure.
Edit the stored procedure.
Finish creating the stored procedure.

This document uses the creation of a department budget stored procedure (proc_total) in OceanBase Developer Center (ODC) as an example. The proc_total stored procedure contains two INT input parameters, budget_r and budget_s, and calculates the sum of the two parameters.

Note

The data used in this document is for example purposes only. Replace it with your own data as needed.

Procedure

Step 1: Specify the stored procedure name

In OceanBase Developer Center (ODC), click a connection's name to open it. In the navigation pane on the left, click the Stored Procedure tab to view the list of stored procedures. In the upper-right corner of the list, click + to create a stored procedure. You can also click New > Stored Procedure in the top navigation bar to create a stored procedure.

In the New Stored Procedure panel, enter a name for the stored procedure.

Step 2: Add parameters

Parameters are the information passed when a procedure is called. Specify the following parameter information:
- Oracle mode: You can specify the Name, Mode, Type, and Default Value of the parameter.
- MySQL mode: Set the parameter's Name, Mode, Type, and Length.

You can use the following three operations to add parameters:

Feature	Description
Pop-up guide menu	Use the pop-up guide menu to add, delete, and move parameters up or down.
Click the ordinal number in the table.	Click a row number to select the entire row and display the context menu (Delete, Move Up/Down). Click and select a row number to drag the entire row to reorder the parameters.
Right-click operation	Drag the mouse to select an entire row, then right-click to copy the row or move it down one row. Select a cell and right-click to copy.

The Mode you specify in the Parameters section defines the parameter mode.

MySQL/Oracle mode supports three parameter modes: IN (input), OUT (output), and INOUT (input/output).

CREATE PROCEDURE proc_name ([[IN |OUT |INOUT ] parameter_name parameter_type...])

Parameters

Parameter	Type
IN	Input parameter. When you call the stored procedure, the value of this parameter is passed to the procedure for use during execution. An IN parameter is generally used only for input and is not modified or returned during the procedure call.
OUT	Output parameter. When you call the stored procedure, the initial value of the output parameter is ignored and a null value is passed in. After execution, the output parameter is assigned the value modified by the procedure. Output parameters are generally used to get result data from the stored procedure. An OUT parameter can be modified and its value returned during the procedure call.
INOUT	Input/output parameter. Combines the functions of both an input and an output parameter.

Parameters

Property	Required	Default	Mode
Name	Required	Empty	Oracle/MySQL
Mode	Required	IN	Oracle/MySQL
Type	Required	VARCHAR	Oracle/MySQL
Length	Required	45	MySQL
Default Value	Optional	Empty	Oracle

Step 3: Confirm the new stored procedure

Click OK to go to the SQL creation page.

Step 4: Edit the stored procedure

On the SQL creation confirmation page, edit the stored procedure statements.

The toolbar on the editing page provides the following functions:

Function	Description
Format	Click this button to format the selected SQL statements or all SQL statements in the current SQL window. This includes operations such as indenting, adding line breaks, and highlighting keywords.
Find/Replace	Enter content in the find box to search the script. After finding the content, you can enter new content in the replace box to replace it.
Undo	Revert the script to the state before the last operation.
Redo	After an Undo operation, re-apply the undone operation to the script.
Case	Provides three options: ALL UPPERCASE, all lowercase, and Initial Caps. Converts the selected statements in the script to the corresponding format.
Indent	Provides two options: Add Indent and Remove Indent. Adds or removes indents for the selected statements in the script.
Comment	Provides two options: Add Comment and Remove Comment. Converts the selected statements in the script to comments or back to SQL statements.
IN Value Conversion	Converts a format such as A B to ('A','B').

You can edit the statements for the stored procedure on the SQL creation page. The syntax is as follows:

CREATE PROCEDURE proc_name (
  [proc_parameter[,...]]) 
IS
BEGIN
proc_body:
    Valid SQL routine statement
END [end_label]

Parameters:

Parameter	Description
proc_name	The name of the stored procedure. By default, it is created in the current database. To create a stored procedure in a specific database, add the database name before the procedure name, such as db_name.sp_name. Important Avoid using names that are the same as MySQL built-in functions to prevent errors.
[proc_parameter[,…] ]	The parameter list for the stored procedure, including `[IN\|OUT\|INOUT] parameter_name parameter_type`. `parameter_name` is the parameter name, and `parameter_type` is the parameter's data type, which can be any valid MySQL data type. When there are multiple parameters, separate them with commas. A stored procedure can have no parameters (the name must still be followed by parentheses), or it can have one or more parameters. Important Avoid using parameter names that are the same as table column names. Otherwise, the SQL statements in the stored procedure might treat the parameter name as a column name, which can cause errors.
proc_body	The main body of the stored procedure. It contains the SQL statements that must be executed when the procedure is called. This part starts with BEGIN and ends with END. If the procedure body contains only one SQL statement, you can omit the BEGIN-END block.

Step 5: Finish creating the stored procedure

Click Create. Once created, you can call the stored procedure with the CALL keyword, just as you would a built-in system function.