其实是写在触发器里的,每次插入都会调用 现在是 10分钟插 500条 但是客户有 200万条啊create or replace procedure pro_stat( N IN VARCHAR2 )
isCURSOR cur_test IS
SELECT
count(*) as A,
Max(TESTHOUHOU) AS max_value,
min(TESTHOUHOU) AS min_value,
trunc(AVG(TESTHOUHOU),1) AS avg_value,
to_char(sokutei,'yyyymmddhh24'),
trunc(LONGITUDE,3),
trunc(LATITUDE,3)
FROM AREACONTROL
where LONGITUDE<>999.999 or LATITUDE<>999.999
group by trunc(LONGITUDE,3),trunc(LATITUDE,3),to_char(sokutei,'yyyymmddhh24');CURSOR cur_Table IS
select column_name as V from user_tab_columns where table_name='AREACONTROL';v_table varchar2(100);
str varchar2(500);
v_a1 varchar2(100);
v_a2 varchar2(100);
v_a3 varchar2(100);
v_a4 varchar2(100);
v_a5 varchar2(200);
v_a6 varchar2(100);
v_a7 varchar2(100);BEGINopen cur_Table;loop --1
fetch cur_Table into v_table;
exit when cur_Table%notfound;
open cur_test;
loop --2
fetch cur_test into v_a1,v_a2,v_a3,v_a4,v_a5,v_a6,v_a7;
exit when cur_test%notfound;
if Noinsert(v_table, v_a5,v_a6,v_a7) = 0 then
str:= ' insert into ' ||v_table || ' values('||v_a2||','||v_a3||','||v_a4||','||v_a5||','||v_a6||','||v_a7||','''','''') ';
else
str:= ' update ' ||v_table || ' set MaxValue='||v_a2||', MinValue='||v_a3||', AvgValue='||v_a4||' where
DayHour='||v_a5||' and X_Messyu='||v_a6||' and Y_Messyu='||v_a7||' ';
end if;
execute immediate str;
end loop;
close cur_test;
end loop;
close cur_Table;
end pro_stat;
isCURSOR cur_test IS
SELECT
count(*) as A,
Max(TESTHOUHOU) AS max_value,
min(TESTHOUHOU) AS min_value,
trunc(AVG(TESTHOUHOU),1) AS avg_value,
to_char(sokutei,'yyyymmddhh24'),
trunc(LONGITUDE,3),
trunc(LATITUDE,3)
FROM AREACONTROL
where LONGITUDE<>999.999 or LATITUDE<>999.999
group by trunc(LONGITUDE,3),trunc(LATITUDE,3),to_char(sokutei,'yyyymmddhh24');CURSOR cur_Table IS
select column_name as V from user_tab_columns where table_name='AREACONTROL';v_table varchar2(100);
str varchar2(500);
v_a1 varchar2(100);
v_a2 varchar2(100);
v_a3 varchar2(100);
v_a4 varchar2(100);
v_a5 varchar2(200);
v_a6 varchar2(100);
v_a7 varchar2(100);BEGINopen cur_Table;loop --1
fetch cur_Table into v_table;
exit when cur_Table%notfound;
open cur_test;
loop --2
fetch cur_test into v_a1,v_a2,v_a3,v_a4,v_a5,v_a6,v_a7;
exit when cur_test%notfound;
if Noinsert(v_table, v_a5,v_a6,v_a7) = 0 then
str:= ' insert into ' ||v_table || ' values('||v_a2||','||v_a3||','||v_a4||','||v_a5||','||v_a6||','||v_a7||','''','''') ';
else
str:= ' update ' ||v_table || ' set MaxValue='||v_a2||', MinValue='||v_a3||', AvgValue='||v_a4||' where
DayHour='||v_a5||' and X_Messyu='||v_a6||' and Y_Messyu='||v_a7||' ';
end if;
execute immediate str;
end loop;
close cur_test;
end loop;
close cur_Table;
end pro_stat;
解决方案 »
- Oracle Insert 非常慢
- 如何判断一个sequence是否能调用currval方法.
- 含有lob,如何在INTERVAL分区中将lob和data分开循环存储?
- 动态表面(表明在其他表的字段中)
- select 中的字符串
- Linux 环境 创建赋权job
- 数据库的安装问题急急急!!!
- 效率的问题: 求几个field的函数最小值——改表还是用游标?(大家都来说说看)
- 查询条件中的(+)表示什么意思
- Oracle中的Numeric字段和Sybase中的smallint字段不兼容
- 请问关于用vb.net设计数据库查询界面的问题
- ORACLE有没有与SQL SERVER的查询分析器一样的工具?
from
(SELECT
count(*) as A,
Max(TESTHOUHOU) AS max_value,
min(TESTHOUHOU) AS min_value,
trunc(AVG(TESTHOUHOU),1) AS avg_value,
to_char(sokutei,'yyyymmddhh24'),
trunc(LONGITUDE,3),
trunc(LATITUDE,3)
FROM AREACONTROL
where LONGITUDE <>999.999 or LATITUDE <>999.999
group by trunc(LONGITUDE,3),trunc(LATITUDE,3),to_char(sokutei,'yyyymmddhh24');
) a,
user_tab_columns b结果是2个结果集的笛卡尔乘积
这样做不一定快,测试一下吧,我没有你的环境,只是一个思路,供参考
from
(SELECT FROM AREACONTROL where group by
) a,
user_tab_columns b
where Noinsert(b.column_name, a.***,a.***,a.***) = 0union allselect Update' ¦ ¦ b.column_name ¦ ¦ ' set MaxValue=' ¦ ¦....
from
(SELECT FROM AREACONTROL where group by
) a,
user_tab_columns b
where Noinsert(b.column_name, a.***,a.***,a.***) <> 0