Used for sampling statistics. Users are randomly selected from the database by category.
Used for sampling statistics. Users are randomly selected from the database by category.
Use the dbms_random package of oracle combined with rownum. For example, 499 users are randomly selected:
Select * from
(Select * from busi. t_ar_userinfo order by dbms_random.value)
Where rownum: <500;
References for dbms_random, link: http://www.psoug.org/reference/dbms_random.html
Deprecated. Use the methods in the DBMS_CRYPTO built-in package, which is no longer recommended
Appendix: Descriptions of several parameters of dbms_random:
Function value return number, returns a random number between 0 and 1. The precision is 38 BITs (Gets a random number, greater than or equal to 0 and less 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 numbers in a standard normal distribution. return the number of groups that follow the normal distribution. The standard deviation is 1 and the expected value is 0, in the returned value, 68% is between + 1 and-1, 95% is between + 2 and-2, and 99% is between + 3 and-3.
Function random return BINARY_INTEGER, (Generate Random Numeric Values ),
Function string (opt char, length Number) return varchar2 (the maximum is 60), returns a string of the specified length (Create Random Strings), opt seed values:
'A', 'A' & n
Q: My questions at work: the supervisor asked me to randomly retrieve qualified EMAIL or mobile phone number users for an activity to issue award notifications or other messages, how does one implement Oracle 9i in our company?
A: You can use PL/SQL to generate random numbers in oracle. The directory name is/ORACLE_HOME/rdbms/admin/dbmsrand. SQL.
Before using it, compile it under the sys User:
SQL> @/ORACLE_HOME/rdbms/admin/dbmsrand. SQL
It actually generates a dbms_random package under the sys user, generates public synonyms at the same time, and authorizes all database users with the execution permission.
Use the dbms_random package to retrieve random data:
1. First create a sequence number tmp_id with a unique Growth
Create sequence tmp_id increment by 1 start with 1 maxvalue 9999999 nocycle nocache;
2. Create a temporary table tmp_1 to retrieve all records that meet the conditions of this activity.
Create table tmp_1 as select tmp_id.nextval as id, email, mobileno from table name where condition;
Find the maximum ID:
Select max (id) from tmp_1;
Assume 5000
3. Set a seed to generate a random number.
Execute dbms_random.seed (12345678 );
Or
Execute dbms_random.seed (TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24: MI: ss '));
4. Call the random number generation function dbms_random.value to generate a temporary table tmp_2
Assume that 200 are randomly selected.
Create table tmp_2 as select trunc (dbms_random.value (201) as id from tmp_1 where rownum <;
[Note: dbms_random.value (5000) is a random number ranging from 1 to with decimal places,
The trunc function is used to integer the value of a random number to correspond to the integer ID field of a temporary table.
NOTE: If tmp_1 has many records (more than 0.1 million records), you can also find a table with more than two hundred rows (for example, tmp_3) to generate tmp_2.
Create table tmp_2 as select trunc (dbms_random.value (201) as id from tmp_3 where rownum <;]
5. tmp_1 and tmp_2 are associated to obtain the qualified 200 users
Select t1.mobileno, t1.email from tmp_1 t1, tmp_2 t2 where t1.id = t2.id;
[NOTE: If tmp_1 has many records (more than 0.1 million records), you must create an index on 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 the temporary tables are used up, delete tmp_1, tmp_2, and tmp_id.
