Java面试准备十八：数据库——临时表、视图

1. Oracle临时表
2. Oracle视图

参考Oracle临时表总结
Oracle临时表
Oracle视图说明

1. Oracle临时表
临时表概念

（1）临时表只在Oracle 8i以上产品中支持。

（2）临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit的时候，临时表的数据自动清空。但是临时表的结构以及元数据还存储在用户的数据字典中。

（3）ORACLE的临时表创建之后基本不占用表空间，临时表并非存放在用户的表空间中，而是存放在session所指定的临时表空间中。如果你没有指定临时表（包括临时表的索引）存放的表空间的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。

（4）可以对临时表创建索引，视图，触发器。

（5）尽管对临时表的DML操作速度比较快，但同样也是要产生Redo Log，只是同样的DML，比对permanent产生的Redo Log要少 。

临时表不足之处

（1）不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时也就无法使用临时表了。

（2）不支持主外键关系。

临时表特性和性能（与普通表和视图的比较）
（1）临时表只在当前连接/事务内有效

（2） 临时表不建立索引，所以如果数据量比较大或进行多次查询时，不推荐使用（临时表概念中的第4点不是说可以对临时表创建索引吗？）

（3）数据处理比较复杂的时候比较快，反之视图比较快

（4）在仅仅查询数据的时候建议用游标：open cursor for ‘sql clause’

临时表的应用

对于一个电子商务类网站，不同消费者在网站上购物，就是一个独立的 SESSION，选购商品放进购物车中，最后将购物车中的商品进行结算。也就是说，必须在整个SESSION期间保存购物车中的信息。同时，还存在有些消费者，往往最终结账时放弃购买商品。如果，直接将消费者选购信息存放在最终表（PERMANENT）中，必然对最终表造成非常大的压力。因此，对于这种案例，就可以采用创建临时表(ON COMMIT PRESERVE ROWS)的方法来解决。数据只在 SESSION 期间有效，对于结算成功的有效数据，转移到最终表中后，ORACLE自动TRUNCATE 临时数据；对于放弃结算的数据，ORACLE 同样自动进行 TRUNCATE ，而无须编码控制，并且最终表只处理有效订单，减轻了频繁的DML的压力。（不是很懂，现实中购物车的数据是永久保存的啊，并不是退出SESSION就被清空掉）
Temp Table 的另一个应用，就是存放数据分析的中间数据。

创建临时表
Oracle临时表，有两种类型：

• 会话级临时表
• 事务级临时表

会话级临时表

（1）表只在当前会话中有效，如果你退出当前SESSION，临时表中的数据就被截断了（truncate table，即数据清空了）。
（2）如果你以一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。

**注意：**ORACLE truncate 掉的数据仅仅是分配给不同的session 或transaction的temp segment上的数据，而不是将整张表数据truncate掉。当commit的时候则数据还在，当rollback的时候数据也是一样被回滚。

会话级的临时表创建方法：

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(<column specification>) ON COMMIT PRESERVE ROWS;

或者

CREATE TEMPORARY TABLE TABLE_NAME ON COMMIT PRESERVE ROWS AS SELECT * FROM TABLE_NAME;

示例：
执行1：

CREATE GLOBAL TEMPORARY TABLE t1(ID NUMBER(2) )ON COMMIT PRESERVE ROWS;INSERT INTO t1 VALUES(1);COMMIT;SELECT * FROM t1;

结果1：

        ID----------         1

执行2：

INSERT INTO t1 VALUES(2);SELECT * FROM t1;

结果2：

        ID----------         1         2

执行3：

ROLLBACK;SELECT * FROM t1;

结果3：

        ID----------         1

事务级临时表（默认类型）

该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表的数据将自行被阶段，其他的内容和会话级的临时表的一致（包括退出session的时候，事务级的临时表也会被自动截断）。一旦commit之后，数据就被自动truncate掉了。

事务级的临时表创建方法：

CREATE GLOBAL TEMPORARY TABLE t2(<COLUMN SPECIFICATION>)ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE t2  ON COMMIT DELETE AS SELECT * FROM t2;

**注：**CREATE GLOBAL TEMPORARY TABLE t2; –在不指名类型的情况下，默认为事务临时表

示例：
SQL语句1：

CREATE  GLOBAL TEMPORARY TABLE t2(ID NUMBER(2))ON COMMIT DELETE ROWS;INSERT INTO t2 VALUES(1);SELECT * FROM t2;

结果1：

    ID----------     1

SQL语句2：

COMMIT;SELECT * FROM t2;

结果2：

未选定行

2. Oracle视图说明

视图的定义

视图（view），也称为虚表，不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候，只是重新执行SQL。

视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。

视图的定义存在数据库中，与此定义相关的数据并没有再存一份在数据库中。通过视图看到的数据存放在基表中。

视图看上去非常像数据库的物理表（物理表示是什么？），对它的操作同任何其他的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反的，基表数据的修改也会自动反映到由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询），下面有说。

视图的作用

（1）简单性
看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必以后的操作每次都指定全部的条件。

（2）安全性
通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象中，但不能授权到数据库特定行和特定列中。通过视图，用户可以被限制在数据的不同子集上。

