mardi 30 novembre 2021

How to make sure that I get all table rows when i choose random? pl/sql

I have two tables and i have to select randomly from the first table until the sum of a column in the other is null.

How can I make sure that all data from the first table are selected at least once?

create table child(name varchar2(20);

BEGIN
insert into child(name) values('A');
insert into child(name) values('B');
insert into child(name) values('C');
insert into child(name) values('D');
END;
create table toys(code varchar2(20), quantity number);

BEGIN
insert into toys(code, quantity) values('1',30);
insert into toys(code, quantity) values('1',30);
insert into toys(code, quantity) values('1',30);
insert into toys(code, quantity) values('1',30);
END;

<how to get all child.names at least once?>

create procedure toys_time
as
type r_name is table of varchar2;
l_name r_name;
type r_code is table of varchar2;
l_code r_code;
type r_quan is table of varchar2;
l_quan r_quan;
l_random_quan number;
i number;
begin
 select name bulk collect into l_name from child order by name;
 select code bulk collect into l_code from toys order by code;
 select quantity bulk collect into l_quan from toys order by code;
 select sum(quantity) into i from toys

while i>0 loop

**< get all child.names at least once>**

rws := FLOOR(DBMS_RANDOM.VALUE(1,l_code.count+1))
if l_quan(rws) > 0 then 
 l_random_quan:= FLOOR(DBMS_RANDOM.value(1,5));  
if l_random_quan < l_quan(rws) then
l_quan(rws):+ l_quan(rws) + l_random_quan;
i:=i-l_random_quan;

update toys
set quantity = l_quan(rws)
where code =l_code(rws);

else
i:=i-l_quan(rws);
l_quan(rqws):=0;

update toys
set quantity = l_quan(rws)
where code =l_code(rws);

end if;
end loop;
end;



Aucun commentaire:

Enregistrer un commentaire