Oracle Object Features

Contact Us or call 1-877-932-8228
Oracle Object Features

Oracle Object Features

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.

Code Sample:

Specialized-Topics/Demos/1_create_object_type.sql
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
);
/

Code Explanation

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.

Code Sample:

Specialized-Topics/Demos/2_create_object_type_body.sql
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;
/

Code Explanation

The type body contains the actual implementation of the type previously defined.

Code Sample:

Specialized-Topics/Demos/3_create_table.sql
CREATE TABLE 
  employee_obj_table OF employee_typ;
  

Code Explanation

The table created will hold specific instances of our class.

Code Sample:

Specialized-Topics/Demos/4_object_inserts.sql
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) );

Code Explanation

This script demonstrates the creation (or instantiation in OO terms) of two person objects.

Code Sample:

Specialized-Topics/Demos/5_object_query.sql
SELECT p.last_name, p.days_service()
FROM employee_obj_table p;

Code Explanation

An object's attributes can be queries and functions called via SQL queries.

Code Sample:

Specialized-Topics/Demos/6_value_query.sql
SELECT VALUE(p)
FROM employee_obj_table p
WHERE p.last_name = 'Ellison';

Code Explanation

The VALUE function returns object instances corresponding to selected rows of the table.

Code Sample:

Specialized-Topics/Demos/7_drop_objects.sql
drop type employee_typ  force;

drop table employee_obj_table;

Code Explanation

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.

Next