Declare global variables

A global variable has its value stored in memory and is available to other SPL routines, run by the same user session, on the same database. A global variable has the following characteristics:

It requires a default value.
It can be used in any SPL routine, although it must be defined in each routine in which it is used.
It carries its value from one SPL routine to another until the session ends.

Restriction: You cannot define a collection variable as a global variable.

The following figure shows two SPL functions that share a global variable.

Figure 1: Two SPL functions that share a global variable.

CREATE FUNCTION func1()  RETURNING INT;
   DEFINE GLOBAL gvar INT DEFAULT 2;
   LET gvar = gvar + 1;
   RETURN gvar;
END FUNCTION;

CREATE FUNCTION func2()  RETURNING INT;
   DEFINE GLOBAL gvar INT DEFAULT 5;
   LET gvar = gvar + 1;
   RETURN gvar;
END FUNCTION;

Although you must define a global variable with a default value, the variable is only set to the default the first time you use it. If you execute the two functions in the following figure in the order given, the value of gvar would be 4.

Figure 2: Global variable default values.

EXECUTE FUNCTION func1();
EXECUTE FUNCTION func2();

But if you execute the functions in the opposite order, as the following figure shows, the value of gvar would be 7.

Figure 3: Global variable default values.

EXECUTE FUNCTION func2();
EXECUTE FUNCTION func1();

For more information, see Executing routines.