create or replace function BankRecImport(groupvalue in varchar2,dirvalue in number,recimportRecmode in number,reconState out number,str in varchar2)
return varchar2 is
areconstr varchar2(300);
fcursorA SYS_REFCURSOR;
fcursorB SYS_REFCURSOR;
fcursorC SYS_REFCURSOR;
fcursorD SYS_REFCURSOR;
counta number;
countb number;
countd number;
acntno varchar2(50);
inneracnt varchar2(50);
recimportCid number;
recimportLogid number;
dira number;
targetDir number; returnCount varchar2(100);
v_sql varchar2(1000);
rectypea varchar2(10); configCid number;
configCurlogid number;
configCfgname varchar2(100);
configInneracnt varchar2(100);
configBankacnt varchar2(100); periodId number; -- 操作recAccConfig表
recAccConfigCid integer;
recAccConfigCurlogid integer;
recAccConfigInneracnt integer;
recAccConfigBankacnt integer;
reconlogId integer;
count1 integer;
sum1 NUMBER(18,2);
count2 integer;
sum2 NUMBER(18,2);
count3 integer;
sum3 NUMBER(18,2);
count4 integer;
sum4 NUMBER(18,2); --操作 reconstate表,查询state字段,判断前台是否在进行对帐
RState Integer; reconstateCount Integer; revocationValue Integer;
begin
reconState:=5; /**
前五种对帐方式
*/
IF dirvalue=1 then
open fcursorA for select reconstr,count(reconstr),acntno,dir from recimport t where t.rectype!=2 and state='0' and reconstr is not null group by reconstr,acntno,dir;
loop
<<next_loop1>>
fetch fcursorA into areconstr,counta,acntno,dira;
exit when fcursorA%notfound; IF dira= 1 THEN
targetDir:= 2;
ELSE
targetDir:= 1;
END IF; begin
select cid,curlogid,inneracnt into recimportCid,recimportLogid,inneracnt from RecAccConfig where BANKACNT =acntno;
Exception
when NO_DATA_FOUND then
goto next_loop1;
end;
select count(*) into countb from recimport t where reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0';
select count(*) into countd from recimport t where reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
IF countb=0 or countd=0 then
goto next_loop1;
end if;
IF counta<countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0'; update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
commit;
goto next_loop1;
END IF;
IF counta = countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0'; update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
commit;
goto next_loop1;
END IF;
IF counta >countb THEN
update recimport set RECONFLAG='1' ,state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0';
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
commit;
goto next_loop1;
END IF;
end loop;
commit;
close fcursorA;
IF recimportRecmode=3 OR recimportRecmode=4 THEN
BankRecImport3_4(dirvalue,recimportRecmode);
END IF; commit;
END IF;
/**
后两种对帐方式
*/
IF dirvalue=2 then
open fcursorB for select reconstr,count(reconstr),acntno,dir,t.rectype from recimport t where state=0 and reconstr is not null group by reconstr,acntno,dir,rectype;
loop
<<next_loop2>> fetch fcursorB into areconstr,counta,acntno,dira,rectypea;
exit when fcursorB%notfound; IF dira= 1 THEN
targetDir:= 2;
ELSE
targetDir:= 1;
END IF; begin
select cid,curlogid,inneracnt into recimportCid,recimportLogid,inneracnt from RecAccConfig where BANKACNT=acntno or inneracnt=acntno;
Exception
when NO_DATA_FOUND then
goto next_loop2;
end; select count(*) into countb from recimport t where reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
select count(*) into countd from recimport t where reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0;
IF countb=0 or countd=0 then
goto next_loop2;
end if;
IF counta<countb THEN
update recimport set RECONFLAG='1', state= '1',cid= recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0;
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
commit;
goto next_loop2;
END IF;
IF counta = countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
commit;
goto next_loop2;
END IF;
IF counta >countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
commit;
goto next_loop2;
END IF;
end loop;
commit;
close fcursorB;
END IF; <<reconstateloop>>
select count(*) into returnCount from recimport where state='0';
return (returnCount);
end BankRecImport;
return varchar2 is
areconstr varchar2(300);
fcursorA SYS_REFCURSOR;
fcursorB SYS_REFCURSOR;
fcursorC SYS_REFCURSOR;
fcursorD SYS_REFCURSOR;
counta number;
countb number;
countd number;
acntno varchar2(50);
inneracnt varchar2(50);
recimportCid number;
recimportLogid number;
dira number;
targetDir number; returnCount varchar2(100);
v_sql varchar2(1000);
rectypea varchar2(10); configCid number;
configCurlogid number;
configCfgname varchar2(100);
configInneracnt varchar2(100);
configBankacnt varchar2(100); periodId number; -- 操作recAccConfig表
recAccConfigCid integer;
recAccConfigCurlogid integer;
recAccConfigInneracnt integer;
recAccConfigBankacnt integer;
reconlogId integer;
count1 integer;
sum1 NUMBER(18,2);
count2 integer;
sum2 NUMBER(18,2);
count3 integer;
sum3 NUMBER(18,2);
count4 integer;
sum4 NUMBER(18,2); --操作 reconstate表,查询state字段,判断前台是否在进行对帐
RState Integer; reconstateCount Integer; revocationValue Integer;
begin
reconState:=5; /**
前五种对帐方式
*/
IF dirvalue=1 then
open fcursorA for select reconstr,count(reconstr),acntno,dir from recimport t where t.rectype!=2 and state='0' and reconstr is not null group by reconstr,acntno,dir;
loop
<<next_loop1>>
fetch fcursorA into areconstr,counta,acntno,dira;
exit when fcursorA%notfound; IF dira= 1 THEN
targetDir:= 2;
ELSE
targetDir:= 1;
END IF; begin
select cid,curlogid,inneracnt into recimportCid,recimportLogid,inneracnt from RecAccConfig where BANKACNT =acntno;
Exception
when NO_DATA_FOUND then
goto next_loop1;
end;
select count(*) into countb from recimport t where reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0';
select count(*) into countd from recimport t where reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
IF countb=0 or countd=0 then
goto next_loop1;
end if;
IF counta<countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0'; update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
commit;
goto next_loop1;
END IF;
IF counta = countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0'; update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
commit;
goto next_loop1;
END IF;
IF counta >countb THEN
update recimport set RECONFLAG='1' ,state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and rectype=2 and acntno=inneracnt and dir=targetDir and state='0';
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and rectype!=2 and acntno=BankRecImport.acntno and dir=dira and state='0';
commit;
goto next_loop1;
END IF;
end loop;
commit;
close fcursorA;
IF recimportRecmode=3 OR recimportRecmode=4 THEN
BankRecImport3_4(dirvalue,recimportRecmode);
END IF; commit;
END IF;
/**
后两种对帐方式
*/
IF dirvalue=2 then
open fcursorB for select reconstr,count(reconstr),acntno,dir,t.rectype from recimport t where state=0 and reconstr is not null group by reconstr,acntno,dir,rectype;
loop
<<next_loop2>> fetch fcursorB into areconstr,counta,acntno,dira,rectypea;
exit when fcursorB%notfound; IF dira= 1 THEN
targetDir:= 2;
ELSE
targetDir:= 1;
END IF; begin
select cid,curlogid,inneracnt into recimportCid,recimportLogid,inneracnt from RecAccConfig where BANKACNT=acntno or inneracnt=acntno;
Exception
when NO_DATA_FOUND then
goto next_loop2;
end; select count(*) into countb from recimport t where reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
select count(*) into countd from recimport t where reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0;
IF countb=0 or countd=0 then
goto next_loop2;
end if;
IF counta<countb THEN
update recimport set RECONFLAG='1', state= '1',cid= recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0;
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
commit;
goto next_loop2;
END IF;
IF counta = countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= counta and reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
commit;
goto next_loop2;
END IF;
IF counta >countb THEN
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and dir=targetDir and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
update recimport set RECONFLAG='1', state= '1',cid=recimportCid,logid=recimportLogid,recmode=recimportRecmode
where rownum<= countb and reconstr=areconstr and dir=dira and rectype=rectypea and acntno=BankRecImport.acntno and state=0 ;
commit;
goto next_loop2;
END IF;
end loop;
commit;
close fcursorB;
END IF; <<reconstateloop>>
select count(*) into returnCount from recimport where state='0';
return (returnCount);
end BankRecImport;
解决方案 »
- 【100分】没用过Oracle,如何备份数据库,如何导入XML文件到库里???
- ORACLE9i客户端升级10的补丁,谢谢大家提供
- 求一个oracle 数据导入问题
- ORA-01092: ORACLE instance terminated. Disconnection forced!!! 高分在线等待悬赏500分,解决后另开贴子给分
- 为什么我建的索引sql语句不用?
- 找不到table
- 请问如下SQL语句怎么写?
- 在Oracle中如何实现主键自增(除了用sequence)
- 关于Oracle中的MD5加密问题...
- 数据导入问题?在线等!请帮个忙!
- forms开发中变量作用域的问题
- oracle 求高手指导
1.里面操作用到的表,看看用到的列是不是建了索引(recimport,RecAccConfig等)
2.另外,重复代码比较多,你的下面几个条件里做的update动作基本都是相同的,为什么要写那么多遍呢:
IF counta<countb THEN和IF counta = countb THEN和IF counta >countb THEN
3.对于这张存放记录的表:recimport
我不知道是只存放一天的数据,还是一个月的还是多长时间的,还是一张临时表。
如果是临时表的话还好说,如果是存放长时间数据的话,这张表是否按天或者按月分区了呢?
4.对于你的每一个银行文件,是只包含一天的数据还是包含多天的数据?
结合表recimport来说,如果包含多天的数据,那么你的函数BankRecImport,是否考虑加上“时间”做为参数传入呢?
....
我觉得,还是应该根据你的业务需求,再好好分析一下。