Oracle view summary)

 

View introduction:
A view is a logical table based on one or more tables or views. It does not contain data and allows you to query and modify the data in the table. A view-based table is called a base table. A view is a SELECT statement stored in the data dictionary. You can create a view to extract a logical set or combination of data.

Advantages of a view:
1. Access to the database because the view can selectively select a part of the database.
2. You can obtain results from complex queries through simple queries.
3. Maintain data independence and try to retrieve data from multiple tables.
4. Different views can be generated for the same data.

View category:
A view can be divided into a simple view and a complex view.

BothDifferencesAs follows:
1. A simple view only obtains data from a single table and a complex view acquires data from multiple tables;
2. A simple view does not contain functions and data groups. A complex view contains functions;
3. You can perform DML operations on a simple view, but not on a complex view.

View creation:

Create [or replace] [force | noforce] view view_name [(alias [, alias]...)]
As subquery
[With check option [constraint]
[With read only]
Where:
Or replace: If the created attempt already exists, Oracle automatically recreates the view;
Force: this view is automatically created no matter whether the base table exists in Oracle;
Noforce: this view is created only when the base table exists in Oracle:
Alias: the alias defined for the columns generated by the view;
Subquery: a complete SELECT statement that defines aliases;
With check option: the inserted or modified data rows must meet the view-defined constraints;
With read only: No DML operations can be performed on this view.

 

DML (data manipulation language): They are select, update, insert, and delete, just like their names. These four commands are used to operate the data in the database.

DDL (Data Definition Language): DDL is more than DML. The main Commands include create, alter, and drop. DDL is mainly used to define or change the table structure, data type,
Most of the initialization tasks such as links and constraints between tables are used when creating tables.

 

View definition principles:
1. View queries can use complex select syntax, including connection/grouping queries and subqueries;
2.The order by clause cannot be used in queries without the with check option and read only clauses.;
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 permission can be re-Granted.

Query View:
After a view is created, you can retrieve data from the view, which is the same as retrieving data from the table.
Example:
SQL> select * From dept_sum_vw;

Modify View:
Use or replace to create a view with the same name.

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

DML operations on the View:
Principles for DML operations:
1. DML operations can be performed in a simple view;
2. Data rows cannot be deleted when the view contains the group function, group by clause, and distinct keywords;
3. You can use the view to modify or insert data in the base table if the view does not meet the following conditions:
A. The view contains the group function, group by clause, and distinct keywords;
B. Columns defined using expressions;
C. rownum pseudo column.
D. Other columns not selected in the view in the base table are defined as non-empty and have no default values.
With check option clause
The inserts and updates operations performed by the view cannot create data rows that cannot be retrieved by the view, because it performs integrity constraints and data validity checks on the inserted or modified data rows. (That is to say, when executing inserts and updates, in addition to the constraints of insert and update, the where condition must be added when the view is created .)

View Function
1) provides a variety of data representations. You can use different methods to present the data in the base table to the user, so as to meet the user's usage habits (main means: use aliases ).
2)Hide the logic complexity of data and simplify query statementsMulti-Table query statements are generally complex, and you need to understand the relationship between tables. Otherwise, it is easy to write an error. If you create a view based on such query statements, you can directly perform a "simple query" on this view to obtain the result. in this way, the data complexity is hidden and the query statements are simplified. this is one of the reasons why Oracle provides various "data dictionary views". all_constraints is a view that contains two subqueries and connects nine tables (in catalog. defined in SQL ).
3) execute some queries that must use views. some queries can be completed only with the help of views. for example, some queries need to connect one table after grouping statistics to another table. In this case, you can create a view based on grouping statistics, connect the view to another table in the query.
4) provides some security assurance. A view provides a controllable way for different users to see different columns without allowing access to sensitive columns, this ensures that sensitive data is not visible to users.
5) simplify the management of user permissions. You can grant view permissions to users without granting permissions to certain columns in the base table. This simplifies the definition of user permissions.

 

 

 

 

