2.4.
Parameters
Most of the stored programs you write will
include one or more parameters. Parameters make stored programs
much more flexible and therefore more useful. Next, let's create a
stored procedure that accepts parameters.
The stored procedure shown in Figure 2-6 accepts
an integer parameter,
input_number
, and calculates the
square root of that number. The resulting number is returned as a
result set.
Place parameters within parentheses that are
located immediately after the name of the stored procedure. Each
parameter has a
name
, a data type, and,
optionally
, a mode. Valid
modes are
IN
(read-only),
INOUT
(read-write), and
OUT
(write-only). No parameter mode appears in Figure 2-6,
because
IN
is the default and this is an
IN
parameter.
We'll take a closer look at parameter modes
following this example.
In addition to the parameter, this stored
procedure introduces two other features of MySQL stored
programs:
-
DECLARE
-
A statement used to create local
variables
for
use in the stored program. In this case, we create a floating-point
number called
l_sqrt
.
-
SET
-
A statement used to assign a value to a
variable. In this case, we assign the square root of our input
parameter (using the built-in
SQRT
function) to the
floating-point number we created with the
DECLARE
command.
We can run this script, and test the resulting
stored procedure in the MySQL client, as shown in Example 2-3.
Example 2-3.
Creating and executing a stored procedure with a parameter
mysql>
SOURCE
my_sqrt.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
CALL
my_sqrt(12)$$
+-----------------+
l_sqrt
+-----------------+
3.4641016151378
+-----------------+
1 row in set (0.12 sec)
Query OK, 0 rows affected (0.12 sec)
|
2.4.1. Parameter
Modes
Parameters in MySQL can be defined as
IN
,
OUT
, or
INOUT
:
-
IN
-
This mode is the default. It indicates that the
parameter can be passed into the stored program but that any
modifications are not returned to the calling program.
-
OUT
-
This mode means that the stored program can
assign a value to the parameter, and that value will be passed back
to the calling program.
-
INOUT
-
This mode means that the stored program can read
the parameter and that the calling program can see any
modifications that the stored program may make to that
parameter.
You can use all of these parameter modes in
stored procedures, but only the
IN
mode in stored
functions (see the later See "Stored Functions" section).
Let's change our square root program so that it
puts the result of its calculations into an
OUT
variable,
as shown in Figure 2-7.
In the MySQL client, we now have to provide a
variable to hold the value of the
OUT
parameter. After the
stored procedure has finished executing, we can look at that
variable to retrieve the output, as shown in Example 2-4.
Example 2-4.
Creating and executing a stored procedure with an OUT
parameter
mysql>
SOURCE
my_sqrt2.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql>
CALL
my_sqrt(12,@out_value) $$
Query OK, 0 rows affected (0.03 sec)
mysql>
SELECT
@out_value $$
+-----------------+
@out_value
+-----------------+
3.4641016151378
+-----------------+
1 row in set (0.00 sec)
|
|
