Object Types
Oracle implements Objects through the use of TYPEs, defined in a similar way to packages. Unlike packages where the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes/properties, defined in the same way as package variables, and member functions/procedures. If the a type contains member functions/procedures, the procedural work for these elements is defined in the TYPE BODY.
- Define A TYPE
- Define A TYPE BODY
- Defining A Table
- Constructors
- Data Access
- Issues
Define A TYPE
First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).
CREATE OR REPLACE TYPE PersonObj AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE,
MEMBER FUNCTION getAge RETURN NUMBER
);
/
First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).
CREATE OR REPLACE TYPE PersonObj AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE,
MEMBER FUNCTION getAge RETURN NUMBER
);
/
Define A TYPE BODY
Next we define a TYPE BODY to add functionality to the getAge member function.
CREATE OR REPLACE TYPE BODY PersonObj AS
MEMBER FUNCTION getAge RETURN NUMBER AS
BEGIN
RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
END getAge;
END;
/
Next we define a TYPE BODY to add functionality to the getAge member function.
CREATE OR REPLACE TYPE BODY PersonObj AS
MEMBER FUNCTION getAge RETURN NUMBER AS
BEGIN
RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
END getAge;
END;
/
Define A TYPE
First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).
CREATE OR REPLACE TYPE PersonObj AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE,
MEMBER FUNCTION getAge RETURN NUMBER
);
/
First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).
CREATE OR REPLACE TYPE PersonObj AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE,
MEMBER FUNCTION getAge RETURN NUMBER
);
/
Define A TYPE BODY
Defining A Table
Now the object type is defined we can use it as a datatype in a table.
Now the object type is defined we can use it as a datatype in a table.
Constructors
To insert data into the PEOPLE table we must use the PersonObj() constructor. This can be done as part of a regular DML statement, or using PL/SQL.
INSERT INTO people
VALUES (1, PersonObj('John','Doe',
TO_DATE('01/01/1999','DD/MM/YYYY')));
COMMIT;
DECLARE
v_person PersonObj;
BEGIN
v_person := PersonObj('Jane','Doe',
TO_DATE('01/01/1999','DD/MM/YYYY'));
INSERT INTO people VALUES (2, v_person);
COMMIT;
END;
/
To insert data into the PEOPLE table we must use the PersonObj() constructor. This can be done as part of a regular DML statement, or using PL/SQL.
INSERT INTO people
VALUES (1, PersonObj('John','Doe',
TO_DATE('01/01/1999','DD/MM/YYYY')));
COMMIT;
DECLARE
v_person PersonObj;
BEGIN
v_person := PersonObj('Jane','Doe',
TO_DATE('01/01/1999','DD/MM/YYYY'));
INSERT INTO people VALUES (2, v_person);
COMMIT;
END;
/
Data Access
Once the data is loaded it can be accessed using the dot notation.
- alias.column.attribute
- alias.column.function()
SELECT p.id,
p.person.first_name,
p.person.getAge() age
FROM people p;
ID PERSON.FIRST_NAME AGE
---------- --------------------------- ----------
1 John 2
2 Jane 2
2 row selected.
SQL>
