Variables in stored programs

A stored program can use system variables and user-defined variables. It can also define local variables by using the DECLARE statement. A stored routine (procedure or function), can declare parameters to pass values with the invoker.

Define local variables

You can use the DECLARE statement to define local variables. When you declare a local variable, you can specify its default value or use the SET statement to assign a value to it.

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

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

In the syntax, the data_type parameter supports SQL data types. PL also supports the following data types:

• INT2 (internally converted into SMALLINT)

• INT4 (internally converted into INT)

• LONG (internally converted into MEDIUMTEXT)

You cannot assign the DEFAULT value to a parameter or local variable of a stored procedure or function. For example, if you execute the SET var_name=DEFAULT statement, a syntax error will be returned.

You can use the SELECT INTO or FETCH INTO (for opening a cursor) statement to retrieve the query result into a local variable.

The variable declaration must be prior to the cursor or exception handler declaration. Names of local variables are case-insensitive. The allowed characters and referencing rules are the same as those of other identifiers.

Applicable scope and parsing of local variables

This section describes the applicable scope of local variables and how to parse information with an indefinite name.

A local variable applies to the BEGIN END block where it is declared. This variable can be referenced by nested blocks of the block where the variable is declared, but cannot be referenced by a block with the same variable name.

A local variable is valid only during the execution of the stored program. Therefore, you cannot reference this local variable in a prepared statement created in the stored program. The prepared statement applies to the current session instead of the stored program. Therefore, this statement can be executed after the program ends. At this time, the local variable does not exist in the application scope. For example, you cannot use the SELECT ... INTO local_var statement as a prepared statement. This restriction also applies to parameters of stored procedures and functions.

In the following example, the database will parse vname in the SELECT statement as a reference to the vname variable instead of a reference to the vname column. Therefore, when you call the proc1() stored procedure, the newname variable will return the value "OceanBase" regardless of what the value of tbl1.vname is.

obclient> DELIMITER //

obclient> CREATE PROCEDURE proc1 (x VARCHAR(10))
    BEGIN
      DECLARE vname VARCHAR(10) DEFAULT 'OceanBase';
      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

In the following example, the SELECT statement in the cursor definition of the stored procedure references vname. The database parses this reference as a reference to the corresponding variable instead of the column.

obclient> CREATE PROCEDURE proc2 (x VARCHAR(10))
    BEGIN
      DECLARE vname VARCHAR(10) DEFAULT 'OceanBase';
      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