（3）逻辑数据独立性。
视图可帮助用户屏蔽真实表结构变化带来的影响。

视图可以使应用程序和数据表在一定程度上独立。

视图的基本语法

视图的创建

CREATE[OR REPLACE][FORCE][NOFORCE]VIEW view_name[(column_name)[, ...]]AS select_statement[WITH CHECK OPTION[CONSTRAINT constraint_name]][WITH READ ONLY]

在下列情况下，必须指定视图列的名称

• 由算术表达式，系统内置函数或者常量得到的列
• 共享同一个表名连接得到的列
• 希望视图中的列名与表中的列名不同的时候

REPLACE：覆盖
FORCE：强制创建视图，如果视图的基表是否存在和是否拥有创建视图的权限
NOFORCE：如果视图的基表不存在或不拥有创建视图的权限时，则不创建视图
WITH CHECK OPTION：指出在视图上所进行的修改都要符合select_statement所指定的限制条件（不懂？）
WITH READ ONLY：只允许查看视图

视图定义的原则

（1）视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询
（2）在没有WITH CHECK OPTION 和 READ ONLY的情况下，查询中不能使用ORDER BY字句;（为什么？）
（3）如果没有为CHECK OPTION约束命名，系统会指定为之命名，形式为SYS_Cn;（不懂）
（4）OR REPLACE 选项可以不删除原始图便可更改其定义并重建，或重新授予对象权限。

视图操作

视图分为简单视图（基于单个基表，且不包含函数和数据分组操作）和复杂视图。
简单视图可以通过视图修改数据，这些修改包括插入数据，更新数据和删除数据。（这里删除视图应该不会删除基表的数据吧）但是对于复杂视图来说，通过视图修改数据必须满足一定的规则。

在视图定义中没有设定READ ONLY的前提下，如果视图包含了下面的内容，那么不能通过视图删除表中的数据：

• 分组函数，如SUM, AVG, MIN, MAX等
• GROUP BY 字句
• 包含了表达式

• ROWNUM伪劣

  插入数据时，除了满足上面的条件外，还需要保证那些没有包含在视图定义中的基表的列必须允许空值。如果在视图定义中还包含了WITH CHECK OPTION字句，那么对视图的修改除了前面的那些原则外，还必须满足指定的约束条件。个人认为：视图利于查询，不利于修改。

（1）查询视图：可依赖于多个基表

SELECT * form view_name;

（2）更新视图的前提

• 没有使用连接函数，集合运算和组函数。
• 创建视图的select语句中没有集合函数且没有group by, start with 子句及distinct关键字。
• 创建视图的select语句中不包含从基表列通过计算得到的列。
• 创建视图没有包含只读属性。

（3）插入数据

INSERT INTO view_name VALUES(...);

（4）修改数据

UPDATE view_name SET...

若有一个视图依赖于多个基表，则一次修改该视图只能修改一个基表的数据。

（5）删除数据：

DELETE FROM view_name where ...

同样，当视图依赖于多个基表时，不能使用此语句来删除基表中的数据，只能删除依赖一个基表的数据。

（6）修改视图定义：
修改视图的好处在于，所有相关的权限都依然存在，语法同创建视图相同。

（7）删除视图

DROP VIEW view_name;

只有视图所有者和具备DROP VIEW权限的用户可以删除视图。删除视图的定义不影响基表中的数据。视图被删除后，基于被删除视图的其他视图或应用将无效。

示例

（1）dba_segments表里面没有视图的信息

SELECT DISTINCT(segment_type) FROM dba_segments GROUP BY segment_type;

（2）对视图的增删改操作都是在基表上进行的

Ⅰ. 创建表和视图
SQL语句：

-- 创建表t1CREATE TABLE t1(ID NUMBER, NAME VARCHAR2(20));-- 向表t1插入数据INSERT INTO t1 VALUES(1, 'moss');COMMIT;SELECT * FROM t1;-- 创建视图view_t1CREATE VIEW view_t1 AS SELECT * FROM t1;SELECT * FROM view_t1;

结果：

Ⅱ. 对视图进行增删改操作

INSERT INTO view_t1 VALUES(2, 'susu');COMMIT;-- sql1SELECT * FROM t1;-- sql2SELECT * FROM view_t1;

执行完sql1之后：

执行完sql2之后：

Ⅲ. 对视图进行delete操作

DELETE FROM view_t1 v WHERE v.ID=2;COMMIT;SELECT * FROM view_t1;SELECT * FROM t1;

结果：
select * from view_t1;

select * from t1;

对视图的delete,同样会影响到基表。

Ⅳ. 对视图进行update

update view_t1 set name="sunjiayu" where id=1;commit;select * from t1;select * from view_t1;

结果：

select * from t1;

select * from view_t1;

（3） 查看视图的信息dba_views

desc dba_views;

（4）查看视图的定义：

select text from dba_views where view_name='view_t1';

从上面的例子来看，对视图进行CRUD操作是对基表的影响大概如下：

• 简单视图：对其进行增删改都会影响到基表
• 复杂视图：对其进行增删改对基表的影响视情况而定
  （时间有限，就先这样吧，日后会再做研究整理）