Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines

Home Contents Index Master Index New Book

Defining and Using Variables

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 14-16 are all legal variable definitions.

Figure 14-16

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

For more information on DEFINE, see the description of "DEFINE" of 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 of the Informix Guide to SQL: Syntax.

Declaring Local Variables

You can define a variable to be either local or global in scope. This section describes local variables. For more information on defining global variables, see "Declaring Global 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:

any built-in data type other than SERIAL, SERIAL8, TEXT, BYTE, CLOB, or BLOB.
any opaque, distinct, collection, or row type defined in the database by the time the SPL routine is executed.

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.

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 14-17, the integer variables x, y, and z are defined and initialized.

Figure 14-17

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, SERIAL8, CLOB, and BLOB as Figure 14-18 shows.

Figure 14-18

DEFINE x INT;
DEFINE y INT8;

DEFINE name CHAR(15);

DEFINE today DATETIME YEAR TO DAY;

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 the variable, you must use the keyword REFERENCES before the data type, as Figure 14-19 shows.

Figure 14-19

DEFINE t REFERENCES TEXT;
DEFINE b REFERENCES BYTE;

A variable for a simple large object does not contain the object itself but rather a pointer to the object.

Declaring Collection Variables

In order to hold a collection fetched from the database, a variable must be of type COLLECTION, SET, MULTISET, or LIST. A variable of COLLECTION type is an untyped collection variable that can hold any type of collection fetched from the database. For example, the variable defined in Figure 14-20 can hold any SET, MULTISET, or LIST defined in the database.

Figure 14-20

DEFINE a COLLECTION;

If you define a variable of COLLECTION type, the variable can acquire different type assignments if it is reused within the same statement block, as in Figure 14-21.

Figure 14-21

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

In this example, varA is an untyped collection variable that changes its data type to the data type of the collection currently assigned to it. The first LET statement makes varA a SET variable. The second LET statement makes varA a LIST variable. If you add another LET statement, you can assign varA still another data type.

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

Figure 14-22

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

Tip: 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; b will hold a MULTISET of non-null row types; and c will hold 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.

If you have defined both typed and untyped collection variables in an SPL routine, you can assign any typed collection variable (SET, MULTISET, or LIST) to an untyped collection variable. However, you cannot assign a typed collection variable to another typed collection variable, unless they have the same type.

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

Declaring Row-Type Variables

Row-type 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. Suppose you define the named row types that Figure 14-23 shows.

Figure 14-23
Some Example Row Types



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;

You can define a generic row variable that can hold any row-type value. Figure 14-24 shows how to use the ROW keyword without the name or definition of a row type to define a generic row variable.

Figure 14-24

DEFINE d ROW;

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 14-25 the person variable can only hold data of employee_t type. The person variable cannot hold data of zip_t type, address_t type, or any other row type in the database.

Figure 14-25

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 14-26 shows.

Figure 14-26

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 14-27.

Figure 14-27

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.

Declaring Opaque- and Distinct-Type Variables

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 14-28 shows.

Figure 14-28

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

Figure 14-29

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 14-30 defines length as an SPL procedure or SPL function, not as the built-in LENGTH function.

Figure 14-30

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 Universal 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, Universal 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 the Extending INFORMIX-Universal Server: Data Types manual.

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 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 14-31 illustrates how to use a subscript with a CHAR(15) variable.

Figure 14-31

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

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 following example, the SPL variable lname is the same as the column name.

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

Figure 14-32

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 14-33 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.

Figure 14-33

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

Procedure Names and SQL Functions

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

Declaring Global Variables

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.

Figure 14-34 shows two SPL functions that share a global variable:

Figure 14-34

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

Figure 14-35

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

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

Figure 14-36

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

Executing SPL routines is described in more detail in "Executing Routines".

Assigning Values to Variables

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 procedure 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 statement.

The LET 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 14-37 is a valid LET statement.

Figure 14-37

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

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

In Universal Server, you can 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 of Figure 14-23. Anytime you define a row-type variable, you must initialize the variable before you can use it. Figure 14-38 shows how you might define and initialize a row-type variable. You can use any row-type value to initialize the variable.

Figure 14-38

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

Once you define and initialize the row-type variable, you can write the LET statements that Figure 14-39 shows.

Figure 14-39

.
.
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 described in "Handling Row Types".

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 14-40, c is an opaque type variable and d holds the value that the external function circum() returns.

Figure 14-40

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 detailed syntax of the LET statement is described in the Informix Guide to SQL: Syntax.

Other Ways to Assign Values to Variables

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

Figure 14-41

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 14-42 to return the full name and address from the SPL function read_address into the specified SPL variables.

Figure 14-42

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;

Informix Guide to SQL: TutorialChapter 14: Creating and Using SPL Routines Home Contents Index Master Index New Book