Object Oriented Concepts in SQL

Object Table

1. Object Table created by User Define Data type.
2. Each row of the Object Table has an object identifier(OID) , which is unique through out the database.
3. Object Table automatically inherit the data type from user define data type. 
4. we can perform SELECT,INSERT,UPDATE and DELETE operation in Object Table.If Object table having REF constraint then DML operation is not possible.

User Define Data Tyepe

1. User Define Data Type is schema object of database .
2. Data Dictionaries  USER_TYPE/USER_OBJECTS.

Follow the Below Steps 

1. Creating User Define Data Type

CREATE OR REPLACE TYPE DEPT_DETAILS AS OBJECT(DEPTNO NUMBER,DNAME VARCHAR2(400),LOC VARCHAR2(400),LOC_CODE VARCHAR2(400));

2. Creating Object Table

CREATE TABLE EMP_DEPT_DETAILS OF  DEPT_DETAILS ;

Now table is ready for SELECT,INSERT,UPDATE and DELETE operation.

Inserting Data
INSERT INTO EMP_DEPT_DETAILS VALUES (10,'DNAME1','KOLKATA','KOL-001');
INSERT INTO EMP_DEPT_DETAILS VALUES (11,'DNAME2','BANGALORE','BAN-001');
INSERT INTO EMP_DEPT_DETAILS VALUES (12,'DNAME3','HYDERABAD','HYD-001');
INSERT INTO EMP_DEPT_DETAILS VALUES (13,'DNAME4','MUMBAI','MUM-001');

Updating Data
UPDATE EMP_DEPT_DETAILS SET DNAME ='HR' WHERE DEPTNO =10;

Deleting Data
DELETE FROM EMP_DEPT_DETAILS WHERE DEPTNO =10;

Selecting Data
SELECT * FROM EMP_DEPT_DETAILS ;
SELECT REF(A) FROM EMP_DEPT_DETAILS A WHERE LOC='BANGALORE';

Now see the use of REF

Create Table
CREATE TABLE EMPLOYEE_DETAILS (EMP_NAME VARCHAR2(50),EMPID NUMBER,DEPERT_DTS REF DEPT_DETAILS );

Insert Data into EMPLOYEE_DETAILS Table
INSERT INTO EMPLOYEE_DETAILS SELECT 'RABINDRA',2315,REF(A) FROM EMP_DEPT_DETAILS A WHERE LOC='BANGALORE';
INSERT INTO EMPLOYEE_DETAILS SELECT 'NANDAN',2316,REF(A) FROM EMP_DEPT_DETAILS A WHERE LOC='KOLKATA';

Select Data from EMPLOYEE_DETAILS Table
SELECT * FROM EMPLOYEE_DETAILS;
SELECT EMP_NAME,EMPID,DEREF(DEPERT_DTS) FROM EMPLOYEE_DETAILS;