Calling a Stored Procedure

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.

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.

• 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
    ]
  }