Create a view in Oracle Tutorial Learn

Share the methods of creating views in the Oracle database.

Oracle's database objects are divided into five types: tables, views, sequences, indexes, and synonyms.

A view is a logical table that is based on a table or multiple tables or views, which itself does not contain data that can be queried and modified by the data in the table. The table that the view is based on is called the base table.

A view is a SELECT statement that is stored in a data dictionary. You can extract the logical collection or combination of data by creating a view.

Benefits of Oracle View:

1. Access to the database because the view can be selected as part of a selective database.

2. Users can get results from complex queries through simple queries.

3. Maintain the independence of data and attempt to retrieve data from multiple tables.

4. Different views can be generated for the same data.

Views are divided into simple views and complex views:

Simple view gets data from single table only
Complex views from multiple tables

Simple view does not contain functions and data groups
Complex views contain

A simple view enables DML operations
Complex views are not allowed.

Oracle creates a view statement:

CREATE [OR REPLACE] [force| Noforce] VIEW view_name

[(alias[, alias] ...)]

As subquery
[With CHECK OPTION [CONSTRAINT CONSTRAINT]]
[With READ only]
which
OR REPLACE: Oracle rebuilds the view automatically if the view you created already exists;
Force: The view is automatically created by Oracle regardless of the presence of the base table;
Noforce: Only the base table exists for Oracle to create the view:
Alias: The aliases defined for the columns generated by the view;
Subquery: A complete SELECT statement in which the alias can be defined;
With CHECK OPTION:
The data rows inserted or modified must satisfy the constraints of the view definition;
With READ only:
No DML operations can be performed on this view.
Example: www.jbxue.com
CREATE OR REPLACE VIEW DEPT_SUM_VW
(name,minsal,maxsal,avgsal)
As SELECT d.dname,min (e.sal), Max (e.sal), avg (e.sal)
From EMP e,dept D
WHERE E.deptno=d.deptno
GROUP by D.dname;

The definition principle of a view:
1. View queries can use complex select syntax, including connection/grouping queries and subqueries;
2. The query cannot be used without the WITH CHECK option and READ only
ORDER BY clause;
3. If you do not name the CHECK option constraint, the system will automatically name it, in the form SYS_CN;
4.OR replace option to change its definition and rebuild, or to re-grant objects without deleting the original view
Permissions.

Query for Views:
Once the view is created successfully, you can retrieve the data from the view, just as you would retrieve data from the table.
You can also query all of the view's information and the specified data rows and columns.
Such as:

Retrieving data:
Sql>select * from DEPT_SUM_VW;

Query View definition:
SELECT View_name,text from User_views;
Where text is displayed is the SELECT statement defined by the view, which is available through the DESC user_views
Get the relevant information.

To modify a view:
Re-create a view with the same name with or REPLACE.

DML operations on the view:
The principles that DML operations should follow:
1. A simple view can perform DML operations;
2. When the view contains a group function, the GROUP BY clause, the DISTINCT keyword cannot be
Delete data rows;
3. You can modify the base table data or insert data from a view without the following:
A. The view contains the group function, the GROUP BY clause, and the DISTINCT keyword;
B. Columns defined with an expression;
C.rownum pseudo-Columns.
D. Other columns in the base table that are not selected in the view are defined as non-null and have no default values.

Views can be used to preserve the integrity of a database, but with limited usefulness.
Referential integrity constraints can be enforced at the database level through a view.

The WITH CHECK OPTION clause qualifies:
The inserts and updates operations performed through the view cannot create data rows that the view cannot retrieve.
Because it performs integrity constraints and data validation checks on data rows that are inserted or modified.
For example:
CREATE OR REPLACE VIEW vw_emp20
As SELECT * from EMP
WHERE deptno=20
With CHECK OPTION constraint vw_emp20_ck;
The view is already established.

Query Result:
SELECT empno,ename,job from Vw_emp20;

EMPNO ename JOB
--------------------- -------------- -------------
7369 SMITH Clerk
7566 JONES MANAGER
7902 FORD ANALYST

Modify:
UPDATE VW_EMP20
SET deptno=20
WHERE empno=7902;
An error will be generated:
UPDATE VW_EMP20
*
The ERROR is on the first line:
ORA-01402: View with CHECK OPTION violates the WHERE clause

Delete of view:
Drop View view_name statement to delete views.
Deleting a view's definition does not affect the data in the base table.
Only the view owner and the user with the Drop view permission can delete the view.
After the view is deleted, other views or apps that are based on the deleted view will be invalid.

Create a view in Oracle Tutorial Learn