--------在存储过程中使用临时表---------------------
--创建临时表
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;