oracle临时存储表,oracle存储过程中使用临时表 | 学步园

--------在存储过程中使用临时表---------------------

--创建临时表

CREATE GLOBAL TEMPORARY TABLE admin_work_area

(startdate DATE,

enddate DATE,

class CHAR(20))

ON COMMIT DELETE ROWS;

----------------------------------------------------------------------------------

/* Formatted on 2011/10/12 20:15 (Formatter Plus v4.8.8) */

DECLARE

CURSOR cr_prjt_cds

IS

SELECT  price   FROM ims.TABLE1;

selSql varchar2(1000);

v_prjt_cd   number;

I_cout int;

strsql      VARCHAR2 (2000);

updateSql   varchar2(2000);

BEGIN

OPEN cr_prjt_cds;

selSql:='select count(1) from ims.admin_work_area ';

LOOP

FETCH cr_prjt_cds

INTO v_prjt_cd;

EXIT WHEN cr_prjt_cds%NOTFOUND;

strsql:='insert into ims.admin_work_area values(sysdate,sysdate,'||v_prjt_cd||')' ;--将数据插入到监理表

--strsql:=strsql||'  union all  '||strsql;

EXECUTE IMMEDIATE strsql;

--   COMMIT;

END LOOP;

EXECUTE IMMEDIATE selSql into I_cout;--提交前有值

CLOSE cr_prjt_cds;

commit;

EXECUTE IMMEDIATE selSql into I_cout;--提交后被清空

END;

--给表授权

--GRANT SELECT,insert,delete,update ON admin_work_area  TO sys;