The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (199, 'ABID', 30); Restrictions on DML operations for views use the following criteria in the order listed: The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows that have a department number of 10 to be inserted into, or updated in, the EMP table.
Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that is, excluding the DEPTNO column): CREATE VIEW Accounts_staff AS SELECT Empno, Ename FROM Emp WHERE Deptno = 10 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the view does not let you alter the DEPTNO field. Loc IN ('HYD', 'BOM', 'DEL'); then there are restrictions on modifying either the EMP or the DEPT base table through this view.
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.
If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid.
Oracle dynamically compiles the invalid view if you attempt to use it.
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.
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.