SITENAME or DBSERVERNAME

If you use the SITENAME or DBSERVERNAME keyword as the default, the variable must be a CHAR, VARCHAR, NCHAR, NVARCHAR, or LVARCHAR data type. Its default value is the name of the database server at runtime. It is recommended that the size of the variable be at least 128 bytes long. You risk getting an error message during INSERT and ALTER TABLE operations if the length of the variable is too small to store the default value.

The following example uses the SITENAME keyword to specify a default value. This example also initializes a global BYTE variable to NULL:

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 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:

           .-,-------.
           V         |
>>-DEFINE----SPL_var-+------------------------------------------>
 
>--+-data_type--------------------------------------+--;-------><
   +-REFERENCES--+-BYTE-+---------------------------+
   |             '-TEXT-'                           |
   +-LIKE--+-view----+--.--column-------------------+
   |       +-synonym-+                              |
   |       '-table---'                              |
   +---PROCEDURE------------------------------------+
   |  (1)                                           |
   '------+-+-BLOB-+------------------------------+-'
          | '-CLOB-'                              |
          |                                  (2)  |
          +-| Subset of Complex Data Types |------+
          +-distinct_type-------------------------+
          '-opaque_type---------------------------'

Notes:

Dynamic Server only
See Subset of Complex Data Types (IDS)

Element	Description	Restrictions	Syntax
column	Column name	Must already exist in the table or view	Identifier;
data_type	Type of SPL_var	Cannot be SERIAL, SERIAL8, TEXT, nor BYTE	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