create or replace procedure p_mi_integaudit(l_memberid in number,integraldate in date) is
i_memberid varchar2(20); --会员号
i_addup number(12, 2); --日累计积分
i_deduct number(12, 2); --日扣除积分
i_gained number(12, 2) := 0; --得到积分
i_deductsum number(12, 2) := 0; --扣除积分
i_addupsum number(12, 2) := 0; --累计积分
i_recount number := 1; --为了判断是否第一次循环游标
i_accountid number(20); --帐户id
i_accountidhis number(20); --帐户id
i_evttype varchar2(1); --积分行为 0表示增加积分 1表示减少积分
i_centsum number(12, 2); --日累计积分
i_putout number(12, 2) := 0; --发出积分
i_restitute number(12, 2) := 0; --得到积分
i_existence number(12, 2) := 0; --现有积分
i_integ number(12, 2); --积分变量
l_count number;
v_ErrorCode number; ---code for the error
v_Errormsg varchar2(200); ---message text for the error
v_CurrentUser varchar2(8); ---current database user
v_Information varchar2(100); ---info about the error
--根据传入的指定会员查询出待审核状态的数据
cursor cur_mi_autoInteg is
select t.memberid, t1.evttype, sum(t.cent)
from integral.t_o_integralday t,
integral.t_c_integralRule t1
where t.integralaction = t1.id and t.state='3' and t.memberid=l_memberid
and to_char(t.day, 'yyyy-mm-dd') = to_char(integraldate, 'yyyy-mm-dd')
group by t.memberid, t1.evttype;
--查询出一个帐户当日所有的累计积分和扣除积分
cursor cur_mi_accountIntegral is
select t.accountid, t1.evttype, sum(t.cent)
from integral.t_o_integralday t,
integral.t_c_integralRule t1,
integral.t_o_accountintegral t2
where t.integralaction = t1.id and t.state='3'
and t.accountid = t2.accountid
and to_char(t.day, 'yyyy-mm-dd') = to_char(sysdate, 'yyyy-mm-dd')
group by t.accountid, t1.evttype order by t.accountid;begin
--更新指定会员积分汇总表（规则说明：得到积分 += 日累计积分；扣除积分 += 日扣除积分；累计积分 += 日累计积分 - 日扣除积分）
open cur_mi_autoInteg;
loop
fetch cur_mi_autoInteg
into i_memberid, i_evttype, i_integ;
exit when cur_mi_autoInteg%notfound;
--计算会员的得到积分，扣除积分，累计积分
if(i_evttype = '0') then
i_gained := i_gained + i_integ;
i_addupsum := i_addupsum + i_integ;
else
i_deductsum := i_deductsum + i_integ;
i_addupsum := i_addupsum - i_integ;
end if;
end loop;
select count(*) into l_count from integral.t_o_integralCollect t where memberid = l_memberid;
if(l_count = 0) then
--如果t_o_integralCollect(会员积分汇总表)中不存在此会员，插入此会员的数据
insert into integral.t_o_integralCollect
values
(i_memberId,
i_gained,
i_deductsum,
i_addupsum,
sysdate);
else
--更新指定会员记录
update integral.t_o_integralCollect
set gained = gained + i_gained,
deduct = deduct + i_deductsum,
addup = addup + i_addupsum,
collectTune = sysdate
where memberid = i_memberid;
end if;
close cur_mi_autoInteg;
--更新账户积分表（规则说明：按账户积分汇总；发出积分 += 账户日累计积分；得到积分 += 账户日扣除积分；现有积分 -= 账户日累计积分)
open cur_mi_accountIntegral;
loop
fetch cur_mi_accountIntegral
into i_accountid, i_evttype, i_centsum;
exit when cur_mi_accountIntegral%notfound;
--因为每个帐户可能出现一条记录类型为0和1的记录
if (i_recount = 1 and i_evttype = '0') then
i_accountidhis := i_accountid;
i_putout := i_putout + i_centsum;
i_existence := i_existence - i_centsum;
i_recount := i_recount + 1;
--存储帐户id，同时给相应的变量赋值
elsif (i_recount = 1 and i_evttype = '1') then
i_accountidhis := i_accountid;
i_restitute := i_restitute + i_centsum;
i_recount := i_recount + 1;
--出现和上一次帐户累加相应结果
elsif (i_accountidhis = i_accountid and i_evttype = '0') then
i_putout := i_putout + i_centsum;
i_existence := i_existence - i_centsum;
--出现和上一次帐户累加相应结果
elsif (i_accountidhis = i_accountid and i_evttype = '1') then
i_restitute := i_restitute + i_centsum;
--如果和上一次帐户不一样，更新上一级帐户，同时把此帐户的值，赋值给相应变量
elsif (i_accountidhis <> i_accountid and i_evttype = '0') then
update integral.t_o_accountIntegral
set putout = putout + i_putout,
restitute = restitute + i_restitute,
existence = existence + i_existence
where accountid = i_accountidhis;
i_putout := 0;
i_existence := 0;
i_restitute := 0;
i_accountidhis := i_accountid;
i_putout := i_putout + i_centsum;
i_existence := i_existence - i_centsum;
elsif (i_accountidhis <> i_accountid and i_evttype = '1') then
--如果和上一次帐户不一样，更新上一级帐户，同时把此帐户的值，赋值给相应变量
update integral.t_o_accountIntegral
set putout = putout + i_putout,
restitute = restitute + i_restitute,
existence = existence + i_existence
where accountid = i_accountidhis;
i_restitute := 0;
i_putout := 0;
i_existence := 0;
i_accountidhis := i_accountid;
i_restitute := i_restitute + i_centsum;
end if;
end loop;
--循环结束更新最后一个帐户的值
update integral.t_o_accountIntegral
set putout = putout + i_putout,
restitute = restitute + i_restitute,
existence = existence + i_existence
where accountid = i_accountidhis;
close cur_mi_accountIntegral;
--把自核通过的日积分数据移到积分历史表中
insert into integral.t_o_IntegralHis
select id,
memberid,
day,
integralaction,
type,
cent,
eventtime,
eventsource,
synctime,
audtime,
auditor,
sysdate,
state,
accountid
from integral.t_o_integralday t3
where t3.memberid =l_memberid and to_char(t3.day,'yyyy-mm-dd') =to_char(integraldate,'yyyy-mm-dd');
--删除放入历史表中的数据
delete from integral.t_o_integralday t3
where t3.memberid = l_memberid
and to_char(t3.day, 'yyyy-mm-dd') =
to_char(integraldate, 'yyyy-mm-dd');
--更新待审核表中状态为1表示手工审核通过
update integral.t_o_integralAudit t1a
set t1a.state = '1'
where t1a.state = '0'
and to_char(t1a.day, 'yyyy-mm-dd') = to_char(integraldate, 'yyyy-mm-dd')
and t1a.memberId = l_memberid;
commit;
EXCEPTION
WHEN OTHERS THEN
rollback; v_ErrorCode :=SQLCode;
v_ErrorMsg :=Sqlerrm;
v_CurrentUSer:=User;
v_Information:='Error encountered on' ||
TO_char(sysdate)||'by database user'||v_CurrentUser;
--把错误日志写到日志表
insert into log_table(code,message,info)
values(v_ErrorCode,v_ErrorMsg,v_Information);
commit;
end p_mi_IntegAudit;
i_memberid varchar2(20); --会员号
i_addup number(12, 2); --日累计积分
i_deduct number(12, 2); --日扣除积分
i_gained number(12, 2) := 0; --得到积分
i_deductsum number(12, 2) := 0; --扣除积分
i_addupsum number(12, 2) := 0; --累计积分
i_recount number := 1; --为了判断是否第一次循环游标
i_accountid number(20); --帐户id
i_accountidhis number(20); --帐户id
i_evttype varchar2(1); --积分行为 0表示增加积分 1表示减少积分
i_centsum number(12, 2); --日累计积分
i_putout number(12, 2) := 0; --发出积分
i_restitute number(12, 2) := 0; --得到积分
i_existence number(12, 2) := 0; --现有积分
i_integ number(12, 2); --积分变量
l_count number;
v_ErrorCode number; ---code for the error
v_Errormsg varchar2(200); ---message text for the error
v_CurrentUser varchar2(8); ---current database user
v_Information varchar2(100); ---info about the error
--根据传入的指定会员查询出待审核状态的数据
cursor cur_mi_autoInteg is
select t.memberid, t1.evttype, sum(t.cent)
from integral.t_o_integralday t,
integral.t_c_integralRule t1
where t.integralaction = t1.id and t.state='3' and t.memberid=l_memberid
and to_char(t.day, 'yyyy-mm-dd') = to_char(integraldate, 'yyyy-mm-dd')
group by t.memberid, t1.evttype;
--查询出一个帐户当日所有的累计积分和扣除积分
cursor cur_mi_accountIntegral is
select t.accountid, t1.evttype, sum(t.cent)
from integral.t_o_integralday t,
integral.t_c_integralRule t1,
integral.t_o_accountintegral t2
where t.integralaction = t1.id and t.state='3'
and t.accountid = t2.accountid
and to_char(t.day, 'yyyy-mm-dd') = to_char(sysdate, 'yyyy-mm-dd')
group by t.accountid, t1.evttype order by t.accountid;begin
--更新指定会员积分汇总表（规则说明：得到积分 += 日累计积分；扣除积分 += 日扣除积分；累计积分 += 日累计积分 - 日扣除积分）
open cur_mi_autoInteg;
loop
fetch cur_mi_autoInteg
into i_memberid, i_evttype, i_integ;
exit when cur_mi_autoInteg%notfound;
--计算会员的得到积分，扣除积分，累计积分
if(i_evttype = '0') then
i_gained := i_gained + i_integ;
i_addupsum := i_addupsum + i_integ;
else
i_deductsum := i_deductsum + i_integ;
i_addupsum := i_addupsum - i_integ;
end if;
end loop;
select count(*) into l_count from integral.t_o_integralCollect t where memberid = l_memberid;
if(l_count = 0) then
--如果t_o_integralCollect(会员积分汇总表)中不存在此会员，插入此会员的数据
insert into integral.t_o_integralCollect
values
(i_memberId,
i_gained,
i_deductsum,
i_addupsum,
sysdate);
else
--更新指定会员记录
update integral.t_o_integralCollect
set gained = gained + i_gained,
deduct = deduct + i_deductsum,
addup = addup + i_addupsum,
collectTune = sysdate
where memberid = i_memberid;
end if;
close cur_mi_autoInteg;
--更新账户积分表（规则说明：按账户积分汇总；发出积分 += 账户日累计积分；得到积分 += 账户日扣除积分；现有积分 -= 账户日累计积分)
open cur_mi_accountIntegral;
loop
fetch cur_mi_accountIntegral
into i_accountid, i_evttype, i_centsum;
exit when cur_mi_accountIntegral%notfound;
--因为每个帐户可能出现一条记录类型为0和1的记录
if (i_recount = 1 and i_evttype = '0') then
i_accountidhis := i_accountid;
i_putout := i_putout + i_centsum;
i_existence := i_existence - i_centsum;
i_recount := i_recount + 1;
--存储帐户id，同时给相应的变量赋值
elsif (i_recount = 1 and i_evttype = '1') then
i_accountidhis := i_accountid;
i_restitute := i_restitute + i_centsum;
i_recount := i_recount + 1;
--出现和上一次帐户累加相应结果
elsif (i_accountidhis = i_accountid and i_evttype = '0') then
i_putout := i_putout + i_centsum;
i_existence := i_existence - i_centsum;
--出现和上一次帐户累加相应结果
elsif (i_accountidhis = i_accountid and i_evttype = '1') then
i_restitute := i_restitute + i_centsum;
--如果和上一次帐户不一样，更新上一级帐户，同时把此帐户的值，赋值给相应变量
elsif (i_accountidhis <> i_accountid and i_evttype = '0') then
update integral.t_o_accountIntegral
set putout = putout + i_putout,
restitute = restitute + i_restitute,
existence = existence + i_existence
where accountid = i_accountidhis;
i_putout := 0;
i_existence := 0;
i_restitute := 0;
i_accountidhis := i_accountid;
i_putout := i_putout + i_centsum;
i_existence := i_existence - i_centsum;
elsif (i_accountidhis <> i_accountid and i_evttype = '1') then
--如果和上一次帐户不一样，更新上一级帐户，同时把此帐户的值，赋值给相应变量
update integral.t_o_accountIntegral
set putout = putout + i_putout,
restitute = restitute + i_restitute,
existence = existence + i_existence
where accountid = i_accountidhis;
i_restitute := 0;
i_putout := 0;
i_existence := 0;
i_accountidhis := i_accountid;
i_restitute := i_restitute + i_centsum;
end if;
end loop;
--循环结束更新最后一个帐户的值
update integral.t_o_accountIntegral
set putout = putout + i_putout,
restitute = restitute + i_restitute,
existence = existence + i_existence
where accountid = i_accountidhis;
close cur_mi_accountIntegral;
--把自核通过的日积分数据移到积分历史表中
insert into integral.t_o_IntegralHis
select id,
memberid,
day,
integralaction,
type,
cent,
eventtime,
eventsource,
synctime,
audtime,
auditor,
sysdate,
state,
accountid
from integral.t_o_integralday t3
where t3.memberid =l_memberid and to_char(t3.day,'yyyy-mm-dd') =to_char(integraldate,'yyyy-mm-dd');
--删除放入历史表中的数据
delete from integral.t_o_integralday t3
where t3.memberid = l_memberid
and to_char(t3.day, 'yyyy-mm-dd') =
to_char(integraldate, 'yyyy-mm-dd');
--更新待审核表中状态为1表示手工审核通过
update integral.t_o_integralAudit t1a
set t1a.state = '1'
where t1a.state = '0'
and to_char(t1a.day, 'yyyy-mm-dd') = to_char(integraldate, 'yyyy-mm-dd')
and t1a.memberId = l_memberid;
commit;
EXCEPTION
WHEN OTHERS THEN
rollback; v_ErrorCode :=SQLCode;
v_ErrorMsg :=Sqlerrm;
v_CurrentUSer:=User;
v_Information:='Error encountered on' ||
TO_char(sysdate)||'by database user'||v_CurrentUser;
--把错误日志写到日志表
insert into log_table(code,message,info)
values(v_ErrorCode,v_ErrorMsg,v_Information);
commit;
end p_mi_IntegAudit;
--如果t_o_integralCollect(会员积分汇总表)中不存在此会员，插入此会员的数据
insert into integral.t_o_integralCollect
values
(i_memberId,
i_gained,
i_deductsum,
i_addupsum,
sysdate);
else
--更新指定会员记录
update integral.t_o_integralCollect
set gained = gained + i_gained,
deduct = deduct + i_deductsum,
addup = addup + i_addupsum,
collectTune = sysdate
where memberid = i_memberid;
end if;
//为什么l_count已经等于0了,还是不执行下边的sql.
update integral.t_o_integralCollect
set gained = gained + i_gained,
deduct = deduct + i_deductsum,
addup = addup + i_addupsum,
collectTune = sysdate
where memberid = i_memberid;
end if;
什么原因会导致这样的错误.
如果>0就执行update
l_count = 0 了肯定不执行下面的else了。