oracle中使用临时表GLOBAL TEMPORARY TABLE

需要在存储过程中返回一个临时结果集，这个结果集又是多个语句通过循环查询出来的，这时候就想到了将结果插入到临时表中，然后返回临时表的数据的思路，于是有了以下操作：

1.创建临时表

-- Create table
create global temporary table T_PROJECT_AMOUNT_TEMP
(
  project_id       VARCHAR2(1000),
  con_amount_tax_s NUMBER
)
on commit preserve rows;
-- Add comments to the table 
comment on table T_PROJECT_AMOUNT_TEMP
  is '临时表';
-- Add comments to the columns 
comment on column T_PROJECT_AMOUNT_TEMP.project_id
  is '项目ID';
comment on column T_PROJECT_AMOUNT_TEMP.con_amount_tax_s
  is '项目合同金额';

2.创建存储过程

CREATE OR REPLACE PROCEDURE PRC_XXXX(
 TODO_CUR OUT PKG_QUERY.CUR_QUERY
) AS


BEGIN

   delete from T_PROJECT_AMOUNT_TEMP;

   --遍历数据
   for dataOjb in (
     select * from  xxx t where xxx
   ) loop
            --往临时表插入记录
            insert into T_PROJECT_AMOUNT_TEMP
            select xx,xx  from XXX;
            
            insert into T_PROJECT_AMOUNT_TEMP
            select xx,xx  from XXX;


   END LOOP;


  --将临时表中的记录返回
  open TODO_CUR for
  select * from T_PROJECT_AMOUNT_TEMP k;
  

END PRC_XXXX;

在存储过程中先将临时表清空，然后再往临时表插入记录，不然数据会重复插入。