Declaring Local Variables

A local variable has as its scope of reference the routine in which it is declared. If you omit the GLOBAL keyword, any variables declared in the DEFINE statement are local variables, and are not visible in other SPL routines.

For this reason, different SPL routines that declare local variables of the same name can run without conflict in the same DB-Access or SQL administration API session.

If a local variable and a global variable have the same name, the global variable is not visible within the SPL routine where the local variable is declared. (In all other SPL routines, only the global variable is in scope.)

The following DEFINE statement syntax is for declaring local variables:

Notes:

¹ See Subset of Complex Data Types


Element	Description	Restrictions	Syntax
column	Column name	Must already exist in the table or view	Identifier;
data_type	Type of SPL_var	Cannot be BIGSERIAL, BYTE, SERIAL, SERIAL8, or TEXT	Data Type
distinct_type	A distinct type	Must already be defined in the database	Identifier
opaque_type	An opaque type	Must already be defined in the database	Identifier
SPL_var	New SPL variable	Must be unique within statement block	Identifier;
synonym, table, view	Name of a table, view, or synonym	Synonym and the table or view to which it points must already exist when the statement is issued	Database Object Name

Local variables do not support default values. The following example shows some typical definitions of local variables:

CREATE PROCEDURE def_ex()
   DEFINE i INT;
   DEFINE word CHAR(15);
   DEFINE b_day DATE;
   DEFINE c_name LIKE customer.fname;
   DEFINE b_text REFERENCES TEXT;
END PROCEDURE