The mutator SET clause syntax permits you to write UPDATE requests for structured UDTs in a form of shorthand notation. Consider the following structured data type definitions:
CREATE TYPE person AS ( last_name VARCHAR(20), first_name VARCHAR(20), birthdate DATE) … ; CREATE TYPE school_record AS ( school_name VARCHAR(20), gpa FLOAT) INSTANTIABLE … ; CREATE TYPE college_record AS ( school school_record, major VARCHAR(20), minor VARCHAR(20)) INSTANTIABLE … ;
Suppose you have the following table definition:
CREATE TABLE student_record ( student_id INTEGER, student person, high_school school_record, college college_record);
Without the mutator SET clause notation, UPDATEs must be written in forms of chained or nested mutator method invocations. For example:
- The following UPDATE request changes student John Doe to Natsuki Tamura:
UPDATE student_record SET student = student.Last_name('Tamura').First_name('Natsuki') WHERE student.First_name() = 'John' AND student.Last_name() = 'Doe'; - The following UPDATE request updates the school name, GPA, and major of student Steven Smith:
UPDATE student_record SET college = college.school(college.school().school_name('UCLA') .GPA(3.20)).major('Computer Science') WHERE student.First_name() = 'Steven' AND student.Last_name() = 'Smith';
Formulating chained or nested mutator method invocations can be complex. However, you can use the mutator SET clause notation to make writing UPDATE requests for a structured type column simpler and more intuitive.
Consider these rewrites of the two previous UPDATE requests (these use the mutator SET clause notation):
- The following UPDATE request changes student John Doe to Natsuki Tamura:
UPDATE student_record SET student.Last_name = 'Tamura', student.First_name = 'Natsuki', WHERE student.First_name() = 'John' AND student.Last_name() = 'Doe';
- The following UPDATE request makes the following updates to the college record of student Steven Smith:
UPDATE student_record SET college.school.school_name = 'UCLA', college.school.GPA = 3.20, college.major = 'Computer Science' WHERE student.First_name() = 'Steven' AND student.Last_name() = 'Smith';