Assigning row values to row variables using SELECT, VALUES, or FETCH statements

A row value can be assigned to a variable of type row by using a SELECT INTO statement, a VALUES INTO statement, or a FETCH INTO statement. The field values of the source row value must be assignable to the field values of the target row variable.

The following is an example of how to assign a single row value to a row variable named empRow using a SELECT statement:

SELECT * FROM employee
INTO empRow
WHERE id=5;

If the select query resolves to more than one row value, an error is raised.

The following is an example of how to assign a row value to a row variable named empEmpBasics using a VALUES INTO statement:

VALUES (5, 'Jane Doe', 10000) INTO empBasics;

The following is an example of how to assign a row value to a row variable named empRow using a FETCH statement that references a cursor named cur1 that defines a row with compatible field values as the variable empRow:

FETCH cur1 INTO empRow;

Other variations of use are possible with each of these statements.