Go 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.

[SQL]View Plaincopyprint?

1. Select RowNum as ID,
2. To_char (sysdate + rownum/24/3600, ' yyyy-mm-dd hh24:mi:ss ') as Inc_datetime,
3. Trunc (dbms_random.value (0)) as random_id,
4. Dbms_random.string (' $ ', random_string)
5. From dual
6. 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

[SQL]View Plaincopyprint?

2. --**************************************** Random digital ***************************************************
4. Select Decode (trunc (dbms_random.value (0, 2)),' 0 ',' female ',' 1 ',' Male ') from dual
6. Select Length (dbms_random.random) from dual; --produce a number with any length of 9,10,11 bit
8. Select ABS (mod (dbms_random.random,100)) from dual; --Generate a random number within 100
10. Select Trunc (10+90*dbms_random.value) from dual; --Generates any 2-bit random integer between 10~100 (contains 10, not including)
12. Select Dbms_random.value from dual; --Produces a decimal number (37 or 38 digits after the decimal point) that is greater than 0 and less than 1
14. 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
16. 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.
22. --**************************************** Random date ***************************************************
24. Select To_date (Trunc (Dbms_random.value (To_char (sysdate-10, ' J ')), To_number (To_char (Sysdate, ' J ')  )))), ' J ') from dual; --Take a random date in the last 10 days
26. Select (Sysdate-1/24/60 *) + dbms_random.value (1, 1800)/3600/24 from dual; --Take random time in the last 30 minutes
28. 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 years
30. --take random time within the specified date range
32. Select To_date (Trunc (Dbms_random.value (To_number (To_char (' To_date', ' 20150501 '),' J ')),
34. To_number (To_char (to_date (' 20150531 ', ' YYYYMMDD ') + 1, ' J ' )))
36. ), ' J ') + dbms_random.value (1, 3600)/3600 prize_time
38. from dual;
42. At any moment between now and the past 100 years
44. Select To_date (Trunc (Dbms_random.value (
46. To_number (To_char (add_months (sysdate,-100 *),' J ')),
48. To_number (To_char (sysdate + 1, ' J ')))
50. ), ' J ') + dbms_random.value (1, 3600)/3600 from dual;
56. --**************************************** Random character (produces a string of any length of 20) ************************************************* **
58. Select Dbms_random.string (' u ', +) from dual --' u ', ' u ' Returns all uppercase strings (uppercase) to be calculated, as long as the parameter is not a Chinese or full-width character, The return data is all uppercase
60. Select Dbms_random.string (' l ', ') from dual --' l ', ' l '-returns all lowercase strings (lowercase)
62. Select Dbms_random.string (' A ', ' a ') from dual--' a ', ' a '-returns the case of a string with a combination of uppercase and lowercase (mixed)
64. Select Dbms_random.string (' x ', ') from dual --' x ', ' X '-Returns all uppercase and numeric strings (Uppercase,alpha&numeric)
66. Select Dbms_random.string (' P ', ') from dual --' P ', ' p '-Returns a random combination of characters appearing on the keyboard (any printable char)
68. To generate 100,000 test records, you can use the following sql:create table mytesttable as
    Select RowNum as ID,
    To_char (sysdate + rownum/24/3600, ' yyyy-mm-dd hh24:mi:ss ') as Inc_datetime,
    Trunc (dbms_random.value (0)) as random_id,
    Dbms_random.string (' x ', ') random_string
    From dual
    Connect by level <= 100000;

70. Dbms_random. String usage:

    1) option ' u ', ' U '-returning string in uppercase alpha characters
    2) option ' l ', ' l '-returning string in lowercase alpha characters
    3) option ' A ', ' a '-returning string in mixed case alpha characters
    4) option ' x ', ' X '-returning string in uppercase Alpha-numeric characters
    5) option ' P ', ' p '-returning string in any printable characters.
    6) Option Otherwise The returning string is in uppercase alpha characters.
    
    

    Example:

    Generate 8-bit passwords consisting of uppercase letters and numbers

    [Email Protected]>select dbms_random.string (' x ', 8) "X_8_password" from dual;
    

    X_8_password

    ----------------------------------------------

    Tt3isguv

Go Oracle dbms_random function Usage quickly generates multiple test data