Variables in stored programs

You can use system variables and user-defined variables in stored programs. You can use the DECLARE statement to define local variables in stored programs. You can declare parameters in stored routines (procedures and functions) to pass values between the routine and its caller.

Define local variables

Use the DECLARE statement to define local variables. You can specify a default value when you declare a variable or use the SET statement to assign a value.

When you define local variables in a stored program, the DECLARE statement provides a default value for the variable using the DEFAULT clause. This default value can be an expression, not necessarily a constant. If the DEFAULT clause is omitted, the initial value is NULL. The syntax is as follows:

DECLARE var_name [, var_name] ... data_type [DEFAULT value]

where data_type is an SQL data type. In addition, PL is compatible with the following data types:

• INT2 (converted internally to SMALLINT)

• INT4 (converted internally to INT)

• LONG (converted internally to MEDIUMTEXT)

You cannot assign a DEFAULT value to parameters or local variables of stored procedures or functions (for example, using the SET var_name=DEFAULT statement), which will result in a syntax error.

You can retrieve query results into local variables using the SELECT INTO statement or the FETCH INTO statement with an open cursor.

Variable declarations must appear before cursor or exception handler declarations. Local variable names are case-insensitive. The allowed characters and reference rules are the same as for other identifiers.

Scope and resolution of local variables

Information about the scope of local variables and how to resolve ambiguous names.

The scope of a local variable is the BEGIN END block in which it is declared. The variable can be referenced in nested blocks within the declaration block, but not in blocks with the same variable name.

Since local variables are only valid during the execution of the stored program, they cannot be referenced in prepared statements created within the stored program. Prepared statements are in scope for the current session, not the stored program, so they can execute after the program has completed, at which point the variable will no longer be in scope. For example, SELECT ... INTO local_var cannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters.

info

Local variables should not have the same name as columns in tables. If an SQL statement, such as a SELECT INTO statement, references a column with the same name as a declared local variable, the database resolves the reference to the variable.

In the following example, seekdb resolves the vname in the SELECT statement to the vname variable, not the vname column. Therefore, when the stored procedure proc1() is called, the newname variable will return the value "seekdb" regardless of the value of tbl1.vname.

DELIMITER //

CREATE PROCEDURE proc1 (x VARCHAR(10))
    BEGIN
      DECLARE vname VARCHAR(10) DEFAULT 'seekdb';
      DECLARE newname VARCHAR(10);
      DECLARE xid INT;

      SELECT vname, id INTO newname, xid
        FROM tbl1 WHERE vname = vname;
      SELECT newname;
    END //
Query OK, 0 rows affected

Similarly, in the following example, the SELECT statement in the cursor definition of the stored procedure references vname. The database resolves the reference to the variable with the same name, not to the column.

 CREATE PROCEDURE proc2 (x VARCHAR(10))
    BEGIN
      DECLARE vname VARCHAR(10) DEFAULT 'seekdb';
      DECLARE newname VARCHAR(10);
      DECLARE xid INT;
      DECLARE done TINYINT DEFAULT 1;
      DECLARE curdo CURSOR FOR SELECT vname, id FROM tbl1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;

      OPEN curdo;
      readlable1: LOOP
        FETCH FROM curdo INTO newname, xid;
        IF done THEN LEAVE readlable1; END IF;
        SELECT newname;
      END LOOP;
      CLOSE curdo;
    END //
Query OK, 0 rows affected