临时表的存储过程

create or replace procedure upd_wbb_quotenum

as

begin

    --创建会话级临时表

    execute immediate 'CREATE GLOBAL TEMPORARY TABLE tmp_wbb_quotenum (project_id NUMBER,quotenum NUMBER) ON COMMIT PRESERVE ROWS';

    --找出第一个SQL需要的count及projectid,插入临时表数据

    insert into tmp_wbb_quotenum

    select project_id,count(distinct seller_id)

    from bid_quote_tables

    where status >= 5

    group by project_id;

    --关联临时表进行修改

    update web_business_bulletin a

    set a.quotenum = tmp_wbb_quotenum.quotenum

    where a.itemState = 1

    and (a.bulletinType = 1 or  a.bulletinType = 2)

    and a.projectid = tmp_wbb_quotenum.project_id;

    --提交

    commit;

    --清空临时表

    execute immediate 'truncate table tmp_wbb_quotenum';

    --找出第二个SQL需要的count及projectid,插入临时表数据

    insert into tmp_wbb_quotenum

    select projectid,count(distinct selleruserid)

    from expr_bid_quotetable

    group by projectid;

    --关联临时表进行修改

    update web_business_bulletin a

    set a.quotenum = tmp_wbb_quotenum.quotenum

    where a.itemState = 1

    and (a.bulletinType = 30 or  a.bulletinType = 31)

    and a.projectid = tmp_wbb_quotenum.project_id;

    --删除临时表

    execute immediate 'drop table tmp_wbb_quotenum';

    commit;

    exception when others then

    raise;

end upd_wbb_quotenum;

/