Passing values to and from stored procedures: input and output parameters
Applications need to pass and receive values from the stored procedures they call. They do this through input and output parameters. When applications process the CREATE PROCEDURE statement, the SQL engine declares Java variables of the same name. Therefore, the stored procedure can refer to input and output parameters as if they were Java variables declared in the body of the stored procedure.
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.
Parameter declarations include the parameter type (IN, OUT, or INOUT), the parameter name, and SQL data type.
Declare input and output parameters in the specification section of a stored procedure, as shown in the following example.
CREATE PROCEDURE order_entry (
IN cust_name CHAR(20),
IN item_num INTEGER,
IN quantity INTEGER,
OUT status_code INTEGER,
INOUT order_num INTEGER
)
When the order_entry stored procedures 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.
