CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清空数据,PRESERVE则一直到会话结束
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清空数据,PRESERVE则一直到会话结束
解决方案 »
- 帮忙解决下具体概念
- 收藏 Spring_batch 分页方式得到Oracle数据 了·列明无效
- 11g rac中 root.sh的执行报错
- 谁有http://www.cnoug.org 下的邀请码,本人急要
- sql 中的(+)
- 急问几个SQL怎么写?
- 高分请教如何恢复ORACLE数据库,在线等!!!
- 怎样把execl中的数据保存到oracle数据库中
- 十分着急,游标出错怎么办?
- 我是菜鸟,各位老师能否给小弟解释oracle与pb各自特点和区别?
- 怎么下这样的sql,select sum(qty) from table1 where name='lile' group by name 当没有lile这条纪录时自动返回 sum(qty) =0 ,
- Oracle数据库怎么清空日期型的数据(在线等待,解决后立即给分)
臨時表是那些只在事務處理或者會話進行期間存在數據的表,數據會在事務處理或者會話開始以後插入臨時表,當事務處理或會話完成之後就會刪除。
臨時表按照數據存在的方式分爲兩種
A. 針對事務處理的臨時表,即事務處理後就數據會刪除(如:DDL語句後,COMMIT後),定義的語法是:
CREATE GLOBAL TEMPOARY TABLE TRANSACTION_TAB ON COMMIT DELETE ROWS AS …(結果集)
B. 針對會話的臨時表,即會話完成之後才刪除數據,定義的語法是:
CREATE GLOBAL TEMPOARY TABLE TRANSACTION_TAB ON COMMIT PRESERVE ROWS AS …(結果集)
臨時表可以建立索引,視圖,觸發器(用來保證數據的完整性)等
臨時表的用例
步驟如下:
1. 建立用創建存儲過程的臨時表;
2. 執行存儲過程;
3. 對臨時表進行操作(基本操作同普通的表)/** 步驟一 **/
create or replace procedure name_pro
/*
* 一個臨時表的時候示例
* AUTHOR: FRED
* CREATE DATE: 2003-5-17
*/
as
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_sql3 varchar2(4000);
isExists number(2);
cursor checkExists is select count(1) from all_tables where owner='ZHENYUAN' AND TABLE_NAME='TEMP_TABLE_1';
begin
open checkExists;
fetch checkExists into isExists;
--判斷臨時表是否存在,如果不存在,就建立臨時表
if isExists<>1 then
v_sql1 := 'CREATE GLOBAL TEMPORARY TABLE temp_table_1 ON COMMIT PRESERVE ROWS AS select * from (select a.SALE_TYPE||''·Ö¸ô·û'' as SALE_TYPE,SUBSTR(a.DEALER_ID,1,2) as s_id,a.DEALER_ID as s_full_id, ''·Ö¸ô·û'' as s_style,count(a.dealer_id) as s_cross,SUBSTR(a.CONFIRMDATE,1,6) as s_date FROM P10_TB_SALE_MASTER a,(SELECT DISTINCT SALE_TYPE FROM P10_TB_SALE_TYPE) b where a.sale_type(+)=b.sale_type group by a.SALE_TYPE,a.DEALER_ID,SUBSTR(a.CONFIRMDATE,1,6) union select a.SALE_TYPE||''·Ö¸ô·û'' as SALE_TYPE,SUBSTR(a.DEALER_ID,1,2) as s_id,a.DEALER_ID as s_full_id, ''·Ö¸ô·û'' as s_style, SUM(c.QTY*c.UNIT_PRICE*c.DISCOUNT) as s_cross,SUBSTR(a.CONFIRMDATE,1,6) as s_date FROM P10_TB_SALE_MASTER a,(SELECT DISTINCT SALE_TYPE FROM P10_TB_SALE_TYPE) b,P10_TB_SALE_DETAIL c where a.sale_type(+)=b.sale_type and c.SALE_NO=a.SALE_NO group by a.SALE_TYPE,a.DEALER_ID,SUBSTR(a.CONFIRMDATE,1,6) union select a.SALE_TYPE||''·Ö¸ô·û'' as SALE_TYPE,SUBSTR(a.DEALER_ID,1,2) as s_id,a.DEALER_ID as s_full_id, ''·Ö¸ô·û'' as s_style,sum(B.STD_COST) as s_cross,SUBSTR(a.CONFIRMDATE,1,6) as s_date FROM P10_TB_SALE_MASTER a,(SELECT DISTINCT SALE_TYPE FROM P10_TB_SALE_TYPE) b ,P10_TB_SALE_DETAIL c, P16_TB_ITEM_COST b where a.sale_type(+)=b.sale_type and c.COM_ID=b.COM_ID AND c.ITEM_ID=b.ITEM_ID and c.SALE_NO=a.SALE_NO group by a.SALE_TYPE,a.DEALER_ID,SUBSTR(a.CONFIRMDATE,1,6) ) aa WHERE 1=2';
execute immediate v_sql1;
v_sql2 := 'CREATE GLOBAL TEMPORARY TABLE temp_table_2 ON PRESERVE DELETE ROWS AS SELECT * FROM (select * from v_4_1_080_i_t union select m.SALE_TYPE, m.s_id, m.s_full_id, m.s_style||''·Ö¸ô·û'' as s_style, s_cross/n.sum_s_cross as s_cross,m.s_date from v_4_1_080_i_t m, ( select sum(s_cross) as sum_s_cross ,s_full_id,s_style ,s_date from v_4_1_080_i_t group by s_full_id,s_style,s_date) n where m.s_full_id=n.s_full_id and m.s_style=n.s_style ) aa WHERE 1=2' ;
execute immediate v_sql2;
end if;
v_sql1 := 'INSERT INTO TEMP_TABLE_1 SELECT * FROM (select a.SALE_TYPE||''·Ö¸ô·û'' as SALE_TYPE,SUBSTR(a.DEALER_ID,1,2) as s_id,a.DEALER_ID as s_full_id, ''·Ö¸ô·û'' as s_style,count(a.dealer_id) as s_cross,SUBSTR(a.CONFIRMDATE,1,6) as s_date FROM P10_TB_SALE_MASTER a,(SELECT DISTINCT SALE_TYPE FROM P10_TB_SALE_TYPE) b where a.sale_type(+)=b.sale_type group by a.SALE_TYPE,a.DEALER_ID,SUBSTR(a.CONFIRMDATE,1,6) union select a.SALE_TYPE||''·Ö¸ô·û'' as SALE_TYPE,SUBSTR(a.DEALER_ID,1,2) as s_id,a.DEALER_ID as s_full_id, ''·Ö¸ô·û'' as s_style, SUM(c.QTY*c.UNIT_PRICE*c.DISCOUNT) as s_cross,SUBSTR(a.CONFIRMDATE,1,6) as s_date FROM P10_TB_SALE_MASTER a,(SELECT DISTINCT SALE_TYPE FROM P10_TB_SALE_TYPE) b,P10_TB_SALE_DETAIL c where a.sale_type(+)=b.sale_type and c.SALE_NO=a.SALE_NO group by a.SALE_TYPE,a.DEALER_ID,SUBSTR(a.CONFIRMDATE,1,6) union select a.SALE_TYPE||''·Ö¸ô·û'' as SALE_TYPE,SUBSTR(a.DEALER_ID,1,2) as s_id,a.DEALER_ID as s_full_id, ''·Ö¸ô·û'' as s_style,sum(B.STD_COST) as s_cross,SUBSTR(a.CONFIRMDATE,1,6) as s_date FROM P10_TB_SALE_MASTER a,(SELECT DISTINCT SALE_TYPE FROM P10_TB_SALE_TYPE) b ,P10_TB_SALE_DETAIL c, P16_TB_ITEM_COST b where a.sale_type(+)=b.sale_type and c.COM_ID=b.COM_ID AND c.ITEM_ID=b.ITEM_ID and c.SALE_NO=a.SALE_NO group by a.SALE_TYPE,a.DEALER_ID,SUBSTR(a.CONFIRMDATE,1,6) ) aa ';
v_sql2 := 'INSERT INTO TEMP_TABLE_2 SELECT * FROM (select * from v_4_1_080_i_t union select m.SALE_TYPE, m.s_id, m.s_full_id, m.s_style||''·Ö¸ô·û'' as s_style, s_cross/n.sum_s_cross as s_cross,m.s_date from v_4_1_080_i_t m, ( select sum(s_cross) as sum_s_cross ,s_full_id,s_style ,s_date from v_4_1_080_i_t group by s_full_id,s_style,s_date) n where m.s_full_id=n.s_full_id and m.s_style=n.s_style ) aa ' ;
v_sql3 := 'insert into temp_table_2 (SALE_TYPE, s_id, s_full_id, s_style, s_cross, s_date) values(''ma'', ''68'', ''1168'', ''ma'', ''68'', ''20030514'')';
execute immediate v_sql1;
execute immediate v_sql2;
execute immediate v_sql3;
end;
/** 步驟二 **/
begin
name_pro;
end;/** 步驟三 **/
set serveroutput ondeclare
type c_cursor is ref cursor;
c_mycursor c_cursor;
SALE_TYPE varchar2(20);
s_id varchar2(20);
s_full_id varchar2(50);
s_style varchar2(50);
s_cross varchar2(50);
s_date varchar2(50);
begin
open c_mycursor for 'select * from temp_table_2 where rownum<5';
dbms_output.put_line(' 以下數據是從臨時表2中取得的: ');
dbms_output.put_line('---------------------------------------------------------');
loop
fetch c_mycursor into SALE_TYPE, s_id, s_full_id, s_style, s_cross, s_date;
dbms_output.put_line(SALE_TYPE || ' ' || s_id || ' ' || s_full_id || ' ' || s_style || ' ' || s_cross || ' ' || s_date);
exit when c_mycursor%notfound;
end loop;
end;