Variables, Literals, Parameters, and Comments

Let's start with a review of how we define and use various data itemsvariables, literals, and parametersin our stored programs and how we can add comments to document our code.

3.1.1. Variables

The first thing we'll look at is how the MySQL stored program language deals with variables and literals, because without some understanding of these items, we can't create any meaningful examples for any other topics.

A variable is a named data item whose value can change during program execution. A literal (described in the next section) is an unnamed data item that can be assigned to a variable. Typically, literals are hardcoded into your stored program code and are usually assigned to variables , passed as parameters, or used as arguments to SELECT statements.

The DECLARE statement allows us to create a variable. As we will see a bit later on, it appears within a block of code before any cursor or handler declarations and before any procedural statements. The syntax of the DECLARE statement is:

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

Multiple variables may be declared in a single DECLARE statement, and the variable(s) can be assigned a default (or initial) value. If you don't use the DEFAULT clause, then the variable starts off with the NULL value.

Using DEFAULT is a good practice because, unless you initialize a variable, any subsequent operations on that variableother than a simple assignmentmay also return NULL. We'll give an example of this type of error later in the chapter.

The datatype may be any of the valid MySQL data types that you can use in a CREATE TABLE statement. We provide detailed descriptions of each data type later in this chapter; Table 3-1 summarizes those most commonly used.

Some examples of variable declarations for each of the data types are shown in Example 3-1.

Example 3-1. Examples of variable declarations

3.1.2. Literals

A literal is a data value hardcoded into your program. You commonly use literals in variable assignment statements or comparisons (IF, for instance), as arguments to procedures or functions, or within SQL statements.

There are three fundamental types of literals :

Numeric literals

A numeric literal represents a number and can be defined as a raw number (300, 30.45, etc.), as a hexadecimal value, or in scientific notation. Scientific notation is a way of representing very large or very high-precision values. The letter 'e' in what otherwise appears to be a number indicates that the numeric value on the left of the 'e' is multiplied by 10 to the power of the number to the right of the 'e'. So 2.4e is equivalent to 2.4 x 104 or 24,000. You cannot use commas in numeric literals.

Hexadecimal values are represented in the traditional format, by prefixing them with '0x'. So 0xA represents the hexadecimal number 'A', which is 10 in decimal.

Date literals

A date literal is a string in the format 'YYYY-MM-DD' orfor the DATETIME data typein the format 'YYYY-MM-DD HH24:MI:SS'. So '1999-12-31 23:59:59' represents the last second of the last century (unless you believe that because there was no year 0, the century actually ended on 2000-12-31).

String literals

