How to select a random value from a collection in PL/SQL?-CodePudding

I have a collection full of data how to select a random data from there?

declare
TYPE r_emp_id IS TABLE OF NUMBER;
    emps r_emp_id;
begin
select employes_id into emps from employes order by employes_id;

for i in <(random number of employes_id from emps)> loop

DBMS_OUTPUT.put_line(employes_id (i)); 
end loop;
end;

CodePudding user response：

You want to BULK COLLECT INTO a collection and then you don't want a loop if you just want a single random value:

DECLARE
  TYPE r_emp_id IS TABLE OF NUMBER;
  emps r_emp_id;
  i    PLS_INTEGER;
BEGIN
  SELECT employes_id
  BULK COLLECT INTO emps
  FROM   employes
  ORDER BY employes_id;

  i := FLOOR(DBMS_RANDOM.VALUE(1, emps.COUNT   1));

  DBMS_OUTPUT.put_line(emps(i)); 
END;
/

If you want all the values in a random order then:

DECLARE
  TYPE r_emp_id IS TABLE OF NUMBER;
  emps r_emp_id;
BEGIN
  SELECT employes_id
  BULK COLLECT INTO emps
  FROM   employes
  ORDER BY DBMS_RANDOM.VALUE;

  FOR i IN 1 .. emps.COUNT LOOP
    DBMS_OUTPUT.put_line(emps(i)); 
  END LOOP;
END;
/

However, for a single value, you could skip the collection entirely:

DECLARE
  emp_id EMPLOYES.EMPLOYES_ID%TYPE;
BEGIN
  SELECT employes_id
  INTO   emp_id
  FROM   employes
  ORDER BY DBMS_RANDOM.VALUE
  FETCH FIRST ROW ONLY;

  DBMS_OUTPUT.put_line(emp_id); 
END;
/

Or, in SQL (and not PL/SQL):

SELECT employes_id
FROM   employes
ORDER BY DBMS_RANDOM.VALUE
FETCH FIRST ROW ONLY;

db<>fiddle here