Informix Guide to SQL: Tutorial

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

Home Contents Index Master Index New Book

Handling Row Types

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 14-94 defines a row type salary_t and an emp_info table, which are the examples that this section uses.

Figure 14-94
The salary_t Row Type and emp_info Table

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.

Updating a Row-Type Column

From within an SPL routine, you can use a row variable to update a row type or specific fields of a row type. Figure 14-95 shows an SPL procedure that increases the base salary of an employee. The procedure retrieves data stored in the form of a row type and updates salary column of the emp_info table.

Figure 14-95

CREATE PROCEDURE 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;
END PROCEDURE;

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

The procedure of Figure 14-95 uses SPL dot notation to directly access the base and bonus fields of the variable row_var. In this case, the dot notation means variable.field. The procedure recalculates the value of row_var.base as
(row.var * pct) and the new value is used to update the salary column of the emp_info table.

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

Precedence of Dot Notation

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

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

Handling Row Types

Updating a Row-Type Column

Precedence of Dot Notation

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

Home Contents Index Master Index New Book