Release 8.1.5
A68003-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5 A68003-01 |
|
This chapter contains an extended example of how to use object views. The chapter has the following major sections:
The view mechanism has been extended to support objects: the view with row objects is called an object view. Why is this important? And how is it useful?
The need to maintain legacy applications, and a variety of other reasons, may require that the data be kept in relational format. The ability to create objects in views means that you can apply the object model to this data without changing its underlying structure. Just as you can define column objects and row objects (objects in object tables) in tables, you can define them in views. These column and row objects need not exist physically in the database and can simply be synthesized from relational data.
This makes objects in views a powerful object modeling tool to work with both relational and object data. For instance, using views for synthesizing objects can provide a stepping stone for "objectizing" relational data -- prototyping the object model without modifying the storage structures. It also allows you to maintain co-existing relational and object applications.
This chapter deals with the various ways of defining and using such objects in views. We use the purchase-order example described in the previous chapters to show how to design the purchase-order object model. Specifically, we show how you would construct this model by using object views to synthesize objects from existing relational tables. We also show how complex views can be made "updatable" by using the INSTEAD-OF trigger mechanism.
The example in this chapter illustrates the most important aspects of defining and using object views. The definitions of triggers use the PL/SQL language. The remainder of the example uses SQL.
PL/SQL and Java provide additional capabilities beyond those illustrated in this chapter, especially in the area of accessing and manipulating the elements of collections.
|
See Also: PL/SQL User's Guide and Reference for a complete discussion of PL/SQL capabilities, and Oracle8i Java Stored Procedures Developer's Guide for a complete discussion of Java. |
Client applications that use the Oracle Call Interface (OCI) can take advantage of OCI's extensive facilities for accessing the objects and collections defined by object views and manipulating them on the client side.
INSTEAD-OF triggers to update complex views since these triggers provide the logic of executing the actual DML. This allows you to control the behavior of updates to complex object models and enforce security restrictions.
You need to understand the operation of a small number of basic elements in order to optimize your implementation of objects in views:
Column objects can be constructed by either selecting them from an underlying column object or by synthesizing them using the column's type constructor.
For example, consider the department table dept which has the following structure,
CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20), deptstreet VARCHAR2(20), deptcity VARCHAR2(10), deptstate CHAR(2), deptzip VARCHAR2(10) );
Suppose you want to view all the departments and their addresses with the address as an object, you could do the following.
CREATE TYPE address_t AS OBJECT ( street VARCHAR2(20), city VARCHAR2(10), state CHAR(2), zip VARCHAR2(10) ); /
CREATE VIEW dept_view AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS deptaddr FROM dept d;
Now that the deptaddr column in the view is a structured object column, you can invoke member methods of the address_t object type on the objects synthesized in this column.
In the example shown above, the address object (deptaddr) can never be null (atomically null). In the relational department table we do not have a column that captures the nullness of the address for the department. If the nullness of the deptstreet column indicates that the whole address is null, then we can write a DECODE() expression to generate the atomic null object.
CREATE VIEW dept_view AS SELECT d.deptno, d.deptname, DECODE(d.deptstreet, NULL, NULL, address_t(d.deptstreet, d.deptcity, d.deptstate, d.deptzip)) AS deptaddr FROM dept d;
We could also create functions other than the DECODE() expression to accomplish the same task. The drawback of these methods is that the deptaddr column becomes inherently "non-updatable" and so we would have to define an INSTEAD-OF trigger over the view to take care of updates to this column.
Collections, both nested tables and VARRAYs, can be columns in views. You can select these collections from underlying collection columns or you can synthesize them using subqueries. The CAST-MULTISET operator provides a way of synthesizing such collections.
Taking the previous example as our starting point, let us represent each employee in an emp relational table with following structure:
CREATE TABLE emp ( empno NUMBER PRIMARY KEY, empname VARCHAR2(20), salary NUMBER, deptno NUMBER REFERENCES dept(deptno) );
Using this relational table, we can construct a dept_view with the department number, name, address and a collection of employees belonging to the department.
CREATE TYPE employee_t AS OBJECT ( eno NUMBER, ename VARCHAR2(20), salary NUMBER ); / CREATE TYPE employee_list_t AS TABLE OF employee_t; /
dept_view can now be defined:
CREATE VIEW dept_view AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS deptaddr, CAST( MULTISET ( SELECT e.empno, e.empname, e.salary FROM emp e WHERE e.deptno = d.deptno) AS employee_list_t) AS emp_list FROM dept d;
The SELECT subquery inside the CAST-MULTISET block selects the list of employees that belong to the current department. The MULTISET keyword indicates that this is a list as opposed to a singleton value. The CAST operator casts the result set into the appropriate type, in this case to the employee_list_t collection type.
A query on this view could give us the list of departments, with each department row containing the department number, name, the address object and a collection of employees belonging to the department.
The object view mechanism provides a way of creating row objects. Since the view data is not stored persistently, and therefore needs to be computed as required, utilizing object identifiers can become a tricky issue.
If the view is based on an object table or an object view, the row objects could take the same identifier as the underlying object entity. However, if the row object is synthesized from relational data, we do not have any object identifiers with which to work.
Oracle solves this problem by introducing primary key based object identifiers. The set of unique keys that identify the resultant row object is chosen to be the identifier for the object. This object identifier is itself synthesized using these key values. It is necessary for these values to be unique within the rows selected out of the view, since duplicates would lead to problems during navigation through object references.
The major benefits of defining these row objects are that they become capable of being referenced and can be pinned in the object cache.
Continuing with our department example, we can create a dept_view object view:
CREATE TYPE dept_t AS OBJECT ( dno NUMBER, dname VARCHAR2(20), deptaddr address_t, emplist employee_list_t ); /
In our case, the department table has deptno as the primary key. Consequently each department row will have a unique department number which can identify the row. This allows us to define the object view dept_view with the dno attribute (which maps to the deptno column value in the SELECT list of the view) as being the object identifier.
CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT e.empno, e.empname, e.salary FROM emp e WHERE e.deptno = d.deptno) AS employee_list_t) FROM dept d;
If the object view is based on an object table or on another object view, the object identifiers need not be synthesized, and the object identifiers from the underlying table or view can be used provided that they still uniquely identify each object in this view. In that case, you either need not specify the WITH OBJECT IDENTIFIER clause, or you can specify WITH OBJECT IDENTIFIER DEFAULT to re-use the object identifiers from the underlying table or view source.
The object view created with the WITH OBJECT IDENTIFIER clause has a primary key based object identifier. If the WITH OBJECT IDENTIFIER DEFAULT clause is used during the creation of the view, the object identifier is either system generated or primary key based, depending on the underlying table or view definition.
In the example we have been developing, each object selected out of the dept_view view has a unique object identifier composed of the department number value. In the relational case, the foreign key deptno in the emp employee table matches the deptno primary key value in the dept department table. We used the primary key value for creating the object identifier in the dept_view. This allows us to use the foreign key value in the emp_view in creating a reference to the primary key value in dept_view.
We accomplish this by using MAKE_REF operator to synthesize a primary key object reference. This takes the view or table name to which the reference points and a list of foreign key values to create the object identifier portion of the reference that will match with a particular object in the referenced view.
In order to create an emp_view view which has the employee's number, name, salary and a reference to the department in which she works, we need first to create the employee type emp_t and then the view based on that type
CREATE TYPE emp_t AS OBJECT ( eno NUMBER, ename VARCHAR2(20), salary NUMBER, deptref REF dept_t ); / CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, MAKE_REF(dept_view, e.deptno) FROM emp e;
The deptref column in the view holds the department reference. We write the following simple query to determine all employees whose department is located in the city of San Francisco:
SELECT e.eno, e.salary, e.deptref.dno FROM emp_view e WHERE e.deptref.deptaddr.city = `San Francisco';
Note that we could also have used the REF modifier to get the reference to the dept_view objects:
CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, REF(d) FROM emp e, dept_view d WHERE e.deptno = d.dno;
In this case we join the dept_view and the emp table on the deptno key. The advantage of using MAKE_REF operator instead of the REF modifier is that in using the former, we can create circular references. For example, we can create employee view to have a reference to the department in which she works, and the department view can have a list of references to the employees who work in that department.
Note that if the object view has a primary key based object identifier, the reference to such a view is primary key based. On the other hand, a reference to a view with system generated object identifier will be a system generated object reference. This difference is only relevant when you create object instances in the OCI object cache and need to get the reference to the newly created objects. This is explained in a later section.
As with synthesized objects, we can also select persistently stored references as view columns and use them seamlessly in queries. However, the object references to view objects cannot be stored persistently.
Views with objects can be used to model inverse relationships.
One-to-one relationships can be modeled with inverse object references. For example, let us say that each employee has a particular computer on her desk, and that the computer 'belongs' to that employee only. A relational model would capture this using foreign keys either from the computer table to the employee table, or in the reverse direction. Using views, we can model the objects so that we have an object reference from the employee to the computer object and also have a reference from the computer object to the employee.
One-to-many relationships (or many-to-many relationships) can be modeled either by using object references or by embedding the objects. One-to-many relationship can be modeled by having a collection of objects or object references. The many-to-one side of the relationship can be modeled using object references.
Consider the department-employee case. In the underlying relational model, we have the foreign key in the employee table. Using collections in views, we can model the relationship between departments and employees. The department view can have a collection of employees, and the employee view can have a reference to the department (or inline the department values). This gives us both the forward relation (from employee to department) and the inverse relation (department to list of employees). The department view can also have a collection of references to employee objects instead of embedding the employee objects.
INSTEAD-OF triggers provide a way of updating complex views which otherwise could not be updated. However, they can also be used to enforce constraints, check privileges and validate the DML. Using these triggers, you can control mutation of the objects created though an object view that might be caused by inserting, updating and deleting.
For instance, suppose we wanted to enforce the condition that the number of employees in a department cannot exceed 10. To enforce this, we can write an INSTEAD-OF trigger for the employee view. The trigger is not needed for doing the DML since the view can be updated, but we need it to enforce the constraint.
We implement the trigger by means of the following code:
CREATE TRIGGER emp_instr INSTEAD OF INSERT on emp_view FOR EACH ROW DECLARE dept_var dept_t; emp_count integer; BEGIN -- Enforce the constraint..! -- First get the department number from the reference UTL_REF.SELECT_OBJECT(:NEW.deptref,dept_var); SELECT COUNT(*) INTO emp_count FROM emp WHERE deptno = dept_var.dno; IF emp_count < 9 THEN -- let us do the insert INSERT INTO emp VALUES (:NEW.eno,:NEW.ename,:NEW.salary,dept_var.dno); END IF; END; /
In Chapter 16, "User-Defined Datatypes" we developed a purchase order example by following these steps:
Using the mechanisms described in the previous section, let us redo the last step by creating an object-relational schema using views over relational tables.
Stock objects are referenced from line item objects, and so we need to synthesize them from the Stock_reltab relational table. This mapping is straightforward as each attribute of the stock type directly maps to a column in the relational table. Since the stock number will uniquely identify each stock, we will use that for our object identifier. We define the stock object view as follows:
CREATE OR REPLACE VIEW Stock_objview OF StockItem_objtyp WITH OBJECT IDENTIFIER(StockNo) AS SELECT * FROM Stock_reltab;
The customer object type (Customer_objtyp) includes an embedded address object (Address_objtyp) and a VARRAY (PhoneList_vartyp) of phone numbers. The relational table, Customer_retab, has three columns to store phone numbers. We can synthesize the VARRAY from these columns as show below,
CREATE OR REPLACE VIEW Customer_objview OF Customer_objtyp WITH OBJECT IDENTIFIER(Custno) AS SELECT c.Custno, C.custname, Address_objtyp(C.Street, C.City, C.State, C.Zip), PhoneList_vartyp(Phone1, Phone2, Phone3) FROM Customer_reltab c;
Again, the customer number forms the identifier for the customer object and the customer's address is synthesized using the default constructor of the Address_objtyp.
The purchase order type uses a reference to the customer and has a collection of line item objects. The customer reference can be created using the MAKE_REF operator on the Customer_objview object view using the Custno foreign key in the purchase order table. We can synthesize the line items from the line item table using a subquery to identify the line items corresponding to the particular purchase order. The line item type also includes a reference to the stock object which can be created using the MAKE_REF operator on the Stock_objview object view.
CREATE OR REPLACE VIEW PurchaseOrder_objview OF PurchaseOrder_objtyp WITH OBJECT IDENTIFIER(PONo) AS SELECT P.PONo, MAKE_REF(Customer_objview, P.Custno), P.OrderDate, P.ShipDate, CAST( MULTISET( SELECT LineItem_objtyp( L.LineItemNo, MAKE_REF(Stock_objview,L.StockNo), L.Quantity, L.Discount) FROM LineItems_reltab L WHERE L.PONo = P.PONo) AS LineItemList_ntabtyp), Address_objtyp(P.ToStreet,P.ToCity, P.ToState, P.ToZip) FROM PurchaseOrder_reltab P;
One minor point to note in the CAST-MULTISET operator is that we have used the base object type LineItem_objtyp in the SELECT list and constructed a list of line item objects and then cast them to the nested table type, LineItemList_ntabtyp. This is not necessary and we could have omitted the constructor and simply written the CAST-MULTISET part as
CAST( MULTISET (SELECT L.LineItemNo, MAKE_REF(..) ...) AS LineItemList_ntabtyp)
Oracle would automatically create the line item objects before creating the collection. However putting the constructor improves the readability of the CREATE-VIEW statement and forces a structural validation with the base type of the collection type specified in the CAST expression.
We have now created an object relational schema model using relational data and views. These views can be queried just like object tables.
Objects synthesized using views will behave in the same way as native objects.
The following query statement implicitly invokes a comparison method. It shows how Oracle uses the ordering of PurchaseOrder_objtyp object types defined by the comparison method:
SELECT p.PONo FROM PurchaseOrder_objview p ORDER BY VALUE(p);
The preceding instruction causes Oracle to invoke the map method getPONo for each PurchaseOrder_objtyp object in the selection. Because that method returns the value of the object's PONo attribute, the result of the selection is a list of purchase order numbers in ascending numerical order. Remember that the object is constructed by the object view from underlying relational data.
The following queries correspond to the queries executed under the relational model.
SELECT DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo, p.OrderDate, LineItemList_ntab FROM PurchaseOrder_objview p WHERE p.PONo = 1001 ;
SELECT p.PONo, p.sumLineItems() FROM PurchaseOrder_objview p ;
SELECT po.PONo, po.Cust_ref.CustNo, CURSOR ( SELECT * FROM TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004 ) FROM PurchaseOrder_objview po ;
This query returns a nested cursor for the set of LineItem_obj objects selected from the nested table. The application can fetch from the nested cursor to obtain the individual LineItem_obj objects. You can implement the same query by unnesting the nested set with respect to the outer result:
SELECT po.PONo, po.Cust_ref.CustNo, L.* FROM PurchaseOrder_objview po, TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004;
This query returns the result set as "flattened" in the First Normal Form form. This is useful when accessing Oracle collection columns from relational tools and APIs that cannot work with collections. In this example of unnesting, only the rows of the PurchaseOrder_objtab table which have any LineItemList_ntab rows are returned. If you need to fetch all rows of PurchaseOrder_objtab table irrespective of whether there are any rows in their corresponding LineItemList_ntab, an outer join (+) is required.
SELECT po.PONo, po.Cust_ref.CustNo, L.* FROM PurchaseOrder_objview po, TABLE (po.LineItemList_ntab) (+) L WHERE L.Stock_ref.StockNo = 1004;
The following statement requires that the rows of all nested tables, LineItemList_ntab, of all PurchaseOrder_objview objects be queried. Again, unnesting is required for this query:
SELECT AVG(L.DISCOUNT) FROM PurchaseOrder_objview po, TABLE (po.LineItemList_ntab) L;
As we have seen from the examples, views can help develop a good object model from any kind of data.
In the purchase-order model, the Stock_objview object view is a simple view and the system can translate any DML on the view into changes made to the underlying tables. However, in the case of the purchase order view (PurchaseOrder_objview), the task becomes complex and the view becomes inherently non-updatable. Such views (and any view) can be made updatable by defining INSTEAD-OF triggers for the view.
INSTEAD-OF triggers are triggers that fire upon an insert, delete or update of a row of a view on which they are defined and the body of the trigger contains the code for performing the DML. This means that when you create an INSTEAD-OF trigger, you specify the exact way to handle an update to the view.
As is the case with any other trigger, the new and old values of the row can be obtained through the respective qualifiers.
For example, let us consider the PurchaseOrder_objview object view. Each row of the view contains a purchase order object with an embedded shipping address object, a reference to a customer object and a list of line items.
To translate an insert on this view into a change of the underlying data, we need to map the object attributes back to the relational tables from which we obtained those values. Specifically, we need to map the customer reference back to the customer number and map the line item list to the line item relational table.
CREATE OR REPLACE TRIGGER POView_instdinserttr INSTEAD OF INSERT on PurchaseOrder_objview DECLARE LineItems_ntab LineItemList_ntabtyp; i INTEGER; CustVar_obj Customer_objtyp; StockVar_obj StockItem_objtyp; StockVarTemp_ref REF StockItem_objtyp; BEGIN LineItems_ntab := :new.LineItemList_ntab; UTL_REF.SELECT_OBJECT(:new.Cust_ref, CustVar_obj); INSERT INTO PurchaseOrder_reltab VALUES(:new.PONo,CustVar_obj.Custno,:new.OrderDate,:new.ShipDate, :new.ShipToAddr_obj.Street,:new.ShipToAddr_obj.City, :new.ShipToAddr_obj.State,:new.ShipToAddr_obj.Zip) ; FOR i in 1..LineItems_ntab.count LOOP UTL_REF.SELECT_OBJECT(LineItems_ntab(i).Stock_ref, StockVar_obj); INSERT INTO LineItems_reltab VALUES(LineItems_ntab(i).LineItemNo,:new.PONo,StockVar_obj.StockNo, LineItems_ntab(i).Quantity,LineItems_ntab(i).Discount); END LOOP; END; / CREATE OR REPLACE TRIGGER POView_instddeletetr INSTEAD OF DELETE on PurchaseOrder_objview BEGIN DELETE FROM LineItems_reltab WHERE PONo = :old.PONo; DELETE FROM PurchaseOrder_reltab WHERE PONo = :old.PONo; END; /
Note the use of the UTL_REF.SELECT_OBJECT function in the trigger. The UTL_REF package provides functions for pinning an object reference and selecting the object value. In the purchase order case, we need to get the object instances for the customer reference and the stock reference so that we can get the customer number and stock number to insert into the relational tables. You use the UTL_REF functions to accomplish this.
Any insert of the form,
INSERT INTO PurchaseOrder_objview SELECT 1001, REF(cust),,....
would fire the INSTEAD-OF trigger to perform the necessary action.
Similarly any deletes on the purchase order view would fire the POView_instddeletetr and delete the purchase order and the corresponding line items.
In the purchase order example, we might also want to be able to update the lineItemList collection elements directly:
INSERT INTO TABLE(SELECT e.lineItemList FROM PurchaseOrder_objview e WHERE e.PONo = 1001) VALUES (101,....);;
To do this we can define an INSTEAD-OF trigger over the nested table column to perform a similar action. These triggers are fired on DML statements that target the nested table using the TABLE<collection subquery>, and fire for each row of the collection being modified. The new and old qualifiers correspond to the new and old values of the collection element.
We can code the trigger in a similar way. One important difference is that the line item list object does not include the purchase order number which we need for inserting a row into the line item list table. However, we have this in the parent row corresponding to the collection entity being modified, and we can access this parent row's value through the parent qualifier.
The example below creates an instead-of trigger for the LineItemList_ntab nested table of object view, PurchaseOrder_objview.
CREATE OR REPLACE TRIGGER POLineItems_instdinsertr INSTEAD OF INSERT ON NESTED TABLE LineItemList_ntab OF PurchaseOrder_objview DECLARE StockVar StockItem_objtyp; BEGIN UTL_REF.SELECT_OBJECT(:NEW.Stock_ref, StockVar); INSERT INTO LineItems_reltab VALUES (:NEW.LineItemNo, :PARENT.PONo, StockVar.StockNo, :NEW.Quantity, :NEW.Discount); END; / CREATE OR REPLACE TRIGGER POLineItems_instddeltr INSTEAD OF DELETE ON NESTED TABLE LineItemList_ntab OF PurchaseOrder_objview BEGIN DELETE FROM LineItems_reltab WHERE LineItemNo = :OLD.LineItemNo AND PONo = :PARENT.PONo; END; /
In the Customer_objview case, we have an embedded object for the customer's address and a VARRAY of phone numbers. Our task is that we need to extract each element of the VARRAY and insert it into the phone columns in the base table.
CREATE OR REPLACE TRIGGER CustView_instdinserttr INSTEAD OF INSERT on Customer_objview DECLARE Phones_var PhoneList_vartyp; TPhone1 Customer_reltab.Phone1%TYPE := NULL; TPhone2 Customer_reltab.Phone2%TYPE := NULL; TPhone3 Customer_reltab.Phone3%TYPE := NULL; BEGIN Phones_var := :new.PhoneList; IF Phones_var.COUNT > 2 then TPhone3 := Phones_var(3); END IF; IF Phones_var.COUNT > 1 then TPhone2 := Phones_var(2); END IF; IF Phones_var.COUNT > 0 then TPhone1 := Phones_var(1); END IF; INSERT INTO Customer_reltab VALUES(:new.Custno,:new.Custname,:new.Address.Street, :new.Address.City, :new.Address.State, :new.Address.Zip, TPhone1,TPhone2,TPhone3); END; /
This trigger function updates the Customer_reltab table with the new information. Most of the program deals with updating the three phone number columns of the Customer_reltab table from the VARRAY of phone numbers. The IF statements assure that the program does not attempt to access elements with indexes greater than the specified number.
There is a slight mismatch between these two representations, because the VARRAY is defined hold up to ten numbers, while the customer table has only three phone number columns. The trigger program discards elements with indexes greater than three.
The Stock_objview is a simple view which is inherently updatable. We do not have to define an INSTEAD-OF trigger for performing DML on it. However, we might want to enforce constraints on it, such as the TaxRate not being greater than 30%. We might also want to record this new Stock addition in the Stock_archive_tab which stores information on the stock purchase and depletion.
The Stock_archive_tab structure is shown below
CREATE TABLE Stock_archive_tab ( archive_date DATE, StockNo NUMBER, Price NUMBER, TaxRate NUMBER ); CREATE OR REPLACE TRIGGER StockView_instdinsertr INSTEAD OF INSERT on Stock_objview BEGIN -- When the TaxRate is greater than 30% we can simply ignore the -- row or raise an exception. IF :new.TaxRate <= 30 THEN -- insert the values into the Stock table INSERT INTO Stock_reltab VALUES(:new.StockNo,:new.Cost,:new.TaxRate); -- Let us record this stock increase in the archive: INSERT INTO Stock_archive_tab VALUES (SYSDATE, :new.StockNo, :new.Cost, :new.TaxRate); END IF; END; /
This trigger function updates the Stock_reltab table with the new information and also archives it. Similarly, we can implement delete and update triggers on the view which would both update the base table and also the archival table.
The statements in this section show how to insert the same data into the object views created. Again, we have used the same examples as the last chapter to show how the synthesized objects in views behaves in the same way as native objects.
INSERT INTO Stock_objview VALUES(1004, 6750.00, 2); INSERT INTO Stock_objview VALUES(1011, 4500.23, 2); INSERT INTO Stock_objview VALUES(1534, 2234.00, 2); INSERT INTO Stock_objview VALUES(1535, 3456.23, 2);
The INSTEAD-OF trigger on the view would automatically record these insertions.
The following insert would not be recorded as our StockView_instdinsertr would prevent a stock object with TaxRate greater than 30% to be inserted.
INSERT INTO Stock_objview VALUES(1535, 3456.23, 32);
Let us insert some customers in our system.
INSERT INTO Customer_objview VALUES ( 1, 'Jean Nance', Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'), PhoneList_vartyp('415-555-1212') ) ; INSERT INTO Customer_objview VALUES ( 2, 'John Nike', Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'), PhoneList_vartyp('609-555-1212','201-555-1212') ) ;
INSERT INTO PurchaseOrder_objview VALUES ( 1001, ( SELECT REF(C) FROM Customer_objview C WHERE C.CustNo = 1), SYSDATE, '10-MAY-1997', LineItemList_ntabtyp(), NULL );
The preceding statement constructs a PurchaseOrder_objtyp object with the following attributes:
PONo 1001 Cust_ref REF to customer number 1 OrderDate SYSDATE ShipDate 10-MAY-1997 LineItemList an empty LineItem_objtyp ShipToAddr NULL
The statement uses a query to construct an object reference to the row object in the Customer_objtab object table that has a CustNo value of 1. Note the use of the subquery in the VALUES clause to construct an object reference to the customer. This query returns a single value.
We could also have used the MAKE_REF operator to construct the object reference with the same result,
INSERT INTO PurchaseOrder_objview VALUES( 1001, MAKE_REF(Customer_objview, 1) , SYSDATE,'10-MAY-1997', LineItemList_ntabtyp(), NULL);
The next statement uses a TABLE expression to identify the nested table as the target for the insertion. In this case, we are targeting the nested table in the LineItemList_ntab column of the row object in the PurchaseOrder_objview view that has a PONo value of 1001.
INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objview P WHERE P.PONo = 1001 ) SELECT 01, REF(S), 12, 0 FROM Stock_objview S WHERE S.StockNo = 1534;
The preceding statement inserts a line item into the nested table identified by the TABLE expression. The line item that it inserts contains a reference to the row object in the object view Stock_objview that has a StockNo value of 1534. Remember that this will fire the POLineItems_instdinsertr trigger to insert the line item values into the LineItems_reltab relational table.
The following example has the same effect as the deletions made in the relational case (see "Deleting Data Under The Relational Model" in Chapter 16, "User-Defined Datatypes"). With views and INSTEAD-OF triggers, when a purchase order object is deleted, all line items belonging to the purchase order is automatically deleted. The relational case requires a separate step.
DELETE FROM PurchaseOrder_objview p WHERE p.PONo = 1001 ;
This concludes the view version of the purchase order example.
We can pin and navigate objects synthesized from object views in the OCI Object Cache similar to the way we do this with object tables. We can also create new view objects, update them, delete them and flush them from the cache. The flush performs the appropriate DML on the view (such as insert for newly created objects and updates for any attribute changes). This would fire the INSTEAD-OF triggers if any on the view and the object would get stored persistently.
There is a minor difference between the two approaches with regard to getting the reference to a newly created instance in the object cache.
In the case of object views with primary key based reference, the attributes that make up the identifier for the object need to be initialized before the OCIObjectGetObjectRef call can be called on the object to get the object reference. For example, to create a new object in the OCI Object cache for the purchase order object, we need to take the following steps:
.. /* Initialize all the settings including creating a connection, getting a environment handle etc. We do not check for error conditions to make the example eaiser to read. */ OCIType *purchaseOrder_tdo = (OCIType *) 0; /* This is the type object for the purchase order */ dvoid * purchaseOrder_viewobj = (dvoid *) 0; /* This is the view object */ /* The purchaseOrder struct is a structure that is defined to have the same attributes as that of PurchaseOrder_objtyp type. This can be created by the user or generated automatically using the OTT generator. */ purchaseOrder_struct *purchaseOrder_obj; /* This is the null structure corresponding to the purchase order object's attributes */ purchaseOrder_nullstruct *purchaseOrder_nullobj; /* This is the variable containing the purchase order number that we need to create */ int PONo = 1003; /* This is the reference to the purchase order object */ OCIRef *purchaseOrder_ref = (OCIRef *)0; /* Pin the object type first */ OCITypeByName( envhp, errhp, svchp, (CONST text *) "", (ub4) strlen( "") , (CONST text *) "PURCHASEORDER_OBJTYP" , (ub4) strlen("PURCHASEORDER_OBJTYP"), (CONST char *) 0, (ub4)0, OCI_DURATION_SESSION, OCI_TYPEGET_ALL, &purchaseOrder_tdo); /* Pin the table object - in this case it is the purchase order view */ OCIObjectPinObjectTable(envhp, errhp, svchp, (CONST text *) "", (ub4) strlen( "" ), (CONST text *) "PURCHASEORDER_OBJVIEW", (ub4 ) strlen("PURCHASEORDER_OBJVIEW"), (CONST OCIRef *) NULL, OCI_DURATION_SESSION, &purchaseOrder_viewobj); /* Now create a new object in the cache. This is a purchase order object */ OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_OBJECT, purchaseOrder_tdo, purchaseOrder_viewobj, OCI_DURATION_DEFAULT, FALSE, (dvoid **) *purchaseOrder_obj); /* Now we can initialize this object, and use it as a regular object. But before getting the reference to this object we need to initialize the PONo attribute of the object which makes up its object identifier in the view */ /* Initialize the null identifiers */ OCIObjectGetInd( envhp, errhp, purchaseOrder_obj, purchaseOrder_nullobj); purchaseOrder_nullobj->purchaseOrder = OCI_IND_NOTNULL; purchaseOrder_nullobj->PONo = OCI_IND_NOTNULL; /* This sets the PONo attribute */ OCINumberFromInt( errhp, (CONST dvoid *) &PoNo, sizeof(PoNo), OCI_NUMBER_SIGNED, &( purchaseOrder_obj->PONo)); /* Create an object reference */ OCIObjectNew( envhp, errhp, svchp, OCI_TYPECODE_REF, (OCIType *) 0, (dvoid *) 0, (dvoid *) 0, OCI_DURATION_DEFAULT, TRUE, (dvoid **) &purchaseOrder_ref); /* Now get the reference to the newly created object */ OCIObjectGetObjectRef(envhp, errhp, (dvoid *) purchaseOrder_obj, purchaseOrder_ ref); /* This reference may be used in the rest of the program ..... */ ... /* We can flush the changes to the disk and the newly instantiated purchase order object in the object cache will become permanent. In the case of the purchase order object, the insert will fire the INSTEAD-OF trigger defined over the purchase order view to do the actual processing */ OCICacheFlush( envhp, errhp, svchp, (dvoid *) 0, 0, (OCIRef **) 0); ...
Views can be used to synthesize objects from remote tables.
Consider the case of a company which has three branches -- one in Washington D.C., another in Seattle and a third in Chicago. Let us say that each of these sites has an employee table that is maintained separately by the respective IT departments. The headquarters in Washington has an department table that has the list of all the departments. Supposing that the CEO wants to get a total view of the entire organization, we can create views over the individual remote tables and then a overall view of the organization:-
Let us create the individual views first.
CREATE VIEW emp_washington_view (eno,ename,salary) AS SELECT e.empno, e.empname, e.salary FROM emp@washington_link e; CREATE VIEW emp_chicago_view AS SELECT e.eno, e.name, e.salary FROM emp_tab@chicago_link e; CREATE VIEW emp_seattle_view (eno,ename,salary) AS SELECT e.employeeno, e.employeename, e.employeesalary FROM employeetab@seattle_link e;
We can now create the global view as follows:-
CREATE VIEW orgnzn_view OF dept_t WITH OBJECT IDENTIFIER (dno) AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT e.eno, e.ename, e.salary FROM emp_washington_view e) AS employee_list_t) FROM dept d WHERE d.deptcity = `Washington' UNION ALL SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT e.eno, e.name, e.salary FROM emp_chicago_view e) AS employee_list_t) FROM dept d WHERE d.deptcity = `Chicago' UNION ALL SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT e.eno, e.ename, e.salary FROM emp_seattle_view e) AS employee_list_t) FROM dept d WHERE d.deptcity = `Seattle;
This view would now have list of all employees for each department. We use UNION ALL in this example since we cannot have two employees working in more than one department. If we had to deal with that eventuality, we could use a UNION of the rows. However, one caveat in using the UNION operator is that we need to introduce an ORDER BY operator within the CAST-MULTISET expressions so that the comparison of two collections is performed properly.
Working with very large tables and indexes may lead you to decompose them into smaller and more manageable pieces called partitions. Since using objects in views does not affect the storage characteristics of the underlying tables, queries on objects with views can be optimized to take advantage of the partitions.
Parallel query is supported on the objects synthesized from views.
To execute queries involving joins and sorts (using the ORDER BY, GROUP BY, and SET operations) in parallel, a MAP function is needed. In the absence of a MAP function, the query automatically becomes serial.
Parallel queries on nested table columns are not supported. Even in the presence of parallel hints or parallel attributes for the view, the query will be serial if it involves the nested table column.
Parallel DML is not supported on views with INSTEAD-OF trigger. However, the individual statements within the trigger may be parallelized.
You can define circular references to views using the MAKE_REF operator: view_A can refer to view_B which in turn can refer to view_A.
For example, in the case of the department and employee, the department object currently includes a list of employees. We may not want to materialize the entire list and instead opt to use references to employee objects. This may be necessary, for instance, if the employee object is large and we do not need all the employee objects to be materialized. We can pin the necessary employee references and extract the information later.
The employee object already has a reference to the department in which the employee works.
If we create object view over this model, we would get circular references between the department view and the employee view.
We can create circular references between object views in two different ways.
NULL value for the MAKE_REF column).
FORCE keyword.
The advantage of Method 2 is that we do not have to repeat the creation of the view. But the disadvantage is other errors in the view creation may get masked because of the FORCE keyword. You need to use USER_ERRORS catalog view to get the errors during the view creation in this case. Use this method only if you are sure that there are no errors in the view creation statement.
Also, if the views do not get automatically recompiled upon use because of errors, you would need to recompile them manually using the ALTER VIEW COMPILE command.
We will see the implementation for both the methods.
Create the emp table to store the employee information.
CREATE TABLE emp ( empno NUMBER PRIMARY KEY, empname VARCHAR2(20), salary NUMBER, deptno NUMBER );
Create the emp_t type with the reference to the department. Create a dummy department type so that the emp_t type creation does not result in any warnings or errors.
CREATE TYPE dept_t; /
Create the employee type that includes a reference to the department.
CREATE TYPE emp_t AS OBJECT ( eno NUMBER, ename VARCHAR2(20), salary NUMBER, deptref REF dept_t ); /
Create the list of references to employee types.
CREATE TYPE employee_list_ref_t AS TABLE OF REF emp_t; /
Create the department table.
CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20), deptstreet VARCHAR2(20), deptcity VARCHAR2(10), deptstate CHAR(2), deptzip VARCHAR2(10) );
Create an address type to store the address information as an object.
CREATE TYPE address_t AS OBJECT ( street VARCHAR2(20), city VARCHAR2(10), state CHAR(2), zip VARCHAR2(10) ); /
Create the department type. Note that we are replacing the existing department type.
CREATE OR REPLACE TYPE dept_t AS OBJECT ( dno NUMBER, dname VARCHAR2(20), deptaddr address_t, empreflist employee_list_ref_t ); /
Having created the necessary types with the underlying relational table definition, let us create the object views on top of them.
Here we will first create the employee view without including the reference to the department view.
CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, NULL FROM emp e; The deptref column has a NULL value in it.
Next, we create the department view which includes references to the employee objects.
CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT MAKE_REF(emp_view, e.empno) FROM emp e WHERE e.deptno = d.deptno) AS employee_list_ref_t) FROM dept d;
Here we have a created a list of references to employee objects in the department view instead of including the entire employee object. We can now proceed to re-create the employee view with the reference to the department view.
CREATE OR REPLACE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, MAKE_REF(dept_view, e.deptno) FROM emp e;
This creates the views.
If we are sure that we do not have any syntax errors in the view creation statement, we can use the FORCE keyword to first force the creation of one of the views without the other view being present.
Let us first create an employee view which includes a reference to the department view. At this point, the department view has not been created and so the employee view is being forced into creation. This view cannot be queried until the department view is created properly.
CREATE FORCE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, MAKE_REF(dept_view, e.deptno) FROM emp e;
Next, we create the department view which includes references to the employee objects. We do not have to use the FORCE keyword here, since emp_view already exists.
CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT MAKE_REF(emp_view, e.empno) FROM emp e WHERE e.deptno = d.deptno) AS employee_list_ref_t) FROM dept d;
This allows us to query the department view, getting the employee object by pinning the object reference.
We can retrieve the entire employee object by de-referencing the employee reference from the nested table empreflist.
SELECT DEREF(e.COLUMN_VALUE) FROM TABLE( SELECT e.empreflist FROM dept_view e WHERE e.dno = 100) e;
The COLUMN_VALUE column is used to get to the scalar value in a scalar nested table. In this case, COLUMN_VALUE denotes the reference to the employee objects in the nested table empreflist.
We could also access only the employee number of all those employees whose name begins with "John".
SELECT e.COLUMN_VALUE.eno FROM TABLE(SELECT e.empreflist FROM dept_view e WHERE e.dno = 100) e WHERE e.COLUMN_VALUE.ename like `John%';
To get a tabular output, unnest the list of references:
SELECT d.dno, e.COLUMN_VALUE.eno, e.COLUMN_VALUE.ename FROM dept_view d, TABLE(d.empreflist) e WHERE e.COLUMN_VALUE.ename like `John%' AND d.dno = 100;
Finally, we could rewrite the above query to use the emp_view instead of the dept_view in order to demonstrate the functionality of circular nature of the reference:
SELECT e.deptref.dno, DEREF(f.COLUMN_VALUE) FROM emp_view e, TABLE(e.deptref.empreflist) f WHERE e.deptref.dno = 100 AND f.COLUMN_VALUE.ename like `John%';
