游标是对数据进行逐条进行处理的时候用!不过游标没有普通和不普通之分!
create or replace procedure pr_rsdl_didelete(
v_tz in varchar2, --台帐
v_time in varchar2,--时间
retcode out number,
errtext out varchar2)
is
v_errmessage varchar2(128); --错误信息
v_num number; --计数
v_Ptctype char(1);--是否单位0:个人 1:单位
v_Ptcaccount number;--余额
v_sum number; --该次生成的全部金额
v_Ptcid varchar2(24);--协议内码
v_Ptcpbiid varchar2(24);--人员内码
v_Ptccpbno varchar2(24);--单位内码
v_month varchar2(8);--上月份
v_nowmonth varchar2(6);--现在月份cursor rsdl_cursor is --查询出该台帐的所以数据
select distinct Crgptcid
from lm_base_compbase,lm_base_pbinfo,lm_rsdl_tzcode,lm_rsdl_cardregister
where cpbno(+)=crgcpbno and pbiid = crgpbiid and tzctzid = crgtzid
and crgtzid = v_tz;
rsdl_vale rsdl_cursor%rowtype;
rsdl_val rsdl_vale.Crgptcid%type; cursor rsdl_cursor2 is --查询出有资金来往的数据
select distinct Crgptcid
from lm_base_compbase,lm_base_pbinfo,lm_rsdl_tzcode,lm_rsdl_cardregister a ,
lm_rsdl_acceptcash b where cpbno(+)=crgcpbno and pbiid = crgpbiid and tzctzid = crgtzid
and Crgptcid = Apcptcid and Apcflag in ('11','12') and crgtzid = v_tz and to_char(Apccreatetime,'yyyymmdd') = v_time;
rsdl_vale2 rsdl_cursor2%rowtype;
rsdl_val2 rsdl_vale2.Crgptcid%type;
begin
select to_char(sysdate,'yyyymm') into v_nowmonth from dual;
if v_nowmonth <> substr(v_time,1,6) then
retcode := -3;
errtext := '只能删除本月的单据费用!';
return;
end if;
select to_char(add_months(to_date(substr(v_time,1,6),'yyyymm'),-1),'yyyymm') into v_month from dual;
--处理状态
open rsdl_cursor;
loop
fetch rsdl_cursor into rsdl_val;
exit when rsdl_cursor%NOTFOUND;
--确定协议,可以不用count(*)来判断数据是否存在,因为游标查询时就已经存在
select Ptcid,Ptcaccount,Ptctype into v_Ptcid,v_Ptcaccount,v_Ptctype from lm_rsdl_protocol where Ptcid = rsdl_val;
if v_Ptctype = '0' then --人员
--修改挂靠状态,只要有一种情况就修改
update lm_rsdl_cardregister set Crgstopflag = '0' where exists (select * from lm_rsdl_feedetail
where Fdtpbiid = v_Ptcpbiid and Fdttzid = v_tz and Fdtdialmonth = v_month); update lm_rsdl_cardregister set Crgstopflag = '0' where exists (select * from lm_rsdl_servtrans
where Svrpbiid = v_Ptcpbiid and Svrtzid = v_tz and Svrendmonth <= substr(v_time,1,6));
else --单位
--修改挂靠状态,只要有一种情况就修改
update lm_rsdl_cardregister set Crgstopflag = '0' where Crgcpbno = v_Ptccpbno and exists (select * from lm_rsdl_feedetail
where Fdtpbiid = Crgpbiid and Fdttzid = v_tz and Fdtdialmonth = v_month); update lm_rsdl_cardregister set Crgstopflag = '0' where Crgcpbno = v_Ptccpbno and exists (select * from lm_rsdl_servtrans
where Svrpbiid = Crgpbiid and Svrtzid = v_tz and Svrendmonth <= substr(v_time,1,6));
end if;
commit;
end loop;
close rsdl_cursor;
--处理金额
open rsdl_cursor2;
loop
fetch rsdl_cursor2 into rsdl_val2;
exit when rsdl_cursor2%NOTFOUND;
select Ptcid,Ptcaccount,Ptctype into v_Ptcid,v_Ptcaccount,v_Ptctype from lm_rsdl_protocol where Ptcid = rsdl_val2;
--本次缴费金额
select sum(decode(Apcflag,'11',Apcacpcash,'12',Apcacpcash,0)) into v_sum from lm_rsdl_acceptcash
where to_char(Apccreatetime,'yyyymmdd') = v_time and Apcptcid = rsdl_val2;
--修改余额
v_Ptcaccount := v_Ptcaccount + v_sum;
update lm_rsdl_protocol set Ptcaccount = v_Ptcaccount where Ptcid = rsdl_val2;
--删除数据
--删除代缴费用
delete from lm_rsdl_feedetail where Fdttzid = v_tz and exists (select * from lm_rsdl_acceptcash
where Fdtapcid = Apcid and Apcptcid = v_Ptcid and to_char(Apccreatetime,'yyyymmdd') = v_time and Apcflag = '11');
--删除管理费用
delete from lm_rsdl_servtrans where Svrtzid = v_tz and exists (select * from lm_rsdl_acceptcash
where Svrapcid = Apcid and Apcptcid = v_Ptcid and to_char(Apccreatetime,'yyyymmdd') = v_time and Apcflag = '12');
--删除资金表
delete from lm_rsdl_acceptcash
where Apcptcid = v_Ptcid and to_char(Apccreatetime,'yyyymmdd') = v_time and Apcflag in ('11','12');
commit;
end loop;
close rsdl_cursor2;
retcode := 0;
exception
when others then
retcode := -6;
v_ErrMessage := SQLERRM;
errtext := SQLERRM;
end;
create or replace procedure pr_rsdl_didelete(
v_tz in varchar2, --台帐
v_time in varchar2,--时间
retcode out number,
errtext out varchar2)
is
v_errmessage varchar2(128); --错误信息
v_num number; --计数
v_Ptctype char(1);--是否单位0:个人 1:单位
v_Ptcaccount number;--余额
v_sum number; --该次生成的全部金额
v_Ptcid varchar2(24);--协议内码
v_Ptcpbiid varchar2(24);--人员内码
v_Ptccpbno varchar2(24);--单位内码
v_month varchar2(8);--上月份
v_nowmonth varchar2(6);--现在月份cursor rsdl_cursor is --查询出该台帐的所以数据
select distinct Crgptcid
from lm_base_compbase,lm_base_pbinfo,lm_rsdl_tzcode,lm_rsdl_cardregister
where cpbno(+)=crgcpbno and pbiid = crgpbiid and tzctzid = crgtzid
and crgtzid = v_tz;
rsdl_vale rsdl_cursor%rowtype;
rsdl_val rsdl_vale.Crgptcid%type; cursor rsdl_cursor2 is --查询出有资金来往的数据
select distinct Crgptcid
from lm_base_compbase,lm_base_pbinfo,lm_rsdl_tzcode,lm_rsdl_cardregister a ,
lm_rsdl_acceptcash b where cpbno(+)=crgcpbno and pbiid = crgpbiid and tzctzid = crgtzid
and Crgptcid = Apcptcid and Apcflag in ('11','12') and crgtzid = v_tz and to_char(Apccreatetime,'yyyymmdd') = v_time;
rsdl_vale2 rsdl_cursor2%rowtype;
rsdl_val2 rsdl_vale2.Crgptcid%type;
begin
select to_char(sysdate,'yyyymm') into v_nowmonth from dual;
if v_nowmonth <> substr(v_time,1,6) then
retcode := -3;
errtext := '只能删除本月的单据费用!';
return;
end if;
select to_char(add_months(to_date(substr(v_time,1,6),'yyyymm'),-1),'yyyymm') into v_month from dual;
--处理状态
open rsdl_cursor;
loop
fetch rsdl_cursor into rsdl_val;
exit when rsdl_cursor%NOTFOUND;
--确定协议,可以不用count(*)来判断数据是否存在,因为游标查询时就已经存在
select Ptcid,Ptcaccount,Ptctype into v_Ptcid,v_Ptcaccount,v_Ptctype from lm_rsdl_protocol where Ptcid = rsdl_val;
if v_Ptctype = '0' then --人员
--修改挂靠状态,只要有一种情况就修改
update lm_rsdl_cardregister set Crgstopflag = '0' where exists (select * from lm_rsdl_feedetail
where Fdtpbiid = v_Ptcpbiid and Fdttzid = v_tz and Fdtdialmonth = v_month); update lm_rsdl_cardregister set Crgstopflag = '0' where exists (select * from lm_rsdl_servtrans
where Svrpbiid = v_Ptcpbiid and Svrtzid = v_tz and Svrendmonth <= substr(v_time,1,6));
else --单位
--修改挂靠状态,只要有一种情况就修改
update lm_rsdl_cardregister set Crgstopflag = '0' where Crgcpbno = v_Ptccpbno and exists (select * from lm_rsdl_feedetail
where Fdtpbiid = Crgpbiid and Fdttzid = v_tz and Fdtdialmonth = v_month); update lm_rsdl_cardregister set Crgstopflag = '0' where Crgcpbno = v_Ptccpbno and exists (select * from lm_rsdl_servtrans
where Svrpbiid = Crgpbiid and Svrtzid = v_tz and Svrendmonth <= substr(v_time,1,6));
end if;
commit;
end loop;
close rsdl_cursor;
--处理金额
open rsdl_cursor2;
loop
fetch rsdl_cursor2 into rsdl_val2;
exit when rsdl_cursor2%NOTFOUND;
select Ptcid,Ptcaccount,Ptctype into v_Ptcid,v_Ptcaccount,v_Ptctype from lm_rsdl_protocol where Ptcid = rsdl_val2;
--本次缴费金额
select sum(decode(Apcflag,'11',Apcacpcash,'12',Apcacpcash,0)) into v_sum from lm_rsdl_acceptcash
where to_char(Apccreatetime,'yyyymmdd') = v_time and Apcptcid = rsdl_val2;
--修改余额
v_Ptcaccount := v_Ptcaccount + v_sum;
update lm_rsdl_protocol set Ptcaccount = v_Ptcaccount where Ptcid = rsdl_val2;
--删除数据
--删除代缴费用
delete from lm_rsdl_feedetail where Fdttzid = v_tz and exists (select * from lm_rsdl_acceptcash
where Fdtapcid = Apcid and Apcptcid = v_Ptcid and to_char(Apccreatetime,'yyyymmdd') = v_time and Apcflag = '11');
--删除管理费用
delete from lm_rsdl_servtrans where Svrtzid = v_tz and exists (select * from lm_rsdl_acceptcash
where Svrapcid = Apcid and Apcptcid = v_Ptcid and to_char(Apccreatetime,'yyyymmdd') = v_time and Apcflag = '12');
--删除资金表
delete from lm_rsdl_acceptcash
where Apcptcid = v_Ptcid and to_char(Apccreatetime,'yyyymmdd') = v_time and Apcflag in ('11','12');
commit;
end loop;
close rsdl_cursor2;
retcode := 0;
exception
when others then
retcode := -6;
v_ErrMessage := SQLERRM;
errtext := SQLERRM;
end;
解决方案 »
- oracle中能不能实现存储“数组”?
- 如何写这样的insert&update触发器
- 如何让blob字段存储非十六进制的字符
- 歧义存储过程看不懂
- 在一个表中,怎么把横的结构,查询成竖的值出来?急!急!急!在线等
- 用pro c写的一个集成访问数据库的动态库(属于16位版本)。访问Oracle8i以下的版本没问题,访问Oracle8i以上版本出错,请教是什么原因?
- 新建了一个数据库为什么manager console的导航器里没显示这个数据库
- Oracle Enterprise Manager启动故障?
- 请问“SELECT /*+ norewrite */ ...”中“/* ... */”中的语句是什么意思?是注释吗?
- sqlplus连接报错ORA-12154: TNS:could not resolve the connect identifier specified
- 想做个和数据库&网络有关的项目
- 在Oracle817中想增加一个定时执行的任务:每隔1小时更新一个数据库用户信息。按照书本上的指示操作,但总提示出错信息。
可能我没问明白, 我问的是
---ref 游标变量----
一般在什么时候用