6.5.1 Defining an abstract data type

The user can use an abstract data type and routines to define and use any desired data type with a complicated structure and a desired data manipulation method.

Organization of this subsection
(1) Definition method
(2) Definition method using inheritance
(3) Null value for the abstract data type
(4) Procedure for deleting the subtype of an abstract data type
(5) Notes

(1) Definition method

The CREATE TYPE definition SQL is used to define a data type with a desired structure (an abstract data type). CREATE TYPE defines a data structure and a data manipulation method. This section explains how to define the abstract data type t_EMPLOYEE with the data structure shown below and then define a data manipulation method as a function:

Data structure
The data consists of NAME, SEX, POSITION, EMPLOYMENT_DATE, ID_PHOTO, and SALARY.
Data manipulation
  • Calculate SERVICE_YEARS from the current date and EMPLOYMENT_DATE.
  • Calculate BONUS_FACTOR according to SERVICE_YEARS.
  • Calculate the employee's bonus by multiplying SALARY times BONUS_FACTOR.
Example

CREATE TYPE t_EMPLOYEE (                           1.
 PUBLIC    NAME     NCHAR(16),
           SEX       CHAR(1),
           POSITION NCHAR(10),
 PRIVATE   EMPLOYMENT_DATE date,                  2.
 PUBLIC    ID_PHOTO BLOB(64K),
 PROTECTED SALARY   INTEGER,                      3.

 PUBLIC FUNCTION t_EMPLOYEE (p_NAME NCHAR(16),    4.
               p_SEX CHAR(1),
               p_POSITION NCHAR(10),
               p_EMPLOYMENT_DATE date,
               p_ID_PHOTO BLOB(64K),
               p_SALARY INTEGER)
        RETURNS t_EMPLOYEE
     BEGIN
        DECLARE d_EMPLOYEE t_EMPLOYEE;            5.
        SET d_EMPLOYEE=t_EMPLOYEE ();             6.
        SET d_EMPLOYEE..NAME=p_NAME;              7.
        SET d_EMPLOYEE..SEX=p_SEX;                7.
        SET d_EMPLOYEE..POSITION=p_POSITION;      7.
        SET d_EMPLOYEE..EMPLOYMENT_DATE
            =p_EMPLOYMENT_DATE;                   7.
        SET d_EMPLOYEE..ID_PHOTO =p_ID_PHOTO;     7.
        SET d_EMPLOYEE..SALARY=p_SALARY;          7.
        RETURN d_EMPLOYEE;                        8.
     END,

PUBLIC FUNCTION SERVICE_YEARS (p t_EMPLOYEE)
RETURNS INTEGER                                    9.
   BEGIN
      DECLARE working_years INTERVAL YEAR TO DAY;
      SET working_years=CURRENT_DATE - p.. EMPLOYMENT_DATE;
      RETURN YEAR(working_years);
   END,

PROTECTED FUNCTION BONUS_FACTOR (p t_EMPLOYEE)
RETURNS FLOAT                                     10.
   BEGIN
      DECLARE rate FLOAT;
     SET rate=SERVICE_YEARS (p)*0.2/30;
     RETURN rate;
  END,

   PUBLIC FUNCTION BONUS (p t_EMPLOYEE)
   RETURNS INTEGER                               11.
       BEGIN
          DECLARE bonus INTEGER;
          SET bonus=p..SALARY*BONUS_FACTOR (p);
          RETURN bonus;
       END
 )

  1. Defines the data structure. This example defines abstract data type t_employee.
  2. Attribute EMPLOYMENT_DATE of the t_EMPLOYEE type is used to access the bonus. Encapsulation level PRIVATE is specified for this attribute, because there is no need to reference or modify it directly from the outside. For details about the encapsulation level, see 12.17 Table containing an abstract data type.
  3. Attribute SALARY of the t_EMPLOYEE type is used to calculate the bonus. This attribute also need not be referenced or modified directly from the outside. However, encapsulation level PROTECTED is specified for this attribute because its subtype is commonly referenced. For details about the encapsulation level, see 12.17 Table containing an abstract data type.
  4. Defines a user-defined constructor function.
  5. Generates a value (instance) and declares an SQL variable to be used as the function's return value with the t_EMPLOYEE type.
  6. Uses the system-provided default constructor function to generate a value (instance) whose attributes are all NULL. The default constructor function has the same name as the t_EMPLOYEE type with no argument
  7. For the value specified in item 6 above, assign the value of each attribute using an assignment statement specifying the component. The assignment statement can be used to set the value obtained from the constructor function's argument or to set the data processed using that value.
  8. The RETURN statement returns a newly generated value (instance). The data type of the return value must be t_EMPLOYEE, because the constructor function has the same name as the abstract data type and the type is determined by the RETURNS clause.
  9. This is a data manipulation function. It returns the employee's SERVICE_YEARS. This value is calculated from the current date and EMPLOYMENT_DATE. This function accesses the EMPLOYMENT_DATE attribute for which PRIVATE is specified as the encapsulation level.
  10. This is another data manipulation function. It returns the employee's BONUS_FACTOR. SERVICE_YEARS is used to calculate this value.
  11. This is another data manipulation function. It returns the employee's BONUS. This value depends on SERVICE_YEARS and is obtained by multiplying SALARY by BONUS_FACTOR.

