Oracle random number (reprint)

Http://blog.sina.com.cn/s/blog_6a01140c0100wimi.html

1. Randomly take records from the table

SELECT * FROM (SELECT * from 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, which is a string consisting of any printable character
The second parameter indicates the length of the returned string

4, theceil (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. Decimals within the specified range (0 ~ 100)
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 ': Uppercase
' L ', ' l ': lowercase letters
' A ', ' a ': large, lowercase letters
' 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

--Create a custom function with a delimiter (-) GUID
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 random number (reprint)