我有一個充滿資料的集合如何從那里選擇隨機資料？
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;
uj5u.com熱心網友回復：
您想要BULK COLLECT INTO一個集合，然后如果您只想要一個隨機值，則不需要回圈：
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;
/
如果您想要隨機順序的所有值，則：
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;
/
但是，對于單個值，您可以完全跳過集合：
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;
/
或者，在 SQL（而不是 PL/SQL）中：
SELECT employes_id
FROM employes
ORDER BY DBMS_RANDOM.VALUE
FETCH FIRST ROW ONLY;
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/366521.html