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.
Related articles.
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 ); /
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; /
Now the object type is defined we can use it as a datatype in a table.
CREATE TABLE people ( id NUMBER(10) NOT NULL, person PersonObj );
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; /
Once the data is loaded it can be accessed using the dot notation.
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>
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/8i/object-types