O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

Executing the stored procedure in the Query Browser
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.

Examples of variables in stored procedures
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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required