The CALL statement is used to invoke a stored procedure previously defined using CREATE PROCEDURE.
Syntax
CALL sp_name([parameter[,...]])
CALL sp_name[()]
You can omit parentheses when calling a stored procedure without parameters. For example, CALL proc() is equivalent to CALL proc.
Parameters
Parameter |
Description |
|---|---|
| sp_name | The name of the stored procedure to be called. You can use database_name.sp_name Specify the database. |
| parameter | Parameters passed to a stored procedure. They can be constants, variables, or expressions. For OUT or INOUT Parameter, you must use a user variable. |
Usage instructions
The CALL statement can use declared OUT or INOUT parameters to pass values back to the caller. The ROW_COUNT() function can also be used to obtain the number of rows affected after all statements in the routine are executed; for the C API, this can be achieved by calling the mysql_affected_rows() function.
You can pass OUT or INOUT parameters through user variables to retrieve values from a stored procedure, and then check the variable values after the procedure returns. If this procedure is called from another stored procedure or function, you can also pass values by using routine parameters or local routine variables as IN or INOUT parameters. For INOUT parameters, the values are initialized before being passed to the procedure.
Examples
Example 1: Call a stored procedure without parameters.
obclient> CREATE TABLE employee_info(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary DECIMAL(10,2)
);
obclient> INSERT INTO employee_info VALUES
(1, 'Alice', 8000.00),
(2, 'Bob', 12000.00),
(3, 'Charlie', 9500.00);
obclient> DELIMITER //
obclient> CREATE PROCEDURE get_employee_list()
BEGIN
SELECT emp_id, emp_name, salary FROM employee_info ORDER BY salary DESC;
END //
obclient> DELIMITER ;
obclient> CALL get_employee_list();
The query result is as follows:
+--------+----------+----------+
| emp_id | emp_name | salary |
+--------+----------+----------+
| 2 | Bob | 12000.00 |
| 3 | Charlie | 9500.00 |
| 1 | Alice | 8000.00 |
+--------+----------+----------+
3 rows in set
Example 2: Call a stored procedure with IN parameters.
obclient> DELIMITER //
obclient> CREATE PROCEDURE get_employee_by_id(IN emp_id_param INT)
BEGIN
SELECT emp_id, emp_name, salary FROM employee_info WHERE emp_id = emp_id_param;
END //
obclient> DELIMITER ;
obclient> CALL get_employee_by_id(1);
The query result is as follows:
+--------+----------+----------+
| emp_id | emp_name | salary |
+--------+----------+----------+
| 1 | Alice | 8000.00 |
+--------+----------+----------+
1 row in set
Example 3: Call a stored procedure with OUT parameters.
obclient> DELIMITER //
obclient> CREATE PROCEDURE calculate_total_salary(OUT total_salary DECIMAL(10,2))
BEGIN
SELECT SUM(salary) INTO total_salary FROM employee_info;
END //
obclient> DELIMITER ;
obclient> SET @total = 0;
obclient> CALL calculate_total_salary(@total);
obclient> SELECT @total;
The query result is as follows:
+----------+
| @total |
+----------+
| 29500.00 |
+----------+
1 row in set
Example 4: Call a stored procedure with INOUT parameters.
obclient> DELIMITER //
obclient> CREATE PROCEDURE proc (OUT ver_param VARCHAR(100), INOUT incr_param INT)
BEGIN
SELECT VERSION() INTO ver_param;
SET incr_param = incr_param + 2;
END //
obclient> DELIMITER ;
obclient> SET @increment = 1;
obclient> CALL proc(@obversion, @increment);
obclient> SELECT @obversion, @increment;
The query result is as follows:
+---------------------------+------------+
| @obversion | @increment |
+---------------------------+------------+
| 5.7.25-OceanBase-v4.5.0.0 | 3 |
+---------------------------+------------+