View definition, function, syntax, and operations

1. view definition

A view, also known as a virtual table, does not occupy physical space. This is also a relative concept, because the definition statements of the view must be stored in the data dictionary. The view is only logically defined. Every time you use it, you only need to re-execute the SQL statement.

A view is obtained from one or more actual tables whose data is stored in the database. The tables used to generate a view are called the base tables of the view. One view can also be generated from another view.

The view definition exists in the database, and the data related to this definition is not stored in the database. The data displayed in the view is stored in the base table.

A view looks very similar to a physical table of a database and operates on it like any other table. When you modify data through a view, the data in the base table is actually changed. On the contrary, the changes to the base table data are automatically reflected in the view generated by the base table. For logical reasons, some Oracle views can modify the corresponding base table, while others cannot (only query ).

 

There is also a kind of VIEW: materialized view (materialized view), also known as materialized view, snapshot (earlier than 8i), which contains data and occupies storage space. For more information about materialized views, see

 

Oracle Materialized View

Http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4713553.aspx

 

Ii. Role of views

2.1 simplicity.

What you see is what you need. A view not only simplifies users' understanding of data, but also simplifies their operations. Frequently Used queries can be defined as views, so that you do not have to specify all the conditions for each subsequent operation.

2.2 Security.

Users can only query and modify the data they can see through the view. Other data in the database is neither visible nor accessible. Database authorization commands allow each user to restrict the retrieval of a database to a specific database object, but cannot authorize the database to a specific row or column. With the Oracle view, users can be restricted to different subsets of data:

The permission can be restricted to a subset of rows in the base table.

The permission can be restricted to a subset of columns in the base table.

The permission can be restricted to the row and column subsets of the base table.

The permission can be restricted to the rows restricted by the connection of multiple base tables.

The permission can be restricted to the Statistical Summary of data in the base table.

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

View security prevents unauthorized users from viewing specific rows or columns. You can only view specific rows in the table as follows:

1. Add a column indicating the user name in the table;

2. Create an Oracle view. You can only see rows marked with your username;

3. Authorize the view to other users.

2.3 Logical Data independence.

View helps you avoid the impact of changes in the real table structure.

View can make applications and database tables independent to a certain extent. If there is no view, the application must be created on the table. With the view, the program can be built on the view, so that the program and the database table are separated by the view. The view can separate the program from the data in the following aspects:

1. If an application is created on a database table, you can create a view on the table when the database table changes. The view shields the changes in the table, so that the application can not move.

2. If an application is created on a database table and the application changes, you can create a view on the table to mask the application changes through the view so that the database table does not move.

3. If the application is built on a view, when the database table changes, you can modify the view on the table and use the view to shield the changes in the table so that the application can remain unchanged.

4. If an application is created on a view, you can modify the view on the table when the application changes. The view shields the application changes, so that the database does not move.

 

III. Basic View Syntax:

CREATE [or replace] [FORCE] [NOFORCE] VIEW view_name

