Oracle Random Number

Oracle Random Number 1. select * from (select * from staff order by dbms_random.random) where rownum <4 indicates to randomly retrieve three records FROM the STAFF table. 2. Generate a random number SELECT DBMS_RANDOM.RANDOM from dual. Generate a random number select abs (MOD (DBMS_RANDOM.RANDOM, 100 )) from dual; generate a random number less than 100 select trunc (100 + 900 * dbms_random.value) FROM dual; generate a 100 ~ SELECT dbms_random.value FROM dual; to generate a random number between 1000 and ~ SELECT dbms_random.value () FROM dual; a random number between 10 and 20 is generated ~ SELECT dbms_random.normal FROM dual; the NORMAL function returns a group of numbers that follow the NORMAL distribution. The standard deviation of this normal distribution is 1, and the expected value is 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 a random string select dbms_random.string ('P', 20) from dual; the first parameter P represents printable, that is, a string consists of any printable characters. The second parameter indicates that the returned string length is 4. The ceil (n) function returns the smallest integer greater than or equal to n. DBMS_RANDOM.VALUE () is the number between random generation (0, 1. To generate two random numbers, DBMS_RANDOM.VALUE () * 100 is used to generate a random number (0,100). When the number between () is generated, by adding 10, we can ensure that all the numbers generated are two. ORACLE's PL/SQL provides multiple methods to generate random numbers and random strings, which are listed as follows: 1. Decimal (0 ~ 1) select dbms_random.value from dual 2. Decimals in the specified range (0 ~ 100) select dbms_random.value (0,100) from dual 3, an integer in the specified range (0 ~ 100) select trunc (dbms_random.value (0,100) from dual 4, a random number string of 20 select substr (cast (dbms_random.value as varchar2 (38) from dual 5, normal distribution of random numbers select dbms_random.normal from dual 6, random string select dbms_random.string (opt, length) from dual opt values: 'U', 'U ': uppercase letters 'l', 'L': lowercase letters 'A', 'A': uppercase letters 'x', 'x': Numbers, uppercase letters 'P ', 'P': printable character 7, random date select to_date (2454084 + TRUNC (DBMS_RANDOM.VALUE (0,365), 'J ') from dual obtain the base number of the specified date using the following statement: select to_char (sysdate, 'J') from dual 8, generate GUID select sys_guid () from dual -- generate a separator (-) create or replace function my_guidreturn varchar2is GUID varchar (36); temp varchar (32); begin temp: = sys_guid (); guid: = substr (temp,) | '-' | substr (temp, 9,4) | '-' | substr (temp,) | '-' | substr (temp, 17,4) | '-' | substr (temp, 21,12); return guid; end;