DBMS_RANDOM package usage in ORACLE

DBMS_RANDOM package usage in ORACLE 1. dbms_random.value method dbms_random is a package that can generate random values or strings. This package includes several functions, including initialize (), seed (), terminate (), value (), normal (), random (), and string (), but value () is the most commonly used, value () is generally used in two ways, the first www.2cto.com function value return number;
If there is no parameter in this usage, a 38-bit precision value is returned, ranging from 0.0 to 1.0, but not 1.0, as shown in the following example: SQL> set serverout on SQL> begin 2 for I in 1 .. 10 loop 3 dbms_output.put_line (round (dbms_random.value * 100); 4 end loop; 5 end; www.2cto.com 6/46 19 45 37 33 57 61 20 82 8 PL/SQL process completed successfully. SQL> the second type of value has two parameters: the first parameter indicates the lower limit and the second parameter indicates the upper limit. A number between the lower limit and the upper limit is generated, but the upper limit is not included, "There is no end to learning" is the second type, as follows: SQL> begin 2 for I in 1 .. 10 loop 3 dbms_output.put_line (trunc (dbms_random.value (1,101); 4 end loop; 5 end; www.2cto.com 6/97 77 13 86 68 16 55 36 54 46 PL/SQL process completed successfully. 2. dbms_random.string method www.2cto.com some user management programs may need to create random passwords for users. You can use dbms_random.string of 10 Gb to implement this function. For example: SQL> select dbms_random.string ('P', 8) from dual; DBMS_RANDOM.STRING ('P', 8) Meaning of the first parameter 3q <M "yf [www.2cto.com: 'U', 'U'-returning string in uppercase alpha characters 'l', 'l'-returning string in lowercase alpha characters 'A ', 'A'-returning string in mixed case alpha characters 'x', 'X'-returning string in upp Ercase alpha-numericcharacters 'P', 'P'-returning string in any printable characters. otherwise the returning string is in uppercase alphacharacters. P indicates printable, that is, the string is composed of any printable characters, and the second parameter indicates the length of the returned string.
3. the dbms_random.random method returns the BINARY_INTEGER type value and generates a random number of any size. For example, Order By dbms_random.value. This statement is used to achieve random sorting of records. In addition: what is the difference between www.2cto.com dbms_random.value and dbms_random.random? 1. Order By dbms_random.value: Calculate a random number for each row in the result set. dbms_random.value is a column in the result set (although this column is not in the select list), and then sort By this column, the order is random.
2. Check the desc information to see the difference between the vlue and random functions. value Returns the number type, tb, and the returned value is between 1 and 0, random returns the BINARY_INTEGER type (a number stored in binary format. It is said that the computing efficiency is higher than the number, but I have not tested it, but the value range must be smaller than the number. For details, check the information) if you want to implement random sorting, use the value function. 4. the dbms_random.normal method www.2cto.com 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. 5. The dbms_random.send method is used to generate a random number seed. The purpose of setting the seed is to generate a random number repeatedly for debugging. Otherwise, it is difficult to schedule different tasks.