declare variable
Syntax
declare_variable:
DECLARE var_name[, var_name]... var_type [DEFAULT constant]
Description
The var_name and cond_name items are standard SQL identifiers.
Simple variables can be declared to be of any supported SQL data type except LONG VARCHAR, LONG WVARCHAR, and LONG VARBINARY. A DEFAULT value can also be specified if desired.
Simple variables can be declared to be global—outside the scope of a CREATE PROCEDURE/FUNCTION at the connection level. The var_name of a global variable is visible to any stored procedure or function that is called from that connection unless, of course, a local variable with the same name is declared inside a called procedure or function. The set statement can be used at the connection level to assign a value to a previously declared global variable. Global variables assigned at the connection level outside of a stored procedure must be prefixed with the "@" symbol. The "@" global variable prefix can, but does not have to, be used inside a stored procedure or function.
Example
open database bookshop;declare @yr_born smallint;set @yr_born = 1819;create procedure var_refs(in yr_born smallint)b1: begin declare yr_born smallint default 1802; b2: begin declare yr_born smallint default 1866; select full_name, yr_born from author where yr_born = b2.yr_born; select full_name, yr_born from author where yr_born = b1.yr_born; select full_name, yr_born from author where author.yr_born = var_refs.yr_born; select full_name, yr_born from author where yr_born = @yr_born; select full_name, yr_born from author b1 where b1.yr_born = var_refs.yr_born; end;end b1;
See Also