Updating views in oracle

Posted by / 15-Apr-2016 02:08

Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10); However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30); A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.

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.

Whatever DML operations you performed on a view they actually affect the base table of the view. You can Query, Insert, Update and delete from views, just as any other table.

A view derives its data from the tables on which it is based. Views can be based on actual tables or another view also.

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.

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.select * from accounts_staff;select sum(sal) from accounst_staff; select max(sal) from accounts_staff; As you can see how views make things easier.For example, suppose all the employee working in Department No.10 belongs to accounts department and most of the time you deal with these people.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.

updating views in oracle-62updating views in oracle-14updating views in oracle-35

One thought on “updating views in oracle”