Object View Example:
In this given example, we look at how object views might be used at a fictitious firm which designs web sites. That existing relational application tracks GIF, JPEG and other Images in which they use when designing client web sites. These types of Images are stored in files but data about them are stored in relational tables. For help the graphic artists in locating the right Image and every Image have one or more related keywords which stored in a straightforward master-detail relationship.
CREATE TABLE Images (
Image_id INTEGER NOT NULL,
file_name VARCHAR2(512),
file_type VARCHAR2(12),
bytes INTEGER,
CONSTRAINT Image_pk PRIMARY KEY ( Image_id)); #define primary key#
...and one table for the keywords associated with the Images:
CREATE TABLE keywords (
Image_id INTEGER NOT NULL,
keyword VARCHAR2(45) NOT NULL,
CONSTRAINT keywords_pk PRIMARY KEY (Image_id, keyword),
CONSTRAINT keywords_for_Image FOREIGN KEY (Image_id)
REFERENCES Images (Image_id));
To create a more useful abstraction, we have decided to logically combine these two tables into a single object view. In sequence to do so, we must first create an object type with appropriate attributes. Because there are commonly only a few keywords for a provided Image, this relationship lends itself to using an Oracle collection to contain the keywords.
we will first describe a collection to contain the keywords before we can create the top-level type.
CREATE TYPE Keyword_tab_t AS TABLE OF VARCHAR2 (44);
From here, it's an easy matter to describe the object type. To remain the example short, we'll define only a couple of methods. In the subsequent object type specification, notice that the keywords attributes are defined on the Keyword_tab_t collection type.
CREATE TYPE Image_t AS OBJECT (
Image_id INTEGER,
file_name VARCHAR2(512),
file_type VARCHAR2(12),
bytes INTEGER,
keywords Keyword_tab_t,
MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
new_file_type IN VARCHAR2, new_bytes IN INTEGER)
RETURN Image_t,
MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t)
RETURN Image_t,
PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS, WNDS, RNPS, WNPS)
);
Here is the body:
CREATE TYPE BODY Image_t
AS
MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
new_file_type IN VARCHAR2, new_bytes IN INTEGER)
RETURN Image_t
IS
Image_holder Image_t := SELF;
BEGIN
Image_holder.file_name := new_file_name;
Image_holder.file_type := new_file_type;
Image_holder.bytes := new_bytes;
RETURN Image_holder;
END;
MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t)
RETURN Image_t
IS
Image_holder Image_t := SELF;
BEGIN
Image_holder.keywords := new_keywords;
RETURN Image_holder;
END;
END;
At this point there is no connection among the relational tables and the object type. That are independent organisms. It is when we establish the object view which we "overlay" the object definition onto the tables.
At last, to create the object view, we use the subsequent statement:
CREATE VIEW Images_v
OF Image_t
WITH OBJECT OID (Image_id)
AS
SELECT i.Image_id, i.file_name, i.file_type, i.bytes, #select statement#
CAST (MULTISET (SELECT keyword
FROM keywords k
WHERE k.Image_id = i.Image_id)
AS Keyword_tab_t)
FROM Images i;