Creating Procedures with INOUT Parameters

JDBC for MySQL - Herong's Tutorial Examples

∟Creating Procedures with INOUT Parameters

This section describes how to create a procedure with INOUT parameters.

To do more tests on CallableStatement objects, I created another stored procedure with INOUT parameters:

herong> local\mysql\bin\mysql -u Herong -pTopSecret

mysql> USE HerongDB;
Database changed

mysql> -- Changing the command delimiter to '$'
mysql> DELIMITER '$';

mysql> -- Creating the procedure
mysql> CREATE PROCEDURE C2F(INOUT C REAL, INOUT F REAL)
    -> BEGIN
    ->   IF C IS NOT NULL THEN
    ->     SET F = 1.8*C + 32.0;
    ->   ELSEIF F IS NOT NULL THEN
    ->     SET C = (F-32.0)/1.8;
    ->   ELSE
    ->     SET C = 0.0;
    ->     SET F = 32.0;
    ->   END IF;
    ->   SELECT C, F;
    -> END$
Query OK, 0 rows affected (0.00 sec)

mysql> -- Testing the procedure
mysql> SET @C = 20$
Query OK, 0 rows affected (0.00 sec)

mysql> CALL C2F(@C,@F)$
+------+------+
| C    | F    |
+------+------+
|   20 |   68 |
+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> -- Checking output parameters
mysql> SELECT @C, @F$
+------+------+
| @C   | @F   |
+------+------+
| 20   | 68   |
+------+------+
1 row in set (0.00 sec)