Informix Guide to SQL: Syntax SPL Statements

Use the DEFINE statement to declare variables that an SPL routine uses and to assign them data types.

Usage

The DEFINE statement is not an executable statement. The DEFINE statement must appear after the routine header and before any other statements.

If you define a local variable (by using DEFINE without the GLOBAL keyword), the scope of the variable is the statement block in which it is defined. You can use the variable anywhere within the statement block. You can also use the same variable name outside the statement block with a different definition.

If you define a variable with the GLOBAL keyword, the variable is global in scope and is available outside the statement block and to other SPL routines.

The REFERENCES keyword lets you use BYTE and TEXT variables. BYTE and TEXT variables do not contain the actual data but are simply pointers to the data. The REFERENCES keyword is a reminder that the SPL variable is just a pointer. Use the SPL variables for BYTE and TEXT data types exactly as you would any other variable.

If you define the same variable twice within the same statement block, you receive an error. You can redefine a variable within a nested block, in which case it temporarily hides the outer declaration. The following example produces an error:

CREATE PROCEDURE example1() 

	DEFINE n INT; DEFINE j INT;

	DEFINE n CHAR (1);	 -- redefinition produces an error

	.

	.

	.

The database server allows the redeclaration in the following example. Within the nested statement block, n is a character variable. Outside the block, n is an integer variable.

CREATE PROCEDURE example2() 

	DEFINE n INT; DEFINE j INT;

	.

	.

	.

	BEGIN

	DEFINE n CHAR (1);	 -- character n masks integer variable

						-- locally

	.

	.

	.

	END

The GLOBAL keyword indicates that the variables that follow are available to other SPL routines through the global environment. 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 within 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 data types of global variables you use in your SPL routine must match the data types of variables in the global environment.

Databases do not share global variables. The database server and any application development tools do not share global variables.

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 the value of 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.

You can provide a literal value or a null value as the default for a global variable. You can also use a call to a built-in function to provide the default value. The following example uses the SITENAME function to provide a default value. It also defines a global BYTE variable.

CREATE PROCEDURE gl_def()

	DEFINE GLOBAL gl_site CHAR(200) DEFAULT SITENAME;

	DEFINE GLOBAL gl_byte REFERENCES BYTE DEFAULT NULL;

	.

	.

	.

END PROCEDURE

If you use the value returned by SITENAME or DBSERVERNAME as the default, the variable must be defined as a CHAR, VARCHAR, NCHAR, or NVARCHAR data type.

If you are using Dynamic Server, Informix recommends 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.

If you are using Enterprise Decision Server, Informix recommends that the length of the variable be at least 18 bytes. 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.

If you use the value returned by USER as the default, the variable must be defined as a CHAR, VARCHAR, NCHAR, or NVARCHAR data type.

If you are using Dynamic Server, Informix recommends that the length of the variable be at least 32 bytes. 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.

If you are using Enterprise Decision Server, Informix recommends that the length of the variable be at least 8 bytes. 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.

If you use CURRENT as the default, the variable must be a DATETIME value. If the YEAR TO FRACTION keyword has qualified your variable, you can use CURRENT without qualifiers. If your variable uses another set of qualifiers, you must provide the same qualifiers when you use CURRENT as the default value. The following example defines a DATETIME variable with qualifiers and uses CURRENT with matching qualifiers:

DEFINE GLOBAL d_var DATETIME YEAR TO MONTH 

		DEFAULT CURRENT YEAR TO MONTH;

If you use TODAY as the default, the variable must be a DATE value.

The only default value possible for a BYTE or TEXT variable is null. The following example defines a TEXT global variable that is called l_blob:

CREATE PROCEDURE use_text()

	DEFINE i INT;

	DEFINE GLOBAL l_blob REFERENCES TEXT DEFAULT NULL;

END PROCEDURE 

Nonglobal (local) variables do not allow defaults. The following example shows 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

A variable of type COLLECTION, SET, MULTISET, and LIST is a collection variable and holds a collection fetched from the database. You cannot define a collection variable as global (with the GLOBAL keyword) or with a default value.

A variable defined with the type COLLECTION is an untyped collection variable. An untyped collection variable is generic and can hold a collection of any type.

A variable defined with the type SET, MULTISET, or LIST is a typed collection variable. A typed collection variable can hold only a collection of its type.

You must use the not null keywords when you define the elements of a typed collection variable, as in the following examples:

DEFINE a SET ( INT NOT NULL );


DEFINE b MULTISET ( ROW ( b1 INT,
					        b2 CHAR(50)
					    ) NOT NULL );


