Variables in stored programs

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

Define local variables

You can use the DECLARE clause to define local variables. You can specify a default value for a local variable when you declare it, 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 DECLARE clause with the DEFAULT clause to specify a default value for the variable. The default value can be an expression, not necessarily a constant. If you omit the DEFAULT clause, the initial value of the variable is NULL. The syntax is as follows:

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

Here, data_type specifies an SQL data type. In addition, PL supports 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 the parameters and local variables of a stored procedure or function (for example, by using the SET var_name=DEFAULT statement). This will result in a syntax error.

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

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

Scope and resolution of local variables

Information about the scope of local variables and how ambiguous names are resolved.

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 a stored program, they cannot be referenced in prepared statements created within the program. Prepared statements are scoped to the current session, not the stored program. Therefore, the statement can be executed after the program has finished, 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 parameters of stored procedures and functions.

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

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

Similarly, in the following example, the SELECT statement in the cursor definition of the stored procedure references vname. The database resolves this to a reference to the variable with the same name, not to a 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