Informix Guide to SQL: Tutorial Creating and Using SPL Routines

Any variable that you use in an SPL routine, other than a variable that is implicitly defined in the parameter list of the routine, must be defined in the body of the routine.

The value of a variable is held in memory; the variable is not a database object. Therefore, rolling back a transaction does not restore the values of SPL variables.

To define a variable in an SPL routine, use the DEFINE statement. DEFINE is not an executable statement. DEFINE must appear after the CREATE PROCEDURE statement and before any other statements. The examples in Figure 10-19 are all legal variable definitions.

DEFINE a INT;
DEFINE person person_t;
DEFINE GLOBAL gl_out INT DEFAULT 13;

For more information on DEFINE, see the description in the Informix Guide to SQL: Syntax.

An SPL variable has a name and a data type. The variable name must be a valid identifier, as described in the Identifier segment in the Informix Guide to SQL: Syntax.

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.

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 10-20, 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.

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 10-21 shows.

DEFINE x INT;
DEFINE y INT8;

DEFINE name CHAR(15);

DEFINE today DATETIME YEAR TO DAY;

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 10-22 shows how to define a variable for BLOB and CLOB objects.

DEFINE a_blob BLOB;
DEFINE b_clob CLOB;

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 10-23 shows.

DEFINE t REFERENCES TEXT;
DEFINE b REFERENCES BYTE;

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

Important: A collection variable must be defined as a local variable. You cannot define a collection variable as a global variable.

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 10-24 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.

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 10-25 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 10-26, 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 10-27 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 10-28.

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 );


Important: Before you can use a row type variable, you must initialize the row variable with a LET statement or SELECT...INTO statement. 

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 10-29 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.

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 10-30, the variable loc1 defines the data type for the locations column in the image table.

DEFINE loc1 LIKE image.locations;

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 10-31 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.

Because Dynamic Server supports routine overloading, you can define more than one SPL routine or external routine with the same name. If you call any routine from an SPL routine, Dynamic Server determines which routine to use, based on the arguments specified and the routine determination rules. For information about routine overloading and routine determination, see Extending Informix Dynamic Server 2000.

Tip: 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 an owner name.

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 10-32 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.

If you define a variable as 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 Informix Guide to SQL: Syntax.

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 10-33, 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;

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 10-34 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

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

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.

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

Figure 10-35 shows 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 Figure 10-36 in the order given, the value of gvar would be 4.

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

But if you execute the functions in the opposite order, as Figure 10-37 shows, the value of gvar would be 7.

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

For more information, see Executing Routines.

Within an SPL routine, use the LET statement to assign values to the variables you have already defined.

If you do not assign a value to a variable, either by an argument passed to the routine or by a LET statement, the variable has an undefined value. An undefined value is different than a null value. If you attempt to use a variable with an undefined value within the SPL routine, you receive an error.

You can assign a value to a routine variable in any of the following ways:

• Use a LET statement.
• Use a SELECT...INTO statement.
• Use a CALL statement with a procedure that has a RETURNING clause.
• Use an EXECUTE PROCEDURE...INTO or EXECUTE FUNCTION...INTO statement.

With a LET statement, you can use one or more variable names with an equal (=) sign and a valid expression or function name. Each example in Figure 10-38 is a valid LET statement.

LET a = 5;
LET b = 6; LET c = 10;
LET a,b = 10,c+d;
LET a,b = (SELECT cola,colb FROM tab1 WHERE cola=10);
LET d = func1(x,y);

Dynamic Server allows you to assign a value to an opaque-type variable, a row-type variable, or a field of a row type. You can also return the value of an external function or another SPL function to an SPL variable.

Suppose you define the named row types zip_t and address_t, as Figure 10-25 on page 10-24 shows. Anytime you define a row-type variable, you must initialize the variable before you can use it. Figure 10-39 shows how you might define and initialize a row-type variable. You can use any row-type value to initialize the variable.

DEFINE a address_t;
LET a = ROW ('A Street', 'Nowhere', 'AA', 
			ROW(NULL, NULL))::address_t

After you define and initialize the row-type variable, you can write the LET statements that Figure 10-40 shows.

.
.
LET a.zip.z_code = 32601;
LET a.zip.z_suffix = 4555;
	-- Assign values to the fields of address_t


Tip: Use dot notation in the form variable.field or variable.field.field to access the fields of a row type, as Handling Row-Type Data describes.

Suppose you define an opaque-type point that contains two values that define a two-dimensional point, and the text representation of the values is '(x,y)'. You might also have a function circum() that calculates the circumference of a circle, given the point '(x,y)' and a radius r.

If you define an opaque-type center that defines a point as the center of a circle, and a function circum() that calculates the circumference of a circle, based on a point and the radius, you can write variable declarations for each. In Figure 10-41, c is an opaque type variable and d holds the value that the external function circum() returns.

DEFINE c point;
DEFINE r REAL;
DEFINE d REAL;


LET c = '(29.9,1.0)' ;	
	-- Assign a value to an opaque type variable


LET d = circum( c, r );
	-- Assign a value returned from circum()

The Informix Guide to SQL: Syntax describes in detail the syntax of the LET statement.

You can use the SELECT statement to fetch a value from the database and assign it directly to a variable, as Figure 10-42 shows.

SELECT fname, lname INTO a, b FROM customer

	WHERE customer_num = 101

Use the CALL or EXECUTE PROCEDURE statements to assign values returned by an SPL function or an external function to one or more SPL variables. You might use either of the statements in Figure 10-43 to return the full name and address from the SPL function read_address into the specified SPL variables.

EXECUTE FUNCTION read_address('Smith') 

	INTO p_fname, p_lname, p_add, p_city, p_state, p_zip;



CALL read_address('Smith')

      RETURNING p_fname, p_lname, p_add, p_city, p_state, p_zip;