oracle存储过程中使用临时表

来源：互联网发布：acdsee mac pro注册码编辑：程序博客网时间：2024/05/17 01:22

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

--创建临时表
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;