oracle/plsql 的临时表

1.临时表分为：

会话级临时表：临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

事务级临时表：临时表中的数据只在事务生命周期中存在。当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。

2.创建：

会话级：CREATE GOLBAL TEMPORARY TABLE  TEMP_TBL1 (RT_ID VARCHAR2(20),PAT_ID VARCHAR2(20)) ON  COMMIT PRESERVE ROWS ;

事务级：CREATE GOLBAL TEMPORARY TABLE  TEMP_TBL2 (RT_ID VARCHAR2(20),PAT_ID VARCHAR2(20)) ON  COMMIT DELETE ROWS ;

(红色字体为固定写法，黑色字体为自定义)

3.区别：

事务级的在sql中只要执行commit命令，则临时表中的数据就被清空。

会话级的在执行完后，即当前访问结束后，临时表中的数据就被清空，如c#调用完存储过程后，就会被清空。

4.相同点：

1. 临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。即：多个客户端同时访问服务器上的同一个临时表时，并不能看到别的用户的数据，也无法对其进行操作，但表是存在的。所以如果要创建临时表，应先执行

     SELECT COUNT(*) INTO TEMP_TBL_COUNT FROM USER_TABLES T  WHERE T.TABLE_NAME = 'TEMP_TBL';

     （TEMP_TBL_COUNT 为number型的变量，保存检索出来的个数；TEMP_TBL为创建的临时表的名字。）

只有当TEMP_TBL_COUNT =0的时候才创建临时表。

2. 当多个人同时使用的时候是无法删除临时表的，虽然多个用户彼此看不到也无法操作对方的数据，但表还是同一个，只是各自有各自的session。如果采用实体表的话，要防止多个人同时操作同一个表，那在创建实体表的时候应多加一个session_id的字段，区别各个用户。

3. EXECUTE IMMEDIATE：执行命令，因为在存储过程中无法直接执行，可以用该命令加上’要执行的命令‘来实现select，insert，delete等操作。如创建临时表 

           EXECUTE IMMEDIATE ' CREATE GOLBAL TEMPORARY TABLE  TEMP_TBL1 (RT_ID VARCHAR2(20),PAT_ID VARCHAR2(20)) ON  COMMIT PRESERVE ROWSROWS ‘  ；

    COMMIT：提交命令，当用EXECUTE IMMEDIATE执行完后，对于insert和delete操作，要记得commit(事务级的临时表不需要，因为commit会清空其数据)。同时commit会导致事务级的临时表的数据清空。

        4. 直接在在plsql中查看临时表是看不到数据的，需要用游标将其取出则可以看到。如：

  PROCEDURE TEMP_TBL_TEST(STR IN VARCHAR) IS
    CUR_A_CURSOR REF_CURSOR;
    V_C_RT_ID    VARCHAR(20);
    V_C_PAT_ID   VARCHAR(20);
  BEGIN
    OPEN CUR_A_CURSOR FOR 'SELECT * FROM TONG_TEMP_TBL';  //TONG_TEMP_TBL为已经创建好的临时表
      LOOP
      FETCH CUR_A_CURSOR
        INTO V_C_RT_ID, V_C_PAT_ID;
      EXIT WHEN CUR_A_CURSOR%NOTFOUND;
    END LOOP;
  END TEMP_TBL_TEST;

5. 举例：

// 查找临时表是否已经存在

SELECT COUNT(*)
      INTO TEMP_TBL_COUNT
      FROM USER_TABLES T
     WHERE T.TABLE_NAME = 'TONG_TEMP_TBL2';

    //如果临时表不存在则创建事务级的临时表
    IF TEMP_TBL_COUNT = 0 THEN
      EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TONG_TEMP_TBL2(C_RT_ID VARCHAR2(20), C_PAT_ID VARCHAR2(20)) ON COMMIT DELETE ROWS';
    ELSE

      //如果临时表存在则清空其数据，在这也可以利用执行commit；命令来清空。
      EXECUTE IMMEDIATE 'truncate TABLE TONG_TEMP_TBL2';
    END IF;
    COMMIT;

  //往临时表中插入数据，在执行完后别忘了执行commit（会话级的临时表需要）；

EXECUTE IMMEDIATE 'INSERT INTO TONG_TEMP_TBL2 ( ' ||
                      'SELECT TONG_TEMP_TBL.C_RT_ID,
                          TONG_TEMP_TBL.C_PAT_ID 
                       FROM TONG_TEMP_TBL WHERE NOT EXISTS( SELECT  1 FROM( ' ||
                      C_FREE_WORD1 ||
                      ') T2 WHERE T2.C_RT_ID = TONG_TEMP_TBL.C_RT_ID AND T2.C_PAT_ID = TONG_TEMP_TBL.C_PAT_ID))';