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 AUTHID DEFINER 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 AUTHID DEFINER AS OBJECT (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
emps emp_tab_type);
/
CREATE OR REPLACE VIEW dept_orv 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;
/
desc dept_orv
set describe depth all
desc dept_orv
col emps format a80
SELECT dname, d.emps AS emps
FROM dept_orv d;
SELECT deptno, dname, loc, COUNT(*)
FROM dept_orv d, table (d.emps)
GROUP BY deptno, dname, loc;
UPDATE dept_orv
SET dname = 'Research'
WHERE deptno = 2;
ROLLBACK;
-- this will produce an error
UPDATE TABLE (
SELECT p.emps
FROM dept_orv p
WHERE deptno = 1)
SET ename = LOWER(ename);
SET ename = LOWER(ename)
*
ERROR at line 5:
ORA-25015: cannot perform DML on this nested table view column
CREATE OR REPLACE TRIGGER emps_io_update
INSTEAD OF UPDATE
ON NESTED TABLE emps OF dept_orv
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_orv p
WHERE deptno = 1 )
SET ename = LOWER(ename);
COMMIT;
SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM dept_orv; |
