MySQL Stored Procedure Programming (2009)

Part I. Stored Programming Fundamentals

Chapter 3. Language Fundamentals

This chapter introduces the MySQL stored program language, a simple, readable but complete programming language based on the ANSI SQL:2003 SQL/PSM (Persistent Stored Module) specification.

The MySQL stored program language is a block-structured language (like Pascal) that includes familiar statements for manipulating variables , implementing conditional execution, performing iterative processing, and handling errors. Users of other stored program languages such as Oracle PL/SQL or Microsoft SQL Server Transact-SQL will find features of the language very familiar. In fact, users of the IBM DB2 SQL Procedural language will find MySQL's stored program language almost identical—both are based on the SQL/PSM specification. Users of other programming languages that are typically used with MySQL—such as PHP, Java, or Perl—might find the stored program language a little verbose, but should have no difficulty at all learning the language.

In this chapter we will look at the fundamental building blocks of the stored program language—variables, literals, parameters, comments, operators, expressions, and data types. We will also discuss MySQL 5 "strict" mode and its implications. In the next chapter we will build on this base by describing the block structure, conditional statements (IF and CASE), and looping capabilities of the language.

Variables, Literals, Parameters, and Comments

Let's start with a review of how we define and use various data items—variables, literals, and parameters—in our stored programs and how we can add comments to document our code.

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 variable—other than a simple assignment—may 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

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

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.4e4 is equivalent to 2.3 × 10⁴ or 23,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' or—for the DATETIME data type—in 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 (\t for a tab, \n 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.

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 case—use sensible naming conventions and avoid overly long variable names (see Chapter 23 for these and other best practices).

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.

Parameters

Parameters are variables that can be passed into—or out of—the 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)

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 uservariables 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 client—or from any other program that can issue MySQL statements—using 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 type—it 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, theglobal 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.

Tip

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.

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

|

*/

Operators

MySQL operators include the familiar operators common to most programming languages, although C-style operators (++,—,+=, etc.) are not supported.

Operators are typically used within the SET statement to change the value of a variable, within comparison statements such as IF or CASE, and in loop control expressions. Example 3-10 shows a few simple examples of using operators within stored programs.

Example 3-10. Examples of operators in a stored program

create procedure operators( )

begin

DECLARE a int default 2;

declare b int default 3;

declare c FLOAT;

set c=a+b; select 'a+b=',c;

SET c=a/b; select 'a/b=',c;

SET c=a*b; Select 'a*b=',c;

IF (a<b) THEN

select 'a is less than b';

END IF;

IF NOT (a=b) THEN

SELECT 'a is not equal to b';

END IF;

end;

The various types of operators (mathematical , comparison , logical, and bitwise) are described in the following subsections.

Mathematical Operators

MySQL supports the basic mathematical operators you learned about in elementary school (pay attention class!): addition (+), subtraction (-), multiplication (*), and division (/).

In addition, MySQL supports two additional operators related to division: the DIV operator returns only the integer portion of division, while the modulus operator (%) returns only the remainder from a division. Table 3-2 lists, describes, and provides an example of the MySQL mathematical operators.

Table 3-2. MySQL mathematical operators

Comparison Operators

Comparison operators compare values and return TRUE, FALSE, or UNKNOWN (usually if one of the values being compared is NULL or UNKNOWN). They are typically used within expressions in IF, CASE, and loop control statements.

Table 3-3 summarizes the MySQL comparison operators .

Table 3-3. Comparison operators

Logical Operators

Logical operators operate on the three-valued logic values TRUE, FALSE, and NULL and return a like value. These operators are typically used with comparison operators to create more complex expressions.

For many of the logical operations, if any of the values being compared is NULL, then the result is also NULL. It is extremely important to remember this simple fact when creating logical expressions since, otherwise, subtle bugs can arise in your code.

The AND operator compares two Boolean expressions and returns TRUE only if both of the expressions are true. Table 3-4 shows the possible values generated by the AND function.

Table 3-4. Truth table for AND operator

The OR operator compares two Boolean expressions and returns TRUE if either of the expressions provided is TRUE (Table 3-5).

Table 3-5. Truth table for the OR operator

The XOR operator returns TRUE if either—but not both—of the values is TRUE. Table 3-6 shows the possible values for an XOR expression.

Table 3-6. Truth table for the XOR operator

Example 3-11 shows the use of the AND operator to combine multiple comparisons.

