Oracle view Summary (create, query, modify, delete, etc.), oracle View

Oracle view Summary (create, query, modify, delete, etc.), oracle View

View definition:

-A view is a virtual table.

--A view is created on the basis of an existing table. The tables on which a view is created are called base tables.
-The statement that provides the data content 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 the base table data.

Why use a view?

--Control Data Access
--Simplified Query
--Avoid repeated access to the same data

Create view: Embed subqueries in the create view statement. subqueries can be complex SELECT statements.

<span style="font-size:14px;">create or replace view empview as select employee_id emp_id,last_name name,department_namefrom employees e,departments dWhere e.department_id = d.department_id</span>

Define an alias for a column in the subquery when creating a view: use an alias when selecting a column in the view.

<span style="font-size:14px;">CREATE VIEW  salvu50AS SELECT  employee_id  ID_NUMBER,             last_name    NAME,            salary*12    ANN_SALARYFROM    employeesWHERE   department_id = 50;View created.</span>

Query view: SELECT * FROM salvu50;

Modify VIEW: Use the create or replace view clause to modify VIEW

<span style="font-size:14px;">CREATE OR REPLACE VIEW empvu80  (id_number, name, sal, department_id)AS SELECT  employee_id, first_name || ' ' || last_name,            salary, department_id   FROM    employees   WHERE   department_id = 80;View created.</span>

The alias of each column in The create view clause should correspond to each column in The subquery.

Example of creating a complex view:

<span style="font-size:14px;">CREATE VIEWdept_sum_vu  (name, minsal, maxsal, avgsal)AS SELECT d.department_name, MIN(e.salary),              MAX(e.salary),AVG(e.salary)   FROM      employees e, departments d   WHERE     e.department_id = d.department_id    GROUP BY  d.department_name;View created.</span>

Rules for using dml in a View:

DML operations can be performed in a simple view.
Delete cannot be used when the view definition contains the following elements:
--Group functions
--Group by clause
--DISTINCT keyword
--ROWNUM pseudo Column

<span style="font-size:14px;">create or replace view sal_viewas selectavg(salary) avg_sal from employeesgroup by department_id</span>

Update is unavailable when the view definition contains the following elements:
--Group functions
--Group by clause
--DISTINCT keyword
--ROWNUM pseudo Column
--Columns are defined as expressions.

Insert cannot be performed when the view definition contains one of the following elements:
--Group functions
--Group by clause
--DISTINCT keyword
--ROWNUM pseudo Column
--Columns are defined as expressions.
--Non-empty columns in the table are not included in the view definition.

Shield 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.

<span style="font-size:14px;">CREATE OR REPLACE VIEW empvu10    (employee_number, employee_name, job_title)AS SELECTemployee_id, last_name, job_id   FROM     employees   WHERE    department_id = 10   WITH READ ONLY;View created.</span>

Delete view: deleting a view only deletes the view definition and does not delete the data of the base table.

<span style="font-size:14px;">DROP VIEW empvu80;View dropped.</span>

Top-N analysis:

Top-N Analysis of the maximum number of values:

<span style="font-size:14px;">SELECT [column_list], ROWNUM  FROM   (SELECT [column_list]         FROM table        ORDER  BY Top-N_column)WHERE  ROWNUM <=  N;</span>

Note:
You can only use <or <= for ROWNUM, and use =,>,> = to return no data.

<span style="font-size:14px;">select *from(select rownum rn,employee_id,salaryfrom(select employee_id,salary,last_namefrom employeesorder by salary desc))where rn <=50 and rn >40</span>

Oracle Delete View

First, you must understand the principle of the view.
View data is generated from tables. The view itself has no data, and the table is the real data source.
There are also multi-level views, that is, you can continue to create views from the view.
Therefore, the data source of a view is a table or view. However, the final data source must be a table.
What kind of view data can be deleted? Only a single VIEW can be introduced from a single table. Such a VIEW operation is actually equivalent to an operation table, but drop view does not affect the data. In other situations, data cannot be deleted. However, drop view can also be used.
The view only captures data from the table based on the association relationship. Actually, there is no dedicated storage for the data like a table. It is just introduced from the table. As long as you keep in mind, the view does not actually store data, but borrow the table data. This is enough. No matter whether you create or delete a view, the data itself will not be affected. Otherwise, when you created a view, you never thought about why there was so much data at once? This is not what you insert, because it is introduced from the table.
The statement used to delete a VIEW is the name of the drop view.
Therefore, deleting a view does not affect data. Because the data is in the table. If you want to restore the view and recreate it, tens of millions of data records will appear again. The effect of a view is the same as that of a SELECT query, but the query is fixed so that you can use it directly.

How does one delete a view in oracle?

Log on to the user who created the view and execute the following code to delete all views, but proceed with caution.
The premise is that the user has the permission to execute code.
Begin
For cur in (select view_name from user_views) loop
Execute immediately 'drop view' | cur. view_name | ';';
End loop;
End;
/