Calling a Stored Procedure

Updated on 2022-09-01 GMT+08:00

View PDF

Currently, a data API cannot create stored procedures, but can execute stored procedures of the MySQL, Oracle, and PostgreSQL data sources. The Oracle database is used as an example.

Data Source Description

Assume that the database contains a table. The table structure is as follows:

create table sp_test(id number,name varchar2(50),sal number);

Insert data into the table. The following table shows the data set.

**Table 1** sp_test table data set
ID	NAME	SAL
1	ZHANG	5000
2	LI	6000
3	ZHAO	7000
4	WANG	8000

The Oracle database contains a stored procedure for querying the value of sal based on name.

create or replace procedure APICTEST.sb_test(nname in varchar, nsal out number) as
begin
	select sal into nsal from sp_test where name = nname;
end;

Statements in a Data API

When a data API calls a stored procedure, parameters can be transferred through Headers, Parameters, or Body of a backend request. The syntax of a parameter name is as follows: {Parameter name}.{Data type}.{Transmission type}.

The data type can be String or int.
Transmission type indicates whether the parameter is an input parameter or output parameter. in indicates an input parameter, and out indicates an output parameter.

The following script is an example statement used for calling the stored procedure in the data API:

call sb_test(${nname.String.in},${nsal.int.out})

In the example script, nname is an input parameter of the String type and the parameter name is nname.String.in. The value is the parameter to be queried. nsal is an output parameter of the numeric type and the parameter name is nsal.int.out. Due to the format restriction, the value of the output parameter must be set. You can set it to any value that meets the data type requirements, which does not affect the output result.

NOTE:

The data API uses String and int to distinguish character strings and values when calling a stored procedure. Single quotation marks are not required. This is different from the SQL requirement.
The parameter names defined in Headers, Body, or Parameters of a backend request must be different. Otherwise, they will be overwritten.

The following script is an example of transferring parameters in Body:
Body of the backend request:
```
{
  "nname.String.in": "zhang",
  "nsal": 0
}
```
Response result:
```
{
  "test": [
    5000
  ]
}
```
The following script is an example of transferring parameters in Parameters:
Parameters of the backend request:
```
https://example.com?nname.String.in=zhang&nsal=0
```
Response result:
```
{
  "test": [
    5000
  ]
}
```