About Oracle Views

Definition of a view

View, also called a virtual table, does not occupy physical space, which is also a relative concept, because the definition statement of the view itself is still stored in the data dictionary. The view has only a logical definition. Every time you use it, you just rerun SQL.

Views are obtained from one or more actual tables, and the data for those tables is stored in the database. The table that is used to produce the view is called the base table for the view. One view can also be generated from another view.

The definition of a view exists in the database, and the data associated with this definition is not stored in the database. The data you see through the view is stored in the base table.

The view looks very much like the physical table of the database, and it operates like any other table. When you modify data through a view, you are actually changing the data in the base table; Conversely, changes to the base table data are automatically reflected in the view produced by the base table. For logical reasons, some Oracle views can modify the corresponding base table, and some cannot (only query).

There is also a view: materialized views (materialized view), also known as manifested views, snapshots (8i ago), which contain data, occupy storage space.

Second, the role of the view

1. Simple nature.

What you see is what you need. Views can not only simplify the user's understanding of the data, but also simplify their operations. Queries that are frequently used can be defined as views so that users do not have to specify all the conditions for subsequent operations.

2, security.

Users can only query and modify the data they can see through the view. Other data in the database is either invisible or not. Database authorization commands allow each user to restrict the retrieval of a database to a specific database object, but not to a specific row and a specific column on the database. With Oracle view, users can be limited to different subsets of data:

Use permissions can be limited to a subset of the rows of a base table.

Use permissions can be limited to a subset of the columns of the base table.

Use permissions can be limited to a subset of the rows and columns of the base table.

Use permissions can be limited to rows that are qualified by the connections of multiple base tables.

Use permissions can be limited to the statistical summary of the data in the base table.

Permission can be restricted to a subset of another view, or to a subset of views and base tables merged.

The security of a view prevents unauthorized users from viewing a particular row or column, and the only way a user can see a specific row in a table is as follows:

(1) Adding a column in the table that marks the user name;

(2) To establish an Oracle view, users can only see the line marked with their own user name;

(3) Authorize the view to other users.

3, logical data independence.

Views help users to mask the impact of real-world table structure changes.

Views allow applications and database tables to be isolated to some degree. If there is no view, the application must be based on the table. With a view, the program can be built on top of the view, and the program is separated from the database table by the view. Views can make programs and data independent in the following ways:

(1) If the application is based on the database table, when the database table changes, you can set up a view of the table, through the view screen changes, so that the application can not move.

(2) If the application is based on the database table, when the application changes, you can set up a view on the table, through the view screen application changes, so that the database table does not move.

(3) If the application is based on the view, when the database table changes, you can modify the view on the table, through the view mask changes in the table, so that the application can not move.

(4) If the application is based on the view, when the application changes, you can modify the view on the table, through the view screen application changes, so that the database can not move.

Third, the basic syntax of the view:

Create[or Replace][force][noforce]view view_name

[(column_name) [,... N]]

As

Select_statement

[With CHECK Option[constraint Constraint_name]]

[With READ only]

Description

View_name: The name of the view

COLUMN_NAME: column names in view

You must specify the name of the view column in the following cases

* Columns derived from arithmetic expressions, system built-in functions, or constants

* Share columns from the same table name connection

* When you want the column names in the view to be different from the column names in the table

REPLACE: If this view already exists when you create the view, recreating the view is equivalent to overwriting

FORCE: Force CREATE VIEW regardless of whether the base table on which the view depends exists or has permission to create

Noforce: You can create a view only if the base table exists and has permission to create views

With CHECK OPTION indicates that the modifications made on the view conform to the restrictions specified by select_statement

With READ only allows viewing views

Definition Principles for Views:

(1) View queries can use complex SELECT syntax, including join/group queries and subqueries;

(2) In the absence of the with CHECK OPTION and READ only, the ORDER BY clause cannot be used in the query;

(3) If the CHECK OPTION constraint is not named, the system is automatically named, in the form of SYS_CN;

(4) The or REPLACE option can change its definition and rebuild, or grant permissions to objects without deleting the original view.

Four, view operation

Views are grouped into simple views (based on a single base table and do not contain functions and data grouping operations) and complex views (based on multiple base tables or views).

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

A simple view can modify data through a view. These modifications include inserting data. Update data and delete data. However, for complex views, modifying data through a view must meet certain rules.

If read only is not set in the view definition, the data in the table cannot be deleted through the view if the view contains the following content:

* Grouping functions, such as SUM,AVG,MIN,MAX, etc.

* GROUP BY clause

* contains an expression

* rownum pseudo column

When inserting data, you need to ensure that columns that are not included in the base table in the view definition must allow null values in addition to the above criteria. If you also include the WITH CHECK OPTION clause in the view definition, the modifications to the views must meet the specified constraints, in addition to those preceding those principles. Personally, views are beneficial to inquiries and are not conducive to modification.

(1) Query view: Can rely on multiple base tables.

SELECT * from View_name; /* Similar to query table data * *

(2) Update the premise of the view:

No connection functions, set operations, and group functions are used. The SELECT statement that creates the view has no aggregate functions and no group by,onnect by, the START with clause, and the DISTINCT keyword. The SELECT statement that creates the view does not contain computed columns from the base table columns. The CREATE view does not contain a read-only property.

(3) Inserting data

INSERT into View_name VALUES ();

(4) Modify the data:

UPDATE view_name SET ...

If a view relies on more than one base table, modifying the view at once can only modify the data in one basic table.

(5) Delete data:

Delete from View_name where ...

Similarly, you cannot use this statement to delete data from a base table when the view relies on multiple base tables. Only data that relies on one base table can be deleted.

(6) Modify the View definition:

The advantage of modifying a view is that all of the relevant permissions are still present. Syntax is the same as creating a view.

(7) Delete view:

DROP VIEW view_name;

Views can be deleted only by the view owner and users with drop View permissions. Deleting the definition of a view does not affect the data in the base table. When a view is deleted, other views or applications that are based on the deleted view will not work.

V. Examples

1, the Dba_segments table does not have the view information

Sql> SELECT DISTINCT (segment_type) from Dba_segments Group by Segment_type;

Segment_type

------------------

Lobindex

INDEX PARTITION

NESTED TABLE

TABLE PARTITION

ROLLBACK

LOB PARTITION

Lobsegment

TABLE

INDEX

CLUSTER

TYPE2 UNDO

11 rows have been selected.

2, the view of additions and deletions to the operation are on the base table.

Sql> CREATE TABLE V_dave (ID number,name varchar2 (20));

Table has been created.

sql> INSERT INTO V_dave values (1, ' Dave ');

1 lines have been created.

Sql> commit;

Submit completed.

Sql> select * from V_dave;

ID NAME

---------- --------------------

1 Dave.

Sql> CREATE VIEW View_dave as SELECT * from V_dave;

The view has been created.

Sql> select * from View_dave;

ID NAME

---------- --------------------

1 Dave.

Above we created a table and a view. Let's add a pruning operation to the view below.

(1) Insert Action on view

sql> INSERT INTO View_dave values (2, ' tianlesoftware ');

1 lines have been created.

Sql> commit;

Submit completed.

Sql> select * from V_dave;

ID NAME

---------- --------------------

1 Dave.

2 Tianlesoftware

Sql> select * from View_dave;

ID NAME

---------- --------------------

1 Dave.

2 Tianlesoftware

The most view added to the record, wrote the corresponding base table.

(2) Delete action on view

Sql> Delete from view_dave where id = 2;