Object Types
An object type is a kind of datatype. You can use it in the same ways that you use more familiar datatypes such as NUMBER or VARCHAR2. For example, you can specify an object type as the datatype of a column in a relational table, and you can declare variables of an object type. You use a variable of an object type to contain a value of that object type. A value of an object type is an instance of that type. An object instance is also called an object. Example below shows how to create a user-defined object type named emp_typ.
Example 1-1 Creating the emp_typ Object
CREATE TYPE emp_typ AS OBJECT (
eno NUMBER,
ename VARCHAR2(30),
cont_no VARCHAR2(20),
MAP MEMBER FUNCTION get_eno RETURN NUMBER );
/
CREATE TYPE BODY emp_typ AS
MAP MEMBER FUNCTION get_eno RETURN NUMBER IS
BEGIN
RETURN eno;
END;
END;
/
Object types are composed of parts, called attributes and methods.
1. Attributes hold the data about an object's features of interest. For example, a student object type might have name, major, and graduation date attributes. An attribute has a declared datatype that can in turn be another object type. Taken together, the attributes of an object instance contain that object's data.
2. Methods are procedures or functions provided to enable applications to perform useful operations on the attributes of the object type. Methods are an optional element of an object type. They define the behavior of objects of that type and determine what (if anything) that type of object can do.
3. Object types are less generic than native datatypes. In fact, this is one of their major virtues. You can define object types to model the actual structure of the real-world entities, such as customers and purchase orders, that application programs deal with. This can make it easier and more intuitive to manage the data for these entities. In this respect object types are like Java and C++ classes.
An object type can be thought of as a blueprint or template that defines structure and behavior. An instantiation of the object type creates an object built according to the template. Object types are database schema objects, subject to the same kinds of administrative control as other schema objects.
Object types enable us to capture the structural interrelationships of objects and their attributes instead of flattening the structure into a two-dimensional, purely relational schema of tables and columns. With object types we can store related pieces of data in a unit along with the behaviors defined for that data. Application code can then retrieve and manipulate these units as objects
Click here for more help on Managing Oracle Object.
Objects
When we create a variable of an object type, we create an instance of the type and the result is an object. An object has the attributes and methods defined for its type. Because an object instance is a concrete thing, we can assign values to its attributes and call its methods.
Above we have defined object type emp_typ.
The indented elements eno, ename, and cont_no in the CREATE TYPE statements are attributes. Each has a datatype declared for it.
Defining an object type does not allocate any storage. After they are defined, object types can be used in SQL statements in most of the same places we use types like NUMBER or VARCHAR2.
For example, we define a relational table to keep track of our contacts:
CREATE TABLE emp_contact (
contact emp_typ,
contact_date DATE );
INSERT INTO emp_contact VALUES (
emp_typ (7369, ‘SMITH’, '91-020-27292427'), '09-june-2008' );
The emp_contact table is a relational table with an object type as the datatype of one of its columns. Objects that occupy columns of relational tables are called column objects.
Objects that are stored in complete rows in object tables are called row objects. Objects that are stored as columns of a table in a larger row, or are attributes of other objects, are called column objects.
Object Methods
Methods are functions or procedures that are declared in an object type definition to implement behavior that objects of that type want to perform. For example, a method is declared in above to allow comparisons between emp_typ objects.
The general kinds of methods that can be declared in a type definition are:
· Member
· Static
· Constructor
A principal use of methods is to provide access to the data of an object. Methods are defined for operations that an application is likely to want to perform on the data so that the application does not have to code these operations itself. To perform the operation, an application calls the appropriate method on the appropriate object.
For example, the following SQL statement uses the get_eno() method to display the employee number of employees in the emp_contact table:
SELECT e_c.contact.get_eno() FROM emp_contact e_c;
Static methods are defined to compare object instances and to perform operations that do not use any particular object's data but instead are global to an object type.
A constructor method is implicitly defined for every object type, unless this default constructor is over-written with a user-defined constructor. A constructor method is called on a type to construct or create an object instance of the type.
Object Tables
An object table is a special kind of table in which each row represents an object. For example, the following statement creates an object table for emp_typ objects:
CREATE TABLE emp_obj_table OF emp_typ;
This table can be viewed in two ways:
1. As a single-column table in which each row is a emp_typ object, allowing to perform object-oriented operations
2. As a multi-column table in which each attribute of the object type emp_typ; such as eno, ename, and cont_no ; occupies a column, allowing you to perform relational operations
For example, you can execute the following instructions:
INSERT INTO emp_obj_table VALUES (
7499, ALLEN', '91-20-27292427');
SELECT VALUE(p) FROM emp_obj_table p
WHERE p.name = ‘SMITH’;
The first statement inserts a emp_typ object into emp_obj_table, treating emp_table as a multi-column table. The second selects from emp_obj_table as a single-column table, using the VALUE function to return rows as object instances.
The VALUE function takes as its argument a correlation variable (table alias) for an object table or object view and returns object instances corresponding to rows of the table or view. The VALUE function may return instances of the declared type of the row or any of its subtypes. For example, the below query returns all employees, from table emp_obj_table of emp_typ:
SELECT VALUE(p) FROM emp_obj_table p;
To retrieve only part time students, that is, instances whose most specific type is account_typ, use the ONLY keyword to confine the selection:
SELECT VALUE(p) FROM emp_obj_table p
WHERE VALUE(p) IS OF (ONLY account_typ);
The following example shows VALUE used to return object instance rows for updating:
UPDATE TABLE(SELECT d.dept FROM dept d
WHERE d.deptno = 10) p
SET VALUE(p) = emp_typ(7782, ‘CLARK’, '91-20-27292428')
WHERE p.eno = 7782;
By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. In a distributed and replicated environment, the system-generated unique identifier lets Oracle identify objects unambiguously.
References
A REF is a logical pointer to a row object that is constructed from the object identifier (OID) of the referenced object and is an Oracle built-in datatype. REFs and collections of REFs model associations among objects, particularly many-to-one relationships, thus reducing the need for foreign keys. REFs provide an easy mechanism for navigating between objects. The dot notation is used to follow the pointers. Oracle does joins when needed, and in some cases can avoid doing joins.
A REF can be used to examine or update the object it refers to. It can also be used to obtain the object it refers to. We can change a REF so that it points to a different object of the same object type hierarchy or assign it a null value.
The following example illustrates a simple use of a REF.
Example 1-2 Using a REF to an Object
CREATE TYPE mgr_emp_typ AS OBJECT (
name VARCHAR2(30),
mgr REF emp_typ );
/
CREATE TABLE mgr_emp_obj_table OF mgr_emp_typ;
INSERT INTO mgr_emp_obj_table VALUES (
mgr_emp_typ ('SMITH', NULL));
INSERT INTO mgr_emp_obj_table
SELECT mgr_emp_typ ('JONES, REF(e)) FROM mgr_emp_obj_table e
WHERE e.name = 'SMITH';
Collections
For modeling multi-valued attributes and many to many relationships, Oracle supports two collection datatypes: varrays and nested tables. Collection types can be used anywhere other datatypes can be used. You can have object attributes of a collection type in addition to columns of a collection type. For example, a purchase order object type might be given a nested table attribute to hold the collection of line items for a given purchase order.
The CREATE TYPE statement is used to define collection types. In below example, the CREATE TYPE statements define the object types emp_type and dept_emp_typ.
Example 1-3 Creating a Collection Datatype
CREATE TYPE mgr_typ AS TABLE OF emp_typ;
/
CREATE TYPE dept_emp_type AS OBJECT (
dept_no CHAR(5),
dept_name CHAR(20),
dept_mgr mgr_typ,
dept_emps mgr_typ);
/
In this simplified example, mgr_typ is a collection type, specifically a nested table type. The dept_emp_type object type has an attribute mgr_typ of this type. Each row in the mgr_typ nested table is an object of type emp_typ defined earlier.
There are several kinds of user-defined types (UDTs). Some for each type and its constraints are shown below:
- Column types: These have no multiple column types but are valid only for single column. The code for a single column is shown below:
CREATE OR REPLACE TYPE NAME_T AS OBJECT (
COL VARCHAR2 (30))
But the problem with column type is that the basic datatype is only valid for a column and if chained records are stored in more than one column then this type cannot be used.
- Multi-Column:
CREATE OR REPLACE TYPE ADDR_T AS OBJECT (
ADDR1 VARCHAR2 (50),
ADDR2 VARCHAR2 (50),
CITY VARCHAR2 (30),
STATE VARCHAR2 (2),
ZIP_4 VARCHAR2(9));
Here each column can have different type but it is not applicable to individual fields.
- Row Types: These include single and multiple rows and form the foundation of object tables/views:
CREATE OR REPLACE TYPE EMP_T AS OBJECT (
EMP_ID NUMBER (10),
LNAME_TX NAME_T,
FNAME_TX NAME_T,
BIRTH_DATE DATE);
Not supported by query systems.
- Constraints such as "SAL BETWEEN 1250 AND 2250" are hard to implement and check.
- Flag / Indicator types are mandatory.
