CALL

You can use the CALL statement to call a stored procedure that is previously defined by using the CREATE PROCEDURE statement.

Syntax:

CALL sp_name([parameter[,...]])
CALL sp_name[()]

Parentheses can be omitted when you call a stored procedure without parameters. For example, CALL proc() and CALL proc are equivalent.

The CALL statement can use the declared OUT or INOUT parameter to pass values to the invoker. You can also call the ROW_COUNT() function to obtain the number of rows affected after all statements in the routine are executed. For the C API, you can call the mysql_affected_rows() function.

You can pass the OUT or INOUT parameter as a user variable to obtain a value from a stored procedure and then check the variable value returned by the procedure. If a stored procedure has been called from another stored procedure or function, you can pass a routine parameter or local routine variable as an IN or INOUT parameter to pass a value. For the INOUT parameter, its value is initialized before it is passed to the procedure.

In the following example, the stored procedure contains an OUT parameter, which is set to the current server version, and an INOUT parameter, which is set to the current value of the procedure incremented by 2.

obclient> DELIMITER //

obclient> CREATE PROCEDURE proc (OUT ver_param VARCHAR(100), INOUT incr_param INT)
   BEGIN
     # Set the value of the OUT parameter.
     SELECT VERSION() INTO ver_param;
     # Increment the value of the INOUT parameter.
     SET incr_param = incr_param + 2;
   END //
Query OK, 0 rows affected

Before you call a procedure, initialize the variables to be passed as INOUT parameters. After you call the procedure, the values of the two variables have been set or modified, as shown in the following example:

obclient> DELIMITER;

obclient> SET @increment = 1;
Query OK, 0 rows affected

obclient> CALL proc(@obversion, @increment);
+------------------------------+------------+
| ver_param                    | incr_param |
+------------------------------+------------+
| 5.7.25-OceanBase_CE-v4.0.0.0 |          3 |
+------------------------------+------------+
1 row in set

obclient> SELECT @obversion, @increment;
+------------+------------+
| @version   | @increment |
+------------+------------+
| 4.0.0.0    |         3 |
+------------+------------+
Query OK, 0 rows affected