MySQL 8.0-13.2.1 CALL Statement

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

The CALL statement invokes a stored procedure that was defined previously with CREATE PROCEDURE.

CALL语句调用前面用CREATE procedure定义的存储过程。

Stored procedures that take no arguments can be invoked without parentheses. That is, CALL p() and CALL p are equivalent.

不带参数的存储过程可以在没有括号的情况下调用。也就是说，CALL p()和CALL p是等价的。

CALL can pass back values to its caller using parameters that are declared as OUT or INOUT parameters. When the procedure returns, a client program can also obtain the number of rows affected for the final statement executed within the routine: At the SQL level, call the ROW_COUNT() function; from the C API, call the mysql_affected_rows() function.

CALL可以使用声明为OUT或INOUT参数的参数将值返回给调用者。当过程返回时，客户端程序还可以获取例程中执行的最终语句受影响的行数:在SQL级别，调用ROW_COUNT()函数;从C API，调用mysql_affected_rows()函数。

For information about the effect of unhandled conditions on procedure parameters, see Section 13.6.7.8, “Condition Handling and OUT or INOUT Parameters”.

有关未处理条件对过程参数的影响，请参见13.6.7.8节“条件处理和OUT或INOUT参数”。

To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.) For an INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter that the procedure sets to the current server version, and an INOUT value that the procedure increments by one from its current value:

要使用OUT或INOUT参数从过程中返回值，可以通过用户变量的方式传递参数，然后在过程返回后检查变量的值。(如果从另一个存储过程或函数内部调用该过程，也可以将例程参数或局部例程变量作为IN或INOUT参数传递。)对于INOUT参数，在将其值传递给过程之前初始化它。下面的过程具有一个OUT参数，该过程将其设置为当前服务器版本，以及一个INOUT值，该过程将其当前值加1:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, you can see that the values of the two variables are set or modified:

在调用过程之前，初始化要作为INOUT参数传递的变量。调用该过程后，可以看到这两个变量的值被设置或修改了:

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+--------------------+------------+
| @version           | @increment |
+--------------------+------------+
| 8.0.3-rc-debug-log |         11 |
+--------------------+------------+

In prepared CALL statements used with PREPARE and EXECUTE, placeholders can be used for IN parameters, OUT, and INOUT parameters. These types of parameters can be used as follows:

在与PREPARE和EXECUTE一起使用的准备好的CALL语句中，占位符可以用于In参数、OUT和INOUT参数。这些类型的参数可以如下使用:

mysql> SET @increment = 10; mysql> PREPARE s FROM 'CALL p(?, ?)'; mysql> EXECUTE s USING @version, @increment; mysql> SELECT @version, @increment; +--------------------+------------+ | @version | @increment | +--------------------+------------+ | 8.0.3-rc-debug-log | 11 | +--------------------+------------+

To write C programs that use the CALL SQL statement to execute stored procedures that produce result sets, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. CLIENT_MULTI_RESULTS must also be enabled if CALL is used to execute any stored procedure that contains prepared statements. It cannot be determined when such a procedure is loaded whether those statements produce result sets, so it is necessary to assume that they do so.

要编写使用CALL SQL语句执行生成结果集的存储过程的C程序，必须启用CLIENT_MULTI_RESULTS标志。这是因为除了过程中执行的语句可能返回的任何结果集之外，每个CALL还返回一个结果来指示调用状态。如果使用CALL来执行任何包含预处理语句的存储过程，也必须启用CLIENT_MULTI_RESULTS。当加载这样的过程时，无法确定这些语句是否产生结果集，因此有必要假定它们会产生结果集。

CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect(), either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passing CLIENT_MULTI_STATEMENTS (which also enables CLIENT_MULTI_RESULTS). CLIENT_MULTI_RESULTS is enabled by default.

可以在调用mysql_real_connect()时启用CLIENT_MULTI_RESULTS，可以显式地传递CLIENT_MULTI_RESULTS标志本身，也可以隐式地传递CLIENT_MULTI_STATEMENTS(它也启用CLIENT_MULTI_RESULTS)。CLIENT_MULTI_RESULTS默认是启用的。

To process the result of a CALL statement executed using mysql_query() or mysql_real_query(), use a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Multiple Statement Execution Support.

要处理使用mysql_query()或mysql_real_query()执行的CALL语句的结果，使用一个循环调用mysql_next_result()来确定是否有更多的结果。有关示例，请参见多语句执行支持。

C programs can use the prepared-statement interface to execute CALL statements and access OUT and INOUT parameters. This is done by processing the result of a CALL statement using a loop that calls mysql_stmt_next_result() to determine whether there are more results. For an example, see Prepared CALL Statement Support. Languages that provide a MySQL interface can use prepared CALL statements to directly retrieve OUT and INOUT procedure parameters.

C程序可以使用预备语句接口执行CALL语句并访问OUT和INOUT参数。这是通过使用调用mysql_stmt_next_result()的循环来处理CALL语句的结果来完成的，以确定是否有更多的结果。有关示例，请参见准备调用语句支持。提供MySQL接口的语言可以使用准备好的CALL语句直接检索OUT和INOUT过程参数。

Metadata changes to objects referred to by stored programs are detected and cause automatic reparsing of the affected statements when the program is next executed. For more information, see Section 8.10.3, “Caching of Prepared Statements and Stored Programs”.

存储程序引用的对象的元数据变化会被检测到，并导致在下一次执行程序时自动重新解析受影响的语句。要了解更多信息，请参见8.10.3节“预编译语句和存储程序的缓存”。