Example 3-11. Example of logical operators in practice

CREATE FUNCTION f_title(in_gender CHAR(1),

in_age INT, in_marital_status VARCHAR(7))

RETURNS VARCHAR(6)

BEGIN

DECLARE title VARCHAR(6);

IF in_gender='F' AND in_age<16 THEN

SET title='Miss';

ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Married' THEN

SET title='Mrs';

ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Single' THEN

SET title='Ms';

ELSEIF in_gender='M' AND in_age<16 THEN

SET title='Master';

ELSEIF in_gender='M' AND in_age>=16 THEN

SET title='Mr';

END IF;

RETURN(title);

END;

Bitwise Operators

Bitwise operators perform operations on the underlying binary representation of a variable. Table 3-7 lists the bitwise operators .

Table 3-7. Bitwise operators

Bitwise operators are similar to logical operators, except that they perform their operations on each bit within a variable.

For instance, consider the integers 5 (binary 101) and 4 (binary 010). The OR operator sets each bit if either of the bits is set in the inputs; so 5|2=7, because 101|010=111, which is 7 in decimal.

The bitwise AND operator sets a bit only if both the bits are true in the input. So 5&6=4, because 101&110=100, which equals 4.

Expressions

An expression is a combination of literals, variables, and operators that resolves to some value. Conditional execution and flow-control statements usually depend on the value of an expression to determine loop continuation or code branching.

Example 3-12 shows a variety of expressions .

Example 3-12. Examples of expressions

Myvariable_name

Myvariable_name+1

ABS(Myvariable_name)

3.14159

IF(Myvariable='M','Male','Female')

(2+4)/12

Built-in Functions

You can use most of the functions that MySQL makes available for use in SQL statements within stored programs. These are fully documented in the MySQL reference manual, and we provide details and examples for most of these functions in Chapter 9. We'll also talk about how you can create your own "stored" functions in the MySQL stored program language in Chapter 10.

The functions that may be used in SQL but not in stored programs are those involved in group (multiple-row) operators. These include functions such as SUM, COUNT, MIN, MAX, and AVG. MySQL accepts these functions within expressions, but they will return NULL as shown in Example 3-13.

Example 3-13. Aggregate functions in stored procedures return NULL

mysql> create procedure functions( )

begin

DECLARE a int default 2;

declare b int default 3;

declare c FLOAT;

SET c=SUM(a); select c;

end;

Query OK, 0 rows affected (0.00 sec)

mysql> call functions( );

+------+

| c |

+------+

| NULL |

+------+

1 row in set (0.00 sec)

MySQL functions fall into the following categories:

String functions

These functions perform operations on string variables. For example, you can concatenate strings, find characters within strings, obtain a substring, and perform other common operations.

Mathematical functions

These functions perform operations on numbers. For example, you can perform exponentiation (raise to a power), trigonometric functions (sine, cosine, etc.), random number functions, logarithms, and so on.

Date and time functions

These functions perform operations on dates and times. For example, you can get the current date, add or subtract time intervals from dates, find the difference between two dates, and extract certain portions of a date (e.g., get the time of day from a date-time).

Miscellaneous functions

These functions include everything not easily categorized in the above three groupings. They include cast functions, flow control functions (e.g., CASE), informational functions (e.g., server version), and encryption functions.

Table 3-8 summarizes some of the most frequently used functions; see Chapter 9 for a more complete coverage of function syntax and examples.

Table 3-8. Commonly used MySQL functions

Functions can be used in any statement that accepts an expression—for example, in SET statements, conditional statements (IF, CASE), and loop control clauses. Example 3-14 shows some examples that use functions in SET and IF clauses.

Example 3-14. Examples of functions in SET and IF clauses

CREATE PROCEDURE function_example( )

BEGIN

DECLARE TwentyYearsAgoToday DATE;

DECLARE mystring VARCHAR(250);

SET TwentyYearsAgoToday=date_sub(curdate( ), interval 20 year);

SET mystring=concat('It was ',TwentyYearsAgoToday,

' Sgt Pepper taught the band to play...');

SELECT mystring;

IF (CAST(SUBSTR(version( ),1,3) AS DECIMAL(2,1)) <5.0) THEN

SELECT 'MySQL versions earlier than 5.0 cannot run stored programs - you

must be hallucinating';

ELSE

SELECT 'Thank goodness you are running 5.0 or higher!';

END IF;

