Oracle (8) View and query database object methods

Views View

A view is one of the objects of a database. A view is also called a virtual table, which is essentially a SELECT statement.

The result set of a SELECT statement is given a name that is the name of the view.

Role:

1. Complex queries can be simplified

2. Access to data can be restricted and the other data of the base table is safe and confidential.

Creating a Table CREATE TABLE EMP (

Empno Number (6), name Char (ten), ID number (4), Deptno number (Ten), Job varchar2 (10));

* Create a view:
Create View emp_v asselect *  from emp where ID 1 between ten;    
When you create a view, the table that the create corresponds to is called the base table. 
* Query View
select *from emp_v;
There are three views depending on how the query is made:
1. Simple view: Based on a single table, and does not contain any function operations, expressions and groupings, both a subset of a single table. 
2. Complex view: Based on a single table, a view containing a single-line function/expression/grouping. 
3. Connection view: A view based on a multi-table setup. 
* Delete View
drop view emp_v;

* A simple view can perform DML operations, but a complex view does not allow DML operations.

Modify View: The structure of the view depends on the corresponding subquery, so modifying the view replaces the corresponding
Sub-query. REPLACE
CREATE OR REPLACE VIEW v_emp_j
as
SELECT empno,ename,sal
From Emp_xiaojie;
SELECT * from V_emp_j;

improper DML operations on a view pollute the base table data:
when you perform a DML operation on a simple view, the view does the corresponding operation on the base table, but the affected data corresponds to the view
is not visible, is the pollution of the base table data.

to prevent the view from polluting the data on the base table. We can add check option requirements to view (check constraints)
whether the operation's data is visible if it is not visible when the view is checked when the operation is made when it is DML
operation is not allowed.

* A simple view can perform DML operations, but a complex view does not allow DML operations.
The view itself is not a data-only logical mapping of the base table, so
when you perform a DML operation on a view, you are actually manipulating the base table.

A DML operation on a view is actually a DML operation on the base table,
If you do not constrain, we do not see the view for DML operations on the
the data from the base table is contaminated.

Basic principles of DML operations on a view:
* A simple view can perform DML operations, except in the following cases
a non-empty column is defined in the base table, but a simple view corresponds to a SELECT
statement does not contain this non-empty column, resulting in non-empty columns that are not considered for the view.
You cannot INSERT operations on the view at this time.
* Complex views do not allow DML operations
The *DML operation cannot violate the constraints of the base table.

creates a constrained view with CHECK OPTION.
CREATE OR REPLACE VIEW v_emp_j
as
SELECT empno,ename,sal
From Emp_xiaojie
WHERE sal<3000
with CHECK OPTION;--the base table cannot be contaminated with data after adding constraints.
SELECT * from V_emp_j;

creates a view with a READ only constraint.
A simple DML operation is legal but not secure if DML is not being done in the view
if necessary, to avoid this, declare read-only when building the view, including
The base table data is not modified illegally.
Unable to perform DML operation on read-only view.
CREATE OR REPLACE VIEW v_emp_j
as
SELECT empno,ename,sal
From Emp_xiaojie
WHERE sal<3000
With read only;--for read-only

Data dictionary:
a data dictionary is a series of tables in which data is maintained by the database and records inventory information.
1. Query all views in the data dictionary user_objects:
SELECT object_name from User_objects
where object_type= ' view ';--Find all views
View : Views table: Table sequence: Sequence index: Index
2. Query all the specified views in the data dictionary user_views:

3. Query the view in the data dictionary user_updatable_columns:
SELECT column_name,insertable,updatable,deletable--See Operation Permissions
From user_updatable_columns
WHERE table_name= ' V_emp_j ';

Oracle (8) View and query database object methods