Oracle Learning (vii) view

A view is a table that is exported from a table or view, or a table that is exported from more than one table or view. A view is a virtual table in which the database does not actually store the data that corresponds to the view, only the definition of the views is stored, and when the data for the view is manipulated, the system operates on the base table associated with the view according to its definition.

Once a view is defined, it can be queried, modified, deleted, and updated as a table.

Using views has the following advantages:

(1) To centralize data for users, simplify the user's data query and processing. Sometimes the data we need may be dispersed in several different tables, and the view can be set together to facilitate our query and processing of the data.

(2) Shielding the complexity of the database. When working with data, it eliminates the need for users to understand the complex database table structure.

(3) Simplify the user's authority management. We may sometimes grant a user the ability to view only certain columns of the corresponding table, which is quite troublesome, and with a view we can extract the columns we can view to create the corresponding view, and then give the user permission to use the view.

(4) Facilitate data sharing. Different users do not have to save their own data, the same data only need to save once.

1. Create a view with a CREATE VIEW statement:

Syntax format:

    create [or replace] [force | noforce] View [schema.] viewname[(columnname[,... n])] as  
        selectstatement [with Check Option[constraint ConstraintName]]  
            [with Read Only]  
--or replace means that if a view of the corresponding name already exists, the view is rebuilt, that is, the original view is destroyed, and the corresponding view is rebuilt

.  
--force represents the force to create a view, regardless of whether the base table of the view exists or whether the owner has permissions.  
--columnname are columns that are included in the view and can have multiple column names. If you use the same column name as the source table or view, you do not have to give a

columnName, but you must indicate the column name:-(1) A column that is obtained by an  
arithmetic expression, a system built-in function, or a constant-  
(2) that shares the same table name  
- (3) When you want the column names in the view to be different from the column names of the base table  
,--selectstatement represents the SELECT statement that is used to create the view, and you can query multiple tables or views in the SELECT statement.  
--with Check option indicates that the modifications made in the view conform to the restrictions specified by selectstatement, so that the

modified data can still be viewed through the view after the data has been modified.  
--with Read Only specifies that the view cannot be deleted, updated, and inserted, and can only be retrieved.

Sample code:

Create or Replace view Personview as select Name,sex from and read only; --

Create a read-only view of the name and sex columns of the person table.  
Create or replace view Personview (sex, average age) as select Sex, avg. from person GROUP by 

sex with CHECK option;  
--group the gender in the person table, draw the average age of men and women, and establish a view in which the column name of the view is changed to sex and the average age.

2. Query view

After the view is defined, you can query the view, which is the same as the query for the base table.

Sample code:

SELECT * from Personview; --this statement will detect all the fields inside the view Personview

When you use a view query, if new fields are added to the base table, you cannot query the newly added fields in the new view until you rebuild the view. For example, suppose you have a table person (Id,name,sex) that creates a view of the table Personview (Create or Replace view Personview as SELECT * from), If later I added a new age field to person, then at this point I call the result set of the query (SELECT * from Personview) that does not contain an age field, and if you want to display an age field, you need to rebuild the view. The reason for this is that the view that was previously said is just a virtual table, when the view is established, the definition of the view is saved in the data dictionary, and the corresponding base table is manipulated according to the definition of the view, so after the new field is added to the base table, the corresponding view is not defined You won't be able to display the new field.

If the table or view associated with the view is deleted, the view is no longer available.