如题,可以对一个view里的数据进行update么?
类似于下边这种。
UPDATE (
SELECT
DEPT_NO, USER_NAME
FROM USER_MASTER
WHERE DEPT_NO = '1002'
) view_1
SET
USER_NAME = USER_NAME || ' 氏',
MODIFIED_ON = SYSDATE如果可以更新的话,那对view实际关联的本表由什么影响。以后每次执行view,是update后固定的数据,还是于本表关联的数据?
类似于下边这种。
UPDATE (
SELECT
DEPT_NO, USER_NAME
FROM USER_MASTER
WHERE DEPT_NO = '1002'
) view_1
SET
USER_NAME = USER_NAME || ' 氏',
MODIFIED_ON = SYSDATE如果可以更新的话,那对view实际关联的本表由什么影响。以后每次执行view,是update后固定的数据,还是于本表关联的数据?
DEPT_NO, USER_NAME
FROM USER_MASTER
WHERE DEPT_NO = '1002'
如果是这样的视图,当然可以更新,这是明显的简单表。
o A set operator
o A DISTINCT operator
o An aggregate or analytic function
o A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
o A collection expression in a SELECT list
o A subquery in a SELECT list
o Joins (with some exceptions as described in the paragraphs that follow).
* In addition, if an inherently updatable view contains pseudocolumns or expressions, you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
* If you want a join view to be updatable, all of the following conditions must be true:
o The DML statement must affect only one table underlying the join.
o For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table in one for which every primary key or unique key value in the base table is also unique in the join view.
o For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
* For a DELETE statement, if the join results in more than one key-preserved table, then Oracle deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.Creating an Updatable View: ExampleThe following statement creates an updatable view named clerk of all sales and purchasing clerks in the employees table. Only the employees' IDs, last names, department numbers, and jobs are visible in this view, and these columns can be updated only in rows where the employee is a king of clerk:CREATE VIEW clerk AS
SELECT employee_id, last_name, department_id, job_id
FROM employees
WHERE job_id = 'PU_CLERK'
or job_id = 'SH_CLERK'
or job_id = 'ST_CLERK';This view lets you change the job_id of a purchasing clerk to purchasing manager (PU_MAN):UPDATE clerk SET job_id = 'PU_MAN' WHERE employee_id = 118;The next example creates the same view WITH CHECK OPTION. You cannot subsequently insert a new row into clerk if the new employee is not a clerk. You can update an employee's job_id from one type of clerk to another type of clerk, but the update in the preceding statement would fail, because the view cannot access employees with non-clerk job_id.CREATE VIEW clerk AS
SELECT employee_id, last_name, department_id, job_id
FROM employees
WHERE job_id = 'PU_CLERK'
or job_id = 'SH_CLERK'
or job_id = 'ST_CLERK'
WITH CHECK OPTION;
就lz得这个例子而言,如果你的set子句中没有MODIFIED_ON = SYSDATE 的话,则是可以更新的。
view是不能更新的,但是如果是类似与LZ这样的写法应该是可以的,不过如ls所说,MODIFIED_ON这里是不能update的。
----------
1OPER@tl> create view v_test as select * from test;视图已创建。OPER@tl> select * from v_test; AAA
----------
1OPER@tl> update v_test set aaa=999;已更新 1 行。OPER@tl> commit;提交完成。OPER@tl> select * from test; AAA
----------
999OPER@tl> select * from v_test; AAA
----------
999OPER@tl>
谢谢指教,我对view的update的概念确实是混淆了我做的实验,
是两个表的view,update不行,同时我根据错误信息cannot modify a column which maps to a non key-preserved table详细的查了查,
莫些情况下的view也是可以update的。
说不能更新的朋友情自己上测试环境试试再说。