VIEW
VIEW A view is a virtual table with the result of a stored query as its “contents,”
which are derived each time you access the view.
the result of a query is volatile
a view is nothing more than a query with a given name
A view is a virtual table: Every view has a name, and that’s why views are also referred to
as named queries. Views have columns, each with a name and a datatype, so you can execute
queries against views, and you can manipulate the “contents” of views(with some
restrictions) with INSERT, UPDATE, DELETE, and MERGE commands.
• A view is a virtual table: it only behaves like a table.Views don’t have any rows; You
define views as named queries, which are stored in the data dictionary; that’s why another
common term for views is stored queries. Each time you access the “contents” of a view, the
Oracle DBMS retrieves the view query from the data dictionary
and uses that query to produce the virtual table.
If you issue data manipulation commands against a view, the DBMS is supposed to
translate those commands
into corresponding actions against the underlying base tables. Note that some views are not
updatable;
Advantages of Views
Views simplify the writing of queries. You can query a single view instead of writing a
complicated SQL statement that joins many tables.
The complexity of the underlying SQL statement is hidden from the user and contained only
in the view.
Views are useful for security reasons because they can hide data. The data retrieved from a
view can show only certain columns if you list those columns in the SELECT list of the
query.
You can also restrict the view to display specific rows with the WHERE clause of the query.
In a view, you can give a column a different name from the one in the base table.
Views may be used to isolate an application from a change in the definition of the base
tables. Rather than change the program, you can make changes to the view.
A view looks just like any other table. You can describe and query the view and also issue
INSERT, UPDATE, and DELETE statements to a certain extent
View Creation
The simplified syntax for creating a view is as follows.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewname
[(column_alias[, column_alias]...)]
AS query
[WITH CHECK OPTION|WITH READ ONLY [CONSTRAINT constraintname]]
Views vs. Tables
SQL> create view dept20_v as
2 select * from employees where deptno = 20;
View created.
SQL> create table dept20_t as
2 select * from employees where deptno = 20;
Table created.
SQL>
Creating a View from a Query:
SQL> select e.empno,e.ename,m.ename from emp e,emp m
2 where e.mgr=m.empno;
EMPNO ENAME ENAME
---------- ---------- ----------
7902 FORD JONES
7788 SCOTT JONES
7900 JAMES BLAKE
7844 TURNER BLAKE
7654 MARTIN BLAKE
7521 WARD BLAKE
7499 ALLEN BLAKE
7934 MILLER CLARK
7876 ADAMS SCOTT
7782 CLARK KING
7698 BLAKE KING
EMPNO ENAME ENAME
---------- ---------- ----------
7566 JONES KING
7369 SMITH FORD
13 rows selected.
SQL> create view v_man as
2 select e.ename,m.ename from emp e,emp m
3 where e.mgr=m.empno;
select e.ename,m.ename from emp e,emp m
*
ERROR at line 2:
ORA-00957: duplicate column name
SQL> ed
Wrote file afiedt.buf
1 create view v_man as
2 select e.ename,m.ename as " is manager" from emp e,emp m
3* where e.mgr=m.empno
SQL> /
View created.
SQL> select * from v_man;
ENAME is manage
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
ENAME is manage
---------- ----------
JONES KING
SMITH FORD
13 rows selected.
SQL> desc v_man
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
is manager VARCHAR2(10)]
SQL> select ename,"is manager" from v_man;
select ename,"is manager" from v_man
*
ERROR at line 1:
ORA-00904: "is manager": invalid identifier
1* select " is manager" from v_man
SQL> /
is manage
----------
JONES
JONES
BLAKE
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
SCOTT
KING
KING
is manage
----------
KING
FORD
13 rows selected.
Replacing/Altering Views
To alter the definition of a view, you must replace the view using one of the following
methods:
A view can be dropped and then re-created. When a view is dropped, all grants of
corresponding view privileges are revoked from roles and users. After the view is recreated,
necessary privileges must be regranted.
A view can be replaced by redefining it with a CREATE VIEW statement that contains the
OR REPLACE option. This option replaces the current definition of a view, but preserves
the present security authorizations.
For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous
example.
You also grant several object privileges to roles and other users. However, now you realize
that
you must redefine the ACCOUNTS_STAFF view to correct the department number specified
in the
WHERE clause of the defining query, because it should have been 30. To preserve the grants
of
object privileges that you have made, you can replace the current version of the
ACCOUNTS_STAFF view with the following statement:
CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Replacing a view has the following effects:
Replacing a view replaces the view's definition in the data dictionary. All underlying
objects referenced by the view are not affected.
If previously defined but not included in the new view definition, then the constraint
associated with the WITH CHECK OPTION for a view's definition is dropped.
All views and PL/SQL program units dependent on a replaced view become invalid.
Altering a View
You can use the ALTER VIEW command to define, modify, or drop view constraints. Also,
the command ALTER VIEW viewname COMPILE command explicitly compiles the view to
make sure it is valid. A view may become invalid if the underlying table is altered or
dropped. If you use Oracle 11g, the view remains valid if you modify a column in the base
table that’s not being used by this view.
You use the ALTER VIEW command to recompile a view if it becomes invalid. This can
occur after you alter one of the base tables. The syntax of the ALTER VIEW
statement is as follows.
>ALTER VIEW viewname COMPILE
Renaming a View
The RENAME command allows you to change the name of a view.
>RENAME stud_enroll TO stud_enroll2
Dropping a View
To drop a view, you use the DROP VIEW command. The following statement drops the
STUD_ENROLL2 view.
>DROP VIEW stud_enroll2
SELECT text
FROM user_views
WHERE view_name = 'TEST_TAB_VIEW'
Forcing the Creation of a View
If a view’s base tables do not exist or if the creator of the view doesn’t have privileges to
access the view, the creation of the view fails. The following example shows the
creation of the view named TEST, based on a nonexistent SALES table.
CREATE VIEW test AS
SELECT *
FROM sales
ERROR at line 3:
ORA-00942: table or view does not exist
If you want to create the view, despite its being invalid, you can create it with the FORCE
option; the default in the CREATE VIEW syntax is NOFORCE. This FORCE option
is useful if you need to create the view and you add the referenced table later or if you expect
to obtain the necessary privileges to the referenced object shortly.
CREATE OR REPLACE FORCE VIEW test AS
SELECT *
FROM sales
Warning: View created with compilation errors.
SQL> CREATE OR REPLACE VIEW EmpSalDet
2 AS
3 SELECT Ename, Sal, NVL(Comm, 0) Comm, Sal + NVL(Comm, 0) TotSal, Deptno
4 FROM Emp;
View created.
SQL> DESC EmpSalDet
SQL> CREATE VIEW DeptSalSummary
2 (
3 DepartmentName,
4 MinimumSalary,
5 MaxSalary,
6 AverageSalary,
7 SalarySum
8 )
9 AS
10 SELECT
11 D.Dname,
12 MIN(E.Sal),
13 MAX(E.Sal),
14 AVG(E.Sal),
15 SUM(E.Sal)
16 FROM Emp E, Dept D
17 WHERE E.Deptno = D.Deptno
18 GROUP BY D.Dname;
View created.
SQL> DESC DeptSalSummary
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENTNAME VARCHAR2(14)
MINIMUMSALARY NUMBER
MAXSALARY NUMBER
AVERAGESALARY NUMBER
SALARYSUM NUMBER
SQL> SELECT * FROM DeptSalSummary;
DEPARTMENTNAME MINIMUMSALARY MAXSALARY AVERAGESALARY
SALARYSUM
-------------- ------------- ---------- ------------- ----------
ACCOUNTING 1300 5000 2916.66667 8750
RESEARCH 800 3000 2175 10875
SALES 950 2850 1566.66667 9400
SQL> CREATE VIEW InsertDept10
2 AS
3 SELECT *
4 FROM Emp
5 WHERE Deptno = 10;
View created.
SQL> SELECT Ename, Sal, Deptno FROm InsertDept10;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
SQL> CREATE TABLE Dept10
2 AS
3 SELECT *
4 FROM InsertDept10;
SQL> SELECT * FROM Dept10;
SQL> CREATE VIEW InsertDept
2 (
3 DeptID,
4 DeptName,
5 Place
6 )
7 AS
8 SELECT
9 Deptno,
10 Dname,
11 Loc
12 FROM Dept;
View created.
SQL> DESC InsertDept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NOT NULL NUMBER(2)
DEPTNAME VARCHAR2(14)
PLACE VARCHAR2(13)
SQL> SELECT * FROM InsertDept;
DEPTID DEPTNAME PLACE
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> INSERT INTO InsertDept
2 VALUES(50, 'SHIPPING', 'CHENNAI');
1 row created.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
SQL> INSERT INTO InsertDept
2 VALUES(60, 'CARGO', 'MUMBAI');
1 row created.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
60 CARGO MUMBAI
SQL> DELETE FROM InsertDept
2 WHERE DeptID = 60;
1 row deleted.
SQL> SELECT * FROM InsertDept;
DEPTID DEPTNAME PLACE
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
SQL> DELETE FROM InsertDept
2 WHERE DeptID = 30;
DELETE FROM InsertDept
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated - child record
found
1 UPDATE InsertDept
2 SET
3 DEPTNAME = 'MARINE'
4* WHERE DeptId = 50
SQL> SELECT * FROM InsertDept;
DEPTID DEPTNAME PLACE
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARINE CHENNAI
Data Manipulation Rules on Views
For a view to be updatable, it needs to conform to a number of rules. The view cannot
contain any of the following.
An expression (for example, TO_DATE(enroll_date))
An aggregate function
A set operator, such as UNION, UNION ALL, INTERSECT, or MINUS
The DISTINCT keyword
The GROUP BY clause
The ORDER BY clause
SQL> CREATE OR REPLACE VIEW EDept30
2 AS
3 SELECT *
4 FROM Emp
5 WHERE Deptno = 30
6 WITH CHECK OPTION CONSTRAINT EDept30ChkView;
View created.
SQL> INSERT INTO
2 EDept30(Empno, Ename, Deptno, Sal, MGR)
3 VALUES(1236, 'SAMPLE03', 20, 2000, 7566);
EDept30(Empno, Ename, Deptno, Sal, MGR)
*
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE VIEW EDept30
2 AS
3 SELECT *
4 FROM Emp
5 WHERE Deptno = 30
6* WITH READ ONLY
SQL> /
View created.
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM EDept30;
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
BLAKE 2850 30 MANAGER
MARTIN 1250 30 SALESMAN
ALLEN 2600 30 SALESMAN
TURNER 1500 30 SALESMAN
JAMES 950 30 CLERK
WARD 1250 30 SALESMAN
6 rows selected.
SQL> DELETE FROM EDept30
2 WHERE Empno = 7566;
DELETE FROM EDept30
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
Understanding the concept of key-preserved tables is essential to understanding the
restrictions on join views. A table is considered key preserved if every key of the table
can also be a key of the result of the join For a join view to be updatable, the DML operation
may affect only the key-preserved table (also known as the child base table), and the child’s
primary key must be included in the view’s definition.
If you are in doubt regarding which table is the key-preserved table, query the Oracle data
dictionary table USER_UPDATABLE_COLUMNS. The result shows you which
columns are updatable
SQL> UPDATE EDept30
2 SET Sal = 2000
3 WHERE Ename = 'ALLEN';
SET Sal = 2000
*
ERROR at line 2:
ORA-01733: virtual column not allowed here
“The Data Dictionary, Scripting, and Reporting.” If you need to manipulate key-preserved
data through a view, you overcome this limitation by using an INSTEAD OF trigger. This
trigger works only against views and allows you to manipulate data based on the code
within the trigger. The INSTEAD OF trigger fires in place of your issued INSERT,
UPDATE, or DELETE command against the view. For example, if you execute an INSERT
command against
the view, the statement may actually perform an UPDATE instead. The view’s associated
INSTEAD OF trigger code can perform any type of data manipulation against one or
multiple tables. You create these powerful INSTEAD OF triggers by using Oracle PL/SQL
Remember that any DDL operation, such as the creation of a view, cannot be rolled back,
and any prior DML operations, such as inserts, updates, and deletes, are automatically
committed.
Accessing an Invalid View
When you access an invalid view, Oracle attempts to recompile it automatically. It is useful
to explicitly compile to the view, as shown in the next command, to ensure that
there are no problems with the view after you make database changes
When a user attempts to reference an invalid view, Oracle 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).
A modifiable join view is a view that contains more than one table in the top-level FROM
clause
of the SELECT statement, and that does not contain any of the following:
DISTINCT operator
Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
Set operations: UNION, UNION ALL, INTERSECT, MINUS
GROUP BY or HAVING clauses
START WITH or CONNECT BY clauses
The following example shows an UPDATE statement that successfully modifies the
EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Sal = Sal * 1.10
WHERE Deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Loc = 'BOM'
WHERE Ename = 'SAMI';
This statement fails with an ORA-01779 error ("cannot modify a column which maps to a
non
key-preserved table"), because it attempts to modify the underlying DEPT table, and the
DEPT
table is not key preserved in the EMP_DEPT view.
In general, all modifiable columns of a join view must map to columns of a key-preserved
table.
If the view is defined using the WITH CHECK OPTION clause, then all join columns and all
columns
of repeated tables are not modifiable.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then
the
following UPDATE statement would fail:
UPDATE Emp_dept_view
SET Deptno = 10
WHERE Ename = 'SAMI';
The statement fails because it is trying to update a join column
VIEW A view is a virtual table with the result of a stored query as its “contents,”
which are derived each time you access the view.
the result of a query is volatile
a view is nothing more than a query with a given name
A view is a virtual table: Every view has a name, and that’s why views are also referred to
as named queries. Views have columns, each with a name and a datatype, so you can execute
queries against views, and you can manipulate the “contents” of views(with some
restrictions) with INSERT, UPDATE, DELETE, and MERGE commands.
• A view is a virtual table: it only behaves like a table.Views don’t have any rows; You
define views as named queries, which are stored in the data dictionary; that’s why another
common term for views is stored queries. Each time you access the “contents” of a view, the
Oracle DBMS retrieves the view query from the data dictionary
and uses that query to produce the virtual table.
If you issue data manipulation commands against a view, the DBMS is supposed to
translate those commands
into corresponding actions against the underlying base tables. Note that some views are not
updatable;
Advantages of Views
Views simplify the writing of queries. You can query a single view instead of writing a
complicated SQL statement that joins many tables.
The complexity of the underlying SQL statement is hidden from the user and contained only
in the view.
Views are useful for security reasons because they can hide data. The data retrieved from a
view can show only certain columns if you list those columns in the SELECT list of the
query.
You can also restrict the view to display specific rows with the WHERE clause of the query.
In a view, you can give a column a different name from the one in the base table.
Views may be used to isolate an application from a change in the definition of the base
tables. Rather than change the program, you can make changes to the view.
A view looks just like any other table. You can describe and query the view and also issue
INSERT, UPDATE, and DELETE statements to a certain extent
View Creation
The simplified syntax for creating a view is as follows.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewname
[(column_alias[, column_alias]...)]
AS query
[WITH CHECK OPTION|WITH READ ONLY [CONSTRAINT constraintname]]
Views vs. Tables
SQL> create view dept20_v as
2 select * from employees where deptno = 20;
View created.
SQL> create table dept20_t as
2 select * from employees where deptno = 20;
Table created.
SQL>
Creating a View from a Query:
SQL> select e.empno,e.ename,m.ename from emp e,emp m
2 where e.mgr=m.empno;
EMPNO ENAME ENAME
---------- ---------- ----------
7902 FORD JONES
7788 SCOTT JONES
7900 JAMES BLAKE
7844 TURNER BLAKE
7654 MARTIN BLAKE
7521 WARD BLAKE
7499 ALLEN BLAKE
7934 MILLER CLARK
7876 ADAMS SCOTT
7782 CLARK KING
7698 BLAKE KING
EMPNO ENAME ENAME
---------- ---------- ----------
7566 JONES KING
7369 SMITH FORD
13 rows selected.
SQL> create view v_man as
2 select e.ename,m.ename from emp e,emp m
3 where e.mgr=m.empno;
select e.ename,m.ename from emp e,emp m
*
ERROR at line 2:
ORA-00957: duplicate column name
SQL> ed
Wrote file afiedt.buf
1 create view v_man as
2 select e.ename,m.ename as " is manager" from emp e,emp m
3* where e.mgr=m.empno
SQL> /
View created.
SQL> select * from v_man;
ENAME is manage
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
ENAME is manage
---------- ----------
JONES KING
SMITH FORD
13 rows selected.
SQL> desc v_man
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
is manager VARCHAR2(10)]
SQL> select ename,"is manager" from v_man;
select ename,"is manager" from v_man
*
ERROR at line 1:
ORA-00904: "is manager": invalid identifier
1* select " is manager" from v_man
SQL> /
is manage
----------
JONES
JONES
BLAKE
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
SCOTT
KING
KING
is manage
----------
KING
FORD
13 rows selected.
Replacing/Altering Views
To alter the definition of a view, you must replace the view using one of the following
methods:
A view can be dropped and then re-created. When a view is dropped, all grants of
corresponding view privileges are revoked from roles and users. After the view is recreated,
necessary privileges must be regranted.
A view can be replaced by redefining it with a CREATE VIEW statement that contains the
OR REPLACE option. This option replaces the current definition of a view, but preserves
the present security authorizations.
For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous
example.
You also grant several object privileges to roles and other users. However, now you realize
that
you must redefine the ACCOUNTS_STAFF view to correct the department number specified
in the
WHERE clause of the defining query, because it should have been 30. To preserve the grants
of
object privileges that you have made, you can replace the current version of the
ACCOUNTS_STAFF view with the following statement:
CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Replacing a view has the following effects:
Replacing a view replaces the view's definition in the data dictionary. All underlying
objects referenced by the view are not affected.
If previously defined but not included in the new view definition, then the constraint
associated with the WITH CHECK OPTION for a view's definition is dropped.
All views and PL/SQL program units dependent on a replaced view become invalid.
Altering a View
You can use the ALTER VIEW command to define, modify, or drop view constraints. Also,
the command ALTER VIEW viewname COMPILE command explicitly compiles the view to
make sure it is valid. A view may become invalid if the underlying table is altered or
dropped. If you use Oracle 11g, the view remains valid if you modify a column in the base
table that’s not being used by this view.
You use the ALTER VIEW command to recompile a view if it becomes invalid. This can
occur after you alter one of the base tables. The syntax of the ALTER VIEW
statement is as follows.
>ALTER VIEW viewname COMPILE
Renaming a View
The RENAME command allows you to change the name of a view.
>RENAME stud_enroll TO stud_enroll2
Dropping a View
To drop a view, you use the DROP VIEW command. The following statement drops the
STUD_ENROLL2 view.
>DROP VIEW stud_enroll2
SELECT text
FROM user_views
WHERE view_name = 'TEST_TAB_VIEW'
Forcing the Creation of a View
If a view’s base tables do not exist or if the creator of the view doesn’t have privileges to
access the view, the creation of the view fails. The following example shows the
creation of the view named TEST, based on a nonexistent SALES table.
CREATE VIEW test AS
SELECT *
FROM sales
ERROR at line 3:
ORA-00942: table or view does not exist
If you want to create the view, despite its being invalid, you can create it with the FORCE
option; the default in the CREATE VIEW syntax is NOFORCE. This FORCE option
is useful if you need to create the view and you add the referenced table later or if you expect
to obtain the necessary privileges to the referenced object shortly.
CREATE OR REPLACE FORCE VIEW test AS
SELECT *
FROM sales
Warning: View created with compilation errors.
SQL> CREATE OR REPLACE VIEW EmpSalDet
2 AS
3 SELECT Ename, Sal, NVL(Comm, 0) Comm, Sal + NVL(Comm, 0) TotSal, Deptno
4 FROM Emp;
View created.
SQL> DESC EmpSalDet
SQL> CREATE VIEW DeptSalSummary
2 (
3 DepartmentName,
4 MinimumSalary,
5 MaxSalary,
6 AverageSalary,
7 SalarySum
8 )
9 AS
10 SELECT
11 D.Dname,
12 MIN(E.Sal),
13 MAX(E.Sal),
14 AVG(E.Sal),
15 SUM(E.Sal)
16 FROM Emp E, Dept D
17 WHERE E.Deptno = D.Deptno
18 GROUP BY D.Dname;
View created.
SQL> DESC DeptSalSummary
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENTNAME VARCHAR2(14)
MINIMUMSALARY NUMBER
MAXSALARY NUMBER
AVERAGESALARY NUMBER
SALARYSUM NUMBER
SQL> SELECT * FROM DeptSalSummary;
DEPARTMENTNAME MINIMUMSALARY MAXSALARY AVERAGESALARY
SALARYSUM
-------------- ------------- ---------- ------------- ----------
ACCOUNTING 1300 5000 2916.66667 8750
RESEARCH 800 3000 2175 10875
SALES 950 2850 1566.66667 9400
SQL> CREATE VIEW InsertDept10
2 AS
3 SELECT *
4 FROM Emp
5 WHERE Deptno = 10;
View created.
SQL> SELECT Ename, Sal, Deptno FROm InsertDept10;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
SQL> CREATE TABLE Dept10
2 AS
3 SELECT *
4 FROM InsertDept10;
SQL> SELECT * FROM Dept10;
SQL> CREATE VIEW InsertDept
2 (
3 DeptID,
4 DeptName,
5 Place
6 )
7 AS
8 SELECT
9 Deptno,
10 Dname,
11 Loc
12 FROM Dept;
View created.
SQL> DESC InsertDept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NOT NULL NUMBER(2)
DEPTNAME VARCHAR2(14)
PLACE VARCHAR2(13)
SQL> SELECT * FROM InsertDept;
DEPTID DEPTNAME PLACE
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> INSERT INTO InsertDept
2 VALUES(50, 'SHIPPING', 'CHENNAI');
1 row created.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
SQL> INSERT INTO InsertDept
2 VALUES(60, 'CARGO', 'MUMBAI');
1 row created.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
60 CARGO MUMBAI
SQL> DELETE FROM InsertDept
2 WHERE DeptID = 60;
1 row deleted.
SQL> SELECT * FROM InsertDept;
DEPTID DEPTNAME PLACE
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
SQL> DELETE FROM InsertDept
2 WHERE DeptID = 30;
DELETE FROM InsertDept
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated - child record
found
1 UPDATE InsertDept
2 SET
3 DEPTNAME = 'MARINE'
4* WHERE DeptId = 50
SQL> SELECT * FROM InsertDept;
DEPTID DEPTNAME PLACE
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARINE CHENNAI
Data Manipulation Rules on Views
For a view to be updatable, it needs to conform to a number of rules. The view cannot
contain any of the following.
An expression (for example, TO_DATE(enroll_date))
An aggregate function
A set operator, such as UNION, UNION ALL, INTERSECT, or MINUS
The DISTINCT keyword
The GROUP BY clause
The ORDER BY clause
SQL> CREATE OR REPLACE VIEW EDept30
2 AS
3 SELECT *
4 FROM Emp
5 WHERE Deptno = 30
6 WITH CHECK OPTION CONSTRAINT EDept30ChkView;
View created.
SQL> INSERT INTO
2 EDept30(Empno, Ename, Deptno, Sal, MGR)
3 VALUES(1236, 'SAMPLE03', 20, 2000, 7566);
EDept30(Empno, Ename, Deptno, Sal, MGR)
*
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE VIEW EDept30
2 AS
3 SELECT *
4 FROM Emp
5 WHERE Deptno = 30
6* WITH READ ONLY
SQL> /
View created.
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM EDept30;
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
BLAKE 2850 30 MANAGER
MARTIN 1250 30 SALESMAN
ALLEN 2600 30 SALESMAN
TURNER 1500 30 SALESMAN
JAMES 950 30 CLERK
WARD 1250 30 SALESMAN
6 rows selected.
SQL> DELETE FROM EDept30
2 WHERE Empno = 7566;
DELETE FROM EDept30
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
Understanding the concept of key-preserved tables is essential to understanding the
restrictions on join views. A table is considered key preserved if every key of the table
can also be a key of the result of the join For a join view to be updatable, the DML operation
may affect only the key-preserved table (also known as the child base table), and the child’s
primary key must be included in the view’s definition.
If you are in doubt regarding which table is the key-preserved table, query the Oracle data
dictionary table USER_UPDATABLE_COLUMNS. The result shows you which
columns are updatable
SQL> UPDATE EDept30
2 SET Sal = 2000
3 WHERE Ename = 'ALLEN';
SET Sal = 2000
*
ERROR at line 2:
ORA-01733: virtual column not allowed here
“The Data Dictionary, Scripting, and Reporting.” If you need to manipulate key-preserved
data through a view, you overcome this limitation by using an INSTEAD OF trigger. This
trigger works only against views and allows you to manipulate data based on the code
within the trigger. The INSTEAD OF trigger fires in place of your issued INSERT,
UPDATE, or DELETE command against the view. For example, if you execute an INSERT
command against
the view, the statement may actually perform an UPDATE instead. The view’s associated
INSTEAD OF trigger code can perform any type of data manipulation against one or
multiple tables. You create these powerful INSTEAD OF triggers by using Oracle PL/SQL
Remember that any DDL operation, such as the creation of a view, cannot be rolled back,
and any prior DML operations, such as inserts, updates, and deletes, are automatically
committed.
Accessing an Invalid View
When you access an invalid view, Oracle attempts to recompile it automatically. It is useful
to explicitly compile to the view, as shown in the next command, to ensure that
there are no problems with the view after you make database changes
When a user attempts to reference an invalid view, Oracle 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).
A modifiable join view is a view that contains more than one table in the top-level FROM
clause
of the SELECT statement, and that does not contain any of the following:
DISTINCT operator
Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
Set operations: UNION, UNION ALL, INTERSECT, MINUS
GROUP BY or HAVING clauses
START WITH or CONNECT BY clauses
The following example shows an UPDATE statement that successfully modifies the
EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Sal = Sal * 1.10
WHERE Deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Loc = 'BOM'
WHERE Ename = 'SAMI';
This statement fails with an ORA-01779 error ("cannot modify a column which maps to a
non
key-preserved table"), because it attempts to modify the underlying DEPT table, and the
DEPT
table is not key preserved in the EMP_DEPT view.
In general, all modifiable columns of a join view must map to columns of a key-preserved
table.
If the view is defined using the WITH CHECK OPTION clause, then all join columns and all
columns
of repeated tables are not modifiable.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then
the
following UPDATE statement would fail:
UPDATE Emp_dept_view
SET Deptno = 10
WHERE Ename = 'SAMI';
The statement fails because it is trying to update a join column
No comments:
Post a Comment