在存储过程中临时表怎样创建？怎么把临时表的结果集传出？

CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空，如果选了DELETE ROWS，则在提交的时候即清空数据，PRESERVE则一直到会话结束

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

一点心得，仅供大家参考：臨時表的概念：
臨時表是那些只在事務處理或者會話進行期間存在數據的表，數據會在事務處理或者會話開始以後插入臨時表，當事務處理或會話完成之後就會刪除。
臨時表按照數據存在的方式分爲兩種
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;

在存储过程中临时表怎样创建 ？怎么把临时表的结果集传出？

解决方案 »

在存储过程中临时表怎样创建？怎么把临时表的结果集传出？