背包算法

以下两个算法均是为了解决我前篇博客上的那个问题的高级方法
但效率有待进一步提高

create table t_money(id int primary key,amount int not null);

insert into t_money values(1,2);
insert into t_money values(2,2);
insert into t_money values(3,3);
insert into t_money values(4,5);
insert into t_money values(5,2);
insert into t_money values(6,8);
insert into t_money values(7,1);
insert into t_money values(8,2);
insert into t_money values(9,2);
insert into t_money values(10,3);

select * from t_money;

+----+--------+
| id | amount |
+----+--------+


1 2
2 2
3 3
4 5
5 2
6 8
7 1
8 2
9 2
10 3

--按位运算
declare
type test_t is table of t_money%rowtype index by binary_integer;
res test_t;
l_row binary_integer:=1;
sums number;
cal varchar2(1000);
seq varchar2(1000);

begin

SELECT *
BULK COLLECT INTO res
FROM t_money;

--dbms_output.put_line(res.count);
for i in res.first .. 2**res.last-1 loop
sums:=0;
seq:='';
cal:='';
for j in res.first .. i loop -- 不会比i更大
if (bitand(i,2**(j-1)) <> 0) then -- 如果不改为j-1, 当总额为31(全选)时解不出来
sums:=sums+res(j).amount;
cal:=cal||res(j).amount||'+';
seq:=seq||res(j).id||',';
IF SUMS >= 10 THEN ---- 尽早退出
EXIT;
END IF;
end if;
end loop;
if (sums=10) then
cal:=substr(cal,1,length(cal)-1);
seq:=substr(seq,1,length(seq)-1);
dbms_output.put_line('结果是:'||cal||'='||sums);
dbms_output.put_line('序列是:'||seq);
exit;
end if;
end loop;
end;


--分支
select path
from (select rownum rn,
hierarchy.sys_connect_by_path(level, id, ',') path,
hierarchy.sys_sum_by_path(level, amount) amount
from t_money
connect by id > prior id
AND hierarchy.sys_sum_by_path(level, amount)<=10 --- 中途裁剪
)
where amount = 10;

----
create or replace package hierarchy is
type strtabletype1 is table of varchar2(4000) index by binary_integer;
type strtabletype is table of strtabletype1 index by binary_integer;

strtable strtabletype;

type numtabletype1 is table of number index by binary_integer;
type numtabletype is table of numtabletype1 index by binary_integer;

numtable numtabletype;
function sys_connect_by_path(p_level in number,
p_value in varchar2,
p_delimiter in varchar2 default ',',
p_index IN NUMBER DEFAULT 1)
return varchar2;
function sys_sum_by_path(p_level in number, p_value in number,p_index IN NUMBER DEFAULT 1)
return number;
pragma restrict_references(sys_connect_by_path, wnds);
pragma restrict_references(sys_sum_by_path, wnds);
end;

---

create or replace package body hierarchy is
ls_ret varchar2(4000);
ln_ret number;
function sys_connect_by_path(p_level in number,
p_value in varchar2,
p_delimiter in varchar2 default ',',
p_index IN NUMBER DEFAULT 1)
return varchar2 is
begin
strtable(p_index)(p_level) := p_value;
ls_ret := p_value;
for i in reverse 1 .. p_level - 1 loop
ls_ret := strtable(p_index)(i) || p_delimiter || ls_ret;
end loop;
return ls_ret;
end;

function sys_sum_by_path(p_level in number, p_value in number,p_index IN NUMBER DEFAULT 1)
return number is
begin
numtable(p_index)(p_level) := p_value;
ln_ret := p_value;
for i in reverse 1 .. p_level - 1 loop
ln_ret := numtable(p_index)(i) + ln_ret;
end loop;
return ln_ret;
end;
end;