I have an associative array 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