Note that when the call function is used to call a stored procedure in a cell, the stored procedure is executed, and the result data set is returned. Only single column data can be returned. If the stored procedure returns multiple
Note that when the call function is used to call a stored procedure in a cell, the stored procedure is executed, and the result data set is returned. Only single column data can be returned. If the stored procedure returns multiple
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. The output parameter is similar to the return value, but the output parameter has two important advantages: the output parameter can be passed from the Stored ProcedureVarChar,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.
,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.