Oracle---View

上一篇 / 下一篇  2010-03-31 12:09:31 / 个人分类:Oracle

View
Logically represents subsets of data from one or more tables
You can present logical subsets or combinations of data by creating views of tables. A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary.
1.Guidelines for Creating a View
The subquery that defines a view can contain complex SELECT syntax, including joins, groups, and subqueries.
The subquery that defines the view cannot contain an ORDER BY clause. The ORDER BY clause is specified when you retrieve data from the view.
If you do not specify a constraint name for a view created with the CHECK OPTION, the system will assign a default name in the format SYS_Cn.
You can use the OR REPLACE option to change the definition of the view without dropping and recreating it or regranting object privileges previously granted on it. 
 
A view provides the following advantages:
Restricts database access
Simplifies queries
Provides data independence
Allows multiple views of the same data
Can be dropped without removing the underlying data
 
SQL> CREATE VIEW         empvu10
  2  AS SELECT  empno, ename, job
  3  FROM                       emp
  4  WHERE                      deptno = 10;
 
Create a view by using column aliases in the subquery.
SQL> CREATE VIEW         salvu30
  2  AS SELECT  empno EMPLOYEE_NUMBER, ename NAME,
  3                             sal SALARY
  4  FROM                               emp
  5  WHERE                              deptno = 30;
View created.
 
retrieve data from view:
SQL>     SELECT *
  2      FROM    salvu30;
 
2.Modifying a View
The OR REPLACE option allows a view to be created even if one exists with this name already, thus replacing the old version of the view for its owner. This means that the view can be altered without dropping, recreating, and regranting object privileges.
SQL> CREATE OR REPLACE VIEW empvu10
  2      (employee_number, employee_name, job_title)
  3  AS SELECT   empno, ename, job
  4  FROM                               emp
  5  WHERE                              deptno = 10;
View created.
 
3.create complex view
SQL> CREATE VIEW        dept_sum_vu
  2                              (name, minsal, maxsal, avgsal)
  3  AS SELECT  d.dname, MIN(e.sal), MAX(e.sal),
  4                             AVG(e.sal)
  5  FROM                               emp e, dept d
  6  WHERE                              e.deptno = d.deptno
  7  GROUP BY    d.dname;
 
 
4.remove view
SQL> DROP VIEW empvu10;
View dropped.

TAG:

 

评分:0

我来说两句

Open Toolbar