1z0-051-ddl-View

View

The view is nothing more than Select statement.

Note: The view is the same namespace as the table share: It is syntactically correct to use the view name anywhere you can use the table name.

1 reasons to use the view

( 1 ) with a view that enforces security

Sql>create View Scott.emp_fin as Select

Hiredate,job_id,sal,comm,deptnofrom scott.emp;

Sql>select Dname,sum (SAL) from Dept Natural joins Emp_fin Group by Dname;

( 2 ) to simplify the user SQL the View

Sql>create View Dept_sal as

Selectd.dname,sum (e.sal) from Dept D left OUTER join EMP E on D.deptno=e.deptno

Group Byd.dname

Order Byd.dname;

Sql>select * from Dept_sal;

( 3 ) To prevent the wrong view

Sql>create table EMP (empno number constraint EMP_EMPNO_PK primary key,

ENAMEVARCHAR2 (x), Deptno number,active varchar2 (1) Default ' Y ');

Sql>create View Current_staff as SELECT * from emp where active= ' Y ';

( 4 A view that makes data easy to understand

In addition to providing data to the user in an easy-to-understand form, the view provides an abstraction layer between the objects that the user sees and the objects stored in the database, which is quite valuable for maintenance. Views are important techniques for enabling applications to be ported between different databases.

( 5 ) to improve the performance of the view

Sql>create View Dept_emp as

Select/*+use_hash (EMP Dept) */Dname,ename

From deptnatural join EMP;

2 , create, change, and delete views

• A simple view extracts data from a schedule, does not apply a function, does not aggregate

• Complex views can join schedules, use functions, and make aggregations.

( 1 syntax to create a view:

CREATE [OR REPLACE] [Force | Noforce] VIEW

[Schema.] viewname[(alias [, alias] ...)]

Assubquery

[Withcheck OPTION [CONSTRAINT ConstraintName]

[Withread only [CONSTRAINT ConstraintName]

• OR REPLACE If the view exists, it is deleted before it is created

• force| Noforce The Force keyword creates a view even though there is no apparent table in the subquery , the schedule does not exist, andnoforce An error occurs

• With CHECK OPTION if the subquery includes where clause, this option prevents the insertion of rows that are not visible in the view, or prevents updates that cause rows to disappear from the view.

• With READ only prevent any DML through the view.

• CONSTRAINT ConstraintName

( 2 ) Delete View

DROP View[schema.] ViewName

Case

Sql>conn Peenboo

Sql>create View Emp_anon_v as

Select Hiredate,job,sla,comm,deptno from EMP;

Sql>create View Dept_anon_v as

selectdeptno,dname,location_id from Dept;

Aggregation of the above two query joins into a complex view.

Sql>create View Dept_sum_v as

Selecte.deptno,count (1) staff,sum (e.sal) salaries,d.dname from Emp_anon_v e Joindept_anon_v D

One.deptno = D.deptno

Group Bye.deptno,d.dname;

This article is from the "struggle more than" blog, please be sure to keep this source http://peenboo.blog.51cto.com/2865551/1793885

1z0-051-ddl-View