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.
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:
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
)
Following is an example of defining the subtype t_OPERATOR with the supertype being the t_EMPLOYEE abstract data type:
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
)
If values are specified with the INSERT data manipulation SQL, the values for the entire abstract data type are set to null.
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 Figure 7-3.
Figure 7-3 Example of table containing abstract data type using substitutability
See (5) as follows for the subtypes of the abstract data type that cannot be deleted.