现想通过存储过程对200706月份的数据进行更新（即把200705月份的CPUAVGVALUE和MEMAVGVALUE的值insert到200706对应的CPUAVGVALUELAST，MEMAVGVALUELAST这2列中）
create table SMDR_ISM_CPU_MEM_MONTH
(
STATISMONTH VARCHAR2(32),
MANAGERIP VARCHAR2(64),
CPUAVGVALUE FLOAT,
CPUMAXVALUE FLOAT,
CPUAVGVALUELAST FLOAT,
CPUMAXVALUELAST FLOAT,
MEMAVGVALUE FLOAT,
MEMMAXVALUE FLOAT,
MEMAVGVALUELAST FLOAT,
MEMMAXVALUELAST FLOAT
)
在此表中保存有5月份和6月份的数据，对于同一行数据
CPUAVGVALUE 本月CPU平均值
CPUAVGVALUELAST上月CPU平均值
MEMAVGVALUE 本月MEM平均值
MEMAVGVALUELAST上月MEM平均值
Statismonth统计月份
表中的数据例如：
Managerip CPUAVGVALUEMEMAVGVALUE CPUAVGVALUELAST MEMAVGVALUELASTstatismonth
192.168.1.115
30
200706
192.168.3.125
40
200706
192.168.1.1 16
35
200705
192.168.3.1 26
45
200705
目前在CPUAVGVALUELAST，MEMAVGVALUELAST这2列数据是空的
现想通过存储过程实现把200705月份的CPUAVGVALUE和MEMAVGVALUE的值insert到200706这一行中
即数据变成
Managerip CPUAVGVALUEMEMAVGVALUE CPUAVGVALUELAST MEMAVGVALUELASTstatismonth
192.168.1.115
30
16
35
200706
192.168.3.125
40
26
45
200706
192.168.1.1 16
35
200705
192.168.3.1 26
45
200705
对于200705月份的数据不修改。
我写了个procedure，但发现不生效
CREATE OR REPLACE PROCEDURE proc_cpu_mem_month (
day
innumber
) IS
p_date date;
p_managerip SMDR_ISM_CPU_MEM_MONTH.managerip%TYPE;
p_cpumaxvalue SMDR_ISM_CPU_MEM_MONTH.CPUMAXVALUE%TYPE;
p_cpuavgvalue SMDR_ISM_CPU_MEM_MONTH.CPUAVGVALUE%TYPE;
p_memmaxvalue SMDR_ISM_CPU_MEM_MONTH.memMAXVALUE%TYPE;
p_memavgvalue SMDR_ISM_CPU_MEM_MONTH.memAVGVALUE%TYPE;
Cursor cur_managerip is
select distinct managerip from SMDR_ISM_CPU_MEM_MONTH
where statismonth = to_char(p_date,'yyyymm');
BEGIN
savepoint a;
p_date:=trunc(sysdate-day);
Open cur_managerip;
Loop
Fetch cur_managerip into p_managerip;
Exit when cur_managerip%notfound;
select CPUMAXVALUE,CPUAVGVALUE, MEMMAXVALUE,MEMAVGVALUE into
p_CPUMAXVALUE,p_CPUAVGVALUE,p_MEMMAXVALUE,p_MEMAVGVALUE
from SMDR_ISM_CPU_MEM_MONTH
where managerip=p_managerip and statismonth = to_char(p_date-31,'yyyymm');
update SMDR_ISM_CPU_MEM_MONTH
set CPUAVGVALUELAST=p_CPUAVGVALUE,CPUMAXVALUELAST=p_CPUMAXVALUE,
MEMAVGVALUELAST=p_MEMAVGVALUE,MEMMAXVALUELAST=p_MEMMAXVALUE
where managerip=p_managerip and statismonth=to_char(p_date,'YYYYMM');
commit;
end loop;
close cur_managerip;
exception
when others then
rollback to a;
END proc_cpu_mem_month;
每月1日执行proc_cpu_mem_month(1);
但执行后发现数据未更新。
请高手指点一下！！！
|
