Oracle in a Nutshell

Using Records

Individual fields of records are referenced via dot notation:

                  record_name.field_name

Individual fields within a record can be read from or written to. They can appear on either the left or right side of the assignment operator. An entire record can be assigned to another record of the same type, but one record cannot be compared to another record via Boolean operators. For example, the following is a valid assignment:

shipto_address_rec := customer_address_rec

But the next example is not a valid comparison (you must compare the individual fields of the record instead):

IF shipto_address_rec = customer_address_rec
THEN
...
END IF;

Values can be assigned to records or to the fields within a record in four different ways:

• You can use the assignment operator to assign a value to a field:

  new_emp_rec.hire_date := SYSDATE;

• You can SELECT INTO a whole record or the individual fields:

  SELECT emp_id,dept,title,hire_date,college_recruit
    INTO new_emp_rec
    FROM emp
   WHERE surname = 'LI'

• You can FETCH INTO a whole record or the individual fields:

  FETCH emp_cur INTO new_emp_rec;
  FETCH emp_cur INTO new_emp_rec.emp_id, 
     new_emp_rec.name;

• You can assign all of the fields of one record variable to another record variable of the same type:

  IF rehire THEN
   new_emp_rec := former_emp_rec;
  ENDIF;

  This aggregate assignment technique works only for records declared with the same TYPE statement.