example:SQL> create or replace procedure pro_test
2 as
3 begin
4 execute immediate 'drop table bao_temp';
5 execute immediate 'create table bao_temp (id varchar2(10),name varchar2(10))';
6 end;
7 /程序已被建立目前歷時: 00:00:00.56
SQL> exec pro_testPL/SQL 程序順利完成目前歷時: 00:00:00.85
SQL> select * from bao_temp
2 /沒有任何資料列被選取
2 as
3 begin
4 execute immediate 'drop table bao_temp';
5 execute immediate 'create table bao_temp (id varchar2(10),name varchar2(10))';
6 end;
7 /程序已被建立目前歷時: 00:00:00.56
SQL> exec pro_testPL/SQL 程序順利完成目前歷時: 00:00:00.85
SQL> select * from bao_temp
2 /沒有任何資料列被選取
execute immediate 'create global temporary table bao_temp (id varchar2(10),name varchar2(10))';
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
所建的临时表虽然是存在的,但是你试一下insert 一条记录然后用别的连接登上去select,记录是空的,明白了吧。
下面两句话再贴一下:
--ON COMMIT DELETE ROWS 说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定,当中断会话时ORACLE将截断表。
这在ORACLE如何实现
步骤如下:
CREATE GLOBAL TEMPORARY temp_name (<column specification> )
ON COMMIT PRESERVE ROWS; insert into temp_name (col_name,...) select * from test where ....