Use of object tables in Oracle, ref and deref object tables

In addition to relational databases, Oracle integrates object-oriented elements, such as the ability to create types and inherit between types, type can include constructors, sorting functions, various member functions, stored procedures, and so on.

An object table indicates that a row of the table is an object with an OID (object ID). The object table does not have a primary/foreign key Association. To reflect this relationship, the ref object is used in oracle.

In the following example, an address type is created. A person has an address attribute. Therefore, a ref address is set in the personnel type to determine the pointer to the address.

-- Create an address type

Create type address as object (
Street varchar2 (35 ),
City varchar2 (15 ),
State char (2 ),
Zip_code integer
);

 

Create table addresses of address; -- create an address object table

 

-- Create personnel type
Create type person as object (
First_name varchar2 (15 ),
Last_name varchar2 (15 ),
Birthday date,
Home_address ref address, -- point to the corresponding address, which should be a row in another object table
Phone_number varchar2 (15)
);

 

Create table persons of person; -- CREATE a person object TABLE

 

-- Insert an address

Insert into addresses values (address ('nanhai ', 'shenzhen', 'gd', '123 '));

Insert into addresses values (address ('shennan ', 'shenzhen', 'gd', '123 '));

-- Insert a member. note how a ref address is inserted in the home_address section.
Insert into persons values (person ('shitou ', 'hahaha', to_date ('2017-07-05', 'yyyy-mm-dd '),
(Select ref (a) from addresses a where street = 'nanhai '),
'123 '));

-- You can also use the following process to insert a employee record.

Declare
Addref ref address;
Begin
Select ref (a) into addref from addresses a where street = 'nanhai ';
Insert into persons
Values (person ('shitou ', 'hahaha', to_date ('2017-07-05', 'yyyy-mm-dd '),
Addref, '000000 '));
Commit;
End;

-- Query the address information of a person
Select first_name, deref (home_address) from persons;

-- Modify the address
Update persons set home_address = (select ref (a) from addresses a where street = 'shennan ');

-- Delete a person

Delete from persons where first_name = 'shitou ';

-- Delete the records of related personnel for an address
Delete from persons where home_address = (select ref (a) from addresses a where street = 'nanhai ');