Part of the appeal of using Java is due to its classification as a Object Oriented (OO) language. Oracle has added object oriented programming features to the database and PL/SQL as well. The following demonstration shows how an object can be defined, constructed and accessed.
CREATE TYPE employee_typ IS OBJECT ( id NUMBER, first_name VARCHAR2(20), last_name VARCHAR2(25), hire_date DATE, salary NUMBER, MAP MEMBER FUNCTION get_id RETURN NUMBER, MEMBER FUNCTION to_string RETURN VARCHAR2, MEMBER FUNCTION days_service RETURN NUMBER, MEMBER FUNCTION months_service RETURN NUMBER ); /
By creating an object type a class is effectively defined. This class will have attributes as well as associated behaviors (subprograms). This object type is simply a specification. Like packages in PL/SQL, the implementation of the object is separated from its definition.
CREATE TYPE BODY employee_typ IS MAP MEMBER FUNCTION get_id RETURN NUMBER IS BEGIN RETURN id; END; MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR(id) || ' ' || first_name || ' ' || last_name||' ' || 'Salary: ' || to_char(salary, '$999,999,999.00'); END; MEMBER FUNCTION days_service RETURN NUMBER IS BEGIN RETURN trunc(sysdate - hire_date); END; MEMBER FUNCTION months_service RETURN NUMBER IS BEGIN RETURN months_between(sysdate, hire_date); END; END; /
The type body contains the actual implementation of the type previously defined.
CREATE TABLE employee_obj_table OF employee_typ;
The table created will hold specific instances of our class.
INSERT INTO employee_obj_table VALUES (employee_typ(101, 'Larry', 'Ellison', sysdate - 365, 30000) ); INSERT INTO employee_obj_table VALUES (employee_typ(102, 'Edward', 'Codd', sysdate - (2* 365), 30000) );
This script demonstrates the creation (or instantiation in OO terms) of two person objects.
SELECT p.last_name, p.days_service() FROM employee_obj_table p;
An object's attributes can be queries and functions called via SQL queries.
SELECT VALUE(p) FROM employee_obj_table p WHERE p.last_name = 'Ellison';
The VALUE function returns object instances corresponding to selected rows of the table.
drop type employee_typ force; drop table employee_obj_table;
When no longer needed, object tables and definitions can be removed using a drop statement. The force option may be required if there are any object dependencies.