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