How Does Oracle Randomly Read values from a specific combination?

How Does Oracle Randomly Read values from a specific combination?

Here, we will use the DBMS_RANDOM package and the case when statement as follows:

1. Use the DBMS_RANDOM.RANDOM function to randomly generate a value and then modulo the value. If we want to randomly read 10 elements, we need to modulo 10.

2. Associate the modulo value with the element using the case when statement.

For example, I have a combination of five elements: "Beijing", "Shanghai", "Guangzhou", "Shenzhen", and "Wuhan, you want to randomly read values from these five elements to fill a field in the table.

First, create a test table.

SQL> create table test (loc varchar2 (2 char ));

Table created.

Construct an SQL statement that can randomly read Elements

 

Select case mod (abs (dbms_random.random), 5)
When 1 then 'beijing'
When 2 then 'shanghai'
When 3 then 'guangzhou'
When 4 then 'shenzhen'
Else 'wuhan 'end "LOC"
From dual;

 

Fill in the loc field of the test table in large batches

 

Begin
For I in 1 .. 10 loop
Insert into test values (case mod (abs (dbms_random.random), 5)
When 1 then 'beijing'
When 2 then 'shanghai'
When 3 then 'guangzhou'
When 4 then 'shenzhen'
Else 'wuhan 'end );
End loop;
End;

 

The final result is as follows:

 

SQL> select * from test;

LOC
--------
Wuhan
Guangzhou
Shanghai
Beijing
Shanghai
Wuhan
Beijing
Shanghai
Wuhan
Shenzhen

10 rows selected.