END$$

CALL function_example( )$$

+---------------------------------------------------------+

| mystring |

+---------------------------------------------------------+

| It was 1985-11-22 Sgt Pepper taught the band to play... |

+---------------------------------------------------------+

1 row in set (0.03 sec)

+-----------------------------------------------+

| Thank goodness you are running 5.0 or higher! |

+-----------------------------------------------+

| Thank goodness you are running 5.0 or higher! |

+-----------------------------------------------+

1 row in set (0.03 sec)

Data Types

Variables in MySQL stored programs can be assigned any of the data types available to columns in MySQL tables. We previewed most of the data types earlier, in Table 3-1.

All variables in MySQL stored programs are scalars , which is to say variables that store only a single item. There are no equivalents to arrays, records, or structures such as you can find in some other programming languages.

String Data Types

MySQL supports two basic string data types : CHAR and VARCHAR. CHAR stores fixed-length strings, while VARCHAR stores variable-length strings. If a CHAR variable is assigned a value shorter than its declared length, it will be blank-padded out to the declared length. This does not occur with VARCHAR variables.

When used in MySQL tables, the choice of CHAR or VARCHAR can be significant because it can affect the amount of disk storage needed. However, in stored programs, the additional memory requirements will be minimal and, use CHARs and VARCHARs can be used interchangeably in all expressions, there is little advantage to either data type. We generally use VARCHARs because they are capable of storing longer strings.

The CHAR data type can store a maximum of 255 bytes, and the VARCHAR a maximum of 65,532 bytes.

The ENUM data type

The ENUM data type is used to store one of a set of permissible values. These values can be accessed as their string value or as their indexed position in the set of possibilities. If you attempt to assign a value into an ENUM that does not appear in the list, MySQL will either issue a warning and insert a NULL or—if the sql_mode includes one of the "strict" values (see the later section "MySQL 5 "Strict" Mode")—issue an error.

Example 3-15 illustrates the use of ENUMs in stored programs.

Example 3-15. Using ENUMs in stored programs

CREATE PROCEDURE sp_enums(in_option ENUM('Yes','No','Maybe'))

BEGIN

DECLARE position INTEGER;

SET position=in_option;

SELECT in_option,position;

END

--------------

Query OK, 0 rows affected (0.01 sec)

--------------

CALL sp_enums('Maybe')

--------------

+-----------+----------+

| in_option | position |

+-----------+----------+

| Maybe | 3 |

+-----------+----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------

CALL sp_enums(2)

--------------

+-----------+----------+

| in_option | position |

+-----------+----------+

| No | 2 |

+-----------+----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------

CALL sp_enums('What?')

--------------

ERROR 1265 (01000): Data truncated for column 'in_option' at row 1

The SET data type

The SET type is similar to the ENUM type, except that multiple values from the list of allowable values can occur in the variables (see Example 3-16). As with the ENUM type, an attempt to assign a value not in the list will generate an error in "strict" mode, and a warning otherwise.

Example 3-16. Behavior of SET variables in stored programs

CREATE PROCEDURE sp_set(in_option SET('Yes','No','Maybe'))

BEGIN

SELECT in_option;

END

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

CALL sp_set('Yes')

--------------

+-----------+

| in_option |

+-----------+

| Yes |

+-----------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

--------------

CALL sp_set('Yes,No,Maybe')

--------------

+--------------+

| in_option |

+--------------+

| Yes,No,Maybe |

+--------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------

CALL sp_set('Yes,No,Go away')

--------------

ERROR 1265 (01000): Data truncated for column 'in_option' at row 1

Numeric Data Types

MySQL supports two families of numeric types:

§ Exact numeric types such as the INT and DECIMAL types

§ Approximate numeric types such as FLOAT

Accurate numeric types store an exact value for a number. The various INT types (INT, BIGINT, TINYINT) differ in that they use different amounts of storage, which therefore restricts the magnitude of the numbers that they can store. Each type can be signed (capable of storing positive or negative numbers) or unsigned, which further restricts the maximum values that the type may store (allowing a variable to be unsigned doubles the maximum possible number that can be stored). Table 3-9 shows the limits for the various integer types.

Table 3-9. Limits for the various integer data types

Floating-point data types (FLOAT, DOUBLE, REAL) store numbers of variable size and precision. In MySQL tables, FLOAT types use 32 bits of storage by default, while DOUBLE uses 64 bits of storage.

