Clash Royale CLAN TAG#URR8PPP

Get random ID from variable - Oracle

FOR v_i IN 1..vt_all_executors.COUNT LOOP
IF NOT vt_executor_total_work_count.EXISTS(vt_all_executors(v_i)) THEN
RETURN vt_all_executors(v_i);
END IF;
END LOOP;


Good afternoon. Here is a piece of code that exactly selects those IDs that are needed and outputs the value (the first ones) via the RETURN function. The question is how to make it choose this ID randomly. I know the function dbms_random, but I still do not have how to apply it in the experience.

dbms_random

If necessary - here is the complete request - https://pastebin.com/EsdXAd3p

1 Answer
1

The following code returns random number between 0 and 10000000, without decimal places:

FOR v_i IN 1..vt_all_executors.COUNT LOOP
IF NOT vt_executor_total_work_count.EXISTS(vt_all_executors(v_i)) THEN
RETURN ROUND(DBMS_RANDOM.value(0, 10000000));
END IF;
END LOOP;


If you need decimal places to be included in possible outputs, remove ROUND function.
If you need some other min or max possible value, change input values to DBMS_RANDOM.value(min, max) function.

If, on the other hand, you need to return random value from one of yours' vt_all_executors values, then you will need this:

-- piece of code before the loop
SELECT MAX(rwn)
INTO v_random_max
FROM (SELECT aex.column_value AS user_id,
ROW_NUMBER() OVER (ORDER BY aex.column_value) rwn
FROM TABLE(vt_all_executors) aex );

FOR v_i IN 1..vt_all_executors.COUNT LOOP
IF NOT vt_executor_total_work_count.EXISTS(vt_all_executors(v_i)) THEN
v_random_chosen := ROUND(DBMS_RANDOM.value(1, v_random_max + 1));

SELECT user_id
INTO v_random_return
FROM (-- same inner select as before the loop, with the same order by
SELECT aex.column_value AS user_id,
ROW_NUMBER() OVER (ORDER BY aex.column_value) rwn
FROM TABLE(vt_all_executors) aex )
WHERE rwn = v_random_chosen;

RETURN v_random_return;
END IF;
END LOOP;


What I am doing in this second code example is connecting every one of your vt_all_executors values to a number. The smallest vt_all_executors value is attached to number 1, next is attached to number 2 and so on, with the largest vt_all_executors value attached to v_random_max).

Then I pick a random value between 1 (smallest vt_all_executors value) and v_random_max (largest vt_all_executors value), as I can't connect to them directly (because there are gaps between those vt_all_executors values).

Having thus the number attached to exact vt_all_executors value, I collect that exact vt_all_executors value and return it with RETURN v_random_return.

ROUND(DBMS_RANDOM.value(1, v_random_max));

vt_all_executors(v_i)

v_i

DBMS_RANDOM.VALUE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.