CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Object Views
Version 11.1
 
Demo

Object View Demo
CREATE TABLE dept (
deptno  NUMBER(2),
dname   VARCHAR2(14),
loc     VARCHAR2(13));

ALTER TABLE dept
ADD CONSTRAINT pk_dept
PRIMARY KEY (deptno)
DEFERRABLE INITIALLY DEFERRED
USING INDEX;

CREATE TABLE emp (
empno     NUMBER(4),
ename     VARCHAR2(10),
job       VARCHAR2(9),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2));

ALTER TABLE emp
ADD CONSTRAINT pk_emp
PRIMARY KEY (empno)
DEFERRABLE INITIALLY DEFERRED
USING INDEX;

ALTER TABLE emp
ADD CONSTRAINT fk_emp_deptno
FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE INITIALLY DEFERRED;

INSERT INTO dept VALUES (1, 'EM', 'Seattle');
INSERT INTO dept VALUES (2, 'IT', 'San Francisco');

INSERT INTO emp
VALUES (1, 'Morgan', 'CIO', NULL, SYSDATE, 72500, 0, 1);

INSERT INTO emp
VALUES (2, 'Townsend', 'CFO', NULL, SYSDATE, 65250, 6, 1);
COMMIT;

CREATE OR REPLACE TYPE emp_type AS OBJECT (
empno     NUMBER(4),
ename     VARCHAR2(10),
job       VARCHAR2(9),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUMBER(7, 2),
comm      NUMBER(7, 2));
/

CREATE OR REPLACE TYPE emp_tab_type
AS TABLE OF emp_type;
/

CREATE OR REPLACE TYPE dept_type AS OBJECT (
deptno  NUMBER(2),
dname   VARCHAR2(14),
loc     VARCHAR2(13),
emps    emp_tab_type);
/

CREATE OR REPLACE VIEW dept_or OF dept_type
WITH OBJECT IDENTIFIER (deptno) AS
SELECT deptno, dname, loc, CAST(MULTISET(
  SELECT empno, ename, job, mgr, hiredate, sal, comm
  FROM emp
  WHERE emp.deptno = dept.deptno ) AS emp_tab_type)
FROM dept;
/

set describe depth all
desc dept_or

col emps format a80

SELECT dname, d.emps AS emps
FROM dept_or d;

SELECT deptno, dname, loc, COUNT(*)
FROM dept_or d, table (d.emps)
GROUP BY deptno, dname, loc;

UPDATE dept_or
SET dname = 'Research'
WHERE deptno = 2;

ROLLBACK;

-- this will produce an error
UPDATE TABLE (
  SELECT p.emps
  FROM dept_or p
  WHERE deptno = 1)
SET ename = LOWER(ename);

CREATE OR REPLACE TRIGGER emps_io_update
INSTEAD OF UPDATE
ON NESTED TABLE emps OF dept_or
BEGIN
  IF (:new.empno = :old.empno) THEN
    UPDATE emp
    SET ename = :new.ename, job = :new.job, mgr = :new.mgr,
    hiredate = :new.hiredate, sal = :new.sal, comm = :new.comm
    WHERE empno = :old.empno;
  ELSE
    raise_application_error(-20001,'Empno cannot be updated');
  END IF;
END;
/

UPDATE TABLE (
  SELECT p.emps
  FROM dept_or p
  WHERE deptno = 1 )
SET ename = LOWER(ename);

COMMIT;

SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM dept_or;
 
Related Topics
Instead Of Trigger
Nested Tables
Types
Views
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [144 users online]    © 2010 psoug.org