|
|
|
Applications need to pass and receive values from the stored procedures they call. They do this through input and output parameters declared in the procedure specification.
Applications can pass and receive values from stored procedures using input and output parameters declared in the stored procedure specification. When it processes the CREATE PROCEDURE statement, FairCom DB SQLFairCom DB SQL declares Java variables of the same name. This means the body of the stored procedure can refer to input and output parameters as if they were Java variables declared in the body of the stored procedure.
Note: Procedure result sets are another way for applications to receive output values from a stored procedure. Procedure result sets provide output in a row-oriented tabular format. See Returning a Procedure Result Set: the RESULT Clause and DhSQLResultSet.
Parameter declarations include the parameter type (IN, OUT, or INOUT), the parameter name, and c-treeSQL data type (see Implicit Data Type Conversion Between SQL and Java Types for details of how FairCom DB SQL data types map to Java data types).
Declare input and output parameters in the specification section of a stored procedure, as shown in the following example.
Example Input, Output, and Input-Output Parameters
CREATE PROCEDURE order_entry (
IN cust_name CHAR(20),
IN item_num INTEGER,
IN quantity INTEGER,
OUT status_code INTEGER,
INOUT order_num INTEGER
)
.
.
.
Note: The input parameter cust_name in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.
When the order_entry stored procedure executes, the calling application passes values for the cust_name, item_num, quantity, and order_num input parameters. The body of the procedure refers to them as Java variables. Similarly, Java code in the body of order_entry processes and returns values in the status_code and order_num output parameters. The variable order_num can be used for both passing input and receiving output value from the procedure.
Note: Procedure parameter names should always be lower case as parameter names are treated as identifiers in the FairCom DB SQL statement and are converted to lowercase by FairCom DB SQL. Parameter names used within the procedure body are not converted to lower case. Hence declaring parameters with upper case may lead to compilation errors.
