背包算法+容差

--续前面的博客内容
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);
begin

SELECT *
BULK COLLECT INTO res
FROM vw_money;

--dbms_output.put_line(res.count);
lv_result := 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;
END IF;
IF lv_result = lv_target THEN
EXIT;
END IF;
end loop;

IF lv_result IS NULL THEN
dbms_output.put_line('不存在符合要求的答案');
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;