Oracle database View creation and processing method tutorial

tutorial on how to create and process Oracle database views.

Learn how Oracle views are used by sharing the methods for creating views and working with views in Oracle.

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.
Views are based on tables called base tables, and Oracle's database objects are divided into five types: tables, views, sequences, indexes, and synonyms.

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:
1, simple view only from a single table to get data, complex view from multiple tables;
2, the simple view does not contain functions and data sets, complex view contains;
3. A simple view enables DML operations, and complex views are not available.

Syntax for Oracle to create views:
CREATE [OR REPLACE] [force| Noforce] VIEW view_name
[(alias[, alias] ...)]
As subquery
[With CHECK OPTION [CONSTRAINT CONSTRAINT]]
[With READ only]

Where: OR REPLACE: If the created attempt already exists, Oracle automatically rebuilds the view;
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: Aliases defined for the columns generated by the view; www.jbxue.com
Subquery: A complete SELECT statement in which the alias can be defined;
With CHECK OPTION: The data rows that are inserted or modified must satisfy the constraints of the view definition;
With READ only: No DML operations can be performed on this view.

For example:
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 ORDER BY clause cannot be used in a query without the WITH CHECK option and READ only;
3. If you do not name the CHECK option constraint, the system will automatically name it, in the form SYS_CN;
The 4.OR replace option allows you to change the definition and rebuild, or re-grant object permissions without deleting the original view.

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 can be obtained through the DESC user_views.

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. Www.jbxue.com

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 a data row that the view cannot retrieve because it performs integrity constraints and data validation checks on the 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.

Oracle database View creation and processing method tutorial