Create a complex view
A view that contains functions, expressions, or grouped data. The main purpose isSimplified Query. It is mainly used to perform query operations, not DML operations.
Note: When a SELECT query in a view contains a function or expression, you must define a column alias for it.

 

Example 1: query the average salary, total salary, maximum salary, and minimum wage of each position.
Create view vw_emp_job_sal
(Job, avgsal, sumsal, maxsal, MINSAL)
As
Select job, AVG (SAL), sum (SAL), max (SAL), min (SAL)
From EMP
Group by job;

 

Select * From vw_emp_job_sal;

 

Change View
Before changing (or redefining) a view, consider the following:
One-because the view is only a virtual table with no data in it, changing the view only changes the definition information of the view in the data dictionary. All basic objects of the view will not be affected.
2 -- after the view is changed, all views and PL/SQL programs dependent on the view will change to invalid (invalid) state.
3 -- if the with check option is available in the previous view but this option is not used during redefinition, the previous option is automatically deleted.

 

Modify the view definition
Method -- execute the create or replace view statement. This method replaces the method created after the permission is deleted (the permission will also be deleted). The permission on the view will be retained, but the stored procedure and view related to the view will be invalid.
Example 1: Create or replace view v_test_tab
As
Select C1, C2 | '+' | C3 C23 from test_tab;

 

View re-Compilation
Syntax: Alter view name compile;
Purpose: when the base table on which the view depends changes, the view becomes invalid ". To ensure that this change does not affect the view and
For other objects, you should use the alter view statement to "explicitly recompile" the view, so that you can find the view again before running it.
Compilation error. After the view is re-compiled, if an error is found, the object dependent on the view will also become invalid. If no error is found,
The view changes to "valid ".
Permission: to recompile views in other modes, you must have the alter any table system permission.
Note: when accessing the changed views of the base table, Oracle will "automatically recompile" these views.

 

Delete View
You can delete any view in the current mode;
To delete a view in other modes, you must have the permission to drop any view;
After a view is deleted, its definition is deleted from the dictionary and the "Permissions" granted to the view are also deleted.
After a view is deleted, other views and stored procedures that reference the view will become invalid.
Example 1: Drop view vw_test_tab;

 

Updatable connection View
If the SELECT query for creating a connection View "does not contain" the following structure and complies with the "update criteria" of the connection view, the connection view is "updatable:
One: Set operators (Union, intersect, minus)
2: distinct keyword
3: group by, order by, connect by or start with clause
4: subquery
5. Grouping Functions
6. columns to be updated are not defined by the "column expression ".
7. All not null columns in the base table belong to this view.

 

Key Value saving table
If a "base table key" (primary key and unique key) in the connection view still exists in its view,
The "base table key" is still the "key in the connection View" (primary key and unique key );
That is, a column is a primary key in the base table | unique key, still a primary key in the View | unique key
The base table is called "key-value saving table ".

Generally, a connection view consists of two tables with a primary-foreign key relationship. A foreign key table is a key-value storage table, but a primary key table is not.

 

Connection view update rules
One: General Principle -- (Lecture)
Any DML operation can only update the table that stores the key values in the view,
That is, "You cannot modify multiple base tables through the connection View ";
In DML operations, "Only columns defined in the connection view can be used ";
All columns in the "self-join View" are updatable (add, delete, and modify ).
Ii. Insert Criterion
In the insert statement, columns (including "Join columns") in the "Save table with non-key values" cannot be used ");
In the view where the insert operation is executed, at least the "include" key value should be saved to all columns in the table with constraints set;
If the with check option is used when defining the connection view,
Then, "no" executes the insert operation on the Connected View.
3: update criteria
Columns in the key-value storage table can be updated;
If the with check option is used when defining the connection view,
The connection columns in the connection view (generally "common columns") and the "other common columns" in the base table are "non-updatable,
The connection column and other columns except the common columns can be updated.
Iv. Delete Criterion
If the with check option is used when defining the connection view,
You can still perform the delete operation on the connection view.