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;
/
