Informix Guide to SQL: Tutorial Creating and Using SPL Routines

In an SPL routine, you can use named row types and unnamed row types as parameter definitions, arguments, variable definitions, and return values. For information about how to declare a row variable in SPL, see Declaring Row-Type Variables.

Figure 10-59 defines a row type salary_t and an emp_info table, which are the examples that this section uses.

CREATE ROW TYPE salary_t(base MONEY(9,2), bonus MONEY(9,2))


CREATE TABLE emp_info (emp_name VARCHAR(30), salary salary_t);

The emp_info table has columns for the employee name and salary information.

With Dynamic Server, a value that uses dot notation (as in proj.name) in an SQL statement in an SPL routine has one of three meanings, in the following order:

1. variable.field
2. column.field
3. table.column

In other words, the expression proj.name is first evaluated as variable.field. If the routine does not find a variable proj, it evaluates the expression as column.field. If the routine does not find a column proj, it evaluates the expression as table.column.

From within an SPL routine, you can use a row variable to update a row-type expression. Figure 10-60 shows an SPL procedure emp_raise that is used to update the emp_info table when an employee's base salary increases by a certain percentage.

CREATE PROCEDURE emp_raise( name VARCHAR(30),
			 		pct DECIMAL(3,2) )


	DEFINE row_var salary_t;


	SELECT salary INTO row_var FROM emp_info 
		WHERE emp_name = name;
	

	LET row_var.base = row_var.base * pct;
	

	UPDATE emp_info SET salary = row_var
		WHERE emp_name = name;
END PROCEDURE;

The SELECT statement selects a row from the salary column of emp_info table into the row variable row_var.

The emp_raise procedure uses SPL dot notation to directly access the base field of the variable row_var. In this case, the dot notation means variable.field. The emp_raise procedure recalculates the value of row_var.base as (row_var.base * pct). The procedure then updates the salary column of the emp_info table with the new row_var value.

Important: A row-type variable must be initialized as a row before its fields can be set or referenced. You can initialize a row-type variable with a SELECT...INTO statement or LET statement.