Create a view in Oracle. Create a view in oracle.

Create a view in Oracle. Create a view in oracle.

This section describes how to create a view in an oracle database.

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 the oracle 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.

Statement for creating a view in oracle:

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 view 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.
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;

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:

Retrieve Data:
SQL> SELECT * FROM dept_sum_vw;

Query view definition:
SELECT view_name, text from user_views;
The content displayed in text is the SELECT statement defined by the view. You can use DESC USER_VIEWS
Obtain related information.

Modify View:
Use or replace to create a view with the same name.

DML operations on the View:
Principles for DML operations:
1. DML operations can be performed in a simple view;
2. The view cannot contain the GROUP function, group by clause, and DISTINCT keyword.
Delete data rows;
3. You can use the view to modify or insert data in the base table if the view does not meet the following conditions:
A. The view contains the GROUP function, group by clause, and DISTINCT keywords;
B. Columns defined using expressions;
C. ROWNUM pseudo column.
D. Other columns not selected in the view in the base table are defined as non-empty and have no default values.

The view can be used to maintain the integrity of the database, but has limited function.
You can use the view to execute the reference Integrity Constraint at the database level.

Limitations of the with check option clause:
The INSERTS and UPDATES operations performed by the view cannot create data rows that cannot be retrieved by the view,
Because it performs integrity constraints and data validity checks on the inserted or modified data rows.
For example:
Create or replace view vw_emp20
As select * FROM emp
WHERE deptno = 20
With check option constraint vw_emp20_ck;
The view has been created.

Query results:
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 e-mapreduce = 7902;
An error occurs:
UPDATE vw_emp20
*
ERROR is located in the first line:
ORA-01402: view with check option violation WHERE clause

Delete View:
Drop view VIEW_NAME statement to delete a VIEW.
Deleting a view does not affect the data in the base table.
Only the VIEW owner and users with the drop view permission can delete the VIEW.
After a view is deleted, other views or applications based on the deleted view are invalid.

How to Create a view in oracle

1 create view V_StudInfoasselect. student ID,. name, B. course No., B. course name, case when c. score between 90 and 100 then 'excellent 'when c. score between 80 and 89 then 'liang' when c. 'When c. score between 60 and 69 then 'pass 'else' failed' end score grade from student Table a, course list B, student Table c where. student ID = c. student ID and B. course No. = c. course No. 2 create view V_Studasselect. student ID,. name, count (*) number of subjects, avg (score) average score from student Table a, course list B, and student Table c where. student ID = c. student ID and B. course No. = c. course No. and. student ID in (select. student ID from student Table a, Course table B, student Table c where. student ID = c. student ID and B. course No. = c. course No. and B. course name = 'English 'and c. score> 75)
Check the table name and field name by yourself.


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