CREATE PROCEDURE example1() 
   DEFINE n INT; DEFINE j INT;
   DEFINE n CHAR (1);    -- redefinition produces an error

CREATE PROCEDURE example2() 
   DEFINE n INT; DEFINE j INT;
   ...
   BEGIN
   DEFINE n CHAR (1);    -- character n masks global integer variable
   ...
END

Use the following syntax for declaring global variables:

                   .-,-------.
                   V         |
>>-DEFINE--GLOBAL----SPL_var-+---------------------------------->
 
                                           (1)
>--+-data_type--DEFAULT--| Default Value |------+--;-----------><
   '-REFERENCES--+-BYTE-+--DEFAULT NULL---------'
                 '-TEXT-'
 

1. See ***

Element	Description	Restrictions	Syntax
data_type	Type of SPL_var	See Declaring Global Variables.	Data Type
SPL_var	New SPL variable	Must be unique within statement block	Identifier

The GLOBAL keyword indicates that the variables that follow have a scope of reference that includes all SPL routines that run in a given DB-Access or SQL API session. The data types of these variables must match the data types of variables in the global environment. The global environment is the memory that is used by all the SPL routines that run in a given DB-Access or SQL API session. The values of global variables are stored in memory.

SPL routines that are running in the current session share global variables. Because the database server does not save global variables in the database, the global variables do not remain when the current session closes.

The first declaration of a global variable establishes the variable in the global environment; subsequent global declarations simply bind the variable to the global environment and establish the value of the variable at that point.

The following example shows two SPL procedures, proc1 and proc2; each has defined the global variable gl_out:

• SPL procedure proc1

  CREATE PROCEDURE proc1()
     ...
     DEFINE GLOBAL gl_out INT DEFAULT 13;
     ...
     LET gl_out = gl_out + 1;
  END PROCEDURE;

• SPL procedure proc2

  CREATE PROCEDURE proc2()
     ...
     DEFINE GLOBAL gl_out INT DEFAULT 23;
     DEFINE tmp INT;
     ...
     LET tmp = gl_out
  END PROCEDURE;

If proc1 is called first, gl_out is set to 13 and then incremented to 14. If proc2 is then called, it sees that gl_out is already defined, so the default value of 23 is not applied. Then, proc2 assigns the existing value of 14 to tmp. If proc2 had been called first, gl_out would have been set to 23, and 23 would have been assigned to tmp. Later calls to proc1 would not apply the default of 13.

Databases of different database server instances do not share global variables, but all the databases of the same database server instance can share global SPL variables in a single session. The database server and any application development tools, however, do not share global variables.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]