两个表:
CREATE TABLE SCOTT.DTEMP (
VNAME VARCHAR2(20) NOT NULL PRIMARY KEY,
VID VARCHAR2(20) NOT NULL
)
CREATE TABLE SCOTT.DTEMPME (
VNAME VARCHAR2(20) NOT NULL PRIMARY KEY,
VSS VARCHAR2(20) NOT NULL
)
在这个基础上建立的视图:
CREATE VIEW SCOTT.BBVIEW AS
select dtemp.vname as pvname, dtemp.vid, dtempMe.* from dtemp,dtempMe
插入数据出现问题:
insert into bbview (pvname, vname, vid, vss) values ('101','102','102','101')
*
ERROR 位于第 1 行:
ORA-01779: 无法修改与非键值保存表对应的列
-----------------------------------------------------------------------------------------------------------------------------------
假定两个表用vname字段连接:
CREATE VIEW SCOTT.BBVIEW AS
select dtemp.vname as pvname, dtemp.vid, dtempMe.*
from dtemp,dtempMe
where dtemp.vname = dtempme.vname;
--对view,每次只能向一个基表中插入数据
--在表dtemp中插入一行
insert into bbview (pvname, vid)
values ('101','102');
--在表dtempme中插入一行,vname相同
insert into bbview (vname, vss)
values ('101','102');
-----------------------------------------------------------------------------------------------------------------------------------
我找了一下资料,不知对你有没有启发:
(通过触发器的INSTEAD OF来修改视图)
CREATE TABLE customers_sj
(
cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2)
);
CREATE TABLE customers_pa
(
cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2)
);
CREATE TYPE customer_t AS OBJECT
(
cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2),
location VARCHAR2(20)
);
CREATE VIEW all_customers (cust)
AS SELECT customer_t (cust, address, credit, ’SAN_JOSE’)
FROM customers_sj
UNION ALL
SELECT customer_t (cust, address, credit, ’PALO_ALTO’)
FROM customers_pa;
CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers
FOR EACH ROW
BEGIN
IF (:new.cust.location = ’SAN_JOSE’) THEN
INSERT INTO customers_sj
VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit);
ELSE
INSERT INTO customers_pa
VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit);
END IF;
END;
-----------------------------------------------------------------------------------------------------------------------------------
还是被我弄出来了:
SQL> CREATE TABLE DTEMP (
2 VNAME VARCHAR2(20) NOT NULL PRIMARY KEY,
3 VID VARCHAR2(20) NOT NULL
4 )
5 ;
表已创建。
SQL> CREATE TABLE DTEMPME (
2 VNAME VARCHAR2(20) NOT NULL PRIMARY KEY,
3 VSS VARCHAR2(20) NOT NULL
4 );
表已创建。
SQL> CREATE VIEW BBVIEW AS
2 select dtemp.vname as pvname, dtemp.vid, dtempMe.* from dtemp,dtempMe where dtemp.vname=dtemp
me.vname;
视图已建立。
----------
建一个TRIGGER:
create or replace trigger BBTrigger
instead of insert on bbview
for each row
declare
-- local variables here
begin
insert into sa.dtemp values(:new.pvname,:new.vid);
insert into sa.DTEMPME values(:new.vname,:new.VSS);
end BBTrigger;
------------
SQL> insert into bbview (pvname, vname, vid, vss) values ('101','102','102','101');
已创建 1 行。
SQL> select *From dtemp;
VNAME VID
-------------------- --------------------
101 102
SQL> select *From DTEMPME;
VNAME VSS
-------------------- --------------------
102 101
SQL>
-------------------------------------------------------
OK!