Oracle _ view, oracle View

Oracle _ view, oracle View

Oracle _ view

① View
1. A view is a virtual table.
A view is built on an existing table. The tables created by the view are called base tables.
The statement that provides data to the view is a SELECT statement, which can be understood as a stored SELECT statement.
View provides users with another form of representation of base table data

2. Why View
Control Data Access
Simplified Query
Avoid repeated access to the same data

3. Simple view and complex view
Features simple view complex view
One or more tables
Function does not have
Group does not have
DML operations can be performed sometimes

② Create a view
Embed a subquery in the create view statement
CREATE [or replace] [FORCE | NOFORCE] VIEW view
[(Alias [, alias]...)]
AS subquery
[With check option [CONSTRAINT constraint]
[With read only [CONSTRAINT constraint];

Subqueries can be complex SELECT statements.

③ Modify View
Use the create or replace view clause to modify a VIEW
The alias of each column in The create view clause should correspond to each column in The subquery.

④ DML usage rules in the view
DML operations can be performed in a simple view.
1. When a view definition contains one of the following elements, delete cannot be used:
Group functions
Group by clause
DISTINCT keyword
ROWNUM pseudo Column

2. update is unavailable when the view definition contains the following elements:
Group functions
Group by clause
DISTINCT keyword
ROWNUM pseudo Column
Column definition as an expression
3. When a view definition contains one of the following elements, insert is not allowed:
Group functions
Group by clause
DISTINCT keyword
ROWNUM pseudo Column
Column definition as an expression
Columns not empty in the table are not included in the view definition.

4. Blocking DML operations
You can use the with read only option to block DML operations on The View.
An Oracle server error is returned for any DML operation.

⑤ Delete a view
Deleting a view only deletes the view definition and does not delete the data in the base table.

⑥ Top-N Analysis
Top-N Analysis of the maximum number of values in a query
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
Order by Top-N_column)
Where rownum <= N;
Note: You can only use <or <= for ROWNUM, and use =,>,> = to return no data.

Example:
Select *
From (
Select rownum rn, t .*
From (
Select * from employees
) T
Where rownum <= 20
)
Where rn> 10

Zookeeper
How to Create a view in Oracle

Create a view in Oracle

Oracle database objects include tables, views, sequences, indexes, and synonyms.
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.
Views are classified into simple views and complex views:
A simple view only obtains data from a single table.
Complex view from multiple tables

A simple view does not contain functions and data groups.
Complex view inclusion
A simple view allows you to perform DML operations.
Complex views are not allowed.
View creation:
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 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:
This view does not support any DML operations.
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;

View definition principles:
1. View queries can use complex SELECT syntax, including connection/grouping queries and subqueries;
2. You cannot use
Order by clause;
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 can be re-authorized.
Permission.

View query:
After a view is created, you can retrieve data from the view, which is the same as retrieving data from the table.
You can also query all view information and specified data rows and columns.
For example:

... The remaining full text>

Create a view in oracle

CREATE or REPLACE view view_will
Select college. collegeid, mat. stuid, sum (score. chinese + score. math + score. english + score. complex)
From college, mat, score
Where college. collegeid = mat. first_will and mat. stuid = score. stuid
Group by SCORE. STUID, COLLEGE. COLLEGEID, MAT. STUID having
Sum (score. chinese + score. math + score. english + score. complex)> 500