Rechercher dans le manuel MySQL
13.2.1 CALL Syntax
The CALL
statement invokes a stored
procedure that was defined previously with
CREATE PROCEDURE
.
Stored procedures that take no arguments can be invoked without
parentheses. That is, CALL p()
and
CALL p
are equivalent.
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.
For information about the effect of unhandled conditions on procedure parameters, see Section 13.6.7.8, “Condition Handling and OUT or INOUT Parameters”.
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:
- # Set value of OUT parameter
- # Increment value of INOUT parameter
- END;
Before calling the procedure, initialize the variable to be passed
as the INOUT
parameter. After calling the
procedure, the values of the two variables will have been set or
modified:
- +--------------------+------------+
- +--------------------+------------+
- +--------------------+------------+
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:
- +--------------------+------------+
- +--------------------+------------+
- +--------------------+------------+
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 will produce result sets, so it is necessary to assume
that they will.
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.
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
Section 28.7.19, “C API Multiple Statement Execution Support”.
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
Section 28.7.21, “C API Prepared CALL Statement Support”. Languages that
provide a MySQL interface can use prepared
CALL
statements to directly
retrieve OUT
and INOUT
procedure parameters.
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”.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-call.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.