ORACLE generates a random number

How ORACLE produces a random number: Dbms_random
--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, integer within the specified range (0 ~ 100)

Select Trunc (Dbms_random.value (0,100)) from dual;

--4, a random number string of length 20

Select substr (CAST (Dbms_random.value as VARCHAR2), 0,10) from dual;

Random number of--5 and normal distribution

Select Dbms_random.normal from dual;

--6, random string

Select Dbms_random.string (' x ', 3) 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;

/* Obtain the cardinality of the specified date by using the following statement */

Select To_char (sysdate, ' J ') from dual;

--8, generating GUIDs

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

1. Preparatory work

Login Sqlplus as Sys
Then run the script
$ORACLE _home/rdbms/admin/dbmsrand.sql

2. Application examples

SELECT Dbms_random. RANDOM from DUAL;

Further requirements, such as generating a random number of 0-100, are slightly more flexible:

Select ABS (mod (DBMS_RANDOM.RANDOM,100)) from dual

3. Advanced Instructions

Dbms_random also has a new function to implement these functions
FUNCTION value RETURN number;
FUNCTION value (low in number, high in number) RETURN number;
FUNCTION normal RETURN number;
FUNCTION string (opt char, len number) RETURN VARCHAR2;

Generates a random number between N and M
SELECT Dbms_random. VALUE (n,m) from DUAL;

Default Dbms_random. Value returns a random number from 0 to 1

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.
Finally, it is a string function. It returns a random string of up to 60 characters in length.

Generate text and date values with Dbms_random

Numbers, text strings, and dates are the three common data types that users will encounter in a table. While you can randomly generate numbers with dbms_random in the PL/SQL Package--it does--it can also generate text and date values randomly.

1. Generate Random Numbers
Let's start with the numbers first. The value function returns a number greater than or equal to 0 but less than 1, with a precision of 38 bits.

SELECT Dbms_random. VALUE from DUAL;

For integers in the specified range, add the parameters Low_value and High_value and intercept the decimals from the result (the maximum value cannot be used as a possible value). So for integers between 0 and 99, you would use the following code:

SELECT TRUNC (dbms_random. VALUE (0, +)) from DUAL;

2. Generating random text strings
To randomly generate a text string, use the string function and write code to specify the type of string and the desired length:

SELECT Dbms_random. STRING (' A ', ') from DUAL;

The type code is described in the Oracle Database 10g PL/SQL package and type reference (Oracle database 10g PL/SQL Packages and Types Reference).

Here are some types of code:

' U ' to create uppercase characters

' L ' is used to generate lowercase characters

' A ' is used to generate mixed-case characters

3. Generate a random date
Oracle takes the date as a key date in the past (if you're curious, I can tell you that this date is January 1, 4712 BC) and the integer offset to save. This means that you can randomly generate a date within a specified range by looking for an integer corresponding to the start date you want, and then adding a random integer to it.

Using the To_char function and the ' J ' format code, you can generate an internal date number for today's date:

SELECT to_char (sysdate, ' J ') from DUAL;

For example, to generate an arbitrary date within 2003 years, you can first determine the date integer of January 1, 2003;

SELECT To_char (to_date (' 01/01/03 ', ' mm/dd/yy '), ' J ') from DUAL;

The result of the system is 2452641. So to generate any date in that year, we're going to use a dbms_random with Low_value equals 2452641 and High_value equals the 2452641+364 parameter. VALUE, then convert it to date:

SELECT to_date (TRUNC (dbms_random. VALUE (2452641,2452641+364)), ' J ') from DUAL;

List A:

Sql> CREATE TABLE Random_stuff (
2 ID number,
3 Date1 DATE,
4 Text1 VARCHAR2 (40)
5);

Table created.

Sql> INSERT into Random_stuff
2 SELECT
3 ROWNUM,
4 To_date (TRUNC (dbms_random. VALUE (2452641,2452641+364)), ' J '),
5 Dbms_random. STRING (' A ', TRUNC (dbms_random). VALUE (6,41)))
6 from User_objects
7 WHERE ROWNUM < 101;

Rows created.

Sql> commit;

Commit complete.

Sql> SELECT *
2 from Random_stuff
3 WHERE ROWNUM < 11;

ID DATE1 TEXT1
---------- --------- ----------------------------------------
1 21-jan-03 GAWQDHHSLBNU
2 28-sep-03 Cessyttblabklmgznshj
3 22-nov-03 nrnsgzcnity
4 05-feb-03 Wcrfojhkic
5 10-sep-03 Vlslicanqxzsbhbvkfignemoxarr
6 13-jun-03 Unylsifovkvezxjtbyopqvgwvslkdfvvapir
7 02-jan-03 Wxffjhdxxn
8 03-oct-03 Qimepqgkvvnbjvzdwzegqgeyxusekje
9 14-jul-03 Pjlmgzennifsejfgidnurkncfizzlmolxmvc
Ten 24-dec-03 JNNLJLNDDCV

Selected. List A sets the above code together. It creates a sample table called Random_stuff, which has three data columns: ID, Date1, and Text1. It then uses rownum and dbms_random to generate data, inserting 100 rows of data inside. You can make subqueries with any table with at least 100 rows of data, because the data columns in the real table are not in the select list.

ORACLE generates a random number