Be aware, however, that the floating-point data types store approximate representations of numbers. Most of the time this is unimportant, but in some circumstances you will want to use the precision data types, such as DECIMAL or NUMERIC, to avoid rounding errors that can occur when performing mathematical operations on floating-point numbers.

Date and Time Data Types

MySQL stores date-times with a precision down to one second. In MySQL tables, columns of the DATE data type can store the date part of a date-time only, while the DATETIME can store both a date and a time.

TEXT and BLOB Data Types

In MySQL tables, the TEXT data type can store up to 64K of data, and LONGTEXT can store up to 4,294,967,295 characters. BLOB and LONGBLOB data types can store similar amounts of data, but are able to store binary as well as character data.

MySQL 5 "Strict" Mode

MySQL 5 "strict " mode applies when either STRICT_TRANS_TABLES or STRICT_ALL_TABLES is included in the list of options supplied to the sql_mode configuration variable. STRICT_ALL_TABLES will cause any attempt to set a column to an invalid value to fail with an error.STRICT_TRANS_TABLES has the same effect, but only if the table is transactional.

If neither of these settings is in effect, MySQL will either accept the update or do a "best fit" of the invalid value into a legal column value. For instance, if you try to assign a string value into an integer column, MySQL may set the value of the column to 0. A warning will be generated whenever such a "truncation" occurs.

Strict mode will also cause errors to occur for missing columns in an INSERT statement, unless that column has an associated DEFAULT clause.

STRICT_ALL_TABLES can have some dubious side effects when you are performing multirow updates or inserts into nontransactional tables. Because there is no rollback capability for a nontransactional table, the error may occur after a certain number of valid row updates have occurred. This means that in the event of a strict-mode error on a nontransactional table, the SQL statement may partially succeed. This is rarely desirable behavior, and for this reason the default setting in MySQL 5.0 is STRICT_TRANS_TABLES.

You can change your strict mode at any time with a SET statement:

SET sql_mode='STRICT_ALL_TABLES'

The strict mode also determines how stored programs deal with attempts to assign invalid values to variables. If either of the strict modes is in effect, then an error will be generated whenever an attempt to assign an invalid value to a variable occurs. If no strict modes are in effect, then only warnings are generated.

Note that this behavior is controlled by the sql_mode settings that are in effect when the program is created, not when it is run. So once a strict stored program is created, it remains strict, even if the sql_mode settings are relaxed later on. In the same way, programs that are created when none of the strict modes are in effect will continue to generate warnings rather than errors when invalid data is assigned, regardless of the sql_mode that is in effect when the program runs.

Stored Program Behavior and Strict Mode

All variables in a MySQL stored program must be declared before use—with the exception of "user" variables, which are prefixed by the @ symbol and may be defined outside of the stored program. Furthermore, variables in MySQL stored programs must be assigned an explicit data type, and this data type cannot change during program execution. In this respect, the MySQL stored program language resembles "strongly typed" languages such as C, Java, and C# rather than dynamically typed languages such as Perl and PHP.

When created in strict mode, as explained in the previous section, stored programs will reject with an error any attempt to assign an invalid or inappropriate value to a variable. Such rejected assignments will include attempts to assign strings to numeric data or attempts to assign values that exceed the storage limitations declared for the variable.

However, when a stored program is created in non-strict mode, MySQL will perform a best attempt to convert invalid data and will generate a warning rather than an error. This allows you to—for instance—assign a string value to a variable defined as an integer. This non-strict behavior can lead to unexpected results or subtle bugs if you do not carefully ensure that you always use variables in ways that are appropriate for their data type. For these reasons it is usually best to create stored programs in strict mode and generate an error that you cannot possibly fail to notice during program testing or execution.

Program Examples

We'll illustrate these differences with an example that compares the behavior of the MySQL stored program in non-strict mode with several other programming languages.

Example 3-17 shows a Java program that intends to concatenate an integer value to a string value with the intention of printing the string "99 bottles of beer on the wall". Unfortunately for the beer, the programmer accidentally declared variable c as an int, rather than as aString. The Java compiler detects this error during compile time when it detects an attempt to assign a string expression to an integer variable, and the program fails to compile—no harm done.

Example 3-17. Type checking in a Java program

$cat simplejava.java

package simplejava;

public class SimpleJava {

public static void main(String[] args) {

String b;

int a;

int c;

a=99;

b="Bottles of beer on the wall";

c=a+" "+c;

System.out.println(c);

}

}