DEFINE c LIST( SET( INTEGER NOT NULL ) NOT NULL );

Note that with variable c, both the INTEGER values in the SET and the SET values in the LIST are defined as NOT NULL.

You can define collection variables with nested complex types to hold matching nested complex type data. Any type or depth of nesting is allowed. You can nest row types within collection types, collection types within row types, collection types within collection types, row types within collection and row types, and so on.

If you define a variable of COLLECTION type, the variable acquires varying type assignments if it is reused within the same statement block, as in the following example:

DEFINE a COLLECTION;
LET a = setB;
.
.
.
LET a = listC;

In this example, varA is a generic collection variable that changes its data type to the data type of the currently assigned collection. The first LET statement makes varA a SET variable. The second LET statement makes varA a LIST variable.

Row variables hold data from named or unnamed row types. You can define a generic row variable, a named row variable, or an unnamed row variable.

A generic row variable, defined with the ROW keyword, can hold data from any row type. A named row variable holds data from the named row type specified in the variable definition. The following statements show examples of generic row variables and named row variables:

DEFINE d ROW;									-- generic row variable


DEFINE rectv rectangle_t;					-- named row variable

A named row variable holds named row types of the same type in the variable definition.

To define a variable that will hold data stored in an unnamed row type, use the ROW keyword followed by the fields of the row type, as in:

DEFINE area ROW ( x int, y char(10) );

Unnamed row types are type-checked only by structural equivalence. Two unnamed row types are considered equivalent if they have the same number of fields, and if the fields have the same type definitions. Therefore, you could fetch either of the following row types into the variable area defined above:

ROW ( a int, b char(10) )
ROW ( area int, name char(10) )

Row variables can have fields, just as row types have fields. To assign a value to a field of a row variable, use the SQL dot notation variableName.fieldName, followed by an expression, as in the following example:

CREATE ROW TYPE rectangle_t (start point_t, length real,
	width real);


DEFINE r rectangle_t;
		-- Define a variable of a named row type
LET r.length = 45.5;
		-- Assign a value to a field of the variable

When you assign a value to a row variable, you can use any allowed expression described in Expression.

Opaque-type variables hold data retrieved from opaque types, which you create with the CREATE OPAQUE TYPE statement. An opaque-type variable can only hold data of the opaque type on which it is defined.

The following example defines a variable of the opaque type point, which holds the x and y coordinates of a two-dimensional point:

DEFINE b point;

If you use the LIKE clause, the database server assigns the variable the same data type as a column in a table, synonym, or view.

The data types of variables that are defined as database columns are resolved at run time; therefore, column and table do not need to exist at compile time.

You can use the LIKE keyword to declare that a variable is like a SERIAL column. For example, if the column serialcol in the mytab table has the SERIAL data type, you can create the following SPL function:

CREATE FUNCTION func1()

DEFINE local_var LIKE mytab.serialcol;

RETURN;

END FUNCTION;

The variable local_var is treated as an INTEGER variable.

The PROCEDURE keyword indicates that in the current scope, the variable is a call to a UDR.

The DEFINE statement does not have a FUNCTION keyword. Use the PROCEDURE keyword, whether you are calling a user-defined procedure or a user-defined function.

Defining a variable of PROCEDURE type indicates that in the current statement scope, the variable is not a call to a built-in function. For example, the following statement defines length as an SPL routine, not as the built-in LENGTH function:

DEFINE length PROCEDURE;

.

.

.

LET x = length (a,b,c)

This definition disables the built-in LENGTH function within the scope of the statement block. You would use such a definition if you had already created a user-defined routine with the name length.

If you create an SPL routine with the same name as an aggregate function (SUM, MAX, MIN, AVG, COUNT) or with the name extend, you must qualify the routine name with the owner name.

The keyword REFERENCES indicates that the variable does not contain a BYTE or TEXT value but is a pointer to the BYTE or TEXT value. Use the variable as though it holds the data.

The following example defines a local BYTE variable:

CREATE PROCEDURE use_byte()

	DEFINE i INT;

	DEFINE l_byte REFERENCES BYTE;

END PROCEDURE --use_byte

If you pass a variable of BYTE or TEXT data type to an SPL routine, the data is passed to the database server and stored in the root dbspace or dbspaces that the DBSPACETEMP environment variable specifies, if it is set. You do not need to know the location or name of the file that holds the data. BYTE or TEXT manipulation requires only the name of the BYTE or TEXT variable as it is defined in the routine.