How ORACLE produces a random number

how ORACLE produces a random number :D bms_random

1, decimals (0 ~ 1)

Select Dbms_random.value from dual;

2. Decimal number within the specified range (0 ~ +)

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

3. Integers within the specified range (0 ~ +)

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

4, the length of the random number string

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

5. Random number of 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 letters

' 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. Generate GUID ( level )

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;

generate a random number of 0- millions, and a little bit of a workaround is possible:

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;

Produce 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 1and a expected value of 0.

The value returned by this function is 68% between 1 and +1 ,95% between 2 between +2 and99% between 3 and +3 between.

Finally, it is The STRING function. It returns a Random string of up to four characters in length.

with Dbms_random Generating text and date values

Numbers, text strings, and dates are the three common data types that users will encounter in a table.

Although you can use The dbms_random in the PL/SQL package randomly generates numbers--it does do this--it can also randomly generate text and date values.

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 , and the precision is the sum of the bits.

SELECT Dbms_random. VALUE from DUAL;

for integers in the specified range, add the parameters Low_value and high_valueandintercept the decimals from the result (the maximum value cannot be used as a possible value).

so for An integer between 0 and one, you'll use the following code:

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

2. generating random text strings

to randomly generate a text string, you will use the String function and write code to specify the type of string and the desired length:

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

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

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 this date is 4712 BC 1 months 1 The integer offset of the day) to be saved.

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.

Use 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 for a 2003 year, you can first determine the integer of the date 2003 1 months 1 days;

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 low_value equals 2452641 and high_value equals The dbms_random of the 2452641+364 parameter . VALUE,

and convert it to a 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

Rows selected. List A put the above code together.

it creates a sample table called Random_stuff, which has three columns of data:ID,date1 , and Text1 .

It then uses the combined ROWNUM and dbms_random generate data, inserting a row of data into it.

you can make subqueries with any table with at least four rows of data, because the data columns in the real table are not in the SELECT list.

Note: The contents of the essay are from the online data collation, for reference only.

How ORACLE produces a random number