In stored programs, you can use system variables and user-defined variables. Stored programs can use the DECLARE statement to define local variables, and stored routines (procedures and functions) can declare parameters to pass values between the routine and its caller.
Define local variables
Use the DECLARE statement to define local variables. You can assign a default value to a local variable when declaring it, or assign a value using the SET statement.
When defining local variables in a stored program, the DECLARE statement can use the DEFAULT clause to assign a default value to the variable. This default value can be an expression and does not necessarily have to be a constant. If the DEFAULT clause is missing, the initial value is NULL. The syntax is as follows:
DECLARE var_name [, var_name] ... data_type [DEFAULT value]
The data_type supports SQL data types. In addition, PL is also compatible with the following data types:
INT2(internally converted toSMALLINT)INT4(internally converted toINT)LONG(internally converted toMEDIUMTEXT)
You cannot assign the DEFAULT value to parameters or local variables of a stored procedure or function (for example, using the SET var_name=DEFAULT statement), as this will cause a syntax error.
You can use the SELECT INTO statement or the FETCH INTO statement with a 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 reference rules are the same as those 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 within nested blocks within the declaring block, but not in blocks that have the same variable name.
Local variables are only valid during the execution of a stored program. Therefore, you cannot reference these local variables in a prepared statement created within the stored program. The scope of a prepared statement is the current session, not the stored program. As a result, the statement can be executed after the program ends, at which point the variables will no longer be within scope. For example, SELECT ... INTO local_var cannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters.
Note
Local variables should not have the same name as table columns. If an SQL statement, such as a SELECT INTO statement, contains a reference to a column that has the same name as a declared local variable, the database resolves the reference to the variable's name.
In the following example, the database parses vname in the SELECT statement as a reference to the vname variable, not a reference to the vname column. Therefore, when the stored procedure proc1() is called, the newname variable returns 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 cursor definition within the stored procedure in the following example, the SELECT statement references vname. The database parses this as a reference to the variable with that name, not a column reference.
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