create or replace procedure ERR_REDUCEDATA is --sx
--定义变量 v_realindiobjid VARCHAR2(100); v_indiobjid VARCHAR2(32); v_residuemoney number ; v_reducemoney number ; v_approbjid VARCHAR2(32); -- v_indiecoid VARCHAR2(32); --v_indiecocode VARCHAR2(32); v_Apprecoid VARCHAR2(32); --v_Apprecocode VARCHAR2(32); v_divid VARCHAR2(32); --单位 v_acctcode VARCHAR2(32); --功能分类 v_sourcetypeid VARCHAR2(32); --来源类型 v_deptid VARCHAR2(32); --处室 v_paymodeid VARCHAR2(32); --支付方式 v_fundkindid VARCHAR2(32); --资金性质 v_projtypeid VARCHAR2(32); --项目类别 v_projid VARCHAR2(32); --项目 v_extendcol20 VARCHAR2(32); --政府经济分类 v_economicprop5 VARCHAR2(1); v_fundprop5 VARCHAR2(1); v_sourceprop5 VARCHAR2(1); v_deptprop5 VARCHAR2(1); v_divprop5 VARCHAR2(1); v_acctprop5 VARCHAR2(1); v_paymodprop5 VARCHAR2(1); v_projtypeprop5 VARCHAR2(1); v_projprop5 VARCHAR2(1); v_extcol205 VARCHAR2(1); -- 政府经济分类是否一致 v_apprvchrtdate Indi_t_ApprBillDetail.apprvchrtdate%type; v_sql VARCHAR2(8000); --v_sql1 VARCHAR2(5000); --v_sql2 VARCHAR2(5000); v_cout number; v_ct number ; v_sumresiduemoney number; TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标 indiinfo ref_cursor_type; -- v_extct number ; -----------------------------------存储的变量 v_reducetype VARCHAR2(32); v_planobjid VARCHAR2(32); v_vchrobjid VARCHAR2(32);
--定义游标 CURSOR emp_cursor IS select --indiobjid, --approbjid, --indiecoid, --indiecocode, h.reducemoney, l.approbjid, l.economictypeid as Apprecoid, -- Apprecocode, l.divid, l.acctcode, --功能分类 l.sourcetypeid, --来源类型 l.deptid, --处室 l.paymodeid, --支付方式 l.fundkindid, --资金性质 l.projtypeid, --项目类别 l.projid, --项目 l.extendcol20, --政府经济分类 substring(g.ColProp_4,5,1) as economicprop5, substring(g.ColProp_5,5,1) as fundprop5, substring(g.SourceProp,5,1) as sourceprop5, substring(g.DeptProp,5,1) as deptprop5, substring(g.DivProp,5,1) as divprop5, substring(g.AcctProp,5,1) as acctprop5, substring(g.PayModeProp,5,1) as paymodprop5, substring(g.ProjTypeProp,5,1) as projtypeprop5, substring(g.ProjProp,5,1) as projprop5, substring(g.ExtCol20Prop,5,1) as extcol205, f.billdate apprvchrtdate from (select approbjid,sum(reducemoney) reducemoney from temp_t_indireduce group by approbjid ) h inner join Indi_t_ApprBillDetail l on h.approbjid = l.approbjid inner join Indi_t_ApprBillmain f on l.billobjid = f.billobjid inner join Indi_t_ApprBillType g on f.billtype = g.billtypeid ;
BEGIN---创建一个临时表 ----存入错误数据 -- execute immediate ' create table temp_t_indireduce as (
-- select * from Indi_t_IndiReduce where approbjid in (select approbjid from -- (select e.reducemoney,a.billobjid,b.indiobjid,a.approbjid,a.economictypeid as Apprecoid, (select acctcode from efm_t_acctitem k where k.acctid=a.economictypeid) as Apprecocode, -- b.economictypeid as indiecoid ,(select acctcode from efm_t_acctitem k where k.acctid=b.economictypeid) as indiecocode -- from Indi_t_ApprBillDetail a -- inner join Indi_t_IndiReduce e on a.approbjid=e.approbjid -- inner join indi_t_indibilldetail b on e.indiobjid=b.indiobjid
-- where a.economictypeid<>b.economictypeid) tt -- where substr(Apprecocode,1,3)<>indiecocode) -- )';
--删除核销表中错误数据 ()
--delete from Indi_t_IndiReduce where approbjid in (select approbjid from -- (select e.reducemoney,a.billobjid,b.indiobjid,a.approbjid,a.economictypeid as Apprecoid, (select acctcode from efm_t_acctitem k where k.acctid=a.economictypeid) as Apprecocode, -- b.economictypeid as indiecoid ,(select acctcode from efm_t_acctitem k where k.acctid=b.economictypeid) as indiecocode -- from Indi_t_ApprBillDetail a --支付信息表 initmoney -- inner join Indi_t_IndiReduce e on a.approbjid=e.approbjid --指标核销表 -- inner join indi_t_indibilldetail b on e.indiobjid=b.indiobjid --指标信息的表
-- where a.economictypeid<>b.economictypeid) tt -- where substr(Apprecocode,1,3)<>indiecocode); -- --commit;
v_cout := 0 ;--循环开始LOOP -- v_sql1 :='select indiobjid,residuemoney '; -- v_sql2 :='select count(*) ';--符合条件的指标 -- v_sql := 'select indiobjid, residuemoney from (select aa.*, aa.indimoney - nvl(reducemoney, 0) residuemoney, mm.saudittime from indi_t_indibilldetail aa join indi_t_indibillmain mm on aa.billobjid = mm.billobjid left join ( select indiobjid, sum(reducemoney) reducemoney from Indi_t_IndiReduce where approbjid not in ( select approbjid from (select e.reducemoney,a.billobjid,b.indiobjid,a.approbjid,a.economictypeid as Apprecoid, (select acctcode from efm_t_acctitem k where k.acctid=a.economictypeid) as Apprecocode, b.economictypeid as indiecoid ,(select acctcode from efm_t_acctitem k where k.acctid=b.economictypeid) as indiecocode from Indi_t_ApprBillDetail a inner join Indi_t_IndiReduce e on a.approbjid=e.approbjid inner join indi_t_indibilldetail b on e.indiobjid=b.indiobjid where a.economictypeid<>b.economictypeid) tt where substr(Apprecocode,1,3)<>indiecocode) group by indiobjid ) bb on aa.indiobjid = bb.indiobjid where (aa.indimoney - nvl(reducemoney, 0)) > 0 and aa.billtypeobjid in (SELECT billtypeid FROM Indi_t_Indibilltype where typeisuse = ''2'') and mm.auditstate = ''2'') where 1 = 1 '; v_sql := ' select indiobjid, residuemoney from (select aa.*, aa.indimoney - nvl(aa.adjustmoney, 0) - nvl(aa.planmoney, 0) - nvl(aa.apprnmoney, 0) residuemoney, mm.saudittime from indi_t_indibilldetail aa join indi_t_indibillmain mm on aa.billobjid = mm.billobjid where aa.indimoney - nvl(aa.adjustmoney, 0) - nvl(aa.planmoney, 0) - nvl(aa.apprnmoney, 0) > 0 and aa.billtypeobjid in (SELECT billtypeid FROM Indi_t_Indibilltype where typeisuse = ''2'') and mm.auditstate = ''2'') where 1 = 1'; --v_sql := 'select indiobjid,residuemoney from (select aa.*,aa.indimoney - nvl(reducemoney, 0) residuemoney ,mm.saudittime from indi_t_indibilldetail aa join indi_t_indibillmain mm on aa.billobjid = mm.billobjid left join (select indiobjid,sum(reducemoney) reducemoney from (select (select acctcode from efm_t_acctitem k where k.acctid=a.economictypeid) as Appr2, (select acctcode from efm_t_acctitem k where k.acctid=b.economictypeid) as indi2, b.indiobjid,e.reducemoney from Indi_t_ApprBillDetail a inner join Indi_t_IndiReduce e on a.approbjid=e.approbjid inner join indi_t_indibilldetail b on e.indiobjid=b.indiobjid ) tt where substr(tt.appr2,1,3)= tt.indi2 or tt.appr2 = tt.indi2 group by indiobjid ) bb on aa.indiobjid = bb.indiobjid where (aa.indimoney - nvl(reducemoney,0))>0 and aa.billtypeobjid in(SELECT billtypeid FROM Indi_t_Indibilltype where typeisuse = ''2'' ) and mm.auditstate = ''2'' ) where 1 = 1 '; --v_sql := 'select * from indi_t_indibilldetail where 1 = 1 '; IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF;
FETCH emp_cursor INTO -- v_indiobjid, v_reducemoney, v_approbjid, -- v_indiecoid, -- v_indiecocode, v_Apprecoid , -- v_Apprecocode, v_divid , --单位 v_acctcode , --功能分类 v_sourcetypeid , --来源类型 v_deptid , --处室 v_paymodeid , --支付方式 v_fundkindid , --资金性质 v_projtypeid , --项目类别 v_projid , --项目 v_extendcol20 , --政府经济分类 v_economicprop5 , v_fundprop5 , v_sourceprop5 , v_deptprop5 , v_divprop5 , v_acctprop5, v_paymodprop5, v_projtypeprop5, v_projprop5, v_extcol205, v_apprvchrtdate; --退出循环的条件 EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL; v_cout := v_cout + 1 ; IF v_economicprop5 = '1' then v_sql := v_sql || ' and (economictypeid = '''||v_Apprecoid||''' or economictypeid = (select superid from pub_t_economictype where economictypeid = ''' ||v_Apprecoid||'''))'; END IF ; IF v_fundprop5 = '1' then v_sql := v_sql || ' and (fundkindid = '''||v_fundkindid||''' or fundkindid = (select superid from Pub_t_FundType where fundkindid = ''' ||v_fundkindid||'''))'; END IF ; IF v_sourceprop5 = '1' then v_sql := v_sql || ' and (sourcetypeid = '''||v_sourcetypeid||''' or sourcetypeid = (select superid from Indi_t_SourceType where sourcetypeid = ''' ||v_sourcetypeid||'''))'; END IF ; IF v_deptprop5 = '1' then v_sql := v_sql || ' and (deptid = '''||v_deptid ||''' or deptid = (select superid from Sys_t_Dept where deptid = ''' ||v_deptid||'''))'; END IF ; IF v_divprop5 = '1' then v_sql := v_sql || ' and (divid = '''||v_divid ||''' or divid = (select superid from Indi_v_Division where divid = ''' ||v_divid||'''))'; END IF ; IF v_acctprop5 = '1' then v_sql := v_sql || ' and (acctcode = '''||v_acctcode ||''' or acctcode = (select superid from indi_v_acctitem where acctid = '''||v_acctcode||'''))'; END IF ; IF v_paymodprop5 = '1' then v_sql := v_sql || ' and (paymodeid = '''||v_paymodeid||''' or paymodeid = (select superid from Indi_t_PayMode where paymodeid = '''||v_paymodeid||'''))'; END IF ; IF v_projtypeprop5 = '1' then v_sql := v_sql || ' and projtype = '''||v_projtypeid||''''; --Indi_t_ProjType END IF ; IF v_projprop5 = '1' then v_sql := v_sql || ' and (projid = '''||v_projid ||''' or projid = (select superid from Indi_t_Project where projid = '''||v_projid||'''))'; END IF ; IF v_extcol205 = '1' then v_sql := v_sql || ' and (extendcol20 = '''||v_extendcol20||''' or extendcol20 = (select superid from pub_t_govecotype where govecotypeid = '''||v_extendcol20||'''))'; END IF ; IF v_apprvchrtdate is not null then v_sql := v_sql || ' and saudittime <= '''||v_apprvchrtdate||''''; END IF ; v_sql := v_sql || ' order by saudittime asc '; --dbms_output.put_line(v_sql);
-- EXECUTE IMMEDIATE V_SQL INTO v_extct; --v_sql2|| --if true then --v_extct > 0 then --存在数据不判断 indiinfo 报错 --if len(v_realindiobjid) = 32 then -----///--delete from Indi_t_IndiReduce where approbjid = :v_approbjid; -- dbms_output.put_line('第'||v_cout||'行数据有一条--'||v_realindiobjid); --elsif len(v_realindiobjid) > 32 then -- dbms_output.put_line('第'||v_cout||'行有多条--'||v_realindiobjid); -- else -- dbms_output.put_line('第'||v_cout||'行无数据--'||v_realindiobjid); -- end if ; --v_sql := 'select * from dual where 1 = 2 '; open indiinfo for v_sql ; --v_sql1|| v_ct :=0 ; v_sumresiduemoney := 0 ; v_realindiobjid := '' ; loop fetch indiinfo into v_indiobjid,v_residuemoney; exit when indiinfo%notfound; v_ct := v_ct + 1 ; v_sumresiduemoney := v_sumresiduemoney + v_residuemoney ; -----有多少钱 v_realindiobjid := v_realindiobjid ||v_indiobjid ; if v_ct = 1 then --取出公用的数据 select reducetype,planobjid,vchrobjid into v_reducetype,v_planobjid,v_vchrobjid from temp_t_IndiReduce where approbjid = v_approbjid and rownum = 1 ;
--删掉原始数据 delete from temp_t_IndiReduce where approbjid = v_approbjid ;
end if ;
--插入新数据 if v_residuemoney >= v_reducemoney then insert into Indi_t_IndiReduce(indiobjid,reducetype,planobjid,approbjid,reducemoney,remainmoney,vchrobjid) values ( v_indiobjid,v_reducetype,v_planobjid,v_approbjid,v_reducemoney,0,v_vchrobjid ) ;
commit ; --符合条件了提交 v_ct := -1 ; exit; else insert into Indi_t_IndiReduce(indiobjid,reducetype,planobjid,approbjid,reducemoney,remainmoney,vchrobjid) values ( v_indiobjid,v_reducetype,v_planobjid,v_approbjid,v_residuemoney,0,v_vchrobjid ) ; v_reducemoney := v_reducemoney - v_residuemoney ; --剩余要和小的钱
end if ;
-- dbms_output.put_line('第'||v_cout||'行--'); --sql语句 --dbms_output.put_line('第'||v_cout||'行--'||v_indiobjid||'剩余'||v_residuemoney); end loop;
-- if v_sumresiduemoney < v_reducemoney then -- dbms_output.put_line(v_ct||'无匹配'||v_cout||v_approbjid); -- end if ; if v_ct = 0 then dbms_output.put_line('第'||v_cout||'条无匹配,approbjid = '||v_approbjid); elsif v_ct > 0 then dbms_output.put_line('第'||v_cout||'条核销金额不够,approbjid = '||v_approbjid); rollback ; --钱不够 回滚
end if ;
close indiinfo; --commit;
END LOOP;
END;
转载于:https://www.cnblogs.com/sx2zx/p/9264507.html
相关资源：数据结构—成绩单生成器