How to call the Oracle Data stored procedure in the jizhi Data Platform report

The dataset data comes from the stored procedure.SQLThe statement must be compiled before execution. The stored procedure is compiled.SQLStatement. The application can be called directly when needed, so the efficiency is relatively high. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation. Stored Procedures can return result sets, parameters, and results sets.

Stored procedure parameters are divided into input parameters and output parameters. The input parameter is the execution condition parameter of the stored procedure. Output parameters are similar to return values, but output parameters have two important advantages: You can use output parameters to passVarChar,Int,MoneyOr any other data type value, and the return value can only return an integer. Another advantage of output parameters is that a stored procedure can contain multiple output parameters. A stored procedure can contain1024Parameters (including input and output parameters), but can only contain one return value.

Whether a program developer calls a stored procedure or usesSmart Data PlatformOfReport System,During the call Process,TheOracleThe stored procedure is special.,BecauseOracleThe database has certain requirements on the output parameters of the stored procedure.,Set to the cursor type,The following is an example.:

Stored Procedure writing:

CREATE OR REPLACE PACKAGE TYPES
TYPE RQ_REF_CURSOR is ref cursor;
-Procedure getData (V_TEMP out types. RQ_REF_CURSOR );
END;

Create or replace procedure RQ_TEST_CUR
(
V_TEMP out types. RQ_REF_CURSOR,
PID IN VARCHAR
)
AS
BEGIN
OPEN V_TEMP for select name from test where id = PID;
END RQ_TEST_CUR;

-Create table
Create table TEST
(
ID VARCHAR2 (10) not null,
NAME VARCHAR2 (10)
)
Tablespace USERS
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents unlimited
);
-Create/Recreate primary, unique and foreign key constraints
Alter table TEST
Add constraint TEST_PK unique (ID)
Using index
Tablespace USERS
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents unlimited
);

Dataset settings in reports:

Set parameters in the dataset:

Report parameter settings:

Report Template Design:

Note that,When used in CellsCallWhen a function calls a stored procedure,Execute the stored procedure and return the result data set. Only single column data can be returned. If multiple fields are returned in the stored procedure, the result value of the first field is returned.