Oracle Object Oriented Concepts

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object.

Class:

          It is a collection of attributes and functions (method) to plan the object.

Object Table :

·   Object table are created by using the user defined data types.

·      In an object table each row or record is treated as an object.

·      Each row in an object table has an object Identified (OID), which is unique through out       the   database.

·     The rows or objects of an object table can be referenced by other objects with in the  database.

·     An object table is created using the CREATE TABLE command.

·     All object types are associated with default method applied upon the relational tables , i.e    INSERT, DELETE, UPDATE and SELECT.

·   The relational DML operation style is accepted only when the user defined data type is a  collection of Build-in data types, and the object table doesn't contain any REF Constraints.

Creating an user defined Object type :-

Syntax

SQL> CREATE OR REPLACE TYPE

         

          Object (Element1 (size),

           (Element2 (size),

           (ElementN (size) );

             

•      All user defined data types are schema objects of the database.
•            The user defined object data type can be used as Reference in other tables.
•            All user defined data types and object are stored permanently in the Data dictionaries.
•            USER_TYPES
•            USER_OBJECTS
•            We can query for the uesr defined data types and objects using the relational SELECT.

  SQL> Select Type_Name, TypeCode,

          Attributes,Methods

          FROM USER_TYPES;

  SQL> Select Object_Name,

          Object_Type

          FROM USER_OBJECTS;

  Creating User Defined Address Type :-

  

  SQL> Create or Replace TYPE Addr_type AS OBJECT ( Hno VARCHAR2 (10),

                              Street VARCHAR2 (20), City VARCHAR2 (20), Pin NUMBER(6));

  SQL> Create or Replace TYPE PF_TYPE AS OBJECT ( PFNO NUMBER(6), AMT      NUMBER(12,2));

• The above statement create the user defined object data type called as Addr_type and      PF_TYPE in the data dictionary called USER_TYPES.
•            This data type is also called as collection in oracle, and this collection is reusable where ever   the same data type collection is expected in project development.

 Creating Table with user Defined Data Type :-

 SQL> CREATE TABLE EMPLOYEE ( ECODE NUMBER(3), NAME VARCHAR2(20),  ADDRESS ADDR_TYPE, BASIC NUMBER(12,2), PF PF_TYPE);

• Once the user defined data types are created we can instantiate them in the normal  relational tables.
•  These instances look as normal attributes with in the table, but can be operated only with  CONSTRUCTOR METHOD or OBJECT VIEWS.
•  In any of the operation we have to provide reference to all attributes with in the instance,  but partial association is not accepted.

SQL> Create table Supp_Det ( Rol number(3), Name varchar2(20),

          Saddr addr_type, COurse varchar2(20));

Inserting the data into EMPLOYEE Table :-

SQL> INSERT INTO EMPLOYEE VALUES (100, 'Ram', ADDR_TYPE ('130 A', 'CROSS LANE', 'HYBD', 500016), 8000, PF_TYPE(1200,400));

Select Data From OBJECT table:-

SQL> SELECT * FROM EMPLOYEE;

SQL> SELECT ADDRESS FROM EMPLOYEE

SQL> SELECT NAME, E.ADDRESS.CITY FROM EMPLOYEE E;

Note :- Alias is must to retrieve/manipulate Object elements.

Update data from Object Table:-

SQL> UPDATE EMPLOYEE E SET BASIC =BASIC+100 WHERE E.ADDRESS.CITY=’HYD’;

Deleting data from Object Table:-

SQL> DELETE FROM EMPLOYEE;

SQL> DELETE FROM EMPLOYEE E WHERE E.PF.PFNO=1200;

Creating User Defined Emp Det Type:

SQL> Create type info As Object (code Number(4), Name Varchar2(20), Hno Varchar2(10), Street Varchar2(20), City Varchar2(20), Pin Number(6));

Creating an Object Table:-

• Is a table which is entirely build from the abstract type.

Syntax

       Create Table (Table_Name) of (Object Name);

Inserting Rows into Object Table:-

•           To insert a record into an Object Table we may use the CONSTRUCTOR METHOD of the actual data type or directly implement the RELATIONAL INSERT statement.
•          The Normal; INSERT or RELATION INSERT is possible only when the table does not contain any nested data types.

Relation Insert:-

SQL> Insert into Emp_info Values(1001, ‘ram’, ‘12-123’, ‘AMEERPET’,’MGS’,’BLORE’,516217);

Inserting Using Construct Method:-

SQL> Insert into Emp_info values(info(1002,’suman’,’12-124’,’vijiinag’,’blore’,516217);

SQL> Create table supp_info of Info;