A string literal is simply any string value surrounded by quotes. If single quotes themselves need to be included within the literal itself delimited by single quotes, they can be represented by two single quotes or prefixed with a backslash ('). You can also enclose strings in double quotes, and you can use escape sequences for special characters ( for a tab, for a new line, \ for a backslash, etc.).

If the server is running in ANSI_QUOTES mode (SET sql_mode='ANSI_QUOTES') then only single quotes can be used for literals. Sequences enclosed in double quotes will be interpreted as identifiers (variables or column names, for instance) that contain special characters, in accordance with the ANSI standard.

3.1.3. Rules for Variable Names

MySQL is amazingly flexible when it comes to naming variables. Unlike most other programming languages, MySQL allows variable names to be extremely long (more than 255 characters); they can contain special characters and can commence with numeric characters. However, we recommend that you not take advantage of MySQL's flexibility in this caseuse sensible naming conventions and avoid overly long variable names (see Chapter 23 for these and other best practices).

3.1.4. Assigning Values to Variables

You manipulate variable values with the SET statement, which has the following syntax:

SET variable_name = expression [,variable_name = expression ...]

As you can see, it is possible to perform multiple assignments with a single SET statement.

Most languages do not require a SET statement for variable assignment, and consequently, one of the easiest mistakes to make when getting started is to try to assign a value to a variable without specifying SET, as in Example 3-2.

Example 3-2. Attempting to manipulate a variable without the SET statement

As is often the case with stored program compilation errors, the error message does not directly identify the absence of the SET statement, so when checking your program for strange compilation errors, double check that all variable assignments include SET.

3.1.5. Parameters

Parameters are variables that can be passed intoor out ofthe stored program from the calling program. Parameters are defined in the CREATE statement for the function or procedure as follows:

Create procedure|function( [[IN |OUT |INOUT ] parameter_name data_type...])

The parameter names follow the same naming rules that apply to variables. The data_type can be any of the types available to local variables. Parameters can be associated with an IN,OUT, or INOUT attribute:

IN

Unless otherwise specified, parameters assume the IN attribute. This means that their value must be specified by the calling program, and any modifications made to the parameter in the stored program cannot be accessed from the calling program.

OUT

An OUT parameter can be modified by the stored program, and the modified value can be retrieved from the calling program. The calling program must supply a variable to receive the output of the OUT parameter, but the stored program itself has no access to whatever might be initially stored in that variable. When the stored program commences, the value of any OUT variables appear as NULL, regardless of what value they may have been assigned in the calling program.

INOUT

An INOUT parameter acts both as an IN and as an OUT parameter. That is, the calling program may supply a value, the stored program itself may modify the value of the parameter, and the calling program may access this changed value when the stored program completes.

The IN, OUT, and INOUT keywords apply only to stored procedures and not to stored functions. In stored functions all parameters behave as IN parameters (although you cannot specify the IN keyword).

The next three examples illustrate these principles.

First, although MySQL lets us change the value of an IN parameter in a stored program, the change cannot be seen by the calling program. The stored program in Example 3-3 prints and then modifies the value of the parameter. While modification of the input parameter is allowed within the stored program, the original variable (@p_in) is unchanged.

Example 3-3. Example of an IN parameter

Next, in Example 3-4 we examine the behavior of an OUT parameter. Although the calling program has initialized the OUT parameter with a value, the stored program does not see that value. The calling program, however, sees the changed values when the procedure completes execution.

Example 3-4. Example of an OUT parameter

Finally, Example 3-5 shows that the value of an INOUT parameter can be seen by the stored program, modified, and returned in its modified form to the calling program.

Example 3-5. Example of an INOUT parameter

3.1.6. User Variables

User variables are special MySQL variables that can be defined and manipulated inside or outside stored programs. They have been available in MySQL since version 3 and are a feature of the MySQL base product, not the stored program language. However, we can make good use of user variables in two ways:

• Since user variables have a scope that is outside of individual stored programs, they can be used to represent variables that should be accessible from any stored program within a session. This approach is similar in principle to the use of global variables in other programming languages.
• User variables can provide an alternative method of passing information to stored programs. Stored programs can access the values of user variables, which can avoid the need to pass in the values as parameters. (See the earlier "Parameters" section for more information on parameters.)

User variables can be created and manipulated from the MySQL command-line clientor from any other program that can issue MySQL statementsusing the SET statement. Example 3-6 shows some examples of using SET from the MySQL client.

Example 3-6. Manipulating user variables in the MySQL client

You can access any user variable defined in the current session (e.g., connection) from within a stored program. For instance, Example 3-7 shows how to pass information to a stored procedure without using a procedure parameter.

Example 3-7. Using user variables to pass information from the calling program to the stored procedure

We can also create a user variable within a stored program. It will then be available from all other stored programs, acting like a global variable would in a language such as PHP. For instance, in Example 3-8, procedure p1( ) creates the user variable, which is visible within procedure p2( ).

Example 3-8. Using a user variable as a "global variable" across stored programs

A user variable is a variant data typeit can store a string, date, or numeric value. Data type conversions are performed automatically. User variables remain in existence for the duration of a MySQL session and can be accessed by any program or statement running within that session. They cannot, however, be accessed by other sessions.

In some programming languages (such as PHP), variables whose scope extends beyond a single function are identified by the global keyword. In other languages the syntax for defining these variables may differ, but they are often still referred to as "global" variables. In MySQL, the global clause of the SET statement allows you to set the server-wide value of system variables, not to create the equivalent of a PHP global variable. For this reason, referring to user variables as "global" in scope can lead to confusion and probably should be avoided. Note that you cannot use the global clause of the SET statement to create your own variables.

Using user variables to implement variables that are available across multiple stored programs can be useful on occasion. However, you should definitely use this technique sparingly. As in all programming languages, overuse of global variables that scope beyond a single program can lead to code that is hard to understand and maintain. Routines that share such variables become tightly coupled and hence hard to maintain, test, or even understand in isolation.

3.1.7. Comments

Two styles of comments are supported in MySQL stored programs:

• Two dashes followed by a space create a comment that continues until the end of the current line. We'll call these single-line comments.
• C-style comments commence with /* and terminate with */. We'll call these multiline comments.

Single-line comments are useful for documenting variable declarations and simple single-line statements. Multiline comments are more useful for creating larger comment chunks, such as a standard comment header that accompanies each stored program definition.

The chunk of code in Example 3-9 illustrates both types of comments.

Example 3-9. Example of stored program comments