背包算法+拆分

--这个算法得以实现，主要得到 newkid 和 nyfor 两位大哥的帮助
declare
   type test_t is table of vw_money%rowtype index by binary_integer;
   res test_t;
   l_row binary_integer:=1;
   sums number;
   cal varchar2(1000);
   seq varchar2(1000);
   j NUMBER;
   lv_result NUMBER;
  
   lv_target NUMBER := 10;     --- 目标
   lv_diff   NUMBER := 1;      --- 允许误差
  
   lv_cal VARCHAR2(1000);
   lv_seq VARCHAR2(1000);
  
   lv_close NUMBER;
  
begin

  SELECT *
  BULK COLLECT INTO res
  FROM vw_money;
 
  --dbms_output.put_line(res.count);
  lv_result := NULL;
  lv_close := NULL;
  for i in res.first .. 2**res.last-1 loop
      sums:=0;
      seq:='';
      cal:='';
      j:=1;
      WHILE j<=res.count AND res(j).pow<=i LOOP
       if (bitand(i,res(j).pow) <> 0) then  -- 2的幂事先算好了
         sums:=sums+res(j).amount;
         cal:=cal||res(j).amount||'+';
         seq:=seq||res(j).id||',';  
         IF sums - lv_target >= ABS(lv_result - lv_target) OR sums - lv_target>lv_diff THEN        ---- 已经有更接近的答案或误差超出范围
            EXIT;
         END IF;
       end if;
       j:=j+1;
      end loop;

      IF ABS(sums - lv_target)<=lv_diff AND (lv_result IS NULL OR ABS(sums - lv_target) < ABS(lv_result - lv_target)) THEN
         lv_result := sums;
         lv_cal := cal;
         lv_seq := seq;
      ELSIF lv_result IS NULL AND sums - lv_target>lv_diff AND (lv_close IS NULL OR sums<lv_close) THEN
         lv_close := sums;
         lv_cal := cal;
         lv_seq := seq;
      END IF;

      IF lv_result = lv_target THEN
         EXIT;
      END IF;

  end loop;

  IF lv_result IS NULL THEN
     IF lv_close IS NULL THEN
        dbms_output.put_line('不存在符合要求的答案');
     ELSE
        lv_cal:=substr(lv_cal,1,length(lv_cal)-1)||'-'||(lv_close-lv_target);
        lv_seq:=substr(lv_seq,1,length(lv_seq)-1);
        dbms_output.put_line('结果是:'||lv_cal||'='||lv_target);
        dbms_output.put_line('序列是:'||lv_seq);

     END IF;
  ELSE
     lv_cal:=substr(lv_cal,1,length(lv_cal)-1);
     lv_seq:=substr(lv_seq,1,length(lv_seq)-1);
     dbms_output.put_line('结果是:'||lv_cal||'='||lv_result);
     dbms_output.put_line('序列是:'||lv_seq);
  END IF;
 
end;