Oracle PL/SQL Object

 

Oracle PL/SQL Object Types

Attributes

Attributes are the column or field in which data are stored.

Each attribute will be mapped to the datatype that defines the processing and storage type for that attribute

Members/Methods

Members or Methods are subprograms that is defined in the object type.

They are not used to store any data. They are mainly used to define process inside the object type.

They are declared in the object type section and defined in the object type body section of the object type.

Create Object in Oracle

An Object type cannot be created at subprogram level, they can be created only at the schema level. The object type can be created using 'CREATE TYPE'.

 

CREATE TYPE<object_type_name> AS OBJECT

(

<attribute_l><datatype>,                                  -- Object

.

.

);

/

CREATE TYPE BODY<object_type_name> AS OBJECT              -- Object Body

(

MEMBER[PROCEDURE|FUNCTION]<member_name>                   -- Method

IS

<declarative section>

BEGIN

<execution part>

END;

.

.      

);

/

 

Object types are needed to be declared before using them in the program.

Once the object type is created it can be used in subprogram declarative section to declare a variable of that object type.

 Whenever any variable is declared in the subprogram as object type, at run-time a new instance of the object type will be created, and this newly created instance can be referred to the variable name. By this way, a single object type can store multiple values under different instances.

 

DECLARE

<variable_name> <object_type_name>; -- Variable is declared as Object Type

BEGIN

.

.

END;

 

Once the variable is declared as an object type in a subprogram, it will be atomically null i.e. the entire object itself a null. It needs to be initialized with values to use them in the program. They can be initialized using constructors.

Constructors are the implicit method of an object that can be referred with the same name as that of the object type.

DECLARE

<variable_name> <object_type_name>; 

BEGIN

<variable_name>:= <object_type_name>();--Initialized with NULL value 

                                          using constructor 

END;

/

 

Constructors

Constructors are the implicit method of an object that can be referred with the same name as that of the object type. Whenever the object is referred for the first time, this constructor will be called implicitly.

The constructor can be defined explicitly by defining the member in object type body with the same name of the object type.

We are going to execute it in below steps.

• Step1:
• Create Object type
• Object type body
• Step2: Creating an anonymous block to call created object type through implicit constructor for emp_no 1005.
• Step3: Creating an anonymous block to call created object type through explicit constructor for emp_no 1006.

Step 1) Create Object type and Object type body

CREATE TYPE emp_object AS OBJECT(

emp_no NUMBER,

emp_name VARCHAR2(50),

salary NUMBER,

manager NUMBER,

CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER, p_emp_name VARCHAR2,

p_salary NUMBER) RETURN SELF AS RESULT), -- Declaring explicit constructor

MEMBER PROCEDURE insert_records,

MEMBER PROCEDURE display_records);

/

 

n  Body creation:

 

CREATE OR REPLACE TYPE BODY emp_object AS

CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2,

p_salary NUMBER)         -- Constructor function to populate default

RETURN SELF AS RESULT

IS

BEGIN

Dbms_output.put_line(’Constructor fired..');

SELF.emp_no:=p_emp_no;

SELF.emp_name:=p_emp_name;

SELF.salary:=p_salary;

SELF.managerial:=1001;

RETURN;

END;

 

MEMBER PROCEDURE insert_records

IS      -- Member Procedure to insert the record

BEGIN

INSERT INTO emp VALUES(emp_no,emp_name,salary,manager);

END;

 

MEMBER PROCEDURE display_records

IS               -- -- Member Procedure to display the record

BEGIN

Dbms_output.put_line('Employee Name:'||emp_name);

Dbms_output.put_line('Employee Number:'||emp_no);

Dbms_output.put_line('Salary':'||salary);

Dbms_output.put_line('Manager:'||manager);

END;

END;

/

Step 2) Creating anonymous block to call created object type through implicit constructor for emp_no 1005

DECLARE

guru_emp_det emp_object;

BEGIN

guru_emp_det:=emp_object(1005,’RRR',20000,1000); -- Calling implicit constructor with

guru_emp_det.display_records;                      actual number of parameters

guru_emp_det.insert_records;

COMMIT;

END;

Output

Employee Name: RRR

Employee Number: 1005

Salary: 20000

Manager: 1000

 

Step 3) Creating anonymous block to call created object type through explicit constructor for emp_no 1006

 

DECLARE

guru_emp_det emp_object;

BEGIN

guru_emp_det:=emp_object(1006,'PPP',20000); -- Calling explicit constructor with 3

                                                  parameters

guru_emp_det.display_records;

guru_emp_det.insert_records;

COMMIT;

END;

/

 

Output

Employee Name:PPP

Employee Number:1006

Salary:20000

Manager:1001  -- By default

 

Inheritance in Object Type

Inheritance property allows the sub-object type to access all the attribute and members of the super object type or parent object type.

The sub-object type is called inherited object type, and the super object type is called parent object type

CREATE TYPE <object_type_name_parent> AS OBJECT  -- Super/Parent

(

<attribute_l><datatype>,

.

.

)NOT FINAL; -- It implies that this can have sub

/

 

CREATE TYPE<object_type_name_sub>UNDER<object_type_name_parent>  -- Sub

(                -- It implies that it is a sub

<attribute_l><datatype>,

.

);

/

 

CREATE TYPE emp_object AS OBJECT(

emp_no NUMBER,

emp_name VARCHAR2(50),

salary NUMBER,

manager NUMBER,

CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2(50),

p_salary NUMBER) RETURN SELF AS RESULT),

MEMBER PROCEDURE insert_records,

MEMBER PROCEDURE display_records) NOT FINAL;  -- Parent Type

/

Creating the 'emp_object' object type with 4 attributes and 3 members. It contains the definition of constructors with only 3 parameters. It has been declared as 'NOT FINAL' so it is parent type.

Create SUB type under SUPER type

CREATE OR REPLACE TYPE sub_emp_object

UNDER emp_object

(default_manager NUMBER, MEMBER PROCEDURE insert_default_mgr);

/ 

 

CREATE OR REPLACE TYPE BODY sub_emp_object -- Creating the body for the inherited object type

AS

MEMBER PROCEDURE insert_default_mgr

IS

BEGIN

INSERT INTO emp

VALUES(emp_no,emp_name,salary,manager);

END;

END;

/

Creating the sub_emp_object as inherited type with additional one attribute 'default_manager' and member procedure declaration.

Defining the member procedure which is inserting the records into "emp" table with the values from 'SUPER' object type, except for manager value. For manager value, it is using the 'default_manager' from 'SUB' type.

Creating anonymous block to call the SUB type

DECLARE

guru_emp_det sub_emp_object;

BEGIN

guru_emp_det:= sub_emp_object(1007,'RRR',20000,1000,1002);

-- Calling implicit constructor with values including attributes from super/parent   type.

--Initializing the object with the implicit constructor. The constructor is having 5 parameters (4 attributes from PARENT type and 2 attributes from SUB type). The last parameter (1002)defines the value for default_manager attribute

 

guru_emp_det.insert_default_mgr; -- Calling the member 'insert_default_mgr' to insert the records with the default manager id passed in the constructor

 

COMMIT;

END;

/