[(Column_name )[,.... N]

AS

Select_statement

[With check option [CONSTRAINT constraint_name]

[With read only]

Note:

View_name: View name

Column_name: column name in the view

 

The View column name must be specified in the following cases

* Columns obtained by arithmetic expressions, built-in functions, or constants

* Share the columns connected by the same table name

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

 

REPLACE: If a view already exists when it is created, re-create the view, which is equivalent to overwriting

FORCE: Creates a view forcibly. no matter whether the base table on which the view depends exists or whether the view has the permission to create it

NOFORCE: You can create a view only when the base table exists and has the permission to create a view.

With check option indicates that all modifications made on the view must comply WITH the restrictions specified by select_statement.

With read only allows viewing views ONLY

 

View definition principles:
1. View queries can use complex SELECT syntax, including connection/grouping queries and subqueries;
2. You cannot use
Order by clause;
3. If the name is not specified as the check option constraint, the system will automatically name it in the form of SYS_Cn;
4. The or replace option can be changed and rebuilt without deleting the original view, OR the object can be re-authorized.
Permission.

 

Iv. View operations

A view is divided into a simple view (based on a single base table and does not contain functions and Data grouping operations) and a complex view (based on multiple base tables or views ).

In a simple view, you can modify data through the view. These modifications include inserting data, updating data, and deleting data. However, for a complex view, modifying data through the view must meet certain rules.

 

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

* Grouping functions, such as sum, AVG, Min, and Max

* Group by clause

* Contains expressions

* Rownum pseudo Column

 

When inserting data, in addition to meeting the preceding conditions, you also need to ensure that columns that are not included in the view definition must allow null values. if the view definition also contains the with check option clause, modifications to the view must meet the specified constraints in addition to the previous principles. in my opinion, views are conducive to queries and are not conducive to modification.

 

1. query view: dependent on multiple base tables.

Select * From view_name;/* similar to querying table data */

 

2. Prerequisites for updating a view:

No connected functions, set operations, and group functions are used. the SELECT statement for creating a view does not contain a set function and does not contain the group by, onnect by, start with clause, and distinct keywords. the SELECT statement used to create a view does not contain columns computed from the base table column. the created view does not contain read-only attributes.

 

3. insert data

Insert into view_name values ();

 

4. modify data:

Update view_name set...

If a view depends on multiple basic tables, only the data of one basic table can be modified at a time.

 

5. delete data:

Delete from view_name where...

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

 

6. Modify the view definition:

The benefit of modifying a view is that all related permissions still exist. The syntax is the same as creating a view.

 

7. Delete A View:

Drop view view_name;

Only the view owner and users with the drop view permission can delete the view. Deleting a view does not affect the data in the base table. After a view is deleted, other views or applications based on the deleted view are invalid.

 

 

V. Example

5.1 no view information in the dba_segments table

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.

 

5.2 add, delete, modify, and delete views on the base table.

SQL> create table v_dave (id number, name varchar2 (20 ));

The table has been created.

SQL> insert into v_dave values (1, 'Dave ');

One row has been created.

SQL> commit;

Submitted.

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

 

We have created a table and a view. Next we will add, delete, and modify views.

 

5.2.1 insert a view

SQL> insert into view_dave values (2, 'tiancesoft ');

One row has been created.

SQL> commit;

Submitted.

SQL> select * from v_dave;

ID NAME

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

1 dave

2 tianlesoftware

SQL> select * from view_dave;

ID NAME

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

1 dave

2 tianlesoftware

 

The records added in the most view are written in the corresponding base table.

 

5.2.2 delete a view

SQL> delete from view_dave where id = 2;

One row has been deleted.

SQL> commit;

Submitted.

SQL> select * from view_dave;

ID NAME

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

1 dave

SQL> select * from v_dave;

ID NAME

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

1 dave

 

The delete operation on the view also modifies the base table.

 

5.2.3 update a view

SQL> update view_dave set name = 'tiancesoft' where id = 1;

1 row updated.

SQL> commit;

Submitted.

SQL> select * from v_dave;

ID NAME

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

1 tianlesoftware

SQL> select * from view_dave;

ID NAME

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

1 tianlesoftware

 

 

5.3 view dba_views

SQL> desc dba_views;

Is the name empty? Type

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

Owner not null VARCHAR2 (30)

VIEW_NAME not null VARCHAR2 (30)

TEXT_LENGTH NUMBER

Text long

Type_text_length number

Type_text varchar2 (4000)

OID_TEXT_LENGTH NUMBER

OID_TEXT VARCHAR2 (4000)

VIEW_TYPE_OWNER VARCHAR2 (30)

View_type varchar2 (30)

Superview_name varchar2 (30)

Editioning_view varchar2 (1)

READ_ONLY VARCHAR2 (1)

 

View the view definition:

SQL> select text from dba_views where view_name = 'view _ DAVE ';

 

TEXT

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

Select "ID", "NAME" from v_dave