Use the Oracle dbms_random function to quickly generate multiple test data entries.

Use the Oracle dbms_random function to quickly generate multiple test data entries.

Database development or management personnel often need to create a large amount of test data, and tens of thousands of data records are required. If one data entry is required, it will waste a lot of time, this article describes how to quickly generate a large amount of test data using an SQL statement in Oracle.

First, simulate 100 random data records.

 select rownum as id,        to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,        trunc(dbms_random.value(0, 100)) as random_id,        dbms_random.string('$', 20) random_string   from dual connect by level <= 100;

The above code is not inserted into the database. To insert data, you only need to add the create table as or insert into select method to the SQL statement.

The preceding SQL statements are implemented using several practical tips in Oracle Database Syntax:
1. test records are generated using the connect by tree connection syntax unique to Oracle. level <= 10 indicates that 10 records are generated;
2. generate incremental integer data using the rownum virtual column;
3. Use the sysdate function to add some simple operations to generate date data. In this example, the time of each record is increased by 1 second;
4. Use the dbms_random.value function to generate random numeric data. In this example, a random integer between 0 and 100 is generated;
5. Use the dbms_random.string function to generate random string data. In this example, a random string with a length of 20 is generated. The string can contain characters or numbers.

Dbms_random. Parameter introduction in the function

--*************************************** * Random numbers ************************************* * ************* select decode (trunc (dbms_random.value (0, 2), '0', 'female ', '1', 'mal') from dualselect length (dbms_random.random) from dual; -- generate a random length of 9, 10, 11-digit select abs (mod (dbms_random.random, 100) from dual; -- generate a random number less than 100 select trunc (10 + 90 * dbms_random.value) from dual; -- generate 10 ~ Any two-digit random integer between 100 (including 10, excluding 100) select dbms_random.value from dual; -- generates a random number decimal number greater than 0 and less than 1 (37 or 38 digits after the decimal point) select dbms_random.value () from dual; -- generate a random number decimal point greater than 10 and less than 20 (38 digits after the decimal point) select dbms_random.normal from dual; -- the normal function returns the number of groups that follow the normal distribution. The standard deviation of this normal distribution is 1, and the expected value is 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; -- select (sysdate-1/24/60*30) + dbms_random.value (1, 1800)/3600/24 from dual in the past 10 days; -- select to_date (to_char (to_date ('2017-01-01 ', 'yyyy-MM-dd'), 'J') in the past 30 minutes ') + trunc (dbms_randm.value (0,365), 'J') from dual; -- Query any date in January 1, 20150501 -- select to_date (trunc (dbms_random.value (to_number (to_char (to_date ('123', 'yyyymmdd') within the specified date range '), 'J'), to_number (to_char (to_date ('000000', 'yyyymmdd') + 1, 'J'), 'J') + dbms_random.value (1, 3600)/3600 prize_time from dual; -- select to_date (trunc (dbms_random.value (to_number (to_char (add_months (sysdate, -100*12), 'J'), to_number (to_char (sysdate + 1, 'J'), 'J') + dbms_random.value (1, 3600) /3600 from dual; --*************************************** * random characters (generate any string with a length of 20) **************************************** * ********* select dbms_random.string ('U ', 20) from dual -- 'U', 'U' returns all uppercase strings (uppercase). It is found that as long as the parameter is not a Chinese character or fullwidth character, the returned data is capital select dbms_random.string ('l', 20) from dual -- 'l', 'l'-return all lowercase strings (lowercase) select dbms_random.string ('A ', 20) from dual -- 'A', 'a'-returns a case-sensitive string (mixed case) select dbms_random.string ('x', 20) from dual -- 'x ', 'X'-returns a string (uppercase, alpha & numeric) select dbms_random.string ('P', 20) from dual -- 'P ', 'P'-return the random combination of characters on the keyboard (any printable char)