PL/SQL query view _view

Transferred from: http://database.e800.com.cn/articles/2009/719/1248015564465_1.html

A view is a table that is exported from one or several basic tables (or views). Unlike a base table, it is a virtual table. The database stores only the definition of the view, not the data for the view, which is still stored in the original base table. So the data in the base table changes, and the data that is queried from the view changes. In this sense, a view is like a window through which you can see the data of interest in the database and its changes.

Since the definition of a view is based on a basic table, why do you define a view? This is because the use of views in a reasonable way can bring many benefits:

1, the view can simplify the user operation

The view mechanism allows users to focus on the data they care about. If the data is not directly from the base table, you can define the view to make the database look simple, clear, and simplify the user's data query operations. For example, a view that defines a number of table joins hides the connection between tables and tables from the user. In other words, the user does just a simple query of a virtual table, and how this virtual table is derived, the user does not need to understand.

2. Views enable users to view the same data in multiple angles

The view mechanism enables different users to view the same data in different ways, and this flexibility is necessary when many different kinds of users share the same database.

3. Views provide a degree of logical independence for refactoring databases

The physical independence of data means that the user's application is not dependent on the physical structure of the database. The logical independence of data is that when a database is re-constructed, such as adding new relationships or adding new fields to existing relationships, the user's application is not affected. Hierarchical database and mesh database generally can support the physical independence of the data, but the logical independence can not be fully supported.

in the lock database, the reconfiguration of the database is often unavoidable. The most common way to refactor a database is to divide a base table "vertically" into multiple base tables. For example: The student relationship student (sno,sname,ssex,sage,sdept), is divided into SX (sno,sname,sage) and Sy (sno,ssex,sdept) two relationships. The original table student is the result of the natural connection of the SX table and the SY table. If you create a view student:
CREATE VIEW Student (sno,sname,ssex,sage,sdept) as SELECT SX. Sno,sx. Sname,sy. Ssex,sx. Sage,sy. Sdept from Sx,sy WHERE SX. Sno=sy. Sno;

This way, although the logical structure of the database changes (to SX and SY two tables), the application does not have to be modified because the newly created view is defined as the user's original relationship, leaving the user's external mode intact, and the user's application can still find the data through the view.
Of course, the view can only provide a certain degree of logical independence of the data, for example, because the update of the view is conditional, the statement that modifies the data in the application may still change due to changes in the base table construction.

4, view can provide security protection for confidential data

With the view mechanism, you can define different views of different users when you design a database application, so that confidential data does not show up on a user view that should not see the data now. This way, the view mechanism automatically provides security protection for confidential data. For example, the student table covers the 15 faculties of the school student data, on which 15 views can be defined, each containing only one faculty student data, and only the director of each faculty is allowed to query and modify the primitive student view.

5, the appropriate use of the view can be more clearly expressed in the query

For example, it is often necessary to perform such a query "to find out the course number for each student who has obtained the highest grade". You can define a view to find the highest score for each student:
CREATE VIEW Vmgrade as SELECT Sno,max (Grade) Mgrade from SC GROUP by Sno;
Then complete the query with the following query statement:
SELECT SC. Sno,cno from Sc,vmgrade WHERE SC. Sno = Vmgrade. Sno and SC. Grade = Vmgrade. Mgrade;

Changes to the view:

　1. A single table view is typically used for querying and modifying, which alters the data of the base table,

2. Multi-table views are typically used for queries and do not alter the data of the underlying table.

　　

PL/SQL query view _view