MySQL Stored Procedure Programming

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 ...