How to create and process images in Oracle

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. Oracle database objects are divided into five types: tables, views, sequences, indexes, and synonyms.

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:

1. A simple view only obtains data from a single table, while a complex view retrieves data from multiple tables;

2. A simple view does not contain functions and data groups, but contains complex views;

3. A simple view can implement DML operations, but not a complex view.

View creation:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name   [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY]

Here: 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: 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;

View definition principles:

1. View queries can use complex select syntax, including connection/grouping queries and subqueries;

2. The order by clause cannot be used in queries without the with check option and read only clauses;

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 permission can be re-Granted.

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, data retrieval:

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 obtain related information through DESC user_views.

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   empno=7902;

An error occurs:

Update vw_emp20

*

Error is located in the first line:

ORA-01402: view with check option violation where clause

View deletion: The drop view view_name statement deletes 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.