Stored Procedures with Output Parameters and Result Sets

Define a mobile business object (MBO) from a stored procedure's Scalar and Cursor output parameters (and Cursor result sets).

Scalar parameters are common to all databases and Cursor parameters are exclusive to Oracle and DB2 databases.

Defining MBOs from stored procedures with output parameters (SPOP)

From a stored procedure, the developer can map attributes of a MBO to:

Scalar output parameters
Cursor as output parameters
Cursor as result sets

Stored Procedures With Output Parameters Definition Examples
Stored Procedure's Output Parameter	SQL Query/Definition
Scalar output parameter (ASA)	{CALL sampledb.dba.testScalarSPOP(:id,:amount)} create PROCEDURE dba.testScalarSPOP (in id INT, out amount INT) BEGIN select * from bonus; select bonus_amount into amount from bonus where emp_id = :id; END
Cursor as output parameter (Oracle)	{CALL TESTCURSORSPOP(["pcursor"=":pcursor"])} create or replace procedure testCursorSPOP( p_cursor out types.cursorType ) as begin open p_cursor for select * from tblSPOP; end;
Cursor as return result set (DB2)	{CALL TESTCURSORSPOP()} CREATE PROCEDURE testCursorSPOP() LANGUAGE SQL DYNAMIC RESULT SETS 1 BEGIN DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM tblSPOP; OPEN C1; END

Stored Procedures With Output Parameters Definition Examples

Stored Procedure's Output Parameter

SQL Query/Definition

Scalar output parameter (ASA)

{CALL sampledb.dba.testScalarSPOP(:id,:amount)}

create PROCEDURE dba.testScalarSPOP (in id INT, out amount INT) 
BEGIN
    select * from bonus;
    select bonus_amount into amount 
    from bonus 
    where emp_id = :id;
END

Cursor as output parameter (Oracle)

{CALL TESTCURSORSPOP(["pcursor"=":pcursor"])}

create or replace procedure testCursorSPOP( p_cursor out types.cursorType ) as 
begin
	    open p_cursor for select * from 
tblSPOP;

end;

Cursor as return result set (DB2)

{CALL TESTCURSORSPOP()}

CREATE PROCEDURE testCursorSPOP() LANGUAGE SQL
DYNAMIC RESULT SETS 1 
BEGIN 
	DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM tblSPOP;
	OPEN C1;
END

Preview dialog

Only In and Inout parameters appear in the Preview dialog. Out parameters are filtered from the parameter table.

When the mouse hovers over an optional result set in the Select a result set to preview field, a tooltip describes the column structure of the result set.

All possible result sets are listed in the corresponding input field, from which you can select one to preview. Result sets can be derived either from an input SQL statement or output parameters of stored procedures, and are represented either as:

DERIVED – identifies the result set that was derived from a list of stored procedure out parameters. Or,
RESULT SET- n – identifies a result set returned from a stored procedure or SQL statement. For a stored procedure that returns multiple result sets the index (1, 2, and so on) identifies the result set in the list of result sets returned by the stored procedure.

All Scalar output parameters are grouped into a single result set (DERIVED in the Select a result set to preview field) while each Cursor output parameter derives a separate result set.

Attributes Mapping and Properties view

Similar to the Preview dialog, in that when the mouse hovers over the result set in the Select a result set to preview field, a tooltip describes the column structure. All possible result sets are listed in the corresponding input field (DERIVED and RESULT SET -n), from which you can select one for attribute mapping.

Whenever you change the result set, the mapping control and mapping table are automatically refreshed.