Oracle random number

Using oracle's dbms_random package combined with rownum to achieve, the example is as follows:
select * from
( select * from busi.t_ar_userinfo order by dbms_random.value)
where rownum < 500;

About dbms_random references, and links for: http: / / www psoug. org/reference/dbms_random. html

Deprecated. Use methods in the DBMS_CRYPTO ES33en-ES34en package. This package is no longer recommended

Attached, the introduction of several parameters of dbms_random:
function value return number, returns a random number between [0, 1), with a precision of 38 bits (Gets a random number, greater than equal 0 and than 1, with decimal 38 digits)
function value (low IN NUMVBER, high IN NUMBER) return number, returns a random number between [low, high]
function normal return number, return random in a standard distribution returns the number of groups that are normally distributed, the standard deviation is 1, the expected value is 0, the return value is 68% between +1 and -1, 95% between +2 and -2, and 99% between +3 and -3.
function random BINARY_INTEGER, (Generate Random Numeric Values),
function string (opt char, length Number) return varchar2 (the maximum is 60), returns 1 string of specified length (Create Random Strings), opt seed values:
'a','A' & n

Q: My problem at work: My supervisor asked me to randomly pick out 1 eligible EMAIL or mobile phone number users for an activity to issue award notification or other messages. How to achieve this?
Answer: can use oracle PL/SQL, generating random Numbers in the directory file name in: / ORACLE_HOME/rdbms/admin/dbmsrand sql.
Compile under sys user before use:
SQL > @/ORACLE_HOME/rdbms/admin/dbmsrand.sql
It actually generates an dbms_random package under the sys user, generates public synonyms, and authorizes all database users to execute.
Using the dbms_random package, extract random data method:
1. Create a sequence number tmp_id that grows only by 1
create tmp_id by 1 start with 1 maxvalue 9999999 nocycle nocache;
2. Then create a temporary table tmp_1 and pull out all the records that meet the conditions of this activity.
create tmp_1 select tmp_id nextval id,email from from
Find the largest id number:
select max (id) from tmp_1;
Assuming that is 5000
3. Set a seed to generate random Numbers
execute dbms_random. seed (12345678);
or
execute dbms_random. seed (TO_CHAR (SYSDATE, 'MM DD - YYYY HH24: MI: SS'));
4. Call the random number generator dbms_ES210en.value to generate the temporary table tmp_2
Let's say I pick 200 at random
create table tmp_2 as select (dbms_random.value(1,5000)) as id tmp_1 where rownum < 201;
[Note: dbms_ES234en. value(1500) is a random number between 1 and 5000, there will be a decimal,
The trunc function rounds off random Numbers to correspond to the ID field of the integer in the temporary table.
Note: If tmp_1 has a large number of records (more than 100,000), you can also find a table with about 200 rows (say, tmp_3) to generate tmp_2
create tmp_2 select trunc(dbms_random.value(1500) < 201; ]
5. tmp_1 and tmp_2 are associated to obtain 200 eligible users
select t1 mobileno, t1. email from tmp_1 t1, tmp_2 t2 where t1. id = t2. id;
[Note: If tmp_1 has a large number of records (more than 100,000), you need to index the id field.]
It can also be output to a text file:
set pagesize 300;
spool/tmp / 200. txt;
select t1 mobileno, t1. email from tmp_1 t1, tmp_2 t2 where t1. id = t2. id order by t1. mobileno;
spool off;
6. After use, delete the temporary table tmp_1, tmp_2 and the serial number tmp_id.