Updating oracle views
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: With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view.
Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
You can only create a view with errors by using the FORCE option of the CREATE VIEW command: CREATE FORCE VIEW AS ...; When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID.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.select e.empno,e.ename,e.sal,e.deptno,d.dname,From emp e, dept d where e.deptno=d.deptno; So everytime we want to see emp details and department names where they are working we have to give a long join query.Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below create view emp_det as select e.empno, e.ename,e.sal,e.deptno,d.dname,from emp e, dept d where e.deptno=d.deptno; Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.
You can clearly see that I did not do such a good job with this. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement.