5.4.5  删除索引
删除索引使用DROP INDEX语句。在当前用户中删除索引时，需要具备DROP INDEX系统权限；如果是其他用户方案中删除索引，则需要具有DROP ANY INDEX系统权限。下面的语句将删除idx_ename_empno索引：

SQL> DROP INDEX idx_ename_empno;
索引已删除。

对于唯一性索引，如果是在定义约束时由Oracle自动建立的，则可以通过使用DISABLE禁用约束或删除约束的方法来删除对应的索引。
注意：在删除表时，所有基于该表的索引也会被自动删除。
当以下情况发生时，需要从数据库中移除索引。
    索引不再需要时，应该删除以释放所占用的空间。
    索引没有经常使用，只是极少数查询会使用到该索引时。
    如果索引中包含损坏的数据块，或者是索引碎片过多时，应删除该索引，然后重建索引。
    如果表数据被移动后导致索引无效，此时应删除该索引，然后重建。
    当使用SQL*Loader给表中装载大量数据时，系统也会给表的索引增加数据，为了加快装载速度，可以在装载之前删除索引，在装载之后重新创建索引。

5.5  使 用 视 图
视图是表的另外一种表示形式，它通过使用SELECT语句定义一个视图所需显示数据的虚表，这个虚表只有对视图的定义，并不包含实际的数据。可以说，视图是在表的基础上用来展现数据的一种方式。
5.5.1  视图简介
视图与表一样，同属于Oracle中的方案对象，因此视图会出现在数据字典中。视图如其名所示，是数据的一种展现方式，视图本身不包含任何数据，它通过SELECT语句使用来自一个或多个表中的数据创建逻辑表，因此可以将视图看成是一个“虚表”，或者只是一个“存储的查询”。在创建视图时，只是将视图的定义信息保存到数据字典中，并不将实际的数据复制到任何地方。
举个例子，emp表和dept表保存了员工和部门的详细信息，emp表中有一个指向dept表的deptno的外键，为了向用户提供emp表和dept表中的详细信息，可以创建一个名为view_dept_emp的视图，这个视图的数据来源于emp表和dept表，如图5.22所示。
 
图5.22  视图示意图
下面的代码5.14创建了view_dept_emp视图。
代码5.14  创建视图语句
CREATE OR REPLACE VIEW view_dept_emp
AS
   SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, dept.dname,
          dept.loc
     FROM emp, dept
    WHERE emp.deptno = dept.deptno;

有了这个视图后，可以像查询普通的表一样查询视图，如以下查询语句所示。

SQL> SELECT * FROM view_dept_emp;
     EMPNO ENAME      JOB             MGR       HIREDATE          DNAME                    
     ----- ----- --------- ---------- ---------- -------------
      7369 史密斯       职员            7902     17-12月-80      研究部
      7499 艾伦           销售人员        7698     20-2月 -81       销售部                   
      7521 沃德           销售人员        7698      22-2月 -81        销售部                   
      7566 约翰           经理            7839        02-4月 -81        研究部                   
      7654 马丁           销售人员        7698        28-2月 -81        销售部                   
      7698 布莱克         经理            7839        01-3月 -81        销售部                   
      7782 克拉克         经理            7839        09-5月 -81        财务部                   
      7788 斯科特         职员            7566        09-12月-82        研究部                   
      7839 金             老板                    17-11月-81       财务部                   
      7844 特纳           销售人员        7698        08-8月 -81        销售部                   
      7876 亚当斯         职员            7788        12-1月 -83        研究部                   
      7900 吉姆           职员            7698        03-12月-81        销售部                   
      7902 福特           分析人员        7566        03-12月-81        研究部                   
      7892 张八           IT                                          研究部                    
      7893 霍九                                                   研究部                    
      7894 霍十                                                   研究部                    
已选择16行。

视图所查询的表叫做基础表，视图是包含了一个或多个基础表（或者是其他视图）中部分数据的一个表。图5.22中emp和dept表是视图view_dept_emp的基础表，view_dept_emp视图并不占用任何实际的存储空间，当emp或dept表的数据发生改变时，视图中的数据也会发生改变。
视图具有如下几个优点。
    视图限制数据的访问，因为视图能够选择性地显示表中的列。 
    视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中查询信息，而用户不必知道怎样写连接语句。 
    视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回         数据。 
    视图提供用户组，按照他们的特殊标准访问数据。

5.5.2  创建视图
视图按照其是否涉及DML操作，又可分为如下两类。
    简单视图：视图的数据仅来自一个表，在视图的SELECT语句中不包含函数或数据分组，总是可以通过视图来执行DML操作。 
    复杂视图：视图的数据来自多个表，可以包含函数或数据分组，并不总是可以通过视图进行DML操作。
视图的创建语法如下所示。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

语法中的关键字的含义如下所示。
    OR REPLACE：如果视图已经存在，重新创建它。 
    FORCE：创建视图，而不管基表是否存在。
    NOFORCE：只在基表存在的情况下创建视图（这是默认值）。
    view：视图的名字。
    alias：为由视图查询选择的表达式指定名字（别名的个数必须与由视图选择的表达式的个数匹配）。
    subquery：是一个完整的SELECT 语句（对于在SELECT 列表中的字段，可以用别名）。
    WITH CHECK OPTION：指定在视图中只有可访问的行才能被插入或修改。
    constraint：为CHECK OPTION 约束指定的名字。
    WITH READ ONLY：确保在该视图中没有DML操作被执行。
一般的创建视图的方式是先测试SELECT语句的正确性，然后将SELECT语句作为视图的subquery进行查询。
1．简单视图
简单视图是指基于单个表建立的，不包含任何函数、表达式和分组数据的视图。例如可以根据emp表中部门编号为20的员工创建一个视图，如以下语句所示。

SQL> CREATE OR REPLACE VIEW v_deptemp
    AS
       SELECT empno, ename, job, mgr, hiredate, sal, comm
         FROM emp
        WHERE deptno = 20;
视图已创建。

上述语法是创建视图的最简单的语法形式，可以像使用表一样来使用这个视图，例如可以查看视图结果，对视图应用DML语句。下面的语句查询视图数据：

SQL> SELECT * FROM v_deptemp;
     EMPNO ENAME    JOB          MGR     HIREDATE          SAL       COMM
     ----- ----- ------------ ------ ------------ ----------- ----------
      7369 史密斯    职员          7902   17-12月-80       2425.08       300
      7566 约翰      经理          7839   02-4月 -81       3570           297.5
      7788 斯科特    职员          7566   09-12月-82       1760.2        129.6
      7876 亚当斯    职员          7788   12-1月 -83       1440           120
      7902 福特      分析人员      7566   03-12月-81       3600           300
      7892 张八      IT
      7893 霍九
      7894 霍十
已选择8行。

下面的语句向视图v_deptemp中插入了一条新的记录：

SQL> INSERT INTO v_deptemp VALUES(7999,'李思','经理'，7369,SYSDATE,8000,NULL);
已创建 1 行。

如果需要限制向视图插入数据，只插入满足视图中约束条件的数据，例如向v_deptemp视图中插入的数据要符合deptno为20这个约束，可以使用WITH CHECK OPTION选项定义CHECK约束。如果在视图上执行INSERT、UPDATE和DELETE语句，就要求所操作的数据必须是SELECT查询所能选择出来的数据，示例如代码5.15所示。

SQL> CREATE OR REPLACE VIEW v_deptemp_check
    AS
       SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
         FROM emp
        WHERE deptno = 20
              WITH CHECK OPTION CONSTRAINT v_empdept_chk;
视图已创建。
SQL> INSERT INTO v_deptemp_check
         VALUES (7992, '赵六', '职员', 7369, SYSDATE, 8000, NULL, 30);
INSERT INTO v_deptemp_check
            *
第 1 行出现错误:
ORA-01402: 视图 WITH CHECK OPTION where 子句违规

代码中使用了WITH CHECK OPTION语句，通过CONSTRAINT关键字指定了约束的名称，当执行DML语句时，如果操作的数据不在SELECT查询所能选择的数据范围内，那么将触发ORA-01402异常。
如果想要禁止在视图上执行INSERT、UPDATE或DELETE操作，可以使用WITH READ ONLY选项。例如下面的语句创建了v_deptemp_readonly视图，尽管SELECT语句与v_deptemp完全相同，但是如果在视图上执行DML语句，将提示异常，如以下代码         所示。

SQL> CREATE OR REPLACE VIEW v_deptemp_readonly
    AS
       SELECT empno, ename, job, mgr, hiredate, sal, comm
         FROM emp
        WHERE deptno = 20
       WITH READ ONLY;
视图已创建。
SQL>  INSERT INTO v_deptemp_readonly VALUES(7999,'李思','经理'，7369,SYSDATE,800
0,NULL);
 INSERT INTO v_deptemp_readonly VALUES(7999,'李思','经理'，7369,SYSDATE,8000,NUL
L)
*
第 1 行出现错误:
ORA-42399: 无法对只读视图执行 DML 操作
上述的代码中在创建视图后，试图向视图中插入一条新的记录时将产生ORA-42399异常。
可以通过为视图指定别名来提供更加友好的视图名称，例如下面的代码指定了视图的中文字段名，以便提供用户友好的视图查询，创建及查询视图的语句如下所示。

SQL> CREATE OR REPLACE VIEW v_deptemp_alias (员工编号,
                                            员工名称,
                                            职位,
                                            经理,
                                            雇佣日期,
                                            薪水,
                                            备注
                                           )
    AS
      SELECT empno, ename, job, mgr, hiredate, sal, comm
        FROM emp
       WHERE deptno = 20;
视图已创建。
SQL>  SELECT * FROM v_deptemp_alias;
    员工编号  员工名称             职位                    
    ------- ------- ---------------------------
      7369   史密斯               职员                    
      7566   约翰                 经理                    
      7788   斯科特               职员                    
      7876   亚当斯               职员                    
      7902   福特                 分析人员                
      7892   张八                 IT
      7893   霍九
      7894   霍十
已选择8行。

可以看到，指定了视图别名后，现在查询视图时，将显示中文的名称。
注意：被列出的别名的个数必须与在子查询中被选择的表达式相匹配。
2．复杂视图
复杂视图中可以包含来自多个表的数据，可以包含函数或分组等。例如要创建一个统计各部门的薪资数的视图，可以创建如下的复杂视图：

CREATE OR REPLACE VIEW v_sumdept(部门名称，部门薪资)
AS
SELECT   dept.dname, SUM (emp.sal) sumsal
    FROM emp, dept
   WHERE emp.deptno = dept.deptno(+)
GROUP BY dept.dname;

这个复杂视图中既包含了表间的连接，也包含了聚合函数来进行聚合操作，普通用户可以通过该视图很轻松地获取部门的薪资总数，而不用编写一些麻烦的查询语句。查询结果如下所示。

SQL> SELECT * FROM v_sumdept;
部门名称                 部门薪资
------------ --------------------------
研究部                  12795.28
财务部                  12117.55
销售部                  9900
                        6500

5.5.3  修改视图
修改视图并不会对视图的基础表进行修改，所做的更改只是改变数据字典中对该视图的定义信息，视图的所有基础对象都不会受到任何影响。有如下4点需要注意。
    由于视图只是虚表，因此对视图的更改不会影响到底层的基础表。
    如果视图中具有WITH CHECK OPTION选项，但是重定义时没有使用WITH CHECK OPTION选项，则以前的此选项将自动删除。
    更改视图后，依赖于该视图的所有视图和PL/SQL程序将都会变成INVALID状态。
    更新基础表后，视图会失效，可以对视图进行重编译使视图有效。
实际上当在CREATE语句中使用了OR REPLACE关键字后，就可以随时对视图进行更改。这种方法先删除原来的视图，然后创建一个新的视图取代原有的视图，同时会保留在该视图上授予的各种权限，但是与该视图相关的存储过程和视图会失效。
下面的语句对视图v_deptemp_check进行了更改，使其过滤部门编号为30的记录，并且去掉了WITH CHECK OPTION选项。

SQL> CREATE OR REPLACE VIEW v_deptemp_check
    AS
       SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
         FROM emp
        WHERE deptno = 30;
视图已创建。

因为去掉了WITH CHECK OPTION约束，所以可以向v_deptemp_check视图中插入不在SELECT列表中的信息，比如可以插入员工编号为20的员工，如以下语句所示。

SQL> INSERT INTO v_deptemp_check
         VALUES (7992, '赵六', '职员', 7369, SYSDATE, 8000, NULL, 20);
已创建 1 行。

当视图的基础表发生改变后，视图会变成失效状态，Oracle会在视图被访问时自动重新编译这些视图，也可以通过使用ALTER语句显式地重新编译视图。当视图重新被编译后，依赖该视图的对象会失效。
注意：ALTER VIEW语句仅能重新编译视图，要修改视图的定义，需要使用CREATE  OR REPLACE VIEW语句。
要查询一个视图的有效性状态，可以通过Toad或PL/SQL Developer等可视化工具，在Toad的Schema Browser的Views节点中可以看到每一个视图的详细信息和有效状态，如图5.23所示。
也可以通过查询数据字典视图user_objects来获取视图的详细信息，例如下面的语句将查询v_deptemp视图的有效性状态：

[img]http://www.tu265.com/di-32a30c3dcf9bb7b0608797b205365484.png[/img]

SQL> COL OBJECT_NAME FORMAT A20;
SQL> SELECT last_ddl_time, object_name, status
      FROM user_objects
     WHERE object_name = 'V_DEPTEMP';
LAST_DDL_TIME    OBJECT_NAME           STATUS
------------- --------------- ----------------------
28-8月 -11         V_DEPTEMP             VALID

下面对v_deptemp视图的基础表emp进行修改，使之影响到视图的有效性，修改语句如下：

ALTER TABLE emp MODIFY ename VARCHAR2(20);

再次查询user_objects视图，会看到v_deptemp视图变为INVALID状态，为了使视图立即有效，可以执行如下的ALTER VIEW语句：

ALTER VIEW v_deptemp COMPILE;

执行完成后，可以看到v_deptemp视图现在又变为VALID状态。

5.5.4  删除视图
当视图不再需要时，可以使用DROP VIEW语句对视图进行删除，如果要删除的视图在其他的方案中，则需要具备DROP ANY VIEW系统权限。
例如要删除v_deptemp视图，可以使用如下语句：

SQL> DROP VIEW v_deptemp;
视图已删除。

当视图被删除之后，视图的定义会从数据字典中删除，在视图上授予的权限也被删除，同时其他引用该视图的视图及存储过程等都会失效。
5.6  小    结
本章介绍了如何创建Oracle表，使用CREATE TABLE语句，以及使用一些集成化开发工具提供的设计器来创建表，介绍了如何根据现有的表创建当前表的副本。在约束小节，讨论了与数据表相关的三大约束的定义和使用，包含主键、外键和检查约束的作用与操作方式。在修改表小节，讨论了如何修改表的列，如何使用ALTER TABLE语句添加修改约束等功能。在索引部分，详细地介绍了Oracle提供的索引类型和索引的使用原理，介绍了如何在Oracle中创建和管理索引。最后的视图一节介绍了如何使用视图来提供表数据的有效显示方式，讨论了如何进行视图的增、删与改操作。

第6章  查询数据表
在操纵Oracle数据库的过程中，使用SELECT的各种组合查询数据库表数据是DBA和PL/SQL开发人员非常频繁的工作之一，查询数据库主要是通过操作SELECT语句来完成的本章将介绍如何使用SELECT语句实现各种各样的数据查询、统计、分组及汇总等         操作。
6.1  简 单 查 询
在关系型数据库系统中，SELECT语句是SQL语言中的核心语句，它是从数据库中检索数据的基础，也是SQL语言中最强大和复杂的语句。通过在SELECT语句中组合其他的关键字和子句，可以实现无数种查找和查看信息的方法。本节将介绍使用SELECT的基本的查询语法，在下一节将讨论使用SELECT的较高级的使用方法。
注意：本章的内容主要基于scott方案下的emp表和dept表，为了实现简体中文记录的显示，请在运行本章的示例代码前，先使用配套源代码中的initial.sql文件包含的SQL语句将emp和dept表的记录更新为中文内容。

6.1.1  查询表数据
SQL SELECT语句可以从数据库中返回下列信息。
    列选择：使用SELECT语句的列选择功能可以选择表中特定的列，这些列是需要作为结果集返回的，或者可以使用通配符*选择所有的列。
    行选择：SELECT语句可以选择表中特定条件的行，可以使用不同的标准限制所能返回的行。
    连接：使用SELECT语句的连接功能可以集合选择多个表的数据。
最基本的SELECT语法如下所示。

SELECT { [alias.]column | expression | [alias.]* [ , … ] }
FROM [schema.]table [alias];

花括号中的内容表示从“|”符号中包含的子句中选择其中一项，而方括号中的内容表示可选择内容。可以看到，一个最基本的SQL语句由如下两部分组成。
    SELECT子句：指定要被显示的列或表达式，可以使用*选择所有的列。
    FROM子句：指定所要查询的表，该表包含SELECT子句中的字段的列表。
下面分别就语法中出现的关键字进行介绍。
1．查询特定的列数据
要选择表中特定的列，可以在SELECT语句后面加上以逗号分隔的列名称。例如要查询数据字典中所有视图的列表，可以使用如下SELECT语句：

SELECT view_name, text FROM user_views;

在语句中，view_name和text是列名，多个列名之间用逗号进行分隔。FROM后面的子句指定表名user_views，本语句不包含任何方案名称，因为这个视图属于当前用户的方案。如果要查询属于其他方案的表或视图，则需要使用“方案名.表名”这种格式进行查询。例如下面的语法查询scott方案中的emp表。

SELECT ename, empno, job, hiredate FROM scott.emp;

SELECT中基本的语法元素的含义如下所示。
    SELECT：一个或多个字段的列表。
    *：选择所有的列。
    DISTINCT：禁止重复。
    column|expression：选择指定的字段或表达式。
    alias：给所选择的列不同的标题。
    FROM table：指定包含列的表。
在本章中，关键字、子句和语句的概念分别如下所示。
    关键字引用一个或单个SQL元素，比如SELECT和FROM是关键字。
    子句是SQL语句的一个部分，比如SELECT ename,empno,...是一个子句。
    语句是两个或多个子句的组合，比如SELECT * FROM scott.emp是一个SQL语句。
2．查询所有列数据
要查询一个表中所有的列，可以使用*通配符，例如要查询emp表中的所有列的数据，可以使用如下的语法：

SELECT * FROM emp;

注意：出于查询性能考虑，Oracle并不建议在获取所有列值时使用*关键字，建议列出所有的列名进行查询。