Object Type Implementation

Creating Types:

Lets now explore the details of implementing object types in Oracle. Here is an example of creating the structure or “class” of an address object named ADD_TY (Robbert, 2002, p. 1):
CREATE TYPE addr_ty AS OBJECT
(street varchar2(60),
city varchar2(30),
state char(2),
zip varchar(9));

Once the object type addr_ty has been defined, it can be imbedded or “nested” within another object (Robbert, 2002, p. 1):

CREATE TYPE person_ty AS OBJECT
(name varchar2(25),
address addr_ty);


CREATE TYPE student_ty AS OBJECT
(student_id varchar2(9),
person person_ty);

The sample above shows how the address type can be imbedded within the person object type. Then the student object type is defined using the person object type as one of its columns. This sort of nesting can continue indefinitely.
Now that the student_ty object type has been defined it can be used in creating an object table like the following:
CREATE TABLE STUDENT
(full_student student_ty);

Since the STUDENT table is an object table one will use it in a much different way. One important difference of an object table is that each individual row in the object table will have an object identifier value (OID). Another important difference is that each row can be referenced as an object. Once the table has been created, records can be inserted:
INSERT INTO STUDENT VALUES
(student_ty('100',
person_ty('John Q. Student',
addr_ty('1000 Chastain Rd.',
'Kennesaw', 'GA', '30144'))));
To extract data, the following query can be entered:
SELECT s.full_student.student_id ID, s.full_student.person.name NAME, s.full_student.person.address.street STREET
FROM student s
WHERE s.full_student.student_id = 100

Giving the results:
ID NAME STREET
--------- ------------------------- -----------------
100 John Q. Student 1000 Chastain Rd.

All this is done without having to do any type of relation joins. Updating and deleting is similar to what one would do in the relational model:
UPDATE STUDENT s
SET s.full_student.person.name = 'JOHN NEWNAME'
WHERE s.full_student.student_id = 100;

1 row updated.


DELETE FROM STUDENT s
WHERE s.full_student.student_id = 100;

1 row deleted.