ORACLE-临时表实战示例

示例一

-- Create table    三目录监控存储过程临时表
create global temporary table L_DIAITEM_ERROR
(
  forgid   NUMBER,            --组织机构id
  forgname VARCHAR2(80),    --组织机构名称
  nccw     NUMBER,            --名称错误数
  wdz      NUMBER            --未对照计数
)
on commit delete rows;        --事务提交时，清空表数据
--插入一条数据
INSERT INTO L_DIAITEM_ERROR (nccw,wdz) SELECT 1,2 FROM DUAL;
--查询该表数据
SELECT * FROM L_DIAITEM_ERROR

示例二

　　第一步：创建临时表

/**
 * 创建临时指标表
 */
CREATE GLOBAL TEMPORARY TABLE TAB_INDEX_TEMP(A_ID NUMBER,--一级指标ID
                                               A_INDEXNAME VARCHAR2(50), --一级指标名称
                                               TOTALWEIGHT NUMBER, --权重占比
                                               B_ID NUMBER, --二级指标ID
                                               B_INDEXNAME VARCHAR2(200), ----二级指标名称
                                               INDEXSCORE NUMBER, --指标分值
                                               COUNTER VARCHAR2(200), --计算方法
                                               REMARK VARCHAR2(200)) ON COMMIT DELETE ROWS;/*事务提交时，清空临时表数据*/

　　第二步：将数据插入临时表

/*将数据批量插入临时表*/
INSERT INTO TAB_INDEX_TEMP
  SELECT A.ID          A_ID,
         A.INDEXNAME   A_INDEXNAME,
         A.TOTALWEIGHT,
         B.ID          B_ID,
         B.INDEXNAME   B_INDEXNAME,
         B.INDEXSCORE,
         B.COUNTER,
         B.REMARK
    FROM INDEX_B B, INDEX_A A
   WHERE A.STATUS = 1
     AND B.STATUS = 1
     AND B.INDEXTYPE = 2 --二级指标
     AND B.INDEXA_ID = A.ID
   ORDER BY B_ID;

　　第三步：查询临时表

　　说明：

　　　　执行插入语句后，如果没有提交，执行查询语句，该表有数据；

　　　　如果提交事务，执行查询语句，清空数据；

　　　　临时表一般和存储过程一起使用。

　　详细介绍

　　参考链接：http://www.cnblogs.com/vigarbuaa/archive/2012/06/04/2534797.html

　　在Oracle8i中，可以创建以下两种临时表：

　　　　(1)会话特有的临时表

　　　　语法：　　　　　

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

　　　　(2)事务特有的临时表

　　　　语法：

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

　　　　举例：

　　　　　　CREATE GLOBAL TEMPORARY TABLE MyTempTable

　　区别：

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

　　　　ON COMMIT PRESERVE ROWS 　　说明临时表是会话指定，每次中断会话ORACLE将截断表（清空表数据）

　　作用域：　　　　

　　　　通过CREATE GLOBAL TEMPORARY TABLE命令创建一个临时表，
　　　　对于事务类型的临时表，数据只是在本事务期间存在；对于会话类型的临时表，数据在本会话期间存在。

　　临时表的特点：

　　　　可以对临时表创建索引，视图，触发器，可以用export和import工具导入导出表的定义，但是不能导出数据。表的定义对所有的会话可见。

　　　　（1）多用户操作的独立性：对于使用同一张临时表的不同用户，ORACLE都会分配一个独立的 Temp Segment，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性；

　　　　（2）数据的临时性：既然是临时表，顾名思义，存放在该表中的数据是临时性的。ORACLE根据你创建临时表时指定的参数（On Commit Delete Rows / On Commit Preserve Rows），自动将数据TRUNCATE掉。

2020/04/18

应用场景：在oracle存储过程中，使用临时表返回数据游标。

　　会话指定：PRESERVE

　　在plsql中测试结果如下：

　　1.如果是会话指定：PRESERVE，存储过程测试窗口运行一次后，未关闭该窗口的情况下，无法删除该临时表；（即使该提交的事务已经提交）

　　以存储过程中往临时表中插入数据为例，当存储过程里没有自动提交，且测试窗口也没有手动提交时

　　2.点击红框左侧按钮执行几次，数据就会插入几次，即使没有提交，只有关闭窗口时，该临时表所对应的会话数据才会消失。

　　在javaWeb项目中调用该存储过程，以上两点也同样适用，只有关闭程序，会话才算结束，临时表数据才会被清空。

　　因此，在程序调用存储过程时，不能使用会话指定：PRESERVE，数据库会判定程序始终是同一会话，这样的话，数据只会越查越多，根本不是我们想要的结果。

　　如果非得使用这种指定方式的话，需要在存储过程调用该临时表前清空该临时表。

　　另外，不同会话之前的临时表数据不是互通的，比如上图所示，A会话里临时表已经有很多数据，当你再打开一个测试窗口进行查询时数据就不会有这么多了。

　　事物指定：DELETE

　　在plsql中测试结果如下：

　　1.在事务提交之前，无法删除该临时表；但事务提交后，就可以删除啦。

　　2.当存储过程没有自动提交，需要手动提交时，在测试窗口查询多少次，就会插入多少次，只有在提交事务后，该会话中临时表的数据才会消失。

　　在javaWeb项目中调用该存储过程，就与上面不同啦：

　　ibatis插件在执行SQL时会自动执行commit事务，所以，临时表想什么时候删就什么时候删掉；

　　由于程序可以自动提交，所以查询出来的数据永远不存在重复插入这种情况。

　　另外，事务指定，会话之间也是独立的，当在plsql中执行两次后，该会话已经出现重复数据，此时，使用程序调用一次存储过程，数据依旧不会出现重复的情况。

　　所以说，使用事务指定，会话之间的临时表数据也是独立的。

　　另外，事务指定还有一个特性就是：即使你没有提交，直接关闭会话，临时表的数据也会被清空。

　　最后说一点，当存储过程要返回的数据是含有临时表数据时，则使用事务指定的时候，对于临时表进行新增或修改的操作都不能提交，因为一旦提交临时表数据就会消失。

　　程序调用的时候也会报错。

posted @ 2017-06-01 17:59 Marydon 阅读(3845) 评论(0) 编辑收藏举报

刷新页面返回顶部

ORACLE-临时表实战示例

写在最后

相关推荐：

公告