(2) Definition method using inheritance

Following is an example of defining the subtype t_OPERATOR with the supertype being the t_EMPLOYEE abstract data type:

Example

CREATE TYPE
CREATE TYPE t_OPERATOR UNDER t_EMPLOYEE
( PUBLIC CHARGE_CLIENT NCHAR(15),
  PUBLIC FUNCTION BONUS (p t_OPERATOR) RETURNS INTEGER
   BEGIN
     DECLARE salebonus INTEGER;
     SET salebonus=TOTAL_CLIENTS (...)*1000+P..SALARY*BONUS (p);
     RETURN salebonus;
   END
 )

(3) Null value for the abstract data type

If values are specified with the INSERT data manipulation SQL, the values for the entire abstract data type are set to null.

(4) Procedure for deleting the subtype of an abstract data type

If an abstract data type is not specified directly in the table definition, but its parent abstract data type (supertype) is specified as a column type, then the value of the abstract data type (subtype) may have been stored in the table due to substitutability. Care must be taken when an abstract data type (subtype) is deleted.

The procedure for deleting a subtype is described as follows, based on a table containing an abstract data type using substitutability, as shown in the following figure.

Figure 6-7 Example of table containing abstract data type using substitutability

[Figure]

  1. Delete STAFF_TABLE.
  2. Delete subtype t_OPERATOR of t_EMPLOYEE.
  3. Delete t_EMPLOYEE.
  4. Delete subtype t_A_COMPANY_STAFF of t_A_COMPANY_STAFF.
  5. Delete t_A_COMPANY_STAFF.

See (5) as follows for the subtypes of the abstract data type that cannot be deleted.

(5) Notes

  1. If a constructor function is used to generate values, the abstract data type as a whole is not null, even if the value of each attribute constituting the abstract data type is null.
  2. If an abstract data type and its supertype are defined in a table, the abstract data type's subtypes cannot be deleted.
  3. If an abstract data type and its supertype are specified as attributes of another abstract data type, the abstract data type's subtypes cannot be deleted.
  4. When a subtype is defined and the parent of the data type being created is one of the following, the corresponding stored procedure and stored function become invalid:
    • Data type specified in the SQL parameter of the stored procedure and stored function.
    • Data type of the function's return value.
    • Data type of the argument and return value of the function that is invoked from the stored procedure and stored function.
    • Data type specified in the stored procedure and stored function (including any intermediate data type if the abstract data type is accessed with a component specified).