Slide 12.3: Creating object types

Creating Object Types

Four categories of user-defined types are (i) simple, (ii) composite, (iii) cyclic, and (iv) collection.

Declaring a Simple Type
The example below declares a simple type because it uses only attributes based on built-in types:

CHAR: fixed-length character string
VARCHAR or VARCHAR2: variable-length character string
INTEGER: integer number
NUMBER: both fixed and floating point number such as “amount NUMBER(5,2)” of which NUMBER(precision,scale): the precision is the total number of digits and the scale dictates the number of digits to the right or left of the decimal point at which rounding occurs.
REAL: real number
DATE: including information about the date, time, and time zone

 CREATE TYPE  person_typ  AS OBJECT (
   name  VARCHAR(64),
   SSN   INTEGER,
   DOB   DATE );

Creating a Composite User-Defined Type
Once you create a type, you can use it to create another user-defined type. The composite user-defined type has at least one attribute of a user-defined type. This type must have been created previously.

 CREATE TYPE  sales_rep_typ  AS OBJECT (
   pers_info   person_typ,
   territory   NUMBER(5) );

The composite type is dependent on the included type. In order to modify the person_typ, you must drop the sales_rep_typ type first.