DBMS_RANDOM package usage in Oracle

Source: Internet
Author: User
Dbms_random is a package that can generate random values or strings. This package includes initialize (), seed (), terminate (), value (), normal (), rando

Dbms_random is a package that can generate random values or strings. This package includes initialize (), seed (), terminate (), value (), normal (), rando

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 common. The following describes the usage of each function in sequence:

1. dbms_random.value Method

First: FUNCTION value return number;

Note:

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.

Example:

BEGIN
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (round (dbms_random.value * 100 ));
End loop;
END;

Note:

Example:

BEGIN
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (trunc (dbms_random.value (1,101 )));
End loop;
END;

Result:

2. dbms_random.string Method

Note:

Parameters:

Opt:

■ '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

Len:

Length of the returned string.

Example:

SELECT dbms_random.string ('P', 8) FROM dual;

Result:

Note:

Example:

SELECT dbms_random.random FROM dual;

Result:

Note: What is the difference between 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. value Returns the number type, and the returned value is between 1 and 0, while random returns the BINARY_INTEGER type (a number stored in binary format, it is said that the calculation 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)

4. dbms_random.normal Method

Note:

Example:

BEGIN
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (round (dbms_random.normal ));
End loop;
END;

Result:

Note:

Used to generate a random number seed. The seed is set to generate a random number for debugging. Otherwise, it is difficult to schedule different tasks.

Example:

Result (no matter how many times it is executed ):

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.