Oracle dbms_random function Usage quickly generates multiple test data

People doing database development or management often want to create a large number of test data, there will be tens of thousands of things, if one entry, it will waste a lot of time, this article describes how Oracle in a SQL to quickly generate a large number of test data method.

First, simulate 100 random data.

Select RowNum as ID,        to_char (sysdate + rownum/24/3600, ' yyyy-mm-dd hh24:mi:ss ') as Inc_datetime,        trunc (Dbms_ra Ndom.value (0, +)) as random_id,        dbms_random.string (' $ ') random_string from   dual connect by level <= 100;

The above code is not inserted into the database, to insert only need to add the CREATE table table to the SQL as or INSERT into select mode

The above SQL is implemented using several practical tips for Oracle database syntax:
1, using Oracle's unique "Connect by" tree connection syntax to generate test records, "level <= 10" means to generate 10 records;
2, using rownum virtual column to generate incremental integer data;
3, using the Sysdate function plus some simple operations to generate date data, in this case, the time of each record plus 1 seconds;
4, using the Dbms_random.value function to generate random numerical data, in this case, a random integer from 0 to 100 is generated;
5, the use of the dbms_random.string function to generate random character data, in this case, the generation of a random string length of 20, the string can include characters or numbers.

Dbms_random. Parameters in the function description

--**************************************** Random digital ***************************************************select decode (  Trunc (Dbms_random.value (0, 2)), ' 0 ', ' female ', ' 1 ', ' Male ') from Dualselect length (dbms_random.random) from dual;  -Generates a digital select ABS (mod (DBMS_RANDOM.RANDOM,100)) from dual with any length of 9,10,11 bit;  -Generates a random number within 100 select Trunc (10+90*dbms_random.value) from dual;   -Generates any 2-bit random integer between 10~100 (contains 10, does not contain a) select Dbms_random.value from dual;   --Produces a decimal number (37 or 38 bits after the decimal point) that is greater than 0 or less than 1, select Dbms_random.value (10,20) from dual;  --Produce a decimal number (38 digits after the decimal point) that is greater than 10 and less than 20, select Dbms_random.normal from dual; --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. --**************************************** Random date ***************************************************select to_date  (Trunc (Dbms_random.value (To_number (To_char (sysdate-10, ' J ')), To_number (To_char (sysdate, ' J ')))), ' J ') from dual; --Take a random date in the last 10 days Select (Sysdate-1/24/60 *) + dbms_random.value (1, 1800)/3600/24 from Dual --Take random time in the last 30 minutes select To_date (To_char (to_date (' 2015-01-01 ', ' yyyy-mm-dd '), ' J ') + trunc (dbms_random.   VALUE (0, 365)), ' J ') from dual; --Query for any date in 2015--take a random time within a specified date select To_date (Trunc (Dbms_random.value (To_number (To_char (' 20150501 ', '                    YYYYMMDD '), ' J '), To_number (To_char (to_date (' 20150531 ', ' YYYYMMDD ') + 1, ' J ')))       ), ' J ') + dbms_random.value (1, 3600)/3600 prize_time from dual; At any time between now and the last 100 years Select To_date (trunc (Dbms_random.value (To_char (add_months -100 *), ' J '), To_number (To_char (sysdate + 1, ' J ')))), ' J ') + Dbms_random.val UE (1, 3600)/3600 from dual;--**************************************** random character (produces a string of any length of 20) ************************* Select Dbms_random.string (' U ', a) from dual--' u ', ' u ' Returns all uppercase strings (uppercase) calculated found, As long as the parameter is not a Chinese or full-width character, the returned data is uppercase Select Dbms_random.string (' L ',From dual--' l ', ' l '-returns all lowercase strings (lowercase) Select dbms_random.string (' A ', ' a ') from dual--' a ', ' a '-returns a case-bound string ( Mixed case) Select Dbms_random.string (' x ', s) from dual--' x ', ' X '-Returns all uppercase and numeric strings (uppercase,alpha&numeric) Select DB Ms_random.string (' P ', ') from dual--' P ', ' p '-Returns a random combination of characters appearing on the keyboard (any printable char)

Oracle dbms_random function Usage quickly generates multiple test data