??? 今天在操作視圖的時候發(fā)生了一個錯誤：ORA-01732: data manipulation operation not legal on this view
?
??? 其實視圖的更新是有很多的
限制的，例如不能有聚合函數、分析函數、排序函數等等，而且如果兩個表關聯后的視圖，則只能更新第一個表的字段等等。具體的說明可以在《SQL Reference》里找到，如下：
?
Notes on Updatable Views The following notes apply to updatable views:
An updatable view is one you can use to insert, update, or delete base table rows. Youcan create a view to be inherently updatable, or you can create an INSTEAD OF triggeron any view to make it updatable.
To learn whether and in what ways the columns of an inherently updatable view canbe modified, query the USER_UPDATABLE_COLUMNS data dictionary view. Theinformation displayed by this view is meaningful only for inherently updatable views.
For a view to be inherently updatable, the following conditions must be met:
?
■ Each column in the view must map to a column of a single table. For example, if aview column maps to the output of a TABLE clause (an unnested collection), thenthe view is not inherently updatable.
?
■ The view must not contain any of the following constructs:
??? A set operator
??? A DISTINCT operator
??? An aggregate or analytic function
??? A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
??? A collection expression in a SELECT list
??? A subquery in a SELECT list
??? A subquery designated WITH READ ONLY
??? Joins, with some exceptions, as documented in Oracle Database Administrator'sGuide
?
■ In addition, if an inherently updatable view contains pseudocolumns orexpressions, then you cannot update base table rows with an UPDATE statementthat refers to any of these pseudocolumns or expressions.
?
■ If you want a join view to be updatable, then all of the following conditions mustbe true:
??? – The DML statement must affect only one table underlying the join.
??? – For an INSERT statement, the view must not be created WITH CHECK OPTION,?and all columns into which values are inserted must come from akey-preserved table. A key-preserved table is one for which every primarykey or unique key value in the base table is also unique in the join view.
??? – For an UPDATE statement, the view must not be created WITH CHECK OPTION,and all columns updated must be extracted from a key-preserved table.
■ For a DELETE statement, if the join results in more than one key-preserved table,then Oracle Database deletes from the first table named in the FROM clause,whether or not the view was created WITH CHECK OPTION.
?
?
?
??? 另外文檔里還列舉了一些例子來說明，也摘錄一下：
?
Creating a View: Example? The following statement creates a view of the sample table employees named emp_view. The view shows the employees in department 20 and their annual salary:
?
CREATE VIEW emp_view AS
?? SELECT last_name, salary*12 annual_salary
?? FROM employees
?? WHERE department_id = 20;
?
The view declaration need not define a name for the column based on the expression salary*12, because the subquery uses a column alias (annual_salary) for this expression.
?
Creating a View with Constraints: Example The following statement creates a restricted view of the sample table hr.employees and defines a unique constraint on the email view column and a primary key constraint for the view on the emp_id view column:
?
CREATE VIEW emp_sal (emp_id, last_name,
???? email UNIQUE RELY DISABLE NOVALIDATE,
?? CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE)
?? AS SELECT employee_id, last_name, email FROM employees;
?
Creating an Updatable View: Example The following statement creates an updatable view named clerk of all 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 kind 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;
?
Creating a Join View: Example A join view is one whose view subquery contains a join. If at least one column in the join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable. For example:
?
CREATE VIEW locations_view AS
?? SELECT d.department_id, d.department_name, l.location_id, l.city
?? FROM departments d, locations l
?? WHERE d.location_id = l.location_id;
?
SELECT column_name, updatable
?? FROM user_updatable_columns
?? WHERE table_name = 'LOCATIONS_VIEW';
?
COLUMN_NAME??????????????????? UPD
------------------------------ ---
DEPARTMENT_ID????????????????? YES
DEPARTMENT_NAME????????????????YES
LOCATION_ID????????????????????NO
CITY???????????????????????????NO
?
In the preceding example, the primary key index on the location_id column of the locations table is not unique in the locations_view view. Therefore, locations is not a key-preserved table and columns from that base table are not updatable.
?
INSERT INTO locations_view VALUES
?? (999, 'Entertainment', 87, 'Roma');
INSERT INTO locations_view VALUES
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
?
You can insert, update, or delete a row from the departments base table, because all the columns in the view mapping to the departments table are marked as updatable and because the primary key of departments is retained in the view.
INSERT INTO locations_view (department_id, department_name)
?
VALUES (999, 'Entertainment');
1 row created.
?
Note: For you to insert into the table using the view, the view must contain all NOT NULL columns of all tables in the join, unless you have specified DEFAULT values for the NOT NULL columns.
Creating a Read-Only View: Example The following statement creates a read-only view named customer_ro of the oe.customers table. Only the customers' last names, language, and credit limit are visible in this view:
?
CREATE VIEW customer_ro (name, language, credit)
????? AS SELECT cust_last_name, nls_language, credit_limit
????? FROM customers
????? WITH READ ONLY;?
?
Creating an Object View: Example The following example shows the creation of the type inventory_typ in the oc schema, and the oc_inventories view that is based on that type:
?
CREATE TYPE inventory_typ
?OID '82A4AF6A4CD4656DE034080020E0EE3D'
?AS OBJECT
??? ( product_id NUMBER(6)
??? , warehouse warehouse_typ
??? , quantity_on_hand NUMBER(8)
??? ) ;
/
CREATE OR REPLACE VIEW oc_inventories OF inventory_typ
?WITH OBJECT OID (product_id)
?AS SELECT i.product_id,
?????????? warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
?????????? i.quantity_on_hand
????? FROM inventories i, warehouses w
????? WHERE i.warehouse_id=w.warehouse_id;
?
?
?
??? 看過文檔之后，一些具體的例子都比較顯而易見了，就不舉了，主要是如果拿不準能不能操作的時候，可以查詢一下USER_UPDATABLE_COLUMNS ，是個很不錯的途徑。
?
?
-The End-