declare variable

SQL/PL

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, 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;