Figure 2-52 shows an example of retrieval from a table that has abstract data types. The example retrieves staff numbers of employees who have worked for at least 20 years in the company. The SQL statement for retrieving the data can be specified as follows:

SELECT STAFF_NUMBER
    FROM STAFF_TABLE
    WHERE YearsOfService(EMPLOYEE)>=20

This example uses the user-defined function YearsOfService to retrieve staff numbers of employees whose years of service are 20 years or longer. The argument for the user-defined function YearsOfService is EMPLOYEE.

Figure 2-52 Example of retrieval from a table with abstract data types

Figure 2-53 shows an example of updating a table that has abstract data types. This example updates the post of the employee with staff number 900123 to MANAGER. The SQL statement for updating the table can be specified as follows:

UPDATE STAFF_TABLE
    SET EMPLOYEE..POST='MANAGER'
    WHERE STAFF_NUMBER='900123'

In this example, the POST attribute in the EMPLOYEE column is updated to MANAGER for the employee whose staff number is 900123. A component specification is used for specifying the attribute of the abstract data type. In this example, EMPLOYEE..POST is the component specification.

Figure 2-53 Example of updating a table with abstract data types

Figure 2-54 shows an example of row deletion from a table that has abstract data types. The example deletes the rows for employees whose POST is CLERK. The SQL statement for deleting the rows can be specified as follows:

DELETE FROM STAFF_TABLE
   WHERE EMPLOYEE..POST='CLERK'

This example deletes the rows of employees whose POST attribute in the EMPLOYEE column is CLERK. A component specification is used to specify the abstract data type attribute. In this example, the component specification is EMPLOYEE..POST.

Figure 2-54 Example of deleting rows from a table with abstract data types

Figure 2-55 shows an example of row insertion into a table that has abstract data types. This example inserts a row into a staff table. The SQL statement for inserting the row can be specified as follows:

INSERT INTO STAFF_TABLE
    VALUES ('950070',t_EMPLOYEE('STONE, JANE,
                                'F'
                                'CLERK'
                                '1995-04-01'
                                :PHOTOGRAPH AS BLOB,
                                1400.00
                                )
            )

In this example, the t_EMPLOYEE constructor function, which was defined when the abstract data type was defined, is used to insert the row for staff number 950070 into the staff table.

:PHOTOGRAPH is a BLOB-type embedded variable in which a photographic image of the employee's face is set.

Figure 2-55 Example of inserting rows into a table with abstract data types