Oracle Fetch random Number

Oracle Fetch random Number

1. Randomly take records from the table

SELECT * FROM (SELECT * from the staff Order by Dbms_random.random) where RowNum < 4

To randomly fetch 3 records from the staff table

2. Generate Random Numbers

SELECT Dbms_random.          RANDOM from DUAL; produce a random number of arbitrary size

SELECT ABS (MOD (dbms_random.          random,100)) from DUAL; Generate a random number within 100

SELECT TRUNC (100+900*dbms_random.value) from dual; Generates a random number between 100~1000

SELECT Dbms_random.value from dual; Generates a random number between 0~1

SELECT Dbms_random.value (10,20) from dual; Generates a random number between 10~20

SELECT dbms_random.normal from dual; The normal function returns a set of numbers that obey a normal distribution. This normal distribution has a standard deviation of 1 and a expected value of 0. 68% of the values returned by this function are between 1 and +1, 95% between 2 and +2, and 99% between 3 and +3.

3. Generate Random string

Select Dbms_random.string (' P ', ') from dual; The first parameter P represents printable, where the string is made up of any printable character and the second parameter represents the return string length

4, the ceil (n) function returns the smallest integer greater than or equal to N.
Dbms_random. VALUE () is the number between randomly generated (0,1).
To generate a two-bit random number, you can dbms_random. VALUE () *100, this produces (0,100)
The random number, when the number between the generation (0,10), as long as the addition of 10 can guarantee that the resulting number is two.

Oracle's PL/SQL provides several ways to generate random numbers and random strings, listed below:
1, Decimals (0 ~ 1)
Select Dbms_random.value from dual
2, the specified range of decimals (0 ~) Select Dbms_random.value (0,100) from dual
3. Integers within the specified range (0 ~ 100)
Select Trunc (Dbms_random.value (0,100)) from dual
4, the length of 20 random number string
Select substr (CAST (Dbms_random.value as VARCHAR2), 3,20) from dual
5. Random number of normal distribution
Select Dbms_random.normal from dual
6. Random string
Select Dbms_random.string (opt, length) from dual
Opt values are as follows: ' U ', ' u ': Capital letter ' l ', ' l ': lowercase ' a ', ' a ': large, lowercase ' x ', ' x ': numbers, uppercase letters ' P ', ' P ': printable characters
7. Random Date
Select To_date (2454084+trunc (dbms_random. VALUE (0,365)), ' J ') from dual
Get the cardinality of the specified date by using the following statement
Select To_char (sysdate, ' J ') from dual
8. Generate GUID
Select Sys_guid () from dual
--Generate a custom function with a delimiter (-) GUID of Create or Replace function My_guid return VARCHAR2 is GUID varchar (36); Temp varchar (32);     Begin Temp:=sys_guid (); guid:= substr (temp,1,8) | | '-' | | substr (temp,9,4) | | '-' | | substr (temp,13,4) | | '-' | | substr (temp,17,4) | | '-' | |     SUBSTR (temp,21,12); return GUID; End

Oracle fetch random number