oracle临时表

oracle临时表简介

--【创建会话级临时表】
--提交事务保留临时表数据
create global temporary table tmp_user_session(
       user_id number not null,
       user_name varchar(20),
       email varchar(20)
) on commit preserve rows;--提交事务保留临时表数据

select * from tmp_user_session;

insert into tmp_user_session(user_id,user_name,email) values(1,'张三','123@123.com');
insert into tmp_user_session(user_id,user_name,email) values(2,'李四','123@123.com');

commit;
--提交事务不保留临时表数据
create global temporary table tmp_user_session2(
       user_id number not null,
       user_name varchar(20),
       email varchar(20)
);
insert into tmp_user_session2(user_id,user_name,email) values(1,'张三','123@123.com');
insert into tmp_user_session2(user_id,user_name,email) values(2,'李四','123@123.com');

select * from tmp_user_session2
commit;

--【事务级临时表】
create global temporary table tmp_user_transaction(
       user_id number not null,
       user_name varchar2(30),
       email varchar2(30)
) on commit delete rows;

insert into tmp_user_transaction(user_id,user_name,email) values(1,'张三','123@123.com');
insert into tmp_user_transaction(user_id,user_name,email) values(2,'李四','123@123.com');
insert into tmp_user_transaction(user_id,user_name,email) values(3,'王五','123@123.com');

select * from tmp_user_transaction

--执行提交事务或者回滚事务都会临时表数据
commit;
rollback;

--【比较临时表和普通表的区别】
select *
from user_tables 
where table_name = 'T_USER' or table_name = 'TMP_USER_SESSION' or table_name = 'TMP_USER_TRANSACTION';

--临时表的应用场景
----------------------------------------------------------------------------
--1.大表分割：先分割为众多小的临时表操作，最后再综合处理                 ---
--2.解决并行问题：一般是锁定表，使用临时表后可以在会话层面透明，互不影响 ---
--3.作为数据缓存：例如存储过程中可以多为函数或者SQL的临时空间            ---
----------------------------------------------------------------------------
select count(*) from user_tables a;
select count(*) from all_tables a;
select count(*) from dba_tables a;

--【dual表】
select * from dual;
select 1+1 result from dual;
select sysdate from dual;
insert into dual values('X');
delete from dual;
select * from user_tables where table_name = 'DUAL'