从一个数组中找出几个数,使其相加等于某个值的算法(数组元素可以重复)

--自定义数组类型

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;

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值