$javac simplejava.java

simplejava.java:11: incompatible types

found : java.lang.String

required: int

c=a+" "+c;

^

1 error

Now let's look at an equivalent example (in a dynamically typed language—in this case, PHP). In PHP and Perl, variable data types change on the fly as required. In Example 3-18, the variable c started as a number, but when subjected to a string assignment, the data type dynamically changed to a string. The program therefore works as required.

Example 3-18. Dynamic variable typing in PHP

$cat simplephp.php

<?php

$a=99;

$b="Bottles of beer on the wall";

$c=0; #c is a number

$c=$a." ".$b; #c is now a string

print $c."\n";

?>

$php simplephp.php

99 Bottles of beer on the wall

Now let's look at the equivalent non-strict MySQL stored program version of this logic, as shown in Example 3-19. This procedure has the same data type error as in the previous examples—the variable c should be defined as a VARCHAR, but it is instead declared as an INT.

Example 3-19. MySQL stored program non-strict type checking

CREATE PROCEDURE strict_test( )

BEGIN

DECLARE a INT;

DECLARE b VARCHAR(20);

DECLARE c INT;

SET a=99;

SET b="Bottles of beer on the wall";

SET c=CONCAT(a," ",b);

SELECT c;

END

--------------

Query OK, 0 rows affected (0.01 sec)

mysql> call strict_test( );

+------+

| C |

+------+

| 99 |

+------+

1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;

+---------+------+----------------------------------------+

| Level | Code | Message |

+---------+------+----------------------------------------+

| Warning | 1265 | Data truncated for column 'b' at row 1 |

| Warning | 1265 | Data truncated for column 'c' at row 1 |

+---------+------+----------------------------------------+

2 rows in set (0.01 sec)

Without the strict mode, MySQL does not generate an error when the attempt to supply a string to an integer value occurs, nor does it dynamically convert the data type of the integer variables. Instead, it assigns only the numeric part of the string expression to the integer—leading to an unexpected and erroneous result. However, if we had created the procedure when in strict mode, we would have generated a runtime error, as shown in Example 3-20.

Example 3-20. Stored program type checking in strict mode

mysql> CALL strict_test( );

ERROR 1406 (22001): Data too long for column 'b' at row 1

It's almost always preferable for your programs to operate in strict mode. While a non-strict program will sometimes be able to continue where a strict program would fail with an error, the risk that the non-strict program will exhibit unexpected and inappropriate behaviors is usually too high. Remember that the behavior of a stored program depends on the setting of the variable sql_mode when the program is created,not when the program is run.

Tip

Stored programs should almost always operate in strict mode to avoid unpredictable behavior when invalid data assignments occur. The strict mode for a stored program is determined by the setting of the sql_mode variable in effect when the program is created, not when the program is run.

As always, the onus is on the programmer to ensure that data types are used appropriately. As Bruce Eckel noted in his article "Strong Typing vs. Strong Testing" (http://www.mindview.net/WebLog/log-0025), strong typing in computer languages only provides an illusion of safety—true validation of correct behavior can only be obtained through strong testing . You should not assume that by declaring a variable as being of a certain type you are implicitly performing validation of the data being applied to that variable.

Conclusion

In this chapter we provided an overview of the building blocks of the MySQL stored program language. The MySQL stored program language—based on the ANSI SQL:2003 PSM specification—is a block-structured language that supports all the programming fundamentals that you would expect from a procedural language. The major aspects of the stored program language with which you should be familiar at this point are:

§ The DECLARE statement, which allows you to define and initialize program variables.

§ Stored program parameters, which allow you to pass information into or—in the case of stored procedures—out of a stored program.

§ The SET statement, which allows you to change the value of a program variable.

§ MySQL functions, operators, and data types—the MySQL stored program language utilizes most of the equivalents available in the MySQL SQL language.

Stored program type checking is very dependent on the setting of the sql_mode configuration variable. If a program is created when the sql_mode variable includes one of the strict settings (STRICT_TRANS_TABLES or STRICT_ALL_TABLES), then the program will reject invalid variable assignments with an error. If neither of the strict modes is in effect, then the stored program will generate an error when invalid data assignments occur, but will continue execution. Non-strict stored program behavior can lead to unexpected and subtle bugs, and we recommend that you usually use the strict mode when creating your stored programs.