在oracle的存储过程中,不能直接使用DDL语句,比如create、alter、drop、truncate等。
那如果我们想在存储过程中建立一张临时表就只能使用动态sql语句了:
- create or replace procedure pro as
- str_sql varchar2(100);
- begin
- -- 创建临时表
- str_sql := 'create global temporary table temp_table (
- col1 varchar2(10),
- col2 number
- ) on commit preserve rows';
- execute immediate str_sql;
- -- 使用临时表
- str_sql := 'insert into temp_table(col1, col2) values(''a'', 1)';
- execute immediate str_sql;
- -- 删除临时表
- str_sql := 'drop table temp_table';
- execute immediate str_sql;
- end;
create or replace procedure pro as
str_sql varchar2(100);
begin
-- 创建临时表
str_sql := 'create global temporary table temp_table (
col1 varchar2(10),
col2 number
) on commit preserve rows';
execute immediate str_sql;
-- 使用临时表
str_sql := 'insert into temp_table(col1, col2) values(''a'', 1)';
execute immediate str_sql;
-- 删除临时表
str_sql := 'drop table temp_table';
execute immediate str_sql;
end;
在oracle中,临时表分为会话级别(session)和事务级别(transaction)两种。
会话级的临时表在整个会话期间都存在,直到会话结束;事务级别的临时表数据在transaction结束后消失,即commit/rollback或结束会话时,
会清除临时表数据。
on commit preserve rows -- 会话级别临时表(退出登录会结束会话)
on commit delete rows -- 事务级别临时表(提交或回滚会结束事务)
1)ON COMMIT DELETE ROWS
它是临时表的默认参数,表示临时表中的数据仅在事物过程(Transaction)中有效,当事物提交(COMMIT)后,临时表的暂时段将被自动截断(TRUNCATE),但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后,最好删除临时表,否则数据库会残留很多临时表的表结构和元数据。
2)ON COMMIT PRESERVE ROWS
它表示临时表的内容可以跨事物而存在,不过,当该会话结束时,临时表的暂时段将随着会话的结束而被丢弃,临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后,最好删除临时表,否则数据库会残留很多临时表的表结构和元数据。
1:会话级的临时表的数据和你当前会话有关系,当前SESSION不退出的情况下,临时表中的数据就还存在,临时表的数据只有当你退出当前SESSION的时候才被截断(TRUNCATE TABLE),如下所示:
会话级别的临时表创建:
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
ID NUMBER ,
NAME VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS
AS
SELECT * FROM TEST;
操作示例:
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
ID NUMBER ,
NAME VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID NAME
---------- ----------------
1 kerry
SQL> INSERT INTO TMP_TEST
SELECT 2, 'rouce' FROM DUAL;
1 row inserted
SQL> ROLLBACK;
Rollback complete
SQL> SELECT * FROM TMP_TEST;
ID NAME
---------- ----------------------
1 kerry
SQL>
2:事务级的临时表(默认),这种类型的临时表与事务有关,当进行事务提交或者事务回滚的时候,临时表的数据将自行截断,即当COMMIT或ROLLBACK时,数据就会被TRUNCATE掉,其它的特性和会话级的临时表一致。
事务级临时表的创建方法:
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
ID NUMBER ,
NAME VARCHAR2(32)
) ON COMMIT DELETE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
ID NUMBER ,
NAME VARCHAR2(32)
) ON COMMIT DELETE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID NAME
---------- ----------------------
1 kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID NAME
---------- ------------------------
SQL>
临时表优缺点:
1. 在仅仅查询数据时建议使用游标。
2. 临时表不会建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用。