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

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

Suppose you define the named row types zip_t and address_t, as Figure 383 shows. Anytime you define a row-type variable, you must initialize the variable before you can use it. Figure 397 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 398 shows.

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

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 399, 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 IBM Informix: Guide to SQL Syntax describes in detail the syntax of the LET statement.

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 400 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 401 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;