Oracle Database object_view

Oracle Database object_view

A view is a very important database object. It is similar to a common table. We can query data from a view.

In fact, it is a virtual table built on a table. It does not store real data in the view, but only saves a SELECT statement. Access to the view will be converted to access to the table.

A view is based on a base table, and a view can be considered as a query operation for a base table.

The main purpose of using a view is to make it easier for users to access the base table and to ensure secure access to the base table.

Users often need to perform a large number of query operations on a table. If the query operations are complex and need to be performed frequently, you can define a view for this query.

Note that the query results for the base table are not saved in the view, but only one SELECT statement is saved.

Only when the view is accessed, the database server executes the SELECT statement in the view to query data from the base table.

Although we have not modified the view, multiple accesses to the view may have different results, because the data in the base table may be modified at any time.

Therefore, the view does not store static data, but dynamically queries from the base table.

From another perspective, the view can ensure secure access to the base table.

When designing a table, we generally consider the structure of the table from the overall perspective, rather than determining the table structure and defining permitted operations from the perspective of each user.

For the same table, different users can perform different operations and access different data.

In this way, we can define different views for different users to ensure that users can only perform permitted operations and access specific data.

View creation, modification, and deletion

You can CREATE a VIEW in your own mode, as long as you have the system permission to create view.

If you want to CREATE a VIEW in another user's mode, you must have the create any view system permission.

If the base table of a view is an object in other user mode, the current user needs to have the SELECT permission on the base table.

The command for creating a VIEW is create view. The format of this command is:

Create or replace view name

As select statement

WITH READ ONLY

With check option;

The last two options are optional. "with read only" indicates that ONLY query operations can be performed on the view, but not DML operations.
"With check option" limits that DML operations must meet certain conditions.

After a view is created, you can run the DESC command to view the view structure.

The method for viewing the view structure is the same as that for viewing the table. The result is to list the definitions of each column in the view.

The VIEW structure is determined when you execute the create view statement to CREATE a VIEW. By default, the column name is the same as the column name of the base table after the SELECT statement, the data type and whether it is null also inherit the corresponding columns in the base table.

If you want to use different names for each column in the view, you must specify the name of each column after the view name when creating the view.

A view is a database object. Its related information is stored in the data dictionary.

The data dictionary related to the current user's view is USER_VIEWS. You can query this data dictionary to obtain information about the current user's view.

The definition and significance of each column in The USER_VIEWS data dictionary view are as follows:

Name meaning

VIEW_NAME Name of the view
TEXT_LENGTH Length of the view text
TEXT View text
TYPE_TEXT_LENGTH Length of the type clause of the object view
TYPE_TEXT Type clause of the object view
OID_TEXT_LENGTH Length of the with object oid clause of the object view
OID_TEXT with object oid clause of the object view
VIEW_TYPE_OWNER Owner of the type of the view if the view is a object view
VIEW_TYPE Type of the view if the view is a object view
SUPERVIEW_NAME Name of the superview, if view is a subview
EDITIONING_VIEW An indicator of whether the view is an Editioning View
READ_ONLY An indicator of whether the view is a Read Only View

Note: indicator.

In column TEXT, SELECT statements are stored when a view is created.

In addition, the data dictionary ALL _ VIEWS stores information about all views that can be accessed by the current user, and the data dictionary DBA_VIEWS stores information about all views in the system, this data dictionary can only be accessed by DBAs.

If you find that the view definition is inappropriate, you can modify it.

In fact, the SELECT statement in the view cannot be directly modified. Therefore, one method of modifying the view is to delete the view and recreate it, another method is to use the or replace option in the CREATE statement for creating a view.

A view can be deleted from the database when it is not needed.

The command for deleting a VIEW is drop view.

You can directly delete a VIEW created by yourself. To delete a VIEW created by another user, you must have the system permission to drop any view.

The format of the drop view command is:

Drop view name;

After a view is deleted, related information is also deleted from the data dictionary.

How to access a view

Access to a view includes queries and restricted DML operations.

The method for accessing a view is basically the same as that for accessing a table.

When accessing a view, this access is converted to access to the base table. Therefore, when performing DML operations on the view, you must also comply with the constraints on the base table.

Complex View

In the past, when creating a VIEW, only one table operation was involved in the SELECT clause of the create view statement, and only a simple query was performed on the columns in the base table. No base tables were found, or when expression or function operations are performed on the columns in the base table, this view is called a simple view.

You can not only query a simple view, but also perform DML operations.

A complex view is a view where columns are computed by expressions or functions from the base table, DISTINCT queries on the base table, or operations involving multiple tables.

All in all, if the create view statement is used to CREATE a VIEW, an expression or function is used in the column name after the SELECT statement, or the DISTINCT keyword is used, or multiple tables are connected for query, such views are complex.

Creating a complex VIEW is still done by executing the create view command, but because an expression or function is used in the SELECT clause, such an operation cannot be the column name in the VIEW, therefore, when creating a complex view, you must specify a column name for each column.

For complex views, only query is allowed. Most views do not allow DML operations.

Now, we will summarize the view on which DML operations can be performed, and the view on which DML operations cannot be performed.

For a simple view, if all columns in the base table are included in the view, or at least the primary key column and all columns that are not allowed to be empty are included in the view, in addition, only the WHERE clause is used in the SELECT statement of the create view statement for creating a VIEW. Such a VIEW can be inserted, deleted, or modified.

If the with check option is used to create such a simple view, the DML operation must comply WITH certain constraints.

For complex views involving multiple base tables, if all columns of at least one table are included in the view, or at least one table's primary key column and all columns that are not allowed to be empty are included in the view, and only the WHERE clause is used in the SELECT statement of the CREATEVIEW statement for creating the view, such views allow insertion, deletion, and modification.

When you perform DML operations on such a view, only one of the tables can be modified, and the modified columns can only be mapped to one table.

For complex views that only involve a base table, if the columns in the view are computed by some operation on the columns in the base table, including expressions, AVG, and other functions, alternatively, you can use the DISTINCT keyword and GROUP clause in the SELECT statement of the create view statement to CREATE a VIEW. You cannot perform DML operations on such views.

For views involving multiple tables, if the columns in the view do not contain all primary key columns and all columns not empty in one table, or some columns in the view are calculated by some operation on the columns in the base table, or the DISTINCT keyword and GROUP clause are used in the SELECT clause when creating the view, such a view cannot perform DML operations.