skip to content »

Updating more than one column

updating more than one column-66

You can specify object identifiers based on the primary key.This turns the set of unique keys that identify the row object into an identifier for the object. 6-4 -- if you have previously created emp_t, you must drop it CREATE TYPE emp_t AS OBJECT ( empno NUMBER, ename VARCHAR2(20), salary NUMBER, deptref REF dept_t); / CREATE OR REPLACE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(empno) AS SELECT e.empno, e.empname, e.salary, SELECT e.empno, e.salary, e.deptref.deptno FROM emp_view e WHERE e.deptref.= 'Redwood S'; EMPNO SALARY DEPTREF.

If column objects of the attribute type already exist in a relational table, you can simply select them; otherwise, you must synthesize the object instances from underlying relational data just as you synthesize the principal object instances of the view. You can run object-oriented applications without converting existing tables to a different physical structure.To do this, you can use object views to prototype or transition to object-oriented applications because the data in the view can be taken from relational tables and accessed as if the table were defined as an object table.trigger procedure for Oracle to execute whenever an application program tries to update data in the object view. After these steps, you can use an object view just like an object table.Example 6-1 contains SQL statements to define an object view, where each row in the view is an object of type CREATE TABLE emp_table ( empnum NUMBER (5), ename VARCHAR2 (20), salary NUMBER (9,2), job VARCHAR2 (20)); CREATE TYPE employee_t AS OBJECT ( empno NUMBER (5), ename VARCHAR2 (20), salary NUMBER (9,2), job VARCHAR2 (20)); / CREATE VIEW emp_view1 OF employee_t (empno) AS SELECT e.empnum, e.ename, e.salary, FROM emp_table e WHERE job = 'Developer'; insert into emp_table values(1,'John',1000.00,'Architect'); insert into emp_table values(2,'Robert',900.00,'Developer'); insert into emp_table values(3,'James',2000.00,'Director'); select * from emp_view1; EMPNO ENAME SALARY JOB ---------- -------------------- ---------- -------------------- 2 Robert 900 Developer Data in the rows of an object view may come from more than one table, but the object view still traverses the network in one operation.Example 6-5 builds an object view containing a multilevel collection.

The view is based on flat relational tables that contain no collections.

As a preliminary to building the object view, the example creates the object and collection types it uses.

An object type (for example, ) is defined to correspond to each relational table, with attributes whose types correspond to the types of the respective table columns.

In addition, the employee type has a nested table (attribute) of projects, and the department type has a nested table (attribute) of employees.

The latter nested table is a multilevel collection.

These values must be unique within the rows selected out of the view, because duplicates would lead to problems during navigation through object references. This takes the view or table name that the reference points to, and a list of foreign key values, to create the object identifier portion of the reference that matches a specific object in the referenced view. DEPTNO ---------- ---------- -------------- 2 1000 100 1 900 100 4 1500 200 3 1000 200 modifier is that with the former, you can create circular references.