Oracle 查询暂存

 

          为了简化代码或者优化算法，减少对表的扫描，减少循环的嵌套，有时候会把相关的数据暂时保存起来，以重复使用。Oracle 11g中提供了两种结构来实现这个功能，通用表达式（CTE）和临时表。

 

通用表达式（CTE）【Oracle 9i推出】

结构：

WITH  <subquery_name>AS(   SQL statement)SELECT  <column_list>FROM  <subquery_name>

 

注意：

1、  WITH保存的数据只能用在WITH结构之后的第一个SELECT语句。

2、  如果利用WITH里面的数据，但是又没有出现SELECT关键字，比如说UPDATE,DELETE等就会报错“ORA-00928:遗漏SELECT关键字错误”

比如说下面的例子：

 

WITH TABLE2 (TABLE2_ID ,TABLE2_NUM ) AS(SELECT 1,3 FROM DUAL UNION ALLSELECT 2,4 FROM DUAL UNION ALLSELECT 3,6 FROM DUAL)UPDATE TABLE1 SET TABLE_NUM=TABLE2_NUMFROM TABLE2 WHERE TABLE2.TABLE2_ID=TABLE1.TABLE_IDAND TABLE2.TABLE2_ID=1;SELECT * FROM TABLE2;----会报错，如下：命令出错, 行: 6 列: 1错误报告:SQL 错误: ORA-00928: 缺失 SELECT 关键字00928. 00000 -  "missing SELECT keyword"*Cause:    *Action: 在行 11 上开始执行命令时出错:SELECT * FROM TABLE2命令出错, 行: 11 列: 14错误报告:SQL 错误: ORA-00942: 表或视图不存在00942. 00000 -  "table or view does not exist"*Cause:    *Action:

临时表

创建临时表

--(1)会话特有的临时表

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

--(2)事务特有的临时表

CREATE GLOBAL TEMPORARY  TABLE <TABLE_NAME> (<column specification>)ON COMMIT DELETE ROWS；

注意：

1、  定义问题。表的定义对于所有的会话可见，也就是说对于并发操作来说，所有的会话的临时表名称不能发生冲突，否则会报错。

2、数据保存问题。超出相应的范围之后，临时表的数据就会被删除掉

--ON COMMIT DELETE ROWS说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）

--ON COMMIT PRESERVE ROWS说明临时表是会话指定，当中断会话时ORACLE将截断表。

3、空间问题。临时表的是放在临时表空间，不在表空间。在临时表被创建的时候，其定义保存在数据字典中，系统并不会给该临时表分配空间，而是等到INSERT语句或者CREATE TABLE SELECT语句，往临时表中插入数据的时候，才会分配空间给临时表。

4、事务回滚问题。当进行ROLLBACK操作时，临时表中的数据就会被清空，不过临时表的定义还在。

5、索引问题。可以对临时表创建索引，当时索引的数据跟临时表具有相同的作用范围，也同样具有临时性。

6、临时表不支持主外键。

       下面是摘自ORACLE 11G Document Library对于临时表的相关介绍内容文档:

      Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions with appropriate privileges. The data in a temporary table is visible only to the session that inserts the data into the table.

      When you first create a temporary table, its table metadata is stored in the data dictionary, but no space is allocated for table data. Space is allocated for the table segment at the time of the first DML operation on the table. The temporary table definition persists in the same way as the definitions of regular tables, but the table segment and any data the table contains are either session-specific or transaction-specific data. You specify whether the table segment and data are session- or transaction-specific with the ON COMMIT keywords.

     You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.

   Also:      Oracle Database Concepts for information on temporary tables and "Creating a Table: Temporary Table Example" on page 16-71

   Restrictions on Temporary Tables Temporary tables are subject to the following  restrictions:

■    Temporary tables cannot be partitioned, clustered, or index organized.

■    You cannot specify any foreign key constraints on temporary tables.

■    Temporary tables cannot contain columns of nested table.

■    You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.

■    Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.

■    The only part of the segment_attributes_clause you can specify for a temporary table is TABLESPACE, which allows you to specify a single temporary tablespace.

■    Distributed transactions are not supported for temporary tables.  

     You can modify, drop columns from, or rename a temporary table. However, for a temporary table you cannot:

■ Add columns of nested table type. You can add columns of other types.

■ Specify referential integrity (foreign key) constraints for an added or modified column.

■ Specify the following clauses of the LOB_storage_clause for an added or modified LOB column: TABLESPACE, storage_clause, logging_clause, allocate_extent_clause, or deallocate_unused_clause.

■ Specify the physical_attributes_clause, nested_table_col_properties, parallel_clause, allocate_extent_clause, deallocate_ unused_clause, or any of the index-organized table clauses.

■ Exchange partitions between a partition and a temporary table.

■ Specify the logging_clause.

■ Specify MOVE.