select * from emp_det; This will show same result as you have type the long join query.Now you can treat this EMP_DET view same as any other table.So every time you have to give a DML or Select statement you have to give a WHERE condition like ..... To avoid this, you can create a view as given below CREATE VIEW accounts_staff AS SELECT Empno, Ename, Deptno FROM Emp WHERE Deptno = 10 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Now to see the account people you don’t have to give a query with where condition you can just type the following query.
To alter the definition of a view, you must replace the view using one of the following methods: 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.
The query that defines the ACCOUNTS_STAFF view references only rows in department 10.
Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select.
For example: SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW'; This might return: OWNER TABLE_NAME COLUMN_NAM UPD---------- ---------- ---------- ---SCOTT EMP_DEPT EMPNO NOSCOTT EMP_DEPT ENAME NOSCOTT EMP_DEPT DEPTNO NOSCOTT EMP_DEPT DNAME NOSCOTT EMP_DEPT LOC NO 5 rows selected.