The CALL statement is used to invoke a stored procedure that was previously defined using CREATE PROCEDURE.
Syntax
CALL sp_name([parameter[,...]])
CALL sp_name[()]
A stored procedure without parameters can be called without using parentheses. For example, CALL proc() and CALL proc are equivalent.
Parameters
Parameter |
Description |
|---|---|
| sp_name | The name of the stored procedure to be called. You can use database_name.sp_name to specify the database. |
| parameter | The parameter passed to the stored procedure. It can be a constant, variable, or expression. For OUT or INOUT parameters, user variables must be used. |
Considerations
CALL can use declared OUT or INOUT parameters to pass values back to the caller. You can also call the ROW_COUNT() function to get the number of rows affected by all statements in the routine after execution. For the C API, you can use the mysql_affected_rows() function to achieve this.
OUT or INOUT parameters can be passed using user variables to retrieve values from the stored procedure. After the procedure returns, check the value of the variable. If the procedure is called from another stored procedure or function, you can also pass routine parameters or local routine variables as IN or INOUT parameters. For INOUT parameters, initialize the value before passing it 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 an IN parameter.
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 an OUT parameter.
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 an INOUT parameter.
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 |
+---------------------------+------------+