Declaring Local Variables

You can define a variable to be either local or global in scope. This section describes local variables. In an SPL routine, local variables:

• Are valid only for the duration of the SPL routine
• Are reset to their initial values or to a value the user passes to the routine, each time the routine is executed
• Cannot have default values

You can define a local variable on any of the following data types:

• Built-in data types (except SERIAL, SERIAL8, TEXT, or BYTE)
• Any extended data type (row type, opaque, distinct, or collection type) that is defined in the database prior to execution of the SPL routine

The scope of a local variable is the statement block in which it is declared. You can use the same variable name outside the statement block with a different definition.

For more information on defining global variables, see Declaring Global Variables.

Scope of Local Variables

A local variable is valid within the statement block in which it is defined and within any nested statement blocks, unless you redefine the variable within the statement block.

In the beginning of the SPL procedure in Figure 378, the integer variables x, y, and z are defined and initialized.

CREATE PROCEDURE scope()
   DEFINE x,y,z INT;
   LET x = 5; 
   LET y = 10;
   LET z = x + y; --z is 15
   BEGIN
      DEFINE x, q INT; 
      DEFINE z CHAR(5);
      LET x = 100;
      LET q = x + y;   -- q = 110
      LET z = 'silly'; -- z receives a character value
   END
   LET y = x; -- y is now 5
   LET x = z; -- z is now 15, not 'silly'
END PROCEDURE;

The BEGIN and END statements mark a nested statement block in which the integer variables x and q are defined as well as the CHAR variable z. Within the nested block, the redefined variable x masks the original variable x. After the END statement, which marks the end of the nested block, the original value of x is accessible again.

Declaring Built-In Type Variables

Built-in type variables hold data retrieved from built-in data types. You can define an SPL variable with any built-in type, except SERIAL and SERIAL8 as Figure 379 shows.

DEFINE x INT;
DEFINE y INT8;
DEFINE name CHAR(15);
DEFINE today DATETIME YEAR TO DAY;

Declaring Variables for Smart Large Objects (IDS)

A variable for a BLOB or CLOB object (or a data type that contains a smart large object) does not contain the object itself but rather a pointer to the object. Figure 380 shows how to define a variable for BLOB and CLOB objects.

DEFINE a_blob BLOB;
DEFINE b_clob CLOB;

Declaring Variables for Simple Large Objects

A variable for a simple large object (a TEXT or BYTE object) does not contain the object itself but rather a pointer to the object. When you define a variable on the TEXT or BYTE data type, you must use the keyword REFERENCES before the data type, as Figure 381 shows.

DEFINE t REFERENCES TEXT;
DEFINE b REFERENCES BYTE;

Declaring Collection Variables (IDS)

In order to hold a collection fetched from the database, a variable must be of type SET, MULTISET, or LIST.

A variable of SET, MULTISET, or LIST type is a collection variable that holds a collection of the type named in the DEFINE statement. Figure 382 shows how to define typed collection variables.

DEFINE a SET ( INT NOT NULL );

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

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

You must always define the elements of a collection variable as NOT NULL. In this example, the variable a is defined to hold a SET of non-NULL integers; the variable b holds a MULTISET of non-NULL row types; and the variable c holds a LIST of non-NULL sets of non-NULL decimal values.

In a variable definition, you can nest complex types in any combination or depth to match the data types stored in your database.

You cannot assign a collection variable of one type to a collection variable of another type. For example, if you define a collection variable as a SET, you cannot assign another collection variable of MULTISET or LIST type to it.

Declaring Row-Type Variables (IDS)

Row-type variables hold data from named or unnamed row types. You can define a named row variable or an unnamed row variable. Suppose you define the named row types that Figure 383 shows.

CREATE ROW TYPE zip_t
(
   z_code      CHAR(5),
   z_suffix    CHAR(4)
);

CREATE ROW TYPE address_t
(
   street      VARCHAR(20),
   city        VARCHAR(20),
   state       CHAR(2),
   zip         zip_t
);

CREATE ROW TYPE employee_t 
(
   name        VARCHAR(30),
   address     address_t
   salary      INTEGER
);

CREATE TABLE employee OF TYPE employee_t;

If you define a variable with the name of a named row type, the variable can only hold data of that row type. In Figure 384, the person variable can only hold data of employee_t type.

DEFINE person employee_t;

To define a variable that holds data stored in an unnamed row type, use the ROW keyword followed by the fields of the row type, as Figure 385 shows.

DEFINE manager ROW (name        VARCHAR(30),
                    department  VARCHAR(30),
                    salary      INTEGER );

Because unnamed row types are type-checked for structural equivalence only, a variable defined with an unnamed row type can hold data from any unnamed row type that has the same number of fields and the same type definitions. Therefore, the variable manager can hold data from any of the row types in Figure 386.

ROW ( name         VARCHAR(30),
      department   VARCHAR(30),
      salary       INTEGER );

ROW ( french      VARCHAR(30),
      spanish     VARCHAR(30),
      number      INTEGER );

ROW ( title       VARCHAR(30),
      musician    VARCHAR(30),
      price       INTEGER );

Declaring Opaque- and Distinct-Type Variables (IDS)

Opaque-type variables hold data retrieved from opaque data types. Distinct-type variables hold data retrieved from distinct data types. If you define a variable with an opaque data type or a distinct data type, the variable can only hold data of that type.

If you define an opaque data type named point and a distinct data type named centerpoint, you can define SPL variables to hold data from the two types, as Figure 387 shows.

DEFINE a point;
DEFINE b centerpoint;

The variable a can only hold data of type point, and b can only hold data of type centerpoint.

Declaring Variables for Column Data with the LIKE Clause

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

If the column contains a collection, row type, or nested complex type, the variable has the complex or nested complex type defined in the column.

In Figure 388, the variable loc1 defines the data type for the locations column in the image table.

DEFINE loc1 LIKE image.locations;

Declaring PROCEDURE Type Variables

In an SPL routine, you can define a variable of type PROCEDURE and assign the variable the name of an existing SPL routine or external routine. Defining a variable of PROCEDURE type indicates that the variable is a call to a user-defined routine, not a built-in routine of the same name.

For example, the statement in Figure 389 defines length as an SPL procedure or SPL function, 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 an SPL or external routine with the name LENGTH.

Using Subscripts with Variables

You can use subscripts with variables of CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, or TEXT data type. The subscripts indicate the starting and ending character positions that you want to use within the variable.

Subscripts must always be constants. You cannot use variables as subscripts. Figure 390 illustrates how to use a subscript with a CHAR(15) variable.

DEFINE name CHAR(15);
LET name[4,7] = 'Ream';
SELECT fname[1,3] INTO name[1,3] FROM customer
   WHERE lname = 'Ream';

In this example, the customer's last name is placed between positions 4 and 7 of name. The first three characters of the customer's first name is retrieved into positions 1 through 3 of name. The part of the variable that is delimited by the two subscripts is referred to as a substring.

Variable and Keyword Ambiguity

If you declare a variable whose name is an SQL keyword, ambiguities can occur. The following rules for identifiers help you avoid ambiguities for SPL variables, SPL routine names, and built-in function names:

• Defined variables take the highest precedence.
• Routines defined with the PROCEDURE keyword in a DEFINE statement take precedence over SQL functions.
• SQL functions take precedence over SPL routines that exist but are not identified with the PROCEDURE keyword in a DEFINE statement.

In general, avoid using an ANSI-reserved word for the name of the variable. For example, you cannot define a variable with the name count or max because they are the names of aggregate functions. For a list of the reserved keywords that you should avoid using as variable names, see the Identifier segment in the IBM Informix: Guide to SQL Syntax.

Variables and Column Names

If you use the same identifier for an SPL variable that you use for a column name, the database server assumes that each instance of the identifier is a variable. Qualify the column name with the table name, using dot notation, in order to use the identifier as a column name.

In the SELECT statement in Figure 391, customer.lname is a column name and lname is a variable name.

CREATE PROCEDURE table_test()

   DEFINE lname CHAR(15);
   LET lname = 'Miller';

   SELECT customer.lname INTO lname FROM customer
      WHERE customer_num = 502;
. . .
END PROCEDURE;

Variables and SQL Functions

If you use the same identifier for an SPL variable as for an SQL function, the database server assumes that an instance of the identifier is a variable and disallows the use of the SQL function. You cannot use the SQL function within the block of code in which the variable is defined. The example in Figure 392 shows a block within an SPL procedure in which the variable called user is defined. This definition disallows the use of the USER function in the BEGIN END block.

CREATE PROCEDURE user_test()
   DEFINE name CHAR(10);
   DEFINE name2 CHAR(10);
   LET name = user; -- the SQL function

   BEGIN
      DEFINE user CHAR(15); -- disables user function
      LET user = 'Miller';
      LET name = user; -- assigns 'Miller' to variable name
   END
   . . .
   LET name2 = user; -- SQL function again

SPL Routine Names and SQL Functions

For information about ambiguities between SPL routine names and SQL function names, see the IBM Informix: Guide to SQL Syntax.