Create a stored procedure

This topic describes how to create a stored procedure in the SQL window.

Overview

A stored procedure is a collection of one or more statements. To perform a series of complex operations on a database, you can use a stored procedure to encapsulate these complex operations into a reusable code block in the database. It helps developers reduce their workload.

In OceanBase Database, a stored procedure is a collection of SQL statements for implementing a specific feature. After you compile and store a stored procedure in the database, you can specify the stored procedure name and the required parameters to call this stored procedure for accelerated execution of the SQL statements.

A stored procedure can contain parameters and return values.

Differences between a stored procedure and a function

• The return value of a function is a specific result value, while the return value of a stored procedure only indicates whether the execution is successful.

• You can call a function by using a SELECT statement. You can call a stored procedure by using a CALL statement.

Benefits of stored procedures

• Improve the versatility and portability of the application: After you create a stored procedure, it can be repeatedly called by a program without rewriting the SQL statements of the procedure. In addition, stored procedures can be modified with no impact on the program source code, thereby improving the portability of the program.

• Accelerate SQL execution: If an operation involves a large amount of SQL statements or repeated execution of the same SQL statements, you can compile the code into a stored procedure and execute the stored procedure, which is faster than separately executing the statements.

• Alleviate the workload of the server: If one SQL statement is called at a time to perform operations on the database objects, a large number of SQL statements are transmitted. If an equivalent stored procedure, only the command for calling the procedure is sent, thereby reducing the network load.

You can create a stored procedure in the following 5 steps:

1. Specify the name of the stored procedure.

2. Specify parameters.

3. Verify the parameters of the stored procedure.

4. Edit the parameters of the stored procedure.

5. Complete the stored procedure creation.

In the following example, a stored procedure named proc_total is created within the database odc_test in the SQL window. The stored procedure contains two INT type input parameters budget_r and budget_s and calculates the sum of the two parameters.

Prerequisites

You need to have the database account and password for the current tenant to log in to the SQL Console.

Procedure

Step 1: Specify the name of the stored procedure

1. After logging in to the SQL console and entering the SQL window, click Stored Procedure in the database list on the left to get a list of stored procedures. To create a stored procedure, click + in the upper-right corner of the Stored Procedure list.

2. In the Create Stored Procedure dialog box, enter a name for the stored procedure.

   

Step 2: Specify parameters

1. Parameters specify the information passed to a function when the function is called.

   • In the Oracle compatible mode, specify the following fields: Name, Mode, Type, and Default Value.

   • In the MySQL compatible mode, specify Name, Mode, Type, and Length.

2. You can specify parameters by using one of the following three methods.

   Method	Description
   Use the quick access toolbar	In the quick access toolbar, you can add, delete, and move up and down parameters.
   Click the row number	You can click a row number to select a row and display the quick access toolbar that allows you to delete the row or move the row up or down. You can click a row number to select the row, and then drag the row to adjust its order.
   Right-click	Copy a row or move one row down: Drag the pointer to select a row, right-click it, and then select Copy or Move Down from the context menu that appears. Copy a cell: Select a cell, right-click it, and then select Copy from the context menu that appears.

   Note

   After copying a row, you can select a row and paste the copied row data with the shortcut keys Command + V / Ctrl + V.

3. You need to specify the mode for the parameters.

   OceanBase Cloud in both the MySQL compatible mode and the Oracle compatible mode supports the following parameter modes: IN, OUT, and INOUT.

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

   • Parameters

     Parameter	Type
     IN	Indicates an input parameter. When a stored procedure is called, the input parameter is passed to the stored procedure and used in the execution of the stored procedure. Usually, input parameters are only used to pass values in, and are not modified or returned in the call of a stored procedure.
     OUT	Indicates an output parameter. When a stored procedure is called, the value of an output parameter is ignored and an empty value is passed to the stored procedure. The value of the output parameter is modified during the execution of the stored procedure. After the execution is complete, the modified value is assigned to the output parameter. Usually, output parameters are used to obtain the execution results of a stored procedure. Output parameters can be used to modify and return values in the call of a stored procedure.
     INOUT	Indicates an inout parameter. An inout parameter is both an input parameter and an output parameter.

   • Parameter settings

     Property	Required	Default	Mode
     Name	Yes	Empty	Oracle compatible/MySQL compatible
     Mode	Yes	IN	Oracle compatible/MySQL compatible
     Type	Yes	VARCHAR	Oracle compatible/MySQL compatible
     Length	Yes	45	MySQL compatible
     Default value	No	Empty	Oracle compatible

Step 3: Verify the parameters of the stored procedure

Click OK to go to the Create Stored Procedure page.

Step 4: Edit the stored procedure

Edit the statements on the Create Stored Procedure page.

In addition, the toolbar on the editing page provides the following buttons.

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

Step 5: Complete the stored procedure creation

Click Create in the upper-right corner to create the stored procedure. After a stored procedure is created, you can use the CALL statement to call it, just like how you call a built-in function.

Syntax:

CALL proc_name ([proc_parameter [,...]]);

Example:

CALL proc_total (30000, 20000);

To manage a stored procedure, right-click the stored procedure name in the left-side navigation pane, and select the required operation from the context menu, which provides the following options: View, Edit, Run, Download, Delete, and Refresh. For more information, refer to Functional keys in SQL Console.