---
title: "oracle update 和 临时表的 弱处！"
author: "winzenghua"
date: "2009-09-21T11:31:00Z"
tags: []
slug: "oracle-update"
draft: false
lastmod: "2009-09-21T11:31:00Z"
id: 987848
url: "https://java4gis.iteye.com/blog/987848"
---

曾经面试的时候有公司问ORACLE 开发不足之处有哪些？ 当时不懂。最近才感受到，我想那就是UPDATE和临时表的使用上。

原本我要对一个表生成统计数据 该表的结构如下

周次，星期，商店，参观人数，购买人数，参观人次，购买人次。

200928，星期一，女装店，30，5，45，6.

虽然看起来很简单，实际上每个人数和人次都是个子查询而得来的数字。还有个问题就是有些店铺可以没有营业状态，那么其数字该为零。

也就说时间的生成 （周次，星期） 是不能依据店铺的营业时间为生成标准，得靠自然时间。从第一家店铺营业时间为起始点到统计的截止时间。

v\_startday--&gt;trunc(sysdate,'d') 然后采用循环插入进去

for i in 0..(v\_endate-v\_startday)

loop

insert into a

select to\_char(v\_currday,'yyyyiw'),to\_char(v\_currday,'day'), store\_name, B.num,C.num,D.num,E.num

left join ( select b.num from ..... where open\_day between v\_currday and v\_currday+1) on .....

left join ( select c.num from ..... where open\_day between v\_currday and v\_currday+1) on .....

left join ( select d.num from ..... where open\_day between v\_currday and v\_currday+1) on .....

left join ( select e.num from ..... where open\_day between v\_currday and v\_currday+1) on .....

order by ......

end loop

速度超慢 而且IO量也好低 从10G的EM上 看一条

2 接着的办法是想到 先插入时间序列 然后在UPDATE 空值的数据。

for i in 0...(v\_enddate-v\_startday)

loop

insert into a (f\_yeaweek,f\_weekday)

values(to\_char(v\_currday,'yyyyiw'),to\_char(v\_currday,'day');

end loop;

update a

set (store\_name,looknum,buynum,looks,buys) =

(

select f\_yearweek,f\_weekday,s.store\_name,B.num,C.num,D.num,E.num

from

( select * from s

left join ( select count() from b where open\_day between v\_startdayand v\_endate+1) on ...

left join ( select count() from b where open\_day between v\_startdayand v\_endate+1) on ...

left join ( select count() from b where open\_day between v\_startdayand v\_endate+1) on ...

left join ( select count() from b where open\_day between v\_startdayand v\_endate+1) on ...

) F

where a.f\_yearweek=f.f\_yearweek and a.f\_weekday= f.f\_weekday

)

where a.f\_yearweek between to\_char(v\_startday,'yyyyiw') and to\_char(v\_enddate,'yyyyiw')

;

结果同样的慢 从EM 图中看到 每更新次 都要执行次查询 虽然条件一样！ 因为一次查询时间要5分钟。

3 想采用临时表。 先把时间数据插入到临时表 然后

insert into a

select *

from tmp

left jon ..

left jon ...

left jon....

问题是 ORACLE 临时表必须动态执行！ 狂混啊！ 不能像MS SQL样。

sql:="create ....tmep ...."

execute sql ;

for i in ....

loop

sql := '......'....'.... '; --动态参数赋值是个麻烦问题

exuecute sql;

end loop

sql:='

insert into a

select *

from tmp

left jon ..

left jon ...

left jon....

'

execute sql;

看到这里就头大了。