用数据字典视图获得可更新的列

 

Oracle 提供了一种数据字典视图 USER_UPDATABLE_COLUMNS 它可列出用户定义的表或视图中可以被修改的所有列。这对我们是非常有用的：当你有一个视图你想对其进行更新操作，但是你又不确定哪些列是可以更新的，可时你就可以用数据字典视图 USER_UPDATABLE_COLUMNS ：
DESC USER_UPDATABLE_COLUMNS

 

 

 

 Name           Null?    Type

 

 -------------- -------- -------------

 

 OWNER          NOT NULL VARCHAR2(30)

 

 TABLE_NAME     NOT NULL VARCHAR2(30)

 

 COLUMN_NAME    NOT NULL VARCHAR2(30)

 

 UPDATABLE               VARCHAR2(3)

 

 INSERTABLE              VARCHAR2(3)

 

 DELETABLE               VARCHAR2(3)

 

看一个例子：

 

SELECT * FROM USER_UPDATABLE_COLUMNS

 

WHERE TABLE_NAME = 'V_RTLR_EMP';

 

 

 

OWNER   TABLE_NAME    COLUMN_NAME      UPD INS DEL

 

------- ------------- ---------------- --- ---

 

DEMO    V_RTLR_EMP    RTLR_NBR         YES YES YES

 

DEMO    V_RTLR_EMP    NAME             YES YES YES

 

DEMO    V_RTLR_EMP    CITY             YES YES YES

 

DEMO    V_RTLR_EMP    EMP_ID           NO  NO  NO

 

DEMO    V_RTLR_EMP    SALESPERSON_ID   YES YES YES

 

DEMO    V_RTLR_EMP    SALES_REP        NO  NO  NO

 

除此之外还有 ALL_UPDATABLE_COLUMNS 可以列出所有你可以访问的视图的可编辑列， DBA_UPDATABLE_COLUMNS( 只有 DBA 可以使用 ) 可以列出数据库中所有视图的可编辑列。

 

 

2.7.1 用 WITH CHECK OPTION 的影响

 

WITH CHECK OPTION 是创建视图指令的一个选项指令，它可以防止你对不存在于视图中的数据做任何更新。举例，如果你有一个视图像下面的定义：

CREATE VIEW emp_20 AS

 

SELECT * FROM employee

 

WHERE dept_id = 20

 

WITH CHECK OPTION;

 

对这个视图进行作操时，你不能插入 dept_id=30 的行 , 也不能把已经存在的行的 dept_id 更新成 30;

 

INSERT INTO emp_20 VALUES

 

(8765, 'SANJAY','MISHRA', 30, 7656, 4000, '01-JAN-88', 765);

 

INSERT INTO emp_20 VALUES

 

            *

 

ERROR at line 1:

 

ORA-01402: view WITH CHECK OPTION where-clause violation

 

 

 

UPDATE emp_20 SET dept_id = 30;

 

UPDATE emp_20 SET dept_id = 30

 

       *

 

ERROR at line 1:

 

ORA-01402: view WITH CHECK OPTION where-clause violation

 

因为被 WITH CHECK OPTION 定义的视图 而这个视图 emp_20 中只有 dept_id=20 的数据 , 所以不允许对此视图中不存在的数据进行更新 !

 

如果连接视图 (join view) 创建时使用了 WITH CHECK OPTION 定义 , 则该连接视图不允许插入操作 , 即使你是对键保护 ( key-preserved) 表进行插入 :

CREATE VIEW v_rtlr_emp_wco AS

 

SELECT c.rtlr_nbr, c.name, c.city, c.salesperson_id, e.lname sales_rep

 

FROM retailer c JOIN employee e

 

ON c.salesperson_id = e.emp_id

 

WITH CHECK OPTION;

 

 

 

View created.

 

 

 

INSERT INTO v_rtlr_emp_wco (rtlr_nbr, name, salesperson_id)

 

VALUES (345, 'X-MART STORES', 7820);

 

INSERT INTO v_rtlr_emp_wco (rtlr_nbr, name, salesperson_id)

 

                            *

 

ERROR at line 1:

 

ORA-01733: virtual column not allowed here

 

 

WITH CHECK OPTION 对于连接视图的删除操作不保护 :

 

DELETE FROM v_rtlr_emp_wco

 

WHERE rtlr_nbr = 215;

 

1 row deleted.

 

然而如果你的连接视图中是自我连接 (self join) 的键保护 ( key-preserved) 表 , 则不能删除 :

 

CREATE VIEW emp_mgr_wco AS

 

SELECT e.lname employee, e.salary salary, m.lname manager

 

FROM employee e, employee m

 

WHERE e.manager_emp_id = m.emp_id

 

WITH CHECK OPTION;

 

 

 

View created.

 

 

 

DELETE FROM emp_mgr_wco WHERE employee = 'JONES';

 

DELETE FROM emp_mgr_wco WHERE employee = 'JONES'

 

            *

 

ERROR at line 1:

 

ORA-01752: cannot delete from view without exactly one key-preserved table

 

如果一个连接视图创建时使用了 WITH CHECK OPTION 刚你不可以对视图中的任何列进行修改 :

 

UPDATE v_rtlr_emp_wco

 

SET salesperson_id = 7784

 

WHERE rtlr_nbr = 215;

 

SET salesperson_id = 7784

 

    *

 

ERROR at line 2:

 

ORA-01733: virtual column not allowed here

 

在上面的例子上 , 显然是报错了 , 同样在自我连接 (self join) 的键保护 ( key-preserved) 表创建的视图中使用更新操作会得到同样的错误 :

 

UPDATE emp_mgr_wco

 

SET salary = 4800

 

WHERE employee = 'JONES';

 

SET salary = 4800

 

    *

 

ERROR at line 2:

 

ORA-01733: virtual column not allowed here