MySQL Stored Procedure Programming

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