Oracle create view语法


  1. 视图实际上是一个或多个表上的预定义查询，这些表称为基表。   
  2. 视图并不存储数据，只是在查询视图时才访问基表。   
  3. 视图的优点：   
  4.     ·限制用户只能通过视图检索数据，对用户屏蔽基表   
  5.     ·可以将复杂的查询编写为视图，减少用户查询的复杂度   
  6.     ·限制某个视图只能访问基表中的部分数据，提高了安全性   
  7. //   
  8. 创建视图   
  9. create [or replace] [{force|noforce}] view view_name   
  10. [(alias_name[,alias_name...])]   
  11. as subquery   
  12. [with {check option|read only} constraint constraint_name]   
  13. or replace:如果视图存在就替换它。   
  14. force:即使基表不存在也要建立该视图。   
  15. noforce:若基表不存在就不建立此视图，默认值。   
  16. view_name:视图名称。   
  17. alias_name:为自查询中的表达式指定一个别名，别名个数与子查询中列数相同。   
  18. subquery:指定一个子查询，它对基表进行查询，如果已经提供别名，可以在select子句之后的列表中使用别名   
  19. with check option:表名只有子查询检索的行才能被插入、删除、更新，   
  20.                   默认情况下，在插入、删除、更新行之前并不会检查这些行是否被子查询检索。   
  21. constraint_name:指定with check option或read only约束的名称。   
  22. with read only:说明只能对基表中的行进行只读访问。   
  23. //   
  24. 视图有两种基表类型   
  25. ·简单视图，包含一个子查询，它只能从一个基表中检索数据   
  26. ·复杂视图，包含一个子查询，有以下特点：   
  27.   ·从多个基表中检索数据   
  28.   ·使用group by或distinct子句对行进行分组   
  29.   ·包含函数调用   
  30. //   
  31. 实例1:   
  32. //   
  33. 测试数据:   
  34. SQL> select * from emp;   
  35. EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO   
  36. ----- ---------- --------- ----- ----------- --------- --------- ------   
  37.  7369 SMITH      CLERK      7902 1980-12-17     800.00               20   
  38.  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30   
  39.  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30   
  40.  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20   
  41.  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30   
  42.  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30   
  43.  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10   
  44.  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20   
  45.  7839 KING       PRESIDENT       1981-11-17    5000.00               10   
  46.  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30   
  47.  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20   
  48.  7900 JAMES      CLERK      7698 1981-12-3      950.00               30   
  49.  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20   
  50.  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10   
  51. //   
  52. SQL> select * from dept;   
  53. DEPTNO DNAME          LOC   
  54. ------ -------------- -------------   
  55.     10 ACCOUNTING     NEW YORK   
  56.     20 RESEARCH       DALLAS   
  57.     30 SALES          CHICAGO   
  58.     40 OPERATIONS     BOSTON   
  59. 为emp表建立一个简单视图，访问emp表的属性(empno,ename,job,sal)，并且sal>1200的员工   
  60. create view view_emp as  
  61. select empno,ename,job,sal   
  62. from emp   
  63. where sal>1200;   
  64. //   
  65. select * from view_emp;   
  66. EMPNO ENAME      JOB             SAL   
  67. ----- ---------- --------- ---------   
  68.  7499 ALLEN      SALESMAN    1600.00   
  69.  7521 WARD       SALESMAN    1250.00   
  70.  7566 JONES      MANAGER     2975.00   
  71.  7654 MARTIN     SALESMAN    1250.00   
  72.  7698 BLAKE      MANAGER     2850.00   
  73.  7782 CLARK      MANAGER     2450.00   
  74.  7788 SCOTT      ANALYST     3000.00   
  75.  7839 KING       PRESIDENT   5000.00   
  76.  7844 TURNER     SALESMAN    1500.00   
  77.  7902 FORD       ANALYST     3000.00   
  78.  7934 MILLER     CLERK       1300.00   
  79. /*从结果可知,我们将emp表中薪水大于1200的员工的empno,ename,sal查询了出来*/  
  80. 实例2：   
  81. 建立一个复杂视图   
  82. create or replace view view_emp_dept as  
  83. select d.deptno,d.dname,min(e.sal) min_sal,max(e.sal) max_sal   
  84. from emp e,dept d   
  85. where e.deptno=d.deptno(+)   
  86. group by d.deptno,d.dname;   
  87. //   
  88. select * from view_emp_dept;   
  89. DEPTNO DNAME             MIN_SAL    MAX_SAL   
  90. ------ -------------- ---------- ----------   
  91.     10 ACCOUNTING           1300       5000   
  92.     20 RESEARCH              800       3000   
  93.     30 SALES                 950       2850   
  94. /*正如我们所期待的,我们将scott用户下的emp表和dept表连接起来:  
  95. emp.deptno=dept.deptno(+),左外连接,  
  96. 既按照emp表中的部门来查询:  
  97. 部门号(deptno),部门名称(dname),部门最低薪水(min_sal),部门最高薪水(max_sal).*/  
  98. 修改视图   
  99. 修改视图时加上or replace即可,例如:   
  100. create view view_emp_sal as  
  101. select ename,job,deptno,sal   
  102. from emp   
  103. where sal>1000;   
  104. //   
  105. create or replace view view_emp_sal as  
  106. select ename,job,deptno,sal*12   
  107. from emp   
  108. where sal>1000;   
  109. //   
  110. 编译视图   
  111. 当基表改变时,建立在基表上的视图就会无效,   
  112. 我们可以通过下面这条语句从数据字典中查询无效的视图:   
  113. select 'alter view'||owner||'.'||object_name||'compile;'  
  114. from dba_objects   
  115. where status='INVALID' and object_type='VIEW';   
  116. //   
  117. 删除视图   
  118. drop view view_name;   
  119. //   
  120. 获取视图信息   
  121. 通过查看user_views视图   
  122. SQL> desc user_views;   
  123. Name             Type           Nullable Default Comments                                                      
  124. ---------------- -------------- -------- ------- ----------------------------------------------------------    
  125. VIEW_NAME        VARCHAR2(30)                    Name of the view                                              
  126. TEXT_LENGTH      NUMBER         Y                Length of the view text                                       
  127. TEXT             LONG           Y                View text                                                     
  128. TYPE_TEXT_LENGTH NUMBER         Y                Length of the type clause of the object view                  
  129. TYPE_TEXT        VARCHAR2(4000) Y                Type clause of the object view                                
  130. OID_TEXT_LENGTH  NUMBER         Y                Length of the WITH OBJECT OID clause of the object view       
  131. OID_TEXT         VARCHAR2(4000) Y                WITH OBJECT OID clause of the object view                     
  132. VIEW_TYPE_OWNER  VARCHAR2(30)   Y                Owner of the type of the view if the view is a object view    
  133. VIEW_TYPE        VARCHAR2(30)   Y                Type of the view if the view is a object view                 
  134. SUPERVIEW_NAME   VARCHAR2(30)   Y                Name of the superview, if view is a subview                   
  135. //   
  136. SQL> select view_name,text_length,text   
  137.   2  from user_views;   
  138. VIEW_NAME                      TEXT_LENGTH TEXT   
  139. ------------------------------ ----------- --------------------------------------------------------------------------------   
  140. VIEW_EMP                                52 select empno,ename,job,sal   
  141.                                            from emp   
  142.                                            where sal>1200   
  143. VIEW_EMP_DEPT                          134 select d.deptno,d.dname,min(e.sal) min_sal,max(e.sal) max_sal   
  144.                                            from emp e,dept d   
  145. //  

相关内容