|
Page 1 of 3
Oracle 10g Free Training - Updating Views
This tutorial teachs you
- how to use Views in Queries along with Restrictions on DML operations for Views.
- how to update a join View along with rules for updatable join views.
- how to use DML Statements on Join Views
- how to Update Views that Involve Outer Joins
Using Views in Queries
To issue a query or an INSERT, UPDATE, or DELETE statement against a view, you must have the SELECT, INSERT, UPDATE, or DELETE object privilege for the view, respectively, either explicitly or through a role.
Views can be queried in the same manner as tables. For example, to query the Division1_staff view, enter a valid SELECT statement that references the view:
SELECT * FROM Division1_staff;
|
ENAME
|
EMPNO
|
JOB
|
DNAME
|
|
CLARK
|
7782
|
MANAGER
|
ACCOUNTING
|
|
KING
|
7839
|
PRESIDENT
|
ACCOUNTING
|
|
MILLER
|
7934
|
CLERK
|
ACCOUNTING
|
|
ALLEN
|
7499
|
SALESMAN
|
SALES
|
|
WARD
|
7521
|
SALESMAN
|
SALES
|
|
JAMES
|
7900
|
CLERK
|
SALES
|
|
TURNER
|
7844
|
SALESMAN
|
SALES
|
|
MARTIN
|
7654
|
SALESMAN
|
SALES
|
|
BLAKE
|
7698
|
MANAGER
|
SALES
|
With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the emp_tab table using the sales_staff view:
INSERT INTO Sales_staff
VALUES (7954, 'OSTER', 30);
Restrictions on DML operations for views use the following criteria in the order listed:
1. If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.
2. If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.
3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a row cannot be inserted into the base table using the view.
4. If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", ...), then rows cannot be inserted into or updated in the base table using the view.
The constraint created by WITH CHECK OPTION of the sales_staff view only allows rows that have a department number of 10 to be inserted into, or updated in, the emp_tab table. Alternatively, assume that the sales_staff view is defined by the following statement (that is, excluding the deptno column):
CREATE VIEW Sales_staff AS
SELECT Empno, Ename
FROM Emp_tab
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT Sales_staff_cnst;
Considering this view definition, you can update the empno or ename fields of existing records, but you cannot insert rows into the emp_tab table through the sales_staff view because the view does not let you alter the deptno field. If you had defined a DEFAULT value of 10 on the deptno field, then you could perform inserts.
When a user attempts to reference an invalid view, the database returns an error message to the user:
ORA-04063: view 'view_name' has errors
This error message is returned when a view exists but is unusable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped).
Updating a Join View
An updatable join view (also referred to as a modifiable join view) is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.
The rules for updatable join views are as follows. Views that meet this criteria are said to be inherently updatable.
|
Rule
|
Description
|
|
General Rule
|
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.
|
|
UPDATE Rule
|
All updatable columns of a join view must map to columns of a key-preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable.
|
|
DELETE Rule
|
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
|
|
INSERT Rule
|
An INSERT statement must not explicitly or implicitly refer to the columns of a non-key-preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.
|
There are data dictionary views that indicate whether the columns in a join view are inherently updatable.
Note:
There are some additional restrictions and conditions which can affect whether a join view is inherently updatable.
If a view is not inherently updatable, it can be made updatable by creating an INSTEAD OF trigger on it.
Additionally, if a view is a join on other nested views, then the other nested views must be mergeable into the top level view. For a discussion of mergeable and unmergeable views, and more generally, how the optimizer optimizes statements referencing views.
Examples illustrating the rules for inherently updatable join views, and a discussion of key-preserved tables, are presented in succeeding sections. The examples in these sections work only if you explicitly define the primary and foreign keys in the tables, or define unique indexes. The following statements create the appropriately constrained table definitions for emp and dept.
CREATE TABLE dept (
deptno NUMBER(4) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13));
CREATE TABLE emp (
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));
You could also omit the primary and foreign key constraints listed in the preceding example, and create a UNIQUE INDEX on dept (deptno) to make the following examples work.
|
