Oracle view basics and Examples

Oracle view basics and Examples

Abstract: This document describes the concepts, advantages and disadvantages, application, and precautions of views.

I. Concept

A view is a logical table based on one or more tables or another view. Unlike tables, a view does not contain any data. A table is an entity that exists independently. It is the basic structure used to store data. The view is just a definition that corresponds to a query statement. View data comes from some tables, which are called base tables. The database only stores the view definition in the data dictionary.

Ii. Advantages

1. Centralized data for users to simplify data query and processing.

2. Eliminate the complexity of the database, so you do not have to understand the complexity of the database.

3. simplify the management of user permissions and grant users the permission to use views only.

4. Data access security can be improved. The view can only access specific parts of the table in the database, which limits the user's access to all rows and columns of the table.

5. easy data sharing. Multiple users do not need to define the required data.

Iii. View Category 1. Simple View

A view that is based on a single table and does not contain functions or expressions. You can execute DML statements (add, delete, or modify operations) on this view ).

2. complex view

A view that is based on a single or multiple tables, or contains functions, expressions, or grouped data. When executing DML statements on this view, it must meet specific conditions. Note: aliases must be defined for functions or expressions when defining complex views.

3. Connection View

A view created based on multiple tables. Generally, INSERT, UPDATE, and DELETE operations are not performed on The View.

4. Read-only view

A view that ONLY allows the SELECT operation. The with read only option is specified for this view. INSERT, UPDATE, and DELETE operations cannot be performed on this view.

5. check constraints View

With check option is used to define CHECK constraints on the view, that is, when the INSERT or UPDATE operation is performed on the view, the data must meet the query results.

Iv. Create view 1. Basic syntax

CREATE [OR REPLACE] VIEW  view_name  [(column_name1[,column_name2… AS select_statement [WITH CHECK OPTION] [WITH READ ONLY]

2. parameter description

Create or Repalce: used to CREATE and modify a view with check option: used to CREATE a view with read only: used to CREATE a READ-ONLY view

3. DML operation principles

A) DML operations can be performed in a simple view.

B) You cannot modify the base table data or insert data in the view in the following situations:

I. Set operators (union, intersect, minus)

Ii. DISTINCT keywords

Iii. group by, order by, connect by, or start with clause

V. subquery

Vi. Grouping Functions

Vii. columns to be updated are not defined by the "column expression ".

Vx. All not null columns in the base table belong to this view.

V. How to query view and table update Permissions

select table_name,column_name,updatable,insertable,deletable from user_updatable_columns; 

Description

Updatable indicates whether the current field can be modified.

Insertable indicates whether the current field can be added.

Deletable indicates whether the current field can be deleted.

Vi. Example

-- Drop table emp1; create table emp1 as select * from emp; -- create or replace view v_emp1asselect * from emp1; -- Query select * from v_emp1; -- update v_emp1 set v_emp1.ENAME = 'andy 'where v_emp1.JOB = 'cler'; -- add insert into v_emp1 values (7777, 'chy', 'manager', 8888, sysdate, 10000,111 1.11, 20); -- delete from v_emp1 where v_emp1.EMPNO = 7777; -- complex view, connected to only two base tables, does not contain grouping functions, group by, distinct commands, etc. Create or replace view v_complexasselect emp1.ename, emp1.job, dept. dname from emp1, dept where emp1.deptno = dept. deptnowith check option; -- Query select * from v_complex; -- modify update v_complex set v_complex.ename = 'andy 'where v_complex.job = 'manager'; -- add -- error: ORA-01776: you cannot use the connection view to modify multiple base tables insert into v_complex (v_complex.ename, v_complex.job, v_complex.dname) values ('chy', 'manager', 'sales'); -- delete from v _ Complex where v_complex.ename = 'chy'; -- complex view, containing elements that cannot be DML, generally used only for queries, can be added with read only; create or replace view v_complex_readonlyas -- aliases must be used for columns using aggregate functions! Select max (emp1.sal) max_sal from emp1, dept where emp1.deptno = dept. deptno group by dept. deptnowith read only; -- Query select * from v_complex_readonly; -- delete the view create or replace view v_for_deleteasselect * from empwith read only; drop view v_for_delete;