Inserting rows that contain structured type values

When you create a structured type, Db2® automatically generates a constructor method for the type, and generates mutator and observer methods for the attributes of the type. You can use these methods to create instances of structured types and to insert these instances into a column of a table.

Example

Assume that you want to add a row to the Employee typed table and that you want that row to contain an address. Just as with built-in data types, you can add this row using INSERT with the VALUES clause. However, when you specify the value to insert into the address, you must invoke the system-provided constructor function to create the value:
   INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept, Address)
      VALUES(Employee_t('m'), 'Marie', 35, 005, 55000, BusinessUnit_t(2),
      US_addr_t ( ) 1
         ..street('Bakely Avenue') 2
         ..number('555') 3
         ..city('San Jose') 4
         ..state('CA') 5
         ..zip('95141')); 6

The previous statement creates an instance of the US_addr_t type by performing the following tasks:

  1. The call to US_addr_t() invokes the constructor function for the US_addr_t type to create an instance of the type with all attributes set to null values.
  2. The call to ..street('Bakely Avenue') invokes the mutator method for the street attribute to set its value to Bakely Avenue.
  3. The call to ..number('555') invokes the mutator method for the number attribute to set its value to 555.
  4. The call to ..city('San Jose') invokes the mutator method for the city attribute to set its value to San Jose.
  5. The call to ..state('CA') invokes the mutator method for the state attribute to set its value to CA.
  6. The call to ..zip('95141') invokes the mutator method for the zip attribute to set its value to 95141.

Notice that although the type of the column Address in the Employee table is defined with type Address_t, the property of substitutability means that you can populate it with an instance of US_addr_t because US_addr_t is a subtype of Address_t.

To avoid having to explicitly call the mutator methods for each attribute of a structured type every time you create an instance of the type, consider defining your own SQL-bodied constructor function that initializes all of the attributes. The following example contains the declaration for an SQL-bodied constructor function for the US_addr_t type: 
   CREATE FUNCTION US_addr_t
         (street VARCHAR(30),
         number CHAR(15),
         city VARCHAR(30),
         state VARCHAR(20),
         zip CHAR(10))
      RETURNS US_addr_t
      LANGUAGE SQL
      RETURN US_addr_t()..street(street)..number(number)
          ..city(city)..state(state)..zip(zip);
The following example demonstrates how to create an instance of the US_addr_t type by calling the SQL-bodied constructor function from the previous example:
   INSERT INTO Employee(Oid, Name, Age, SerialNum, Salary, Dept, Address)
      VALUES(Employee_t('m'), 'Marie', 35, 005, 55000, BusinessUnit_t(2),
         US_addr_t('Bakely Avenue', CHAR('555'), 'San Jose', 'CA', CHAR('95141')));