-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
Views Types of view
Syntax to create view: Create view < view_name> What is the need for view? Lets say I want to display the rows from emp which satisfies following conditions So, your query should be If you always want to retrieve the data which satisfies the above three conditions, better to create a view so that your work is simplified. Ex: View created. Select * from v1; Table which is used for creating the view is called as base table.
When views are created using one base table it is called simple view. Few more examples Create view v10 Create view v20 Create view v30 I am logically classifying the data of emp table into 3 views ie v10, v20, v30 So, when I write
Similary Select * from v20;
Select * from v30;
All the above views as they are created using one base table, it is Simple views. Can we perform DML operations on simple view? Insert into v30 values ( 1111, ‘AAA’, ‘CLERK’ , 7698 , ’11-jan-1981’, 1000, 100, 30); The response is So, we can perform DML operations on simple views. Similarly, when we update the data in the simple view , the base table is updated. Note:
Can we see the list of all the view? Select view_name from user_views; Look at this example Create view v35 Select * from v35;
Now, I want to add new column JOB in the view V35. Can we add or drop columns in a view? Ex Select * from v35; Output
By using create or replace, we can add columns, remove columns as well as change where conditions of the view.
When views are created using multiple base tables, it is called complex views. Ex: Create view v40 When we write
The view v40, is created using more than one base table, it is a complex view.
Note: When a view is created using arithmetic operations or functions or group by clause, it is also called as complex views. Ex: Select * from v50;
Annual_Sal column is not present in the base table, we are calculating it in the view. Lets have another example of complex view
Select * from v60;
V60, is also an example of complex views. As we have used group functions / group by clause. Note:
We can only read the view. Ex: Now, we cannot perform insert, update and delete operations on View v70.
DML operations are allowed only when where clause is satisfied. Ex: Select * from v80; Output
Consider the two insert commands Consider the two update commands Update v80 set sal = 3100 Update v80 set sal = 1000 When we perform DML operations on WITH CHECK OPTION views, it validates the where clause. DML operations are allowed only when WHERE clause is satisfied. |