在去年一个实验性项目中有这样一个问题,饭店根据顾客消费意愿进行菜单自动生成,如:699元套餐、899元套餐等。菜品类型有凉菜、热菜、蒸菜等等,具体需求不详细描述。关键是一个根据消费金额自动生成菜单的算法。可惜当时的代码找不到了。
今天在论坛中又遇到一个类似问题,正好可用以回顾。如下:
表结构:
SQL> create table t_money(id int primary key,amount int not null);
Table created
Executed in 0.468 seconds
插入数据:
Code
SQL> insert into t_money values(1,2);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(2,2);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(3,3);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(4,5);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(5,2);
1 row inserted
Executed in 0.016 seconds
SQL> insert into t_money values(6,8);
1 row inserted
Executed in 0.016 seconds
SQL> insert into t_money values(7,1);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(8,2);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(9,3);
1 row inserted
Executed in 0 seconds
SQL> insert into t_money values(10,3);
1 row inserted
Executed in 0 seconds
Code
SQL> select substr(id,1,3) id,substr(amount,1,3) amount from t_money;
ID AMOUNT
------ ------
1 2
2 2
3 3
4 5
5 2
6 8
7 1
8 2
9 3
10 3
10 rows selected
Executed in 0.156 seconds
问题是:
从表中选取若干条数据,使得被选出来的几条数据的amount(金额)字段之和等于10,要求存储过程能返回被选取出来的数据的序列号。 举例:2+3+5=10 返回序列号:1,3,4
存储过程代码:
Code
create or replace procedure P_test(O_str out varchar2)exp user1/pwd@server owner=user1 file=c:\file.dmp
imp user2/pwd@server fromuser=user1 touser=user2 file=c:\file.dmp
as
v_str varchar2(20):=',';
v_id number;
v_count number;
v_tmp number :=0;
v_money number :=0;
begin
select count(0) into v_count from t_money;
loop
select count(0) into v_count from t_money where not instr(v_str,',' || id || ',')>0 and amount <= 10-v_money;
if v_count = 0 then
exit;
-- or restart
end if;
select id,amount into v_id,v_tmp from
(select id,amount from t_money where not instr(v_str,',' || id || ',')>0 and amount <= 10-v_money order by dbms_random.value)
where rownum <2;
v_money := v_money + v_tmp;
v_str := v_str || v_id ||',';
exit when v_money =10;
end loop;
O_str := v_str;
-- Error
end;
结果:,7,9,10,3,