Define Actions associated with Data
The main difference between object-oriented design and relational design is that object-oriented design allows you to not only define what data you can store in your database, you also define a set of actions (methods) associated with the data.
You can transfer a relational database table design into an object-oriented design by making the set of columns in a table into an object type and defining an object table. There is a different name for a foreign key within object-oriented design. It is called a reference, or
REF. This is similar to a foreign key column because it points to a row in another table.
It is different because it uses an internal format called an object-id (OID) to store the pointer rather than a readable value like a
- NUMBER or
There are two object-oriented objects that are useful to store small sets of repeating data within a single column in a table. These are called arrays and nested tables. The two objects are very similar, but there are some subtle differences. The table below outlines the similarities and differences between these two objects.
| Array || Nested table |
| Has a predefined number of rows || Has an unlimited number of rows |
| Rows can be retrieved using their position (row 1, 2, and so on) || Rows cannot be retrieved using their position. However, you can add an index onto a nested table. |
| Cannot be queried using SQL, you must use PL/SQL || Can be queried using SQL via special extensions for "flattening" a nested table. |
Here is an example of an array of ten phone numbers:
CREATE TYPE PHONE_ARRAY AS
VARRAY (10) OF VARCHAR2(15)
Here is an example of creating a nested table of phone numbers:
CREATE TYPE PHONE_NUMBER_LIST
AS TABLE OF VARCHAR2(15)
You can use an object type as the datatype for a nested table or an array.