Update a row-type expression

From within an SPL routine, you can use a ROW variable to update a row-type expression. The following figure 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.

Figure 1. SPL procedure used to update the emp_info table.

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.