create or replace
procedure costmonthlymony(acid IN VARCHAR2 )
as
costdate varchar(20);
cid varchar(20);
costmoney varchar(20);
cursor cur_1 is select A_C_ID,a_m_amount from zj_c_account_info;
costmonth varchar(20);
sqlstr varchar(200);
costcount varchar(10);
earlymoney number(18,0);
oldmonthlymoney number(18,0);
czjnumber varchar(20);
czjid varchar(20);
type cur_type is ref cursor;
cur cur_type;
selectstr varchar(200);
monthlymoney number(18,0);
resultflag number(18,0);
begin---判断执行时间 select to_char(last_day(sysdate),'yyyy-MM-DD') into costdate from dual;
costdate:=concat(costdate,'-23'); --每月最后一天23:00执行if to_char(sysdate,'MM-DD-hh24')=costdate then --如果当前时间为每月的最后一天的23:00
begin
select to_char(ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1),'yyyy-MM') into costmonth from dual; --如果执行时间是月底 那么扣除月租的月份为下月月初
end;---判断执行时间 --如果传过来的cid不为空 那么查找这一个客户下扣费的 情况,并扣费
if acid is not null then
begin
select a_m_amount into earlymoney from zj_c_account_info where a_c_id=acid;
cid:=acid;
sqlstr:=' select count(*) from sys_MonthlymoneyLog where M_c_id='''||cid||''' and substr(M_costDate,5,2)='''||substr(costmonth,5,2)||'''';
execute immediate sqlstr into costcount;
if costcount='0' then
begin
monthlymoney:=0;
selectstr:='select Z_ZJ_NUMBER,z_zj_id from zj_c_zj_info where z_c_id ='''||cid||'''';
open cur for selectstr;
fetch cur into czjnumber,czjid;
while cur%found
loop
pro_yz(2,czjnumber,0,'',cid,costmoney,resultflag);
monthlymoney:=monthlymoney+costmoney;
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.Nextval,cid,czjid,'0','0',costmoney,to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'存储过程','系统',costmonth,'','2');
fetch cur into czjnumber,czjid;
EXIT WHEN cur%NOTFOUND;
end loop;
update zj_c_account_info set a_m_amount=a_m_amount-monthlymoney where a_c_id=cid; --跟新客户月租 金额
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.nextval,cid,'0',earlymoney,earlymoney-monthlymoney,monthlymoney,to_char(sysdate,'yyyy-MM'),'存储过程','系统',costmonth,'','1');
insert into zj_logg_monthly_info values(S_S_LOGG_MONTHLY.Nextval,'客户',cid,'每月扣费','',0,0,'','','存储过程',to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'每月扣除客户月租',earlymoney,earlymoney-monthlymoney,costmonth); --月租账户金额
end;
commit;
end if;
select a_m_amount into oldmonthlymoney from zj_c_account_info where a_c_id=cid;
---如果月租小于零 了 那么就要把客户的状态置为欠费冻结
if oldmonthlymoney<0 then
begin
update zj_c_account_info set a_account_state='3' where a_c_id=cid; --如果客户的月租小于0 把状态置为欠费冻结
end;
end if;
exception
when others then
rollback;
end;
else --如果传过来的cid为空 查询所有客户
if cur_1%isopen = false then
begin
open cur_1;
fetch cur_1 into cid,earlymoney;
while cur_1%found
loop
sqlstr:=' select count(*) from sys_MonthlymoneyLog where M_c_id='''||cid||''' and substr(M_costDate,5,2)='''||substr(costmonth,5,2)||'''';
execute immediate sqlstr into costcount;
if costcount='0' then --没有扣除 要扣月份的记录
begin
monthlymoney:=0;
selectstr:='select Z_ZJ_NUMBER,z_zj_id from zj_c_zj_info where z_c_id ='''||cid||'''';
open cur for selectstr;
fetch cur into czjnumber,czjid;
while cur%found
loop
if czjnumber is not null then
pro_yz(2,czjnumber,0,'',cid,costmoney,resultflag);
--扣除
monthlymoney:=monthlymoney+costmoney;
--写日志
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.Nextval,cid,czjid,'0','0',costmoney,to_char(sysdate,'yyyy-MM'),'存储过程','系统',costmonth,'','2');
end if;
---循环计算 总机的月租 并写日志
fetch cur into czjnumber,czjid;
EXIT WHEN cur%NOTFOUND;
end loop;
end;
update zj_c_account_info set a_m_amount=a_m_amount-monthlymoney where a_c_id=cid; --跟新客户月租 金额
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.nextval,cid,czjid,earlymoney,earlymoney-monthlymoney,monthlymoney,to_char(sysdate,'yyyy-MM'),'存储过程/正常执行','系统',monthlymoney,'','1');
insert into zj_logg_monthly_info values(S_S_LOGG_MONTHLY.Nextval,'客户',cid,'每月扣费','',0,0,'','','存储过程',to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'每月扣除客户月租',earlymoney,earlymoney-monthlymoney,costmonth); --月租账户金额
end if;
commit;
select a_m_amount into oldmonthlymoney from zj_c_account_info where a_c_id=cid;
---如果月租小于零 了 那么就要把客户的状态置为欠费冻结
if oldmonthlymoney<0 then
begin
update zj_c_account_info set a_account_state='3' where a_c_id=cid; --如果客户的月租小于0 把状态置为欠费冻结
end;
end if;
fetch cur_1 into cid,earlymoney;
EXIT WHEN cur_1%NOTFOUND;
end loop;
close cur_1;
exception
when others then
rollback;
end;
end if;
end if;end if;
end; 这是我的存储过程 我想在 外面的循环里加上错误处理,可是我把 错误处理移到循环里面的时候就会报错,求给如何解决。。
procedure costmonthlymony(acid IN VARCHAR2 )
as
costdate varchar(20);
cid varchar(20);
costmoney varchar(20);
cursor cur_1 is select A_C_ID,a_m_amount from zj_c_account_info;
costmonth varchar(20);
sqlstr varchar(200);
costcount varchar(10);
earlymoney number(18,0);
oldmonthlymoney number(18,0);
czjnumber varchar(20);
czjid varchar(20);
type cur_type is ref cursor;
cur cur_type;
selectstr varchar(200);
monthlymoney number(18,0);
resultflag number(18,0);
begin---判断执行时间 select to_char(last_day(sysdate),'yyyy-MM-DD') into costdate from dual;
costdate:=concat(costdate,'-23'); --每月最后一天23:00执行if to_char(sysdate,'MM-DD-hh24')=costdate then --如果当前时间为每月的最后一天的23:00
begin
select to_char(ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1),'yyyy-MM') into costmonth from dual; --如果执行时间是月底 那么扣除月租的月份为下月月初
end;---判断执行时间 --如果传过来的cid不为空 那么查找这一个客户下扣费的 情况,并扣费
if acid is not null then
begin
select a_m_amount into earlymoney from zj_c_account_info where a_c_id=acid;
cid:=acid;
sqlstr:=' select count(*) from sys_MonthlymoneyLog where M_c_id='''||cid||''' and substr(M_costDate,5,2)='''||substr(costmonth,5,2)||'''';
execute immediate sqlstr into costcount;
if costcount='0' then
begin
monthlymoney:=0;
selectstr:='select Z_ZJ_NUMBER,z_zj_id from zj_c_zj_info where z_c_id ='''||cid||'''';
open cur for selectstr;
fetch cur into czjnumber,czjid;
while cur%found
loop
pro_yz(2,czjnumber,0,'',cid,costmoney,resultflag);
monthlymoney:=monthlymoney+costmoney;
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.Nextval,cid,czjid,'0','0',costmoney,to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'存储过程','系统',costmonth,'','2');
fetch cur into czjnumber,czjid;
EXIT WHEN cur%NOTFOUND;
end loop;
update zj_c_account_info set a_m_amount=a_m_amount-monthlymoney where a_c_id=cid; --跟新客户月租 金额
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.nextval,cid,'0',earlymoney,earlymoney-monthlymoney,monthlymoney,to_char(sysdate,'yyyy-MM'),'存储过程','系统',costmonth,'','1');
insert into zj_logg_monthly_info values(S_S_LOGG_MONTHLY.Nextval,'客户',cid,'每月扣费','',0,0,'','','存储过程',to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'每月扣除客户月租',earlymoney,earlymoney-monthlymoney,costmonth); --月租账户金额
end;
commit;
end if;
select a_m_amount into oldmonthlymoney from zj_c_account_info where a_c_id=cid;
---如果月租小于零 了 那么就要把客户的状态置为欠费冻结
if oldmonthlymoney<0 then
begin
update zj_c_account_info set a_account_state='3' where a_c_id=cid; --如果客户的月租小于0 把状态置为欠费冻结
end;
end if;
exception
when others then
rollback;
end;
else --如果传过来的cid为空 查询所有客户
if cur_1%isopen = false then
begin
open cur_1;
fetch cur_1 into cid,earlymoney;
while cur_1%found
loop
sqlstr:=' select count(*) from sys_MonthlymoneyLog where M_c_id='''||cid||''' and substr(M_costDate,5,2)='''||substr(costmonth,5,2)||'''';
execute immediate sqlstr into costcount;
if costcount='0' then --没有扣除 要扣月份的记录
begin
monthlymoney:=0;
selectstr:='select Z_ZJ_NUMBER,z_zj_id from zj_c_zj_info where z_c_id ='''||cid||'''';
open cur for selectstr;
fetch cur into czjnumber,czjid;
while cur%found
loop
if czjnumber is not null then
pro_yz(2,czjnumber,0,'',cid,costmoney,resultflag);
--扣除
monthlymoney:=monthlymoney+costmoney;
--写日志
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.Nextval,cid,czjid,'0','0',costmoney,to_char(sysdate,'yyyy-MM'),'存储过程','系统',costmonth,'','2');
end if;
---循环计算 总机的月租 并写日志
fetch cur into czjnumber,czjid;
EXIT WHEN cur%NOTFOUND;
end loop;
end;
update zj_c_account_info set a_m_amount=a_m_amount-monthlymoney where a_c_id=cid; --跟新客户月租 金额
insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.nextval,cid,czjid,earlymoney,earlymoney-monthlymoney,monthlymoney,to_char(sysdate,'yyyy-MM'),'存储过程/正常执行','系统',monthlymoney,'','1');
insert into zj_logg_monthly_info values(S_S_LOGG_MONTHLY.Nextval,'客户',cid,'每月扣费','',0,0,'','','存储过程',to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'每月扣除客户月租',earlymoney,earlymoney-monthlymoney,costmonth); --月租账户金额
end if;
commit;
select a_m_amount into oldmonthlymoney from zj_c_account_info where a_c_id=cid;
---如果月租小于零 了 那么就要把客户的状态置为欠费冻结
if oldmonthlymoney<0 then
begin
update zj_c_account_info set a_account_state='3' where a_c_id=cid; --如果客户的月租小于0 把状态置为欠费冻结
end;
end if;
fetch cur_1 into cid,earlymoney;
EXIT WHEN cur_1%NOTFOUND;
end loop;
close cur_1;
exception
when others then
rollback;
end;
end if;
end if;end if;
end; 这是我的存储过程 我想在 外面的循环里加上错误处理,可是我把 错误处理移到循环里面的时候就会报错,求给如何解决。。
begin
...
exception
...
end;
end loop;