Mastering Oracle SQL学习笔记(join句法专题第八部份)

2008-06-14 16:38:05

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

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

本文出自 “感性V理性” 博客，请务必保留此出处http://longsoft.blog.51cto.com/378540/82043

昵称：
验证码：		点击图片可刷新验证码　　博客过2级，无需填写验证码
内容：