create table test1234 ( id number); insert into test1234 values(10); insert into test1234 values(20); insert into test1234 values(30); insert into test1234 values(40); insert into test1234 values(50);
create or replace procedure fun_id(v in number, v_ex_id in number) is v_flag number := 0; v_id number := 0; v_rest number := 0; begin select count(1) into v_flag from test1234 where id = v; if v_flag > 0 then select id into v_id from test1234 where id = v; dbms_output.put_line(v_id); else select count(1) into v_flag from test1234 where id < v and id not in (v_ex_id); if v_flag > 0 then select id into v_id from (select * from test1234 where id < v and id not in (v_ex_id) order by dbms_random.value) where rownum <= 1; dbms_output.put_line(v_id); v_rest := v - v_id; fun_id(v_rest,v_id); else dbms_output.put_line('No record found'); end if; end if; end fun_id; /execute fun_id(70, 0);剔除重复id那里有点问题,没法完全去重,可以试试每次在procedure参数2传一个数组进去。 时间有限,自己看看吧
create or replace procedure fun_id(v in number, v_ex_id in number) is v_flag number := 0; v_id number := 0; v_rest number := 0; begin if v = 0 then dbms_output.put_line('done'); else select count(1) into v_flag from test1234 where id <= v and id not in (v_ex_id); if v_flag > 0 then select id into v_id from (select * from test1234 where id <= v and id not in (v_ex_id) order by dbms_random.value) where rownum <= 1; dbms_output.put_line(v_id); v_rest := v - v_id; fun_id(v_rest,v_id); else dbms_output.put_line('No record found'); end if; end if; end fun_id; /
http://topic.csdn.net/u/20090825/20/7b2c44b0-d2f5-4628-82d1-7d5b57f1ab66.html
insert into test1234 values(10);
insert into test1234 values(20);
insert into test1234 values(30);
insert into test1234 values(40);
insert into test1234 values(50);
create or replace procedure fun_id(v in number, v_ex_id in number)
is
v_flag number := 0;
v_id number := 0;
v_rest number := 0;
begin
select count(1) into v_flag from test1234 where id = v;
if v_flag > 0 then
select id into v_id from test1234 where id = v;
dbms_output.put_line(v_id);
else
select count(1) into v_flag from test1234 where id < v and id not in (v_ex_id);
if v_flag > 0 then
select id into v_id from
(select * from test1234 where id < v and id not in (v_ex_id) order by dbms_random.value)
where rownum <= 1;
dbms_output.put_line(v_id);
v_rest := v - v_id;
fun_id(v_rest,v_id);
else
dbms_output.put_line('No record found');
end if;
end if;
end fun_id;
/execute fun_id(70, 0);剔除重复id那里有点问题,没法完全去重,可以试试每次在procedure参数2传一个数组进去。
时间有限,自己看看吧
is
v_flag number := 0;
v_id number := 0;
v_rest number := 0;
begin
if v = 0 then
dbms_output.put_line('done');
else
select count(1) into v_flag from test1234 where id <= v and id not in (v_ex_id);
if v_flag > 0 then
select id into v_id from
(select * from test1234 where id <= v and id not in (v_ex_id) order by dbms_random.value)
where rownum <= 1;
dbms_output.put_line(v_id);
v_rest := v - v_id;
fun_id(v_rest,v_id);
else
dbms_output.put_line('No record found');
end if;
end if;
end fun_id;
/