Syntax
------ 
DECLARE var_name [, var_name] ... [[ROW] TYPE OF]] type
[DEFAULT value]
 
Description
----------- 
This statement is used to declare local variables within
stored programs. To
provide a default value for the variable, include a DEFAULT
clause. The
value can be specified as an expression (even subqueries are
permitted); it need not be a constant. If the
DEFAULT clause is missing, the initial value is NULL.
 
Local variables are treated like stored routine parameters
with respect to data
type and overflow checking. See CREATE PROCEDURE.
 
Local variables must be declared before CONDITIONs, CURSORs
and HANDLERs.
 
Local variable names are not case sensitive.
 
The scope of a local variable is within the BEGIN ... END
block where it is
declared. The variable can be referred to in blocks nested
within the declaring
block, except those blocks that declare a variable with the
same name.
 
TYPE OF / ROW TYPE OF
 
TYPE OF and ROW TYPE OF anchored data types for stored
routines were introduced in MariaDB 10.3.
 
Anchored data types allow a data type to be defined based on
another object, such as a table row, rather than
specifically set in the declaration. If the anchor object
changes, so will the anchored data type. This can lead to
routines being easier to maintain, so that if the data type
in the table is changed, it will automatically be changed in
the routine as well.
 
Variables declared with ROW TYPE OF will have the same
features as implicit ROW variables. It is not possible to
use ROW TYPE OF variables in a LIMIT clause.
 
The real data type of TYPE OF and ROW TYPE OF table_name
will become known at the very beginning of the stored
routine call. ALTER TABLE or DROP TABLE statements performed
inside the current routine on the tables that appear in
anchors won't affect the data type of the anchored
variables, even if the variable is declared after an ALTER
TABLE or DROP TABLE statement.
 
The real data type of a ROW TYPE OF cursor_name variable
will become known when execution enters into the block where
the variable is declared. Data type instantiation will
happen only once. In a cursor ROW TYPE OF variable that is
declared inside a loop, its data type will become known on
the very first iteration and won't change on further loop
iterations.
 
The tables referenced in TYPE OF and ROW TYPE OF
declarations will be checked for existence at the beginning
of the stored routine call. CREATE PROCEDURE or CREATE
FUNCTION will not check the referenced tables for existence.
 
Examples
-------- 
TYPE OF and ROW TYPE OF from MariaDB 10.3:
 
DECLARE tmp TYPE OF t1.a;
 -- Get the data type from the column {{a}} in the table
{{t1}}
 
DECLARE rec1 ROW TYPE OF t1;
 -- Get the row data type from the table {{t1}}
 
DECLARE rec2 ROW TYPE OF cur1;
 -- Get the row data type from the cursor {{cur1}}
 


URL: https://mariadb.com/kb/en/declare-variable/