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.

Figure 2-4. Executing the stored procedure in the Query Browser

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.

Figure 2-5. Examples of variables in stored procedures

 

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> SOURCEmy_sqrt.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALLmy_sqrt(12)$$
+-----------------+
| l_sqrt |
 +-----------------+
| 3.4641016151378 |
+-----------------+
1 row in set (0.12 sec)

Query OK, 0 rows affected (0.12 sec)

Figure 2-6. A stored procedure with parameters

 

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.

Figure 2-7. Example of using OUT parameter in a stored procedure

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> SOURCEmy_sqrt2.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

 mysql> CALLmy_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)