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:
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
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:
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.
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.
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)
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development