Oracle-----View

View Introduction:  
views are logical tables that are based on a table or multiple tables or views, and do not contain data in it, which allows you to query and modify the data in the table. The table that the view is based on is called the base table. A view is a SELECT statement that is stored in a data dictionary. You can extract the logical collection or combination of data by creating a view.  

View advantages:  
1. Access to the database, because the view can be selected as part of a selective database.  
2. Users can get results from complex queries through simple queries.  
3. Maintain data independence and attempt to retrieve data from multiple tables.  
4. Different views can be generated for the same data.  

View classification:  
views are divided into simple and complex views.  

The difference is as follows:  
1. Simple views get data from only one table, complex views get data from multiple tables,  
2. Simple views do not contain functions and data groups, complex views contain;  
3. A simple view implements DML operations, and complex views are not available.  

View:  

Create [OR REPLACE] [force| Noforce] VIEW view_name [(alias[, alias] ...)  
as subquery 
[with CHECK OPTION [CONSTRAINT constraint]] 
[with READ only] 
Where:  
OR REPLACE: Oracle automatically rebuilds the view if it is created,  
Force: The view is automatically created by Oracle regardless of the existence of the base table;  
Noforce: Only the base table exists for Oracle to create the view:  
Alias: Alias for the column defined for the view;  
Subquery: A complete SELECT statement in which you can define an alias; 
with CHECK OPTION: The data row that is inserted or modified must meet the constraints defined by the view;  
with READ only: No DML operations can be performed on this view.  

For example:

1 CREATE  OR  REPLACE  VIEWDEPT_SUM_VW2 (name,minsal,maxsal,avgsal)3  as SELECTD.dname,min(E.sal),Max(E.sal),avg(e.sal)4  fromEMP e,dept D5 WHEREE.deptno=D.deptno6 GROUP   byD.dname;

The definition principle of a view:
1. View queries can use complex select syntax, including connection/grouping queries and subqueries;
2. The ORDER by clause cannot be used in a query without the WITH CHECK option and READ only;
3. If you do not name the CHECK option constraint, the system will automatically name it, in the form SYS_CN;
The 4.OR replace option allows you to change the definition and rebuild, or re-grant object permissions without deleting the original view.

Query view:
Once the view is created successfully, you can retrieve the data from the view, just as you would retrieve data from the table.
Example:
Sql>select * from DEPT_SUM_VW;

To modify a view:
Re-create a view with the same name with or REPLACE.

To delete a view:
Drop View view_name statement to delete views.
Deleting a view's definition does not affect the data in the base table.
Only the view owner and the user with the Drop view permission can delete the view.
After the view is deleted, other views or apps that are based on the deleted view will be invalid.

Query View definition:
SELECT View_name,text from User_views;
Where text is displayed is the SELECT statement defined by the view, which is available through the DESC user_views
Get the relevant information.

DML operations on the view:
The principles that DML operations should follow:
1. A simple view can perform DML operations;
2. In the view contains the group function, the GROUP BY clause, the DISTINCT keyword cannot delete the data row;
3. You can modify the base table data or insert data from a view without the following:

A. The view contains the group function, the GROUP BY clause, and the DISTINCT keyword;
B. Columns defined with an expression;
C.rownum pseudo-Columns.
D. Other columns in the base table that are not selected in the view are defined as non-null and have no default values.
With CHECK OPTION clause
The inserts and updates operations performed through the view cannot create a data row that the view cannot retrieve because it performs integrity constraints and data validation checks on the data rows that are inserted or modified. (That is, when performing inserts, updates, the Where condition requires addition of the insert, the update itself, plus the Where condition when the view is created.) ）

For example:

CREATE OR REPLACE VIEW vw_emp20
As SELECT * from EMP
WHERE deptno=20
With CHECK OPTION constraint vw_emp20_ck;
The view is already established.

Query Result:

SELECT empno,ename,job from Vw_emp20;
EMPNO ename JOB
---------------------           --------------          -------------
7369 SMITH Clerk
7566 JONES MANAGER
7902 FORD ANALYST
Modify:

UPDATE VW_EMP20
SET deptno=20
WHERE empno=7902;
An error will be generated:

UPDATE VW_EMP20
*
The ERROR is on the first line:
ORA-01402: View with CHECK OPTION violates the WHERE clause

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1,oracle is possible to modify the base table through a view. The so-called base table is the table used to build the view, which is the data source table for the view. But this modification is conditional. Like what:
CREATE View v_emp as select Empno,ename,job,deptno from emp where deptno=10 with CHECK option constraint Emp_cnst;
If there is this restriction, the value of the Deptno field that inserts data through the view v_emp must be 10, otherwise the exception "ORA-01402: View with CHECK OPTIDN violates the WHERE clause" is reported.

2, junction view:
CREATE View Dept1_staff as Select E.ename, E.empno, E.job, D.deptno, d.dname from emp e,dept D where E.deptno in (10,30) a nd e.deptno = D.deptno;
Linking the data of two tables should look like an inner junction (Inner joint).
The modification rules for the junction view (Joint view) are slightly more complex and are designed to the concept of so-called key_preserved table. The base table is modified by a join view, and only those key_preserved tables can be modified. The EMP is the Key_preserved table and dept is not when the EMP and dept in the CREATE VIEW statement form a view through Deptno. Why? Because the value empno in Dept1_staff is unique and deptno is not unique. So EMP is key_preserved and dept is not. Therefore, the EMP can only be modified through this view, and the Dept data cannot be modified.

One of the most powerful features of the 3,oracle view is that it can create a view with errors. For example, if the field in the view does not exist in the base table, the view can still be created successfully, but it is illegal and cannot be executed. When the base table is added to the field, or if a field is modified to the name of the field in the view, the view can become legal immediately. This is a very interesting feature.
Example:
Creating a base table: Create TABLE V_test (name VARCHAR2 (+), age number (12));
Create a view with errors:
Create force view view_test as select Name,age,address from V_test; (note plus force option)
Because the address field does not exist in V_test, it will be reported Warning:view created with compilation errors warning, and execute select * from View_test; "ORA-04063: View "SCOTT. View_test "There is an error" exception.
But if you add the Address field to the V_test, the view will be legal.
To modify a base table:
ALTER TABLE v_test Add (address varchar2 (128));

Now execute the SELECT * from View_test; it will execute successfully.

From:http://www.blogjava.net/jinhualee/archive/2006/07/14/58115.html

Oracle-----View