049.【实验：update (view)】

当对视图进行update时, 会出现ora-01779错误, 这个错误是因为要修改的字段在视图中不能保证物理上唯一,
多出现在与多表关联时, 一个表因与另一个表的关联, 原表中的字段产生了多条记录的原故.

1.准备数据
CREATE TABLE plch_departments
( department_id INTEGER PRIMARY KEY,
department_name VARCHAR2(30) )
;

CREATE TABLE plch_departments2
( department_id INTEGER PRIMARY KEY,
department_name VARCHAR2(30) )
;
CREATE TABLE plch_employees
( employee_id INTEGER PRIMARY KEY,
last_name VARCHAR2(30),
salary NUMBER NOT NULL,
department_id INTEGER REFERENCES plch_departments (department_id)
)
;
INSERT INTO plch_departments2 VALUES (10, 'Accounting');
INSERT INTO plch_departments2 VALUES (20, 'Marketing');
INSERT INTO plch_departments2 VALUES (30, 'Sales');
INSERT INTO plch_departments2 VALUES (40, 'Advertising');
commit;

INSERT INTO plch_departments VALUES (10, 'Accounting');
INSERT INTO plch_departments VALUES (20, 'Marketing');
INSERT INTO plch_departments VALUES (30, 'Sales');
INSERT INTO plch_departments VALUES (40, 'Advertising');
INSERT INTO plch_employees VALUES (100, 'Jobs', 3000, 10);
INSERT INTO plch_employees VALUES (200, 'Ellison', 5000, 10);
INSERT INTO plch_employees VALUES (300, 'Gates', 4000, 20);
INSERT INTO plch_employees VALUES (400, 'Scott', 2500, 30);
INSERT INTO plch_employees VALUES (500, 'Tiger', 6000, 30);
INSERT INTO plch_employees VALUES (600, 'Mickey', 5000, 30);
INSERT INTO plch_employees VALUES (700, 'Mouse', 1500, NULL);
commit;
ALTER TABLE plch_departments ADD ( total_salary NUMBER DEFAULT 0 NOT NULL );
ALTER TABLE plch_departments2 ADD ( total_salary NUMBER DEFAULT 0 NOT NULL );

2.下面的语句，会出现ora-01779错误。--plch_employees表的关联字段不是主键
UPDATE
( SELECT d.total_salary, NVL(e.total_sal,0) total_sal
FROM plch_departments d
LEFT OUTER JOIN
( SELECT department_id, SUM(salary) total_sal
FROM plch_employees
GROUP BY department_id ) e
ON ( d.department_id = e.department_id )
)
SET total_salary = NVL(total_sal,0);

3.子查询的两个表用主键关键--执行成功
update
(select a.total_salary total_sal1,b.total_salary total_sal2
from plch_departments a
left join plch_departments2 b on(a.department_id=b.department_id)
)
set total_sal1=total_sal2;
commit;

4.删除主键，创建唯一索引--扫行成功
create unique index INDEX_UNI on PLCH_DEPARTMENTS2 (DEPARTMENT_ID)

update
(select a.total_salary total_sal1,b.total_salary total_sal2
from plch_departments a
left join plch_departments2 b on(a.department_id=b.department_id)
)
set total_sal1=total_sal2;
commit;
【小结】当update对视图更新时，对视图有如下限制条件。
1.FROM后面是一张表时，通常不会报错
2.FROM后面有多张表时，这些表必须使用主键或唯一索引进行关联

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060032/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060032/