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.

Table 3-1. Commonly used MySQL data types

Data type	Explanation	Examples of corresponding values
INT, INTEGER	A 32-bit integer (whole number). Values can be from approximately -2.1 billion to +2.1 billion. If unsigned, the value can reach about 4.2 billion, but negative numbers are not allowed.	123,345 -2,000,000,000
BIGINT	A 64-bit integer (whole number). Values can be from approximately -9 million trillion to +9 million trillion or from 0 to 18 million trillion if unsigned.	9,000,000,000,000,000,000 -9,000,000,000,000,000,000
FLOAT	A 32-bit floating-point number. Values can range from about -1.7e38 to 1.7e38 for signed numbers or 0 to 3.4e38 if unsigned.	0.00000000000002 17897.890790 -345.8908770 1.7e21
DOUBLE	A 64-bit floating-point number. The value range is close to infinite ( 1.7e308).	1.765e203 -1.765e100
DECIMAL(precision,scale) NUMERIC(precision,scale)	A fixed-point number. Storage depends on the precision, as do the possible numbers that can be stored. NUMERICs are typically used where the number of decimals is important, such as for currency.	78979.00 -87.50 9.95
DATE	A calendar date, with no specification of time.	'1999-12-31'
DATETIME	A date and time, with resolution to a particular second.	'1999-12-31 23:59:59'
CHAR(length)	A fixed-length character string. The value will be right-padded up to the length specified. A maximum of 255 bytes can be specified for the length.	'hello world '
VARCHAR(length)	A variable-length string up to 64K in length.	'Hello world'
BLOB, TEXT	Up to 64K of data, binary in the case of BLOB, or text in the case of TEXT.	Almost anything imaginable
LONGBLOB, LONGTEXT	Longer versions of the BLOB and TEXT types, capable of storing up to 4GB of data.	Almost anything imaginable, but a lot more than you would have imagined for BLOB or TEXT

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

Example 3-1. Examples of variable declarations

DECLARE l_int1 int default -2000000;
DECLARE l_int2 INT unsigned default 4000000;
DECLARE l_bigint1 BIGINT DEFAULT 4000000000000000;
DECLARE l_float FLOAT DEFAULT 1.8e8;
DECLARE l_double DOUBLE DEFAULT 2e45;
DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95;

DECLARE l_date DATE DEFAULT '1999-12-31';
DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59';

DECLARE l_char CHAR(255) DEFAULT 'This will be padded to 255 chars';
DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded';

DECLARE l_text TEXT DEFAULT 'This is a really long string. In stored programs
 we can use text columns fairly freely, but in tables there are some
 limitations regarding indexing and use in various expressions.';

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

mysql> Create procedure no_set_stmt( )
BEGIN
 DECLARE i INTEGER;
 i=1;
END;
$$

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds
to your MySQL server version for the right syntax to use near 
'procedure no_set_stmt( )
BEGIN
 DECLARE i INT;
 i=1;
END' at line 1

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

mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
 /* We can see the value of the IN parameter */
 SELECT p_in;
 /* We can modify it*/
 SET p_in=2;
 /* show that the modification took effect */
 select p_in;
END;

/* This output shows that the changes made within the stored program cannot be accessed 
from
 the calling program (in this case, the mysql client):*/

mysql> set @p_in=1

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_demo_in_parameter(@p_in)

+------+------------------------------------------+
| p_in | We can see the value of the IN parameter |
+------+------------------------------------------+
| 1 | We can see the value of the IN parameter |
+------+------------------------------------------+
1 row in set (0.00 sec)

+------+-------------------------------------+
| p_in | IN parameter value has been changed |
 +------+-------------------------------------+
| 2 | IN parameter value has been changed |
+------+-------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p_in,'We can''t see the changed value from the calling program'

+-------+---------------------------------------------------------+
| @p_in | We can't see the changed value from the calling program |
+-------+---------------------------------------------------------+
| 1 | We can't see the changed value from the calling program |
+-------+---------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)

BEGIN
 /* We can't see the value of the OUT parameter */
 SELECT p_out,'We can''t see the value of the OUT parameter';
 /* We can modify it*/
 SET p_out=2;
 SELECT p_out,'OUT parameter value has been changed';

END;

mysql> SET @p_out=1

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_demo_out_parameter(@p_out)

+-------+-------------------------------------------------------------------+
| p_out | We can't see the value of the OUT parameter in the stored program |
+-------+-------------------------------------------------------------------+
| NULL | We can't see the value of the OUT parameter in the stored program |
+-------+-------------------------------------------------------------------+
 1 row in set (0.00 sec)

+-------+--------------------------------------+
| p_out | OUT parameter value has been changed |
+-------+--------------------------------------+
| 2 | OUT parameter value has been changed |
+-------+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_out,"Calling program can see the value of the changed OUT parameter"

+----------------------------------------------------------------+
| Calling program can see the value of the changed OUT parameter |
+----------------------------------------------------------------+
| 2 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)

BEGIN

 SELECT p_inout,'We can see the value of the INOUT parameter in the stored program';

 SET p_inout=2;
 SELECT p_inout,'INOUT parameter value has been changed';

END;
//
 Query OK, 0 rows affected (0.00 sec)

set @p_inout=1
//

Query OK, 0 rows affected (0.00 sec)

call sp_demo_inout_parameter(@p_inout) //

+---------+-------------------------------------------------------------------+
| p_inout | We can see the value of the INOUT parameter in the stored program |
+---------+-------------------------------------------------------------------+
| 1 | We can see the value of the INOUT parameter in the stored program |
+---------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

+---------+----------------------------------------+
| p_inout | INOUT parameter value has been changed |
+---------+----------------------------------------+
| 2 | INOUT parameter value has been changed |
+---------+----------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

select @p_inout ,"Calling program can see the value of the changed INOUT parameter"
//

+----------+------------------------------------------------------------------+
| @p_inout | Calling program can see the value of the changed INOUT parameter |
+----------+------------------------------------------------------------------+
| 2 | Calling program can see the value of the changed INOUT parameter |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT 'Hello World' into @x;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @x;
+-------------+
| @x |
+-------------+
| Hello World |
+-------------+
1 row in set (0.03 sec)

mysql> SET @y='Goodbye Cruel World';
Query OK, 0 rows affected (0.00 sec)

mysql> select @y;
+---------------------+
 | @y |
+---------------------+
| Goodbye Cruel World |
+---------------------+
1 row in set (0.00 sec)

mysql> SET @z=1+2+3;
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z |
+------+
| 6 |
+------+
1 row in set (0.00 sec)

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

mysql> CREATE PROCEDURE GreetWorld( )
 -> SELECT CONCAT(@greeting,' World');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @greeting='Hello';
Query OK, 0 rows affected (0.00 sec)

mysql> CALL GreetWorld( );
+----------------------------+
| CONCAT(@greeting,' World') |
+----------------------------+
| Hello World |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE PROCEDURE p1( )
 -> SET @last_procedure='p1';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE p2( )
 -> SELECT CONCAT('Last procedure was ',@last_procedure);
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p1( );
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p2( );
+-----------------------------------------------+
| CONCAT('Last procedure was ',@last_procedure) |
+-----------------------------------------------+
| Last procedure was p1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

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.

Use "user" variables sparingly in your stored programs. Excessive use of variables that scope beyond a single program leads to code that is nonmodular and hard to maintain.

 

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

create procedure comment_demo
 (in p_input_parameter INT -- Dummy parameter to illustrate styles
 )
/*
| Program: comment_demo
| Purpose: demonstrate comment styles
| Author: Guy Harrison
| Change History:
| 2005-09-21 - Initial
|
*/