--自定义数组类型
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (20);
--自定义结构类型
CREATE OR REPLACE TYPE OBJ_TRAY_EXCHANGE AS OBJECT (TRAY_LIST VARCHAR2(500), TRAY_SIZE NUMBER, TRAY_COUNT NUMBER)
--自定义结构数组
CREATE OR REPLACE TYPE TY_TRAY_EXCHANGE AS TABLE OF OBJ_TRAY_EXCHANGE
--用于交换的算法
create or replace function FUN_EXCHANGE_LIST(
p_TrayList1 varchar2,
p_CntList1 varchar2,
p_TrayList2 varchar2,
p_CntList2 varchar2,
p_Diff number,
p_ReturnList2 out varchar2,
p_RetId out Integer)
return varchar2 is
Result varchar2(500);
--cj.Lu 2011-4-6
--Jig combine核心算法
--分别用到了堆交换法及排列组合交换法.
--p_TrayList1是不大于目标数的大List,
--即从p_TrayList2任意找出一个Tray加到TrayList1上都会使用TrayList1数大于目标数;
v_Index integer;
v_TrayList1 varchar2(500);
v_TrayList2 varchar2(1500);
spltTrayNo1 ty_str_split;
spltTrayNo2 ty_str_split;
spltCnt1 ty_str_split;
spltCnt2 ty_str_split;
spltTrayEx1 ty_tray_exchange:=ty_tray_exchange();
spltTrayEx2 ty_tray_exchange:=ty_tray_exchange();
v_TraySize Integer;
i number;
j number;
ii number;
jj number;
iTotal number;
jTotal number;
v_SameSize boolean;
v_Diff Integer;
v_OldDiff Integer;
v_ReturnList varchar2(500);
v_CntList1 varchar2(500);
v_ReturnList2 varchar2(1500);
v_CntList2 varchar2(1500);
v_RetId Integer;
begin
if p_TrayList2 is null then
return null;
end if;
p_RetId:=0;
--堆交换法,速度快,但必须按Size大小排序,可多换多,即N换M
--原理:把p_TrayList1,p_TrayList2分别分成若干堆,且每一堆的数值大小相等,
--然后从p_TrayList1的第i堆的ii个元素,从p_TrayList2的第j堆的jj个元素,
--比较差值=p_Diff,则表示找到了需要的元素,进行交换.
--p_Diff=0时,可用于交换6+6+6+6=8+8+8
v_TrayList1:=replace(p_TrayList1,',,',',');
spltTrayNo1:=fun_split(v_TrayList1,',');
spltCnt1:=fun_split(p_CntList1,',');
for v_Index in 1..spltTrayNo1.Count loop
v_TraySize:=spltCnt1(v_Index);
v_SameSize:=false;
for i in 1..spltTrayEx1.Count loop
if spltTrayEx1(i).TRAY_SIZE=v_TraySize then
spltTrayEx1(i).Tray_List:=spltTrayEx1(i).Tray_List||','||spltTrayNo1(v_Index);
spltTrayEx1(i).Tray_Count:=spltTrayEx1(i).Tray_Count+1;
v_SameSize:=true;
exit;
end if;
end loop;
if v_SameSize=false then
spltTrayEx1.Extend;
spltTrayEx1(spltTrayEx1.Count):=obj_tray_exchange(spltTrayNo1(v_Index),v_TraySize,1);
end if;
end loop;
v_TrayList2:=replace(p_TrayList2,',,',',');
spltTrayNo2:=fun_split(v_TrayList2,',');
spltCnt2:=fun_split(p_CntList2,',');
for v_Index in 1..spltTrayNo2.Count loop
v_TraySize:=spltCnt2(v_Index);
v_SameSize:=false;
for i in 1..spltTrayEx2.Count loop
if spltTrayEx2(i).TRAY_SIZE=v_TraySize then
spltTrayEx2(i).Tray_List:=spltTrayEx2(i).Tray_List||','||spltTrayNo2(v_Index);
spltTrayEx2(i).Tray_Count:=spltTrayEx2(i).Tray_Count+1;
v_SameSize:=true;
exit;
end if;
end loop;
if v_SameSize=false then
spltTrayEx2.Extend;
spltTrayEx2(spltTrayEx2.Count):=obj_tray_exchange(spltTrayNo2(v_Index),v_TraySize,1);
end if;
end loop;
v_ReturnList:=v_TrayList1;
v_ReturnList2:=v_TrayList2;
for i in 1..spltTrayEx1.Count loop
for ii in 1..spltTrayEx1(i).TRAY_COUNT loop
iTotal:=ii*spltTrayEx1(i).TRAY_SIZE;
for j in 1..spltTrayEx2.Count loop
if spltTrayEx1(i).TRAY_SIZE<>spltTrayEx2(j).TRAY_SIZE then
for jj in 1..spltTrayEx2(j).TRAY_COUNT loop
jTotal:=jj*spltTrayEx2(j).TRAY_SIZE;
/*if iTotal+p_Diff-jTotal<=1 and iTotal+p_Diff-jTotal>=0 then*/
if iTotal+p_Diff-jTotal=0 then
--redim spltTrayNo2
spltTrayNo2:=fun_split(spltTrayEx2(j).Tray_List,',');
for n in 1..jj loop
v_ReturnList:=v_ReturnList||','||spltTrayNo2(n);
v_ReturnList2:=replace(v_ReturnList2,spltTrayNo2(n),'');
end loop;
--redim spltTrayNo1
spltTrayNo1:=fun_split(spltTrayEx1(i).Tray_List,',');
for n in 1..ii loop
v_ReturnList:=replace(v_ReturnList,spltTrayNo1(spltTrayEx1(i).TRAY_COUNT+1-n),'');
v_ReturnList2:=v_ReturnList2||','||spltTrayNo1(spltTrayEx1(i).TRAY_COUNT+1-n);
end loop;
v_ReturnList:=fun_formatlist(v_ReturnList,',');
v_ReturnList2:=fun_formatlist(v_ReturnList2,',');
p_ReturnList2:=v_ReturnList2;
return(v_ReturnList);
end if;
end loop;
end if;-------
end loop;
end loop;
end loop;
--排列组合交换法,速度较慢,不须按Size大小排序,只能一换一,或N换N
--p_Diff=0时,不可用于交换6+6+6+6=8+8+8
v_Diff:=p_Diff;
v_CntList1:=p_CntList1;
v_CntList2:=p_CntList2;
v_ReturnList:=p_TrayList1;
v_ReturnList2:=p_TrayList2;
loop
v_OldDiff:=v_Diff;
spltTrayNo1:=fun_split(v_ReturnList,',');
spltTrayNo2:=fun_split(v_ReturnList2,',');
spltCnt1:=fun_split(v_CntList1,',');
spltCnt2:=fun_split(v_CntList2,',');
for i in reverse 1..spltTrayNo1.Count loop --reverse 由大到小,保证先进先出
iTotal:=spltCnt1(i);
for j in 1..spltTrayNo2.Count loop
jTotal:=spltCnt2(j);
if jTotal<iTotal and v_Diff+jTotal-iTotal>=-1 then
--if jTotal<iTotal and v_Diff+jTotal-iTotal=0 then
--exchange list & cnt
v_CntList1:='';
v_ReturnList:='';
for ii in 1..spltCnt1.Count loop
if ii<>i then
if v_ReturnList is null then
v_ReturnList:=spltTrayNo1(ii);
v_CntList1:=spltCnt1(ii);
else
v_ReturnList:=v_ReturnList||','||spltTrayNo1(ii);
v_CntList1:=v_CntList1||','||spltCnt1(ii);
end if;
end if;
end loop;
v_CntList2:='';
v_ReturnList2:='';
for jj in 1..spltCnt2.Count loop
if jj<>j then
if v_ReturnList2 is null then
v_ReturnList2:=spltTrayNo2(jj);
v_CntList2:=spltCnt2(jj);
else
v_ReturnList2:=v_ReturnList2||','||spltTrayNo2(jj);
v_CntList2:=v_CntList2||','||spltCnt2(jj);
end if;
end if;
end loop;
v_ReturnList:=v_ReturnList||','||spltTrayNo2(j);
v_CntList1:=v_CntList1||','||spltCnt2(j);
v_ReturnList2:=v_ReturnList2||','||spltTrayNo1(i);
v_CntList2:=v_CntList2||','||spltCnt1(i);
v_ReturnList:=fun_formatlist(v_ReturnList,',');
v_CntList1:=fun_formatlist(v_CntList1,',');
v_ReturnList2:=fun_formatlist(v_ReturnList2,',');
v_CntList2:=fun_formatlist(v_CntList2,',');
v_Diff:=v_Diff+iTotal-jTotal;
if v_Diff=0 then
p_ReturnList2:=v_ReturnList2;
p_RetId:=0;
if length(p_TrayList2)<1200 and length(v_ReturnList2)<1200 then
select seq_exchange_log.nextval into v_RetId from dual;
p_RetId:=v_RetId;
insert into EXCHANGE_LOG(id,List1,Cnt1,List2,Cnt2,Ret_List1,Ret_Cnt1,Ret_List2,
Ret_Cnt2,Diff,Ret_Diff,Remark,Update_Time)values(v_RetId,
p_TrayList1,p_CntList1,p_TrayList2,p_CntList2,v_ReturnList,v_CntList1,
v_ReturnList2,v_CntList2,p_Diff,v_Diff,'Normal',sysdate);
end if;
return(v_ReturnList);
end if;
exit;
end if;
end loop;
if v_OldDiff<>v_Diff then
exit;
end if;
end loop;
if v_OldDiff=v_Diff then
exit;
end if;
end loop;
--Reverse exchange
spltTrayNo2:=fun_split(p_TrayList2,',');
spltCnt2:=fun_split(p_CntList2,',');
if spltTrayNo2.Count<=1 then
return '';
end if;
v_ReturnList:=p_TrayList1||','||spltTrayNo2(1);
v_CntList1:=p_CntList1||','||spltCnt2(1);
v_CntList2:='';
v_ReturnList2:='';
for jj in 2..spltCnt2.Count loop
if v_ReturnList2 is null then
v_ReturnList2:=spltTrayNo2(jj);
v_CntList2:=spltCnt2(jj);
else
v_ReturnList2:=v_ReturnList2||','||spltTrayNo2(jj);
v_CntList2:=v_CntList2||','||spltCnt2(jj);
end if;
end loop;
v_Diff:=spltCnt2(1)-p_Diff;
loop
v_OldDiff:=v_Diff;
spltTrayNo1:=fun_split(v_ReturnList,',');
spltTrayNo2:=fun_split(v_ReturnList2,',');
spltCnt1:=fun_split(v_CntList1,',');
spltCnt2:=fun_split(v_CntList2,',');
for i in reverse 1..spltTrayNo1.Count loop --reverse 由大到小,保证先进先出
iTotal:=spltCnt1(i);
for j in 1..spltTrayNo2.Count loop
jTotal:=spltCnt2(j);
if jTotal<iTotal and v_Diff+jTotal-iTotal>=-1 then
/*if jTotal<iTotal and v_Diff+jTotal-iTotal=0 then */
--exchange list & cnt
v_CntList1:='';
v_ReturnList:='';
for ii in 1..spltCnt1.Count loop
if ii<>i then
if v_ReturnList is null then
v_ReturnList:=spltTrayNo1(ii);
v_CntList1:=spltCnt1(ii);
else
v_ReturnList:=v_ReturnList||','||spltTrayNo1(ii);
v_CntList1:=v_CntList1||','||spltCnt1(ii);
end if;
end if;
end loop;
v_CntList2:='';
v_ReturnList2:='';
for jj in 1..spltCnt2.Count loop
if jj<>j then
if v_ReturnList2 is null then
v_ReturnList2:=spltTrayNo2(jj);
v_CntList2:=spltCnt2(jj);
else
v_ReturnList2:=v_ReturnList2||','||spltTrayNo2(jj);
v_CntList2:=v_CntList2||','||spltCnt2(jj);
end if;
end if;
end loop;
v_ReturnList:=v_ReturnList||','||spltTrayNo2(j);
v_CntList1:=v_CntList1||','||spltCnt2(j);
v_ReturnList2:=v_ReturnList2||','||spltTrayNo1(i);
v_CntList2:=v_CntList2||','||spltCnt1(i);
v_ReturnList:=fun_formatlist(v_ReturnList,',');
v_CntList1:=fun_formatlist(v_CntList1,',');
v_ReturnList2:=fun_formatlist(v_ReturnList2,',');
v_CntList2:=fun_formatlist(v_CntList2,',');
v_Diff:=v_Diff+jTotal-iTotal;
if v_Diff=0 then
p_ReturnList2:=v_ReturnList2;
p_RetId:=0;
if length(p_TrayList2)<1200 and length(v_ReturnList2)<1200 then
select seq_exchange_log.nextval into v_RetId from dual;
p_RetId:=v_RetId;
insert into EXCHANGE_LOG(id,List1,Cnt1,List2,Cnt2,Ret_List1,Ret_Cnt1,Ret_List2,
Ret_Cnt2,Diff,Ret_Diff,Remark,Update_Time)values(v_RetId,
p_TrayList1,p_CntList1,p_TrayList2,p_CntList2,v_ReturnList,v_CntList1,
v_ReturnList2,v_CntList2,p_Diff,v_Diff,'Reverse',sysdate);
end if;
return(v_ReturnList);
end if;
exit;
end if;
end loop;
if v_OldDiff<>v_Diff then
exit;
end if;
end loop;
if v_OldDiff=v_Diff then
exit;
end if;
end loop;
Result:='';
return(Result);
end FUN_EXCHANGE_